pg_show_plans: Watching execution plans in PostgreSQL LIVE
After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of […]
CLUSTER: Improving PostgreSQL performance
When designing a database application, the layout of data in disk is often neglected. However, the way data is stored by PostgreSQL can have a major performance impact. Therefore it makes sense to take a look at what can be done to improve speed and throughput. In this post you will learn one of the […]
Reduce WAL by increasing checkpoint distance
If you are running PostgreSQL in production you might have already noticed that adjusting checkpoints can be a very beneficial thing to tune the server and to improve database performance overall. However, there is more to this: Increasing checkpoint distances can also help to actually reduce the amount of WAL that is created in the […]
shared_buffers: Looking into the PostgreSQL I/O cache
The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of […]
Migrating from MS SQL to PostgreSQL: Uppercase vs. Lowercase
When migrating from MS SQL to PostgreSQL, one of the first things people notice is that in MS SQL, object names such as tables and columns all appear in uppercase. While that is possible on the PostgreSQL side as well it is not really that common. The question therefore is: How can we rename all […]
pg_timetable: Advanced PostgreSQL scheduling
pg_timetable is a brand-new job scheduler for PostgreSQL implemented completely from scratch (in Go). Traditional job scheduling is quite limited and does not cover all we needed in real life. Therefore, we thought about job execution in more detail and came up with a new concept that has not been seen in the PostgreSQL world […]
PostgreSQL: You might need to increase max_locks_per_transaction
“out of shared memory”: Some of you might have seen that error message in PostgreSQL already. But what does it really mean, and how can you prevent it? The problem is actually not as obscure as it might seem at first glance. max_locks_per_transaction is the critical configuration parameter you need to use to avoid trouble. […]
Setting PostgreSQL configuration parameters
A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration […]
Abusing PostgreSQL as an SQL beautifier
SQL is query a language that is usually pretty easy to read. However, if people don’t format their queries properly even SQL turns out to be a nightmare. That’s why developers often turn to an SQL beautifier to turn an ugly query into a nicely formatted string. Various tools are available on the web to […]
effective_cache_size: What it means in PostgreSQL
Many PostgreSQL database users might have stumbled over the effective_cache_size parameter in postgresql.conf. But how can it be used to effectively tune the database and how can we speed up PostgreSQL using effective_cache_size? This blog will hopefully answer some of my readers’ questions and reveal the hidden power of this secretive setting. RAM, Linux, PostgreSQL […]
Prewarming PostgreSQL I/O caches
PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce disk I/O and to speed up the database in the most efficient way possible. During normal operations your database cache will be pretty useful and ensure good response times. However, what happens if your database instance is restarted – for whatever reason? […]
What is autovacuum doing to my temporary tables?
Did you know that your temporary tables are not cleaned up by autovacuum? If you did not, consider reading this blog post about PostgreSQL and autovacuum. If you did – well, you can still continue to read this article. Autovacuum cleans tables automatically Since the days of PostgreSQL 8.0, the database has provided this miraculous […]