© Laurenz Albe 2022 Recently, a customer asked me why a bulk load into a PostgreSQL table kept slowing down as time went by. They saw that it was a trigger on the table that took longer and longer, and in that trigger, the updates were getting slower all the time. Now slow updates are […]

Spot a missing PostgreSQL index Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible. 🙂  However, if you want to ensure that your database performs well and if you are generally not in favor of user complaints – better watch out […]

When I recently stumbled on an article comparing some main properties, and also the read-write performance of two very popular pieces of caching software, often used to speed up applications heavy on database queries, I immediately felt an itch – how would it actually look for PostgreSQL vs Redis vs Memcached on the performance side? […]

UPDATED March 2023: In this post, we’ll focus our attention on PostgreSQL performance and detecting slow queries. Performance tuning does not only mean adjusting postgresql.conf properly, or making sure that your kernel parameters are properly tuned. Performance tuning also implies that you need to first find performance bottlenecks, isolate slow queries and understand what the […]

A few years ago, I wrote a short post on a similar topic; since then, I’ve often seen that the whole concept of suggesting to do more INSERT-s in critical parts of the code seems pretty strange to most customers. It’s even alien to those who are not new to databases in general. So I […]

LIKE and ILIKE are two fundamental SQL features. People use those things all over the place in their application and therefore it makes sense to approach the topic from a performance point of view. What can PostgreSQL do to speed up those operations and what can be done in general to first understand the problem […]

This blog is about table functions and performance. PostgreSQL is a really powerful database and offers many features to make SQL even more powerful. One of these impressive things is the concept of a composite data type. In PostgreSQL a column can be a fairly complex thing. This is especially important if you want to […]

© Laurenz Albe 2020 (Updated 2023-02-22) In my daily work, I see many databases with a bad setting for max_connections. There is little advice out there for setting this parameter correctly, even though it is vital for the health of a database. So I decided to write up something. What is max_connections? According to the […]

By Kaarel Moppel – In my last post I described what to expect from simple PL/pgSQL triggers in performance degradation sense, when doing some inspection/changing on the incoming row data. The conclusion for the most common “audit fields” type of use case was that we should not worry about it too much and just create […]

First, the big question – should we be using good old triggers at all? Well, actually I’m not going to recommend anything here, as it’s an opinionated topic:) People well-versed in databases would probably see good use cases for them, whereas modern application developers would mostly say it’s an awful practice – doing some “magic […]

By Kaarel Moppel – Improve transaction latency and consequently performance – The topic of transaction performance is as relevant as ever, in spite of constant hardware improvements, we’re quite often asked how to improve in that area. But the truth is that when the most important PostgreSQL configuration parameters are already more or less tuned, […]

Pgbench is a very well-known and handy built-in tool that Postgres DBAs can use for quick performance benchmarking. Its main functionality/flow is super simple, but it also has some great optional features, like running custom scripts and specifying different probabilities for them. One can also use bash commands to fill query variables for example. But […]