Neues vom PostgreSQL Planet

Before, during, and after the GDPR came into town in 2018, there have been many ideas to solve the problem of deleting or hiding user data, using various layers of the software stack but also using various approaches (hard deletion, soft deletion, anonymization). Anonymization has been one of them which is known to be popular among the PostgreSQL-based organizations/companies.

Active Record provides a great balance between the ability to perform simple queries simply, and also the ability to access the raw SQL sometimes required to get our jobs done. In this article, we will see a number of real-life examples of business needs that may arise at our jobs. They will come in the form of a request for data from someone else at the company, where we will first translate the request into SQL, and then into the Rails code necessary to find those records. We will be covering…

PostgreSQL has quite a few compelling advantages, some of which are not found in any other database management system. This article will outline the ones that are most interesting to the Oracle developer seeking to do a migration or investigate alternatives for new development. To prevent this article from becoming another list of PostgreSQL features, it […]

We’ve had a small flurry of customers asking about tuning their OS for the best PostgreSQL performance. While the answer to this question is always ‘that depends on your hardware and workload’ and involves a lot of iteration between changing a setting and benchmarking, I thought I’d take a moment to point out that once you do manage to dial-in the settings, you should be writing a profile and deploying to your systems for tuned to make use of. Please, for the love of $diety, stop editing sysctl.conf and friends!

With the advent of column store databases, there was an urge to make use of SIMD vector processing. It naturally fits into the way table data is arranged. Let's first briefly check what is SIMD. It stands for Single Instruction Multiple Data. Today, CPU instructions support this kind of mechanism where the same instruction can be executed simultaneously on multiple data elements. E.g. Say, you want to double all the column values. Or remove the red component of the RGB values of pixels of an image. For large data, these operations are CPU bottlenecks.

After my last post about ltree and recursive data in PostgreSQL people have asked me privately about performance issues. To share this information, I decided to come up with a follow up post to discuss this topic in a bit more detail. WITH RECURSIVE in PostgreSQL is efficient. However, ltree does have its strengths as well. Let us take a closer look …

Preparing sample data

In preparation for a test I have created a table which contains a balanced tree:

How can you force a table to have at most one row? It is actually very easy by creating a unique expression index on a constant, with no column name references:

PostgreSQL Person of the Week Interview with Simon Riggs: I live in the UK with my wife, and spend a lot of time with my extended family, though “kids” have now grown up and left home. Born a Londoner, so my favourite drink is Tea, but I’m partly Irish with a small hint of Chilean blood.

Finding relevant information quickly speeds up performance. For example, while reading a book in which you have to find a topic that you would like to read, if you know that it is in a certain chapter then you will simply go to that chapter, perhaps look through it and start reading the desired topic.

Last week, we examined Postgres XID wraparound complications in greater depth to see exactly how easily they can surprise even prepared enterprises. But we also found the real problem areas and how to mitigate them specifically. In this continuing series to plumb the depths of Postgres constructively, we’re going to focus on some of the […]

There are some very good responses in an email thread about whether to store binary data in Postgres or externally. The binary storage options discussed were:

1. Introduction

PostgreSQL backend is a collection of processes forked from the main process called Postmaster. Each forked process has different roles and responsibilities in the backend. This article describes the responsibility of core backend processes that power the PostgreSQL system as we know it today. The overall PostgreSQL backend architecture can be illustrated by the image below:

“If we had this data what would it mean?” – these kinds of questions can be answered using plain SQL. The technique you will need in PostgreSQL is a “hypothetical aggregate” which is of course part of the ANSI SQL standard. This post will show what an hypothetical aggregate is good for and how it works.

What happens if you try to drop a table that is referenced by another table?

ORA-2449 and the Constraint Dependencies

Oracle clients seems somehow a little goofy when you have to deal with dependencies.
Imagine you have two tables, a that references table b; you can generate the tables as follows:

My employer, EnterpriseDB, has been organizing events where potential customers and interested people can ask me questions while enjoying a meal. I thought the idea was strange, but I have done it ten times, and they have gone very well. The Q&A portion usually lasts one hour and forty-five minutes.

This article provides the Oracle database administrator with equivalent PostgreSQL architecture knowledge. The process is a bit loose, but it is sufficient to bootstrap the concepts that are transferable and identify the ones that are not.

A reader provided us a feedback about a wrong listing.

PostgreSQL 11 Server Side Programming Errata Corrige

I have already written about how my first book on PostgreSQL, named PostgreSQL 11 Server Side Programming Quick Start Guide, gained more attention.

Overview This article is an extended version atop of the previous article which was a kickstart for building an application using Spring Boot and PostgreSQL. There is no internal feature supported by Java which offers mapping between class objects and database tables; therefore, we use Object Relational Model (ORM). JPA is an abstraction above JDBC […]

Since SQL/MED (Management External Data) was implemented in PostgreSQL, hundreds of projects have emerged that try to connect PostgreSQL with other data sources. Just by doing a simple search on GitHub with the keys “postgres” + “fdw” you can figure that out.