Month: March 2020
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/ execution 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 […]
Embedded SQL in C for PostgreSQL with ecpg
Embedded SQL is by no means a new feature — in fact it is so old-fashioned that many people may not know about it at all. Still, it has lots of advantages for client code written in C. So I’d like to give a brief introduction and talk about its benefits and problems. What […]
pg_timetable: Advanced PostgreSQL cron-like scheduler released!
pg_timetable: Advanced PostgreSQL cron-like scheduler released! Hello there, this is your developer speaking… I’m struggling with how to start this post, since I’ve procrastinated a lot. There should be at least 5 published posts on this topic already. But there is only one by Hans-Jürgen Schönig. I’m thanking him for trying to cover my back, […]
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 […]
Subtransactions and performance in PostgreSQL
(Last updated on 2023-01-24) Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction that spans multiple statements […]
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 […]