Category: Performance
Boost your database performance with the latest expert knowledge from CYBERTEC.
Our blog post topics are drawn from the real-life experiences of CYBERTEC’s top technicians, which means you get the maximum benefit from their experience. These tips come from tests done at top-tier companies – they are robust and reliable. Whether you are interested in improving query performance or speeding up your database, there is a post with step-by-step instructions waiting for you in the CYBERTEC blog.
Some of our top blogs include: Detecting Slow Queries Quickly, Find and Fix a Missing PostgreSQL Index, and Subqueries and Performance.
You may want to get expert advice in person – feel free to contact us.
Faceting large result sets in PostgreSQL
While the term faceting may sound foreign to you, you almost certainly have run into it in your adventures online. It is those helpful little boxes that turn up when browsing in web shops or searching in catalogs, telling how to further narrow down the search results, (for example, by color) and how many items […]
EXPLAIN that parameterized statement in PostgreSQL!
© Laurenz Albe 2022 For detailed query performance analysis, you need EXPLAIN (ANALYZE, BUFFERS) output for an SQL statement. With a parameterized statement, it can be difficult to construct a run-able statement for EXPLAIN (ANALYZE). Sometimes, you don’t even know the parameter values. I’ll show you how you can get at least plain EXPLAIN output […]
Rewrite OR to UNION in PostgreSQL queries
Rewriting OR is not always the best solution © Laurenz Albe 2022 In my article that reviles OR, I showed how in certain cases, it is possible to rewrite OR in a WHERE condition to a longer query with UNION that can perform much better (the “ugly” OR). Now people have asked me repeatedly why […]
btree vs. BRIN: 2 options for indexing in PostgreSQL data warehouses
Indexing is the key to good performance. However, people often ask: Is there an alternative to btree indexing? Can we make indexes in PostgreSQL smaller? Can we create indexes in PostgreSQL faster? And how can we index in a data warehouse? This blog will answer all those questions and show which options you have to […]
Why are my PostgreSQL updates getting slower?
© 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 […]
gexec in psql: PostgreSQL poweruser practice
For PostgreSQL powerusers, automating repeated steps is becoming more and more necessary, and gexec can help. This blog will show you how to use the || operator and the \gexec command to avoid unnecessary repetition in your workflow. The CLI client that ships with PostgreSQL is called psql. Like many CLI clients, it is often […]
Column order in PostgreSQL does matter
I’ve recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don’t […]
Case-insensitive pattern matching in PostgreSQL
© Renée Albe 2022 Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer […]
How to cancel a hanging PostgreSQL query
© Laurenz Albe 2022 Sometimes a PostgreSQL query takes forever. Usually, it is easy to interrupt (cancel) such a statement, but sometimes it can turn into a problem, and a query cannot be canceled. This article discusses what might be the cause. I’ll also show you a trick how to solve the problem (not for […]
Find and fix a missing PostgreSQL Index
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 […]
Query parameter data types and performance
© Laurenz Albe 2022 Recently, I could help solve a “mysterious” performance problem for a customer. The problem turned out to be a badly chosen query parameter type, which led to poor query performance. After working on this customer’s case, I realized that the meaning of the data type of a query parameter is not […]
Pipeline mode for better PostgreSQL performance on slow networks
© Laurenz Albe 2022 It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting […]