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.
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!
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:
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.
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:
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.
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.
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.