Recent content on Tomas Vondra
A couple months back I posted about maybe adjusting random_page_cost to better reflect how current storage handles random and sequential access. I had a bunch of great discussions about the topic since then, but ultimately I got distracted by other stuff. POSETTE happened last week, with my pre-recorded talk about this very topic (and many other great talks, BTW). Which reminded me that I started…
At a couple recent conferences, I got to describe the process Postgres uses to select new committers/maintainers. Usually to users and developers using Postgres, but in some cases it was unclear even to experienced Postgres contributors. The official docs are rather brief, and don’t explain various important details. Let me explain how I understand the informal process, who’s responsible for what…
The random_page_cost was introduced ~25 years ago, and since the very beginning it’s set to 4.0 by default. The storage changed a lot since then, and so did the Postgres code. It’s likely the default does not quite match the reality. But what value should you use instead? Flash storage is much better at handling random I/O, so maybe you should reduce the default? Some places go as far…
If you attended FOSDEM 2026 , you probably noticed discussions on how AI impacts FOSS, mostly in detrimental ways. Two of the three keynotes in Janson mentioned this, and I assume other speakers mentioned the topic too. Moreover, it was a very popular topic in the “hallway track.” I myself chatted about it with multiple people, both from the Postgres community and outside of it. And…
I do a fair amount of benchmarks as part of development, both on my own patches and while reviewing patches by others. That often requires dealing with noise, particularly for small optimizations. Here’s an overview of ways I use to filter out random variations / noise. Most of the time it’s easy - the benefits are large and obvious. Great! But sometimes we need to care about cases…
A couple weeks ago I posted about how setting maintenance_work_mem too high may make things slower . Which can be surprising, as the intuition is that memory makes things faster. I got an e-mail about that post, asking if the conclusion would change on a busy system. That’s a really good question, so let’s look at it. To paraphrase the message I got, it went something like this: Lower…
I’ve been using Qubes OS as my primary OS since version 2, released in 2014. That means I’m using Qubes OS for about a decade. Despite having to deal with a couple issues over the years, I think it’s a great Linux system. I can’t quite imagine switching to a more traditional one. Yet I know very few other Qubes OS users in my developer community, which surprises me. Let me…
I work remotely from home, and over the years I’ve amassed a bunch of machines related to that (development, testing, benchmarking, …), and other devices you may usually find at home (printer, NAS, …). Occasionally I need remote access, and for a while SSH tunnels were good enough. I decided to simplify and clean this up and use a proper VPN wireguard. This blog post explains…
From time to time I get to investigate issues with some sort of a batch process. It’s getting more and more common that such processes use very high memory limits ( maintenance_work_mem and work_mem ). I suppose some DBAs follow the logic that “more is better”, not realizing it can hurt the performance quite a bit. Let me demonstrate this using an example I ran across while…
PostgreSQL 18 was stamped earlier this week, and as usual there’s a lot of improvements . One of the big architectural changes is asynchronous I/O (AIO), allowing asynchronous scheduling of I/O, giving the database more control and better utilizing the storage. I’m not going to explain how AIO works, or present detailed benchmark results. There have been multiple really good blog posts…
Row-level security (RLS) is a great feature. It allows restricting access to rows by applying filters defined by a policy. It’s a tool useful for cases when the data set can’t be split into separate databases. Sadly, using RLS may be quite cumbersome. RLS requires some sort of “trusted context” for the RLS policies. The policies need to filter using data the user…
When I started working with Postgres (or databases in general) 25 years ago, storage systems looked very different. All storage was “spinning rust” - rotational disks with various interfaces (SATA/SAS/…) and speeds (7.2K/10k/15k/…). The spindle speed was the main performance determining feature, and everyone knew what IOPS and bandwidth to expect from a disk. The general…
Last week I posted about how we often don’t pick the optimal plan . I got asked about difficulties when trying to reproduce my results, so I’ll address that first (I forgot to mention a couple details). I also got questions about how to best spot this issue, and ways to mitigate this. I’ll discuss that too, although I don’t have any great solutions, but I’ll briefly…
The basic promise of a query optimizer is that it picks the “optimal” query plan. But there’s a catch - the plan selection relies on cost estimates, calculated from selectivity estimates and cost of basic resources (I/O, CPU, …). So the question is, how often do we actually pick the “fastest” plan? And the truth is we actually make mistakes quite often.…
I do a fair number of benchmarks, not only to validate patches, but also to find interesting (suspicious) stuff to improve. It’s an important part of my development workflow. And it’s fun ;-) But we’re dealing with complex systems (hardware, OS, DB, application), and that brings challenges. Every now and then I run into something that I don’t quite understand. Consider a…
The pgconf.dev conference, a revamp of the original PGCon , happened about two weeks ago. It’s the main event for Postgres developers, and one of the things we’re trying is an Advanced Patch Feedback Session (APFS). We first tried that last year in Vancouver, and then again in Montreal. But I realized many people attending the conference either are not aware of the event at all, or are…
We’re now in the “feature freeze” phase of Postgres 18 development. That means no new features will get in - only bugfixes and cleanups of already committed changes. The goal is to test and stabilize the code before a release. PG 18 beta1 was released a couple days ago, so it’s a perfect time to do some testing and benchmarking. One of the fundamental changes in PG 18 is…
Last week I visited the Malmö PUG to talk about performance cliffs . It’s a really nice meetup - cozy environment, curious audience asking insightful questions. I highly recommend attending or even giving a talk there. After the meetup I realized it’s been a while since I posted about some patch idea , and the performance cliff talk has a couple good candidates. Some might be a bit too…
It’s been a couple weeks since P2D2 (Prague PostgreSQL Developer Day) 2025. We’ve been busy with the various tiny tasks that need to happen after the conference - processing feedback, paying invoices, and so on. But it’s also a good opportunity to look back - I realized this was the 15th year of the event I’ve helped to organize, so let me share some of that experience.
A couple days ago I wrote about performance improvements on OLTP workloads since Postgres 8.0, released 20 years ago. And I promised to share a similar analysis about analytical workloads in a follow-up post. So here we go ;-) Let me show you some numbers from a TPC-H benchmark, with some basic commentary and thoughts about the future.
The Postgres open source project is nearly 30 years old, I personally started using it about 20 years ago. And I’ve been contributing code for at least 10 years. But even with all that experience I find it really difficult to make judgments about how the performance changed over the years. Did it improve? And by how much? I decided to do some benchmarks to answer this question.
If you’ve done any Postgres development in C, you’re probably aware of the concept of memory contexts. The primary purpose of memory contexts is to absolve the developers of having to track every single piece of memory they allocated. But it’s about performance too, because memory contexts cache the memory to save on malloc / free calls. But malloc gets the memory from another…
There are multiple tools to run benchmarks on Postgres, but pgbench is probably the most widely used one. The workload is very simple and perhaps a bit synthetic, but almost everyone is familiar with it and it’s a very convenient way to do quick tests and assessments. It was improved in various ways (e.g. to do partitioning), but the initial data load is still serial - only a single process…
A couple days ago I had a bit of free time in the evening, and I was bored, so I decided to play with BOLT a little bit. No, not the dog from a Disney movie , the BOLT tool from LLVM project, aimed at optimizing binaries. It took me a while to get it working, but the results are unexpectedly good, in some cases up to 40%. So let me share my notes and benchmark results, and maybe there’s…
Time for yet another “first patch” idea post ;-) This time it’s about BRIN indexes. Postgres has a contrib module called amcheck , meant to check logical consistency of objects (tables and indexes). At the moment the module supports heap relations (i.e. tables) and B-Tree indexes (by far the most commonly used index type). There is a patch adding support for GiST and GIN indexes…
I’ve submitted a lot of talk proposals to a lot of Postgres conferences over the years. Some got accepted, many more were not. And I’ve been on the other side of this process too, as a member of the CfP committee responsible for selecting talks. So let me give you a couple suggestions on how to write a good talk proposal.
Let me present another “first patch” idea, related to a runtime stats on access to files storing data. Having this kind of information would be very valuable on instances with many files (which can happen for many reasons). This is a very different area than the patch idea , which was about an extension. The runtime stats are at the core of the system, and so is the interaction with…
I’ve decided to experiment a little bit and do regular “office hours.” I’ll be available to chat about almost anything related to Postgres. It might be a technical discussion about a patch you’re working on, or a topic about the community etc. This is not an entirely new thing. I’ve been telling people to just ping me if they want to discuss something off-list,…
In an earlier post I mentioned I plan to share a couple patch ideas, suitable for new contributors. This is the first one, about using COPY protocol for postgres_fdw batching. This would replace the current implementation, based on prepared statements. Let me share a couple thoughts on the motivation and how it might be implemented.
A couple weeks ago I needed to move my mailing list communication to a different mailbox. That sounds straightforward - go to the community account and resubscribe to all the lists with the new address, and then import a bit of history from the archives so that the client can show threads, search etc. The first part worked like a charm, but importing the archives turned out to be a bit tricky, and…
Picking the topic for your first patch in any project is hard, and Postgres is no exception. Limited developer experience with important parts of the code make it difficult to judge feasibility/complexity of a feature idea. And if you’re not an experienced user, it may not be very obvious if a feature is beneficial. Let me share a couple simple suggestions on how to find a good topic for the…
Postgres is pretty old. The open source project started in 1996, so close to 30 years ago. And since then, Postgres has become one of the most successful and popular databases. But it also means a lot of the development process reflects how things were done back then. The reliance on mailing lists is a good example of this heritage. Let’s talk about if / how this might change.
About a month ago I presented a keynote at Swiss PGDay 2024 about the state of the Postgres community. My talk included a couple charts illustrating the evolution and current state of various parts of the community - what works fine and what challenges will require more attention. Judging by the feedback, those charts are interesting and reveal things that are surprising or at least not entirely…