Hacker News Reader

Comment by Eikon | Hacker News Reader

Mostly, be careful with long-running transactions (hours long), and modify your autovacuum settings to be as aggressive as possible based on your workload. Be careful with the freezing threshold too.

I ran into

    multixact "members" limit exceeded

Quite a bit when starting out :)

View on HN

Can you recommend a good rule of thumb for autovacuum settings given a large workload like yours?

Here's mine:

- autovacuum_max_workers to my number of tables (Only do so if you have enough IO capacity and CPU...).

- autovacuum_naptime 10s

- autovacuum_vacuum_cost_delay 1ms

- autovacuum_vacuum_cost_limit 2000

You probably should read https://www.postgresql.org/docs/current/routine-vacuuming.ht... it's pretty well written and easy to parse!

Do you ever need to VACUUM FULL?

It’s too slow at that scale, pg_squeeze works wonders though.

I only “need” that because one of my table requires batch deletion, and I want to reclaim the space. I need to refactor that part. Otherwise nothing like that would be required.

Yes, I was asking because you have mention deletes. Thanks for the answer, cool stuff!

Reply on news.ycombinator.com