Auto vacuum and analyse larger tables

Postgresql database operations becomes slower if table gets larger. In order to improve the performance, we can configure to auto-vacuum the table for every x inserts/updates/deletes.

Using below settings, a table would be auto vacuumed and analysed every 5,000 inserts, updates, or deletes.

ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 10000);

 

 

Advertisements

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