Tag: performance tuning
Performance tuning: MAX and GROUP BY
These days everybody is talking about time series, time series analysis and alike for performance tuning. Analyzing time series data in PostgreSQL can provide valuable insights, help in making informed decisions and understanding data more deeply. By utilizing PostgreSQL’s powerful features, we can efficiently query all types of measurement data to track trends, patterns, and […]
PostgreSQL parallel query problems in JDBC and DBeaver
I recently investigated a surprising behavior of the DBeaver database client in connection with PostgreSQL parallel query, which I’d like to share with you. This might be interesting for everybody who accesses PostgreSQL using the JDBC driver. Some basics about PostgreSQL parallel query Parallel query concepts Parallel query was introduced in PostgreSQL 9.6 and […]
Optimizing PostgreSQL functions with preloaded libraries
In PostgreSQL, functions and procedures can be written in many different programming languages including but not limited to PL/pgSQL, PL/Perl, PL/Python and a lot more. This flexibility gives the end user the choice to write code in languages of their choice. However, this flexibility has some implications which are often forgotten. Loading libraries early is one […]
PostgreSQL hash index performance
Among the many index types in PostgreSQL, the hash index is the most widely ignored. This came home to me when somebody asked me a question about hash index performance recently. High time to explore that little-known corner of PostgreSQL and run some benchmarks! The history of the hash index PostgreSQL has had hash […]
Why are my PostgreSQL updates getting slower?
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 a frequent problem, […]
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 […]
PostgreSQL vs Redis vs Memcached performance
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? […]
PostgreSQL: Detecting slow queries quickly
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 […]
Insert-only data modelling to smooth peaks on slow disks
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 […]
PostgreSQL: More performance for LIKE and ILIKE statements
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 […]
Composite type performance issues in PostgreSQL
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 […]
Tuning max_connections in PostgreSQL
(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 documentation, max_connections determines […]