Neues vom PostgreSQL Planet
It has been a while since I have written about the new features in a major pgpool II release. Well pgpool II 4.2 is in the works and the plan is to release it towards the end of this year. As usual every major release of pgpool II is compatible with the parser of latest PG release so pgpool II 4.2 will be compatible with PG-13 parser. There are many nice features that will be part of next major release however the ones that particularly catches my attention is supporting LDAP authentication with pgpool II.
© Laurenz Albe 2020
There are three join strategies in PostgreSQL that work quite differently. If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. This article explains the join strategies, how you can support them with indexes, what can go wrong with them and how you can tune your joins for better performance.
An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called "ora2pg".
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.