The PostgreSQL roles and privileges system can be full of surprises. Let’s say we have a database test, owned by user owner. In it, we create a very secret function f that we do not want just anyone to be able to execute: test=> select current_user; current_user -------------- owner (1 row) test=> CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; CREATE FUNCTION test=> select f(); f --- 1 (1 row) There are two other users: hipriv and lowpriv. We want hipriv to be able to run the function, but not lowpriv. So, we grant EXECUTE to hipriv, but revoke it from lowpr ..read more

The slides for my talk “Look It Up: Real-Life Database Indexing” are now available ..read more

I shouldn’t have to say this, but don’t use ChatGPT for technical advice. In an experiment, I asked 40 questions about PostgreSQL. 23 came back with misleading or simply inaccurate information. Of those, 9 came back with answers that would have caused (at best) performance issues. One of the answers could result in a corrupted database (deleting WAL files to recover disk space). LLMs are not a replacement for expertise ..read more

Recently on one of the PostgreSQL mailing lists, someone wrote in asking if it was possible to get PostgreSQL to listen on two ports. The use case, to paraphrase, was that there was a heterogeneous mix of clients, some of which could connect with TLS, some of which couldn’t. They wanted the clients that could use TLS to do so, while allowing the non-TLS clients access. The simple answer is: Upgrade your non-TLS clients already! But of course the world is a complicated place. And for reasons that weren’t given (but which we will accept for now), it has to be two different ports. The PostgreSQL ..read more

I’ll be speaking about Writing a Foreign Data Wrapper at PGCon 2023 in Ottawa, May 30-June 2, 2023. Do come! It’s the premiere technical/hacker conference for PostgreSQL ..read more

In a comment on my earlier post on max_wal_size, Lukas Fittl asked a perfectly reasonable question: Re: “The only thing it costs you is disk space; there’s no other problem with it being too large.” Doesn’t this omit the fact that a higher max_wal_size leads to longer recovery times after a crash? In my experience that was the reason why you wouldn’t want max_wal_size to e.g. be 100GB, since it means your database might take a while to get back up and running after crashes. The answer is… as you might expect, tricky. The reason is that there are two different ways a checkpoint can be started ..read more

The reality is that most PostgreSQL configuration parameters don’t have a huge impact on overall system performance. There are, however, a couple that really can make a huge difference when tuned from the defaults. work_mem is one of them, and max_wal_size is another. max_wal_size controls how large the write-ahead log can get on disk before PostgreSQL does a checkpoint. It’s not a hard limit; PostgreSQL adapts checkpoint frequency to keep the WAL on disk no larger than that, but excursions above it can definitely happen. The only thing it costs you is disk space; there’s no other problem with ..read more

The slides from my presentation “Real-World Logical Replication” are now available ..read more

The slides are now available for my talk “Database Antipatterns, and where to find them” at SCaLE 20x ..read more

If you google around for how to set work_mem in PostgreSQL, you’ll probably find something like: To set work_mem, take the number of connections, add 32, divide by your astrological sign expressed as a number (Aquarius is 1), convert it to base 7, and then read that number in decimal megabytes. So, I am here to tell you that every formula setting work_mem is wrong. Every. Single. One. They may not be badly wrong, but they are at best first cuts and approximations. The problem is that of all the parameters you can set in PostgreSQL, work_mem is about the most workload dependent. You are tryin ..read more