PostgreSQL

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.

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

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.

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

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

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

I’m currently scheduled to speak at:

• SCaLE 20x in Pasadena, California, on Database Antipatterns and How to Find Them, March 9.
• Nordic PgDay 2023 in Stockholm, on Real-World Logical Replication, March 21.
• PgDay/MED 2023 in Malta, on Extreme PostgreSQL, April 13.

I hope to see you at one of these!

Over the course of the last few versions, PostgreSQL has introduces all kinds of background worker processes, including workers to do various kinds of things in parallel. There are enough now that it’s getting kind of confusing. Let’s sort them all out.

You can think of each setting as creating a pool of potential workers. Each setting draws its

Normally, when you drop a column from PostgreSQL, it doesn’t have to do anything to the data in the table. It just marks the column as no longer alive in the system catalogs, and gets on with business.

There is, however, a big exception to this: ALTER TABLE … SET WITHOUT OIDS. This pops up when using pg_upgrade

This topic pops up very frequently: “Should we use UUIDs or bigints as primary keys?”

One of the reasons that the question gets so many conflicting answers is that there are really two different questions being asked:

• “Should our keys be random or sequential?”
• “Should our keys be 64 bits, or larger?”