Optimizing Postgres's autovacuum for high-churn tables | Hacker News Reader

Optimizing Postgres's autovacuum for high-churn tables

tembo.io

91 pointschuckhend35 comments

Very helpful article! It jives with the evolution of my own understanding of autovacuum tuning over a decade and a half of writing high-performance Postgres-backed VoIP routing systems.

I would add to this that if you truly have a high-churn table, perhaps a traditional RDBM is not the best choice. Ask me how I learned that lesson the hard way. There are many things that, in hindsight, really should have gone into ELK or Splunk or Redis or something more suited to ephemeral or short-lived data sets that also turn over constantly.

But, sometimes there is no choice, or the churn is high but not quite high enough to rule out the use of an RDBM altogether. For those scenarios, the tips in this article really shine.

How'd you learn that lesson the hard way?

I made the mistake of writing a searchable syslog archiver where the parsing was done in stored functions (very basic string-splitting) and the log entries were stored in a heavily indexed Postgres table.

At the time it was created, the company was in its infancy, there wasn't much throughput, the service emitting the logs was kind of obscure, and the solution was a quick, dirty stopgap meant to be easily digestible and consumable to the customer, compatible with the rest of their in-house knowledge and the rest of their backend stack. However, as happens too often, the company grew meteorically, the volume got to be insane, many more default-on log entries were added to the standard event loop, and the system was put straight into production without any interest in redesign; after all, it worked fine so far.

As you might guess, it got to the point where we were seeing insanely degraded performance and cascade failures after only a few days' log activity, and increasing time and resources were devoted to the endless care and feeding of this beast. It took a surprisingly long time for the rubber band to snap and for the customer to consider ELK, despite the fact that I advocated for this almost from the very beginning.

I found that partitioning the table to allow parallel auto vacuuming was necessary to scale.

Vacuuming is such a complex task in optimize in Postgres. Depending on your access pattern you will need to also configure index with conditions and/or partitioning (on top of sane vacuuming settings). And when you realise that Update is the same as Delete+Create it adds up to the confusion.

Fwiw many databases have update be delete+create because you've already invented those primitives so why do something else?

This is useful for transactional operations where you want to preserve the previous state until the transaction completes. If you don't need transactions, or you don't need point-in-time queries, then updating in place would save time.

Except updating in place is almost never what is actually happens outside of RAM - there are usually various layers of Copy on Write happening anyway. So unless your database (or the high-churn subset) cleanly fits in RAM I would generally expect that the significance of any time saved is debatable. Denying yourself the possibility of adding transactions later down the line is a huge risk in most situations.

In any case, update in place will mean at least half as many CoW operations. Many databases have (or, at least, had) the ability to manage block storage directly without the need for a filesystem. If transactions can be supported only for some operations while others can be transactionless, you can speed up the operations that won't need them.

Direct block storage is good, but note that SSDs are essentially just doing CoW internally too.

It's perhaps also worth a mention that embracing the internal characteristics of SSD throughout the software stack looks like the inevitable direction we're headed (short of another major breakthrough in storage tech) - e.g. see "A Survey on the Integration of NAND Flash Storage in the Design of File Systems and the Host Storage Software Stack" https://arxiv.org/pdf/2307.11866.pdf

In a multi version database you have little choice in the matter. You’re going to CoW anyway.

CoW?

copy on write

Yep not complaining about the implementation, just it's not the mental model you have when you begin using Databases. It took me many years to understand this bit.

> Update is the same as Delete+Create

I gave a talk titled "UPDATE Considered Harmful" featuring exactly this idea a few months back. The etymology still makes no sense to me...they should have named it REPLACE instead of UPDATE: https://www.youtube.com/watch?v=JxMz-tyicgo

Good talk! It was surprising to me when I first learned that UPDATE does not literally overwrite what is on disk, its more like an abstracted API that gives me the impression that I updated the row on disk.

Thanks :) The surprises don't stop though - it's abstract turtles all the way down...meaning plenty of fat to trim between UPDATE and what's happening in the MOSFETs. Might be of interest: https://arxiv.org/pdf/2307.11866.pdf

The first real time GC I encountered accomplished its goal by amortizing deletes across creates so the max allocation time could be guaranteed.

After all these years, I wonder why vacuum is still such a PITA with Postgres. Vacuuming should be at least partially concurrent with CREATE.

How often are you executing create, compared to how often do you need to vacuum?

If the data set isn't growing, how big of a priority is vacuuming? You're at homeostasis if you aren't creating or updating records.

If memory serves the GC implementation was freeing/defragging 10 chunks of memory for each one it allocated. In a real time system if you're allocating a bunch of memory, you're already on notice, so having it cost a few thousand instructions instead of a few hundred isn't that big of an inconvenience.

And with SQL the network time is going to dominate.

Did you mean insert…?

I haven't had much success with finding an answer to this: what flags should be set to allow postgres to run as fast as possible without caring about data loss at all? This is for integration tests where the database is thrown away at the end of the run anyway.

I'll definitely be looking at further tweaking vacuuming based on this article.

You get rid of fsync and a few smiliar flags. Made my tests about 50% faster

https://postgresqlco.nf/doc/en/param/fsync/#:~:text=Setting%....

Check out unlogged tables!

https://www.crunchydata.com/blog/postgresl-unlogged-tables

I would be interested if anyone had similar tweaks for MySQL. I used to mount the directory where MySQL stores data into memory, this used to be very effective until upgrading to MySQL 8. Now my test suite takes three times as long to run and I never really figured out why.

> I never really figured out why.

How is memory consumption during the tests? If you run out of memory on a tmpfs, you'll hit swap and that'll slow you down considerably.

Do you see other disk access during the test?

For the postgres config, set fsync=off and full_page_writes=false, and increase min_wal_size, max_wal_size and checkpoint interval with the hope that your tests pass before having to flush the WAL. Maybe slap in some tunings from PGTune.

If you're using docker/podman or docker-compose and your db size is small, a major speedup on linux is to just mount the entire data dir into memory with --tmpfs /var/lib/postgresql/data (or tmpfs: - /var/lib/postgresql/data in docker-compose)

Additionally, if you constantly reset your db in the tests, consider making a template db at the start and later just doing CREATE DATABASE ... TEMPLATE foo; to copy the pages from that template instead of running migrations that produce WAL log. In fact, consider making a db for every test suite from that template at the start - then you can run each suite in parallel (if your app's only state is the db and a single backend).

I was surprised but CREATE DATABASE TEMPLATE is still pretty slow, a few hundred ms on my machine. Better than alternatives though.

A few more application-level optimisations: if you need to clear your database between tests, the best way is to wrap each test in a transaction and roll it back (Postgres supports nested transactions with checkpoints which might help make that transparent to the application under test). The second best way is usually to use DELETE rather than TRUNCATE, the latter is much slower on small tables (but much faster on big ones). The third fastest way is to create a new DB from template, although it plays nice with parallelisation.

How much does an fsync cost against memory (tmpfs/RAM disk)?

Just made me curious.

Thank you!

> This is for integration tests where the database is thrown away at the end of the run anyway.

For situations like these, creating the database in a tmpfs volume and having enough memory on the db host would do wonders.

-c fsync=off -c synchronous_commit=off -c full_page_writes=off

I asked Karen Jex this question at her Djangocon 2023 talk on obscure postgres switches.

I used this among other things to build some model integration tests which exhibit best practices: https://github.com/hitchdev/hitchstory/tree/master/examples/...

I considered using tmpfs but since I wanted to cache the entire database volume after it had been built according to the fixtures and couldnt figure out how to do that with podman and tmpfs.

On a more generic front, there's always libeatmydata which forcibly disables fsync. I've used it in the past for doing, eg, faster database restores. Run mysqld under it, do the restore, manually force a sync, shut down MySQL, restart and then carry out validation activities. Usually dramatically speeds up bringing up a replica.

https://www.flamingspork.com/projects/libeatmydata/

Additional tricks I didn't see in the thread:

I just disable autovacuum completely for test runs with autovacuum=off. Remember that it kicks in according to the percentage of data updated so with a table which starts empty you are guaranteed to see autovacuum run once you insert a few rows.

If you have a lot of updates you can also try change the table fillfactor. This makes HOT updates a bit more efficient.

Reply on news.ycombinator.com