PostgreSQL parallel query problems in JDBC and DBeaver
I recently investigated a surprising behavior of the DBeaver database client in connection with PostgreSQL parallel query, which I’d like to share with you. This might be interesting for everybody who accesses PostgreSQL using the JDBC driver. Some basics about PostgreSQL parallel query Parallel query concepts Parallel query was introduced in PostgreSQL 9.6 and […]
Broken foreign keys: how can that happen in PostgreSQL?
One of the fundamental requirements for a relational database is that the transaction system always maintains consistency. That means that database constraints must always be satisfied, even in the face of concurrent data modifications. PostgreSQL certainly strives to live up to this requirement. Still, you can end up with broken foreign key constraints if […]
PostgreSQL hash index performance
Among the many index types in PostgreSQL, the hash index is the most widely ignored. This came home to me when somebody asked me a question about hash index performance recently. High time to explore that little-known corner of PostgreSQL and run some benchmarks! The history of the hash index PostgreSQL has had hash […]
What you should know about PostgreSQL minor upgrades
© Laurenz Albe 2023 The PostgreSQL documentation is rather terse on the subject of minor upgrade. The reason is probably that the procedure is so simple. Still, I find that many people don’t understand this topic well, so I thought I should compile the relevant information in an article. What is a PostgreSQL minor upgrade? […]
Conditional foreign keys and polymorphism in SQL: 4 Methods
This article is about the notoriously difficult problem of “conditional foreign keys”. In object-oriented programming languages, polymorphism is a natural concept. However, it does not translate well to SQL. As a consequence, many people whose data models are driven by an object-oriented application design end up trying to implement conditional foreign keys. In this […]
Postgres v16: 14 Cool New Features
PostgreSQL v16 contains many new features and enhancements. Here are PG v16’s 14 best new features: Everybody's favorite: You no longer need an alias for subqueries in FROM This in an extension to the standard, but makes porting queries from Oracle easier. Improve performance of vacuum freezing Have you ever suffered from massive anti-wraparound autovacuum […]
Indexing “LIKE” in PostgreSQL and Oracle
© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in PostgreSQL is not straightforward. This keeps surprising Oracle users, who claim that a regular b-tree index will of course always support LIKE. I decided to explore the differences between Oracle and PostgreSQL when it comes […]
Why does my pg_wal keep growing?
“Why does my pg_wal keep growing?” That’s a question I keep hearing again and again. It is an urgent question, since the PostgreSQL server will crash if pg_wal runs out of disk space. I decided to answer the question once and for all. What is pg_wal and why is it growing? You can skip […]
Bulk load performance in PostgreSQL
There are several techniques to bulk load data into PostgreSQL. I decided to compare their performance in a simple test case. I’ll add some recommendations for parameter settings to improve the performance even more. An example table to bulk load data The table is simple enough: It is a narrow table (only two columns), […]
Subqueries and performance in PostgreSQL
SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be hard to read. […]
Memory context: private memory management in PostgreSQL
PostgreSQL uses shared memory for data shared between processes. With the exception of the dynamic shared memory segments used for exchanging data between parallel workers, the server allocates shared memory with a fixed size when it starts. But each PostgreSQL backend process also has to manage private memory to process SQL statements. In this […]
Use HOT, so CLUSTER won’t rot in PostgreSQL
CLUSTER is sometimes the last resort to squeeze performance out of an index scan. Normally, you have to repeat CLUSTER regularly to maintain good performance. In this article, I will show you how you can get away without re-CLUSTERing even in the face of concurrent UPDATEs. Thanks to Steven Hulshof for the idea! Why […]