Hacker News new | ask | show | jobs
by marsavar 460 days ago
That's amazing! Could you elaborate more on your VACUUM planning?
1 comments
Eikon 460 days ago
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 :)
link
whitepoplar 460 days ago
Can you recommend a good rule of thumb for autovacuum settings given a large workload like yours?
link
Eikon 460 days ago
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!

link
ahoka 460 days ago
Do you ever need to VACUUM FULL?
link
Eikon 460 days ago
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.

link
ahoka 459 days ago
Yes, I was asking because you have mention deletes. Thanks for the answer, cool stuff!
link