Vacuum tables in postgresql

sql postgresql postgresql-performance vacuum
user3814846 picture user3814846 · Sep 13, 2014 · Viewed 14k times · Source

Using ,at this moment am finding the tables that have dead_tuples using the following query

select relname from pg_stat_user_tables where n_dead_tup > 0

this will retuns the table name and then I'll run 

VACUUM VERBOSE ANALYZE <table_name>

Is that a good method or do I need to change it,if so please suggest me some methods

Thanks

Answer

Hasan Ramezani picture Hasan Ramezani · Sep 13, 2014

according to Documentation

VACUUM reclaims storage occupied by dead tuples.

But according to this post

Dead rows are deleted rows that will later be reused for new rows from INSERTs or UPDATEs. Some dead rows (or reserved free space) can be particularly useful for HOT (Heap-Only Tuples) updates that can reuse space in the same data page efficiently

Related questions

Improving query speed: simple SELECT in big postgres table

I'm having trouble regarding speed in a SELECT query on a Postgres database. I have a table with two integer columns as key: (int1,int2) This table has around 70 million rows. I need to make two kind of simple SELECT …

Read More
Best way to delete millions of rows by ID

I need to delete about 2 million rows from my PG database. I have a list of IDs that I need to delete. However, any way I try to do this is taking days. I tried putting them in a table …

Read More
Optimize GROUP BY query to retrieve latest row per user

I have the following log table for user messages (simplified form) in Postgres 9.2: CREATE TABLE log ( log_date DATE, user_id INTEGER, payload INTEGER ); It contains up to one record per user and per day. There will be approximately 500K …

Read More