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

Read more

Unexpected downsides of UUID keys in PostgreSQL

There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able to generate keys independently of the database To move sets of related records between different databases without having to deal with renumbering everything However, like everything good in life, UUID’s come with their own downsides. […]

Read more

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

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

Huge Pages and PostgreSQL

When talking to customers, sometimes I get the question: How should PostgreSQL installations deal with huge pages and large memory allocations? In particular, experienced Oracle DBA’s are interested in the details behind PostgreSQL and Huge Pages on Linux, so I’ll try to explain it in a bit more detail in the following blog post. What […]

Read more

Forcing a join order in PostgreSQL

  Different from many other database systems, PostgreSQL does not support query hints. That makes it difficult to force the hand of the query planner when it comes to a certain join order that you know to be good. This article explains how you can influence execution plans in PostgreSQL. Why no query hints? The […]

Read more

EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16

  A while ago, I wrote about how difficult it is to get an execution plan for a parameterized query. The method suggested in that article works, but is still somewhat complicated. So I wrote a patch to support an EXPLAIN option GENERIC_PLAN, which provides native support for that. My patch got committed by Tom […]

Read more

PostgreSQL: DELETE vs. TRUNCATE

Data isn’t only about storage and accumulation – sometimes it’s also about deletion, cleanup and archiving. In SQL there’s more than one way to empty a table. Two essential methods are available: DELETE TRUNCATE DELETE vs. TRUNCATE Both commands serve totally different purposes, which are sometimes not fully understood. The key difference is that DELETE […]

Read more

Stored procedures in PostgreSQL: getting started

Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike. Stored procedures basics In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command […]

Read more

PostgreSQL: Finding the current timestamp

PostgreSQL provides all kinds of time-related functions. But time is not as easy as it seems. One question many people keep asking: How can I find the current timestamp? What time is it? While this seems like an easy question the answer isn’t as clear-cut as you might expect. Basically, there are three functions we […]

Read more

JSON logs in PostgreSQL 15

As of version 15, PostgreSQL offers you the ability to write the server log in JSON format. This feature has been desired for a long time and has finally made it to PostgreSQL core. In this post we will discuss how JSON logs can be configured and what this brand new feature does for users. […]

Read more