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

© Laurenz Albe 2023 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 […]

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?

© Laurenz Albe 2023 “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? […]

Read more

Bulk load performance in PostgreSQL

© Laurenz Albe 2023 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 […]

Read more

Subqueries and performance in PostgreSQL

© Laurenz Albe 2023 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 […]

Read more

View Access Logging – Fix it in PostgreSQL

By Kirk Wolak Continuous Improvement is an important part of reducing technical debt. Over 30 years our active database has collected some technical debt. We wanted to rename all of our views to be consistently named. Besides that, we had a lack of code reviews for what is happening in the database. The latter situation […]

Read more

Memory context: private memory management in PostgreSQL

© Laurenz Albe 2023 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 […]

Read more

Use HOT, so CLUSTER won’t rot in PostgreSQL

© Laurenz Albe 2023 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 […]

Read more

What is a schema in PostgreSQL?

One way to organize data in PostgreSQL is to make use of schemas. What is a schema in PostgreSQL? And more importantly: What is the purpose of a schema and how can schemas be used to make your life easier? Let’s dive in and find out. The purpose of a schema Before you figure out […]

Read more