Tag: query
Reproducible builds: a PostgreSQL query optimization example
This post shows how to optimize a slow query that came out of the Reproducible Builds project. The Reproducible Builds initiative aims to make software compilation entirely deterministic, with no variation in the output when the build is run again. This makes software supply chain attacks much harder, but has also advantages for software quality […]
Subqueries and performance in PostgreSQL
SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be hard to read. […]
How to cancel a hanging PostgreSQL query
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 the faint of […]
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 […]
How the PostgreSQL query optimizer works
Just like any advanced relational database, PostgreSQL uses a cost-based query optimizer that tries to turn your SQL queries into something efficient that executes in as little time as possible. For many people, the workings of the optimizer itself remain a mystery, so we have decided to give users some insight into what is really […]
Tips and tricks to kick-start the Postgres year 2021
I decided to start out this year by looking into my notes from last year and extracting from them a small set of Postgres tips for you. This might not be, strictly speaking, fresh news… but I guess the average RAM of tech workers is about 64KB or so nowadays, so some repetition might not […]
Query performance in PostgreSQL 13 RC1
By Kaarel Moppel – If you read this blog post the new PostgreSQL version will be probably already officially released to the public for wider usage…but seems some eager DBA already installed the last week’s Release Candidate 1 and took it for a spin 😉 The “spin” though takes 3 days to run for my […]
Join strategies and performance in PostgreSQL
(Updated 2023-02-24) There are three join strategies in PostgreSQL that work quite differently. If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. This article explains the join strategies, how you can support them with indexes, what can go wrong with them and how you can tune your joins for better performance. […]
Tracking view dependencies in PostgreSQL
We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it: Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is. In this article I want to explore the mechanics […]
Implementing “AS OF”-queries in PostgreSQL
Over the years many people have asked for “timetravel” or “AS OF”-queries in PostgreSQL. Oracle has provided this kind of functionality for quite some time already. However, in the PostgreSQL world “AS OF timestamp” is not directly available. The question now is: How can we implement this vital functionality in user land and mimic Oracle […]
Query performance of the upcoming version 11 of PostgreSQL
By Kaarel Moppel – Last week the first release candidate of the upcoming Postgres version 11 was released, stating that everything is going more or less as planned – great news! The 11 release is promising a lot of very cool stuff as usual, including – better partitioning, more parallezation and JIT capabilities (which are […]
3 ways to detect slow queries in PostgreSQL
(Last updated 18.01.2023) When digging into PostgreSQL performance, it is always good to know which options one has to spot performance problems, and to figure out what is really happening on a server. Finding slow queries in PostgreSQL and pinpointing performance weak spots is therefore exactly what this post is all about. There are many […]