postgres. Super turbo buster button

The default postgres configuration as seen in a lot of vmware appliances may not be optimum to ensure autovacuum is keeping up to date.

postgres is different to other many other databases in that when a row is deleted its not actually removed from disk. Its marked as a dead row.

Vacuuming is the process which clears up these dead rows. Leaving them build will cause queries to perform worse and worse over time.

postgres has an automated process which takes care these dead rows.

However sometimes its not aggressive enough.

Postgres enginning in VMware have suggested the following update and its working great! soon to be seen in a product near you.

You need to update the postgresql.conf and modify 2 default values.

autovacuum_max_workers = 2 + CPU count, autovacuum_vacuum_cost_limit = 300 * CPU count.

autovacuum_max_workers – default is 3 – should be 10

autovacuum_vacuum_cost_limit – default is 200 – should be 1600

also run a vacuum analyze on a cron during a quiet time is an excellent way to ensure your database statistics stay up to date.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s