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 […]

Read more

The Golden Trunk 2023 Award: My Playful Ode to Bloggers

Because Who Needs Another Gold Statue Anyway? In the world of databases, where seriousness often reigns supreme… Well, you know me! I always want to inject a bit of humor and fun. Welcome to the inaugural “Golden Trunk 2023” award – my impulsive celebration of PostgreSQL community brilliance, where a golden elephant trunk steals the […]

Read more

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 […]

Read more

Reflections on PGConf.eu 2023

Anticipation Coming to Prague was something that had been in my planning for quite a while. And what should have been a smooth preparation, turned into a fascinating journey. As I dare consider myself a part of this fantastic PostgreSQL community, skipping PGConf.EU 2023 would be an impossible thought. Thanks to CYBERTEC and the personal […]

Read more

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 […]

Read more

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? […]

Read more

kill -9 explained for PostgreSQL

Do you want to kill a database connection? Or maybe you want to use kill -9? On your PostgreSQL database server? Well, there are things you should be aware of before you do that. The question we want to answer in this blog therefore is: How do I kill PostgreSQL processes properly? Killing PostgreSQL processes […]

Read more

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 […]

Read more

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 […]

Read more

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 […]

Read more

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), […]

Read more

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. […]

Read more