Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

Why does my first tablespace in PostgreSQL always get the OID 16384?

By | Database Administration & Monitoring | No Comments

For those already familiar with this topic and wonder about the heading, hold on and continue reading. One of the exercises we do in our PostgreSQL DBA Essentials workshop is to create a tablespace. This is to demonstrate how tablespaces work in PostgreSQL and, especially to show the symbolic link that gets created in $PGDATA/pg_tblspc. In one of the last workshops, two attendees noticed that they got the same OID and asked if that is…

Read More
Daniel Westermann

How to efficiently add constraints to existing tables in PostgreSQL

By | Database Administration & Monitoring | No Comments

Is is not a surprise that applications change over time. New tables get added, columns need to be changed or new ones are required, and sometimes you need to add check constraints or foreign keys to already existing tables which contain data. The issue with adding constraints to tables that already contain data is, that PostgreSQL needs to scan the whole table, to validate that existing data does not violate the constraints you are adding….

Read More
Daniel Westermann

PostgreSQL indexes and operators

By | Database Administration & Monitoring | No Comments

Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators or operator classes. In those cases the standard operator class for the data type is applied and all is fine. But sometimes we need to know about the possibilities PostgreSQL comes with, and…

Read More
Daniel Westermann

Some basics about time zones in PostgreSQL

By | Database Administration & Monitoring | No Comments

As soon as you have an application which works across time zones you will have to deal with that properly in the database. I’ve seen many applications that didn’t care of that at the beginning, and much work had to be done to change the implementation afterwards. The one and only recommendation is: Do it properly from the beginning and you don’t have to think much about it in the future. PostgreSQL comes with all…

Read More
Daniel Westermann

In which order do triggers fire in PostgreSQL?

By | Database Administration & Monitoring | No Comments

When you are working with triggers it might be important to know in which order they fire. Of course, a “before” triggers fires before an “after” trigger. But what happens if you have two or more triggers for the same event? Maybe you want to make sure that a specific trigger is executed first (for whatever reason) or you want to make sure that another trigger is executed last. The good news is: You can…

Read More
Daniel Westermann

How much shared memory is PostgreSQL using? (2) – details

By | Database Administration & Monitoring | No Comments

In the last post in this two post series we’ve briefly looked at configuring huge pages on Linux for PostgreSQL. It became clear, that calculating the number of huge pages purely based on the setting of shared_buffers will not work. PostgreSQL requires more shared memory, but what exactly is it? Well, this is the goal of this post and I’ll introduce a new feature of PostgreSQL 15 which helps in identifying those bits without digging…

Read More
Daniel Westermann

How much shared memory is PostgreSQL using? (1) – basics

By | Database Administration & Monitoring | No Comments

Have you ever wondered if the value of shared_buffers is the only setting which controls how much shared memory PostgreSQL is actually using? There was a nice addition committed to PostgreSQL 15 recently, which helps in answering this question (more on that later). shared_buffers for sure is the biggest chunk of shared memory that PostgreSQL is using but there might be other factors which come into the game. You might ask yourself why that is…

Read More
Daniel Westermann

Temporal tables for PostgreSQL 15?

By | Database Administration & Monitoring | 3 Comments

One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don’t know what it is, here is a short overview. Basically you can ask for a row as it was at a specific point in time. The patch which implements this, is currently in status “Ready for Committer”, which does not…

Read More