Postgres DB Fundamentals

 

vacuum --> Defrag in Oracle

 

In postgres Tables gets Bloated due to DML's

A tuple is an internal representation of a row

 

Vacuum	Vacuum FULL
Free up dead rows for reuse, Selects and DMLs Allowed No Exclusive Locks, OS Space NOT released	Rewrite the table with no dead rows (Tuple), No DML’s Including select Allowed, Puts EXCLUSIVE Lock, OS Space will be released

Tune AutoVacuum:

set Vacuum_cost_page IO limit parameters
No. of Workers (I/O Intensive)
 

Autovacuum_vacuum_threshold :
min number of updates/deletes tuples needed to trigger a VACUUM in any table. The default is 50 tuples.

Autovacuum_vacuum_scale_factor:
Fraction of the table size (in terms of no. of rows) to decide whether vacuum should be triggered. Default is 0.2
 

SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%';

Parameter	Default	Meaning
autovacuum	on	Enables autovacuum
autovacuum_vacuum_scale_factor	0.2 (20%)	Vacuum when 20% of table is dead
autovacuum_analyze_scale_factor	0.1 (10%)	Analyze when 10% of table changes
autovacuum_vacuum_threshold	50	Minimum dead tuples before vacuum
autovacuum_analyze_threshold	50	Minimum changes before analyze
autovacuum_naptime	1 min	How often autovacuum checks tables
autovacuum_max_workers	3	Max parallel autovacuum workers
autovacuum_vacuum_cost_limit	-1 (use global)	Cost-based throttle
autovacuum_vacuum_cost_delay	20 ms	Delay between vacuum cycles

Transaction wraparound

·        TXID's can go up till ~4 billion (32 bit ID)

·        Postgres will stop accepting commands when there are fewer than one million transactions left before the maximum XID value is reached.

 

   Why Happens this ?

·        Auto-vacuum is set to turned off

·        Heavy DML Operation

·        Many session or connection’s holding lock’s for very long time

 

   What happens in this Situation

·        PostgreSQL will stop accepting DML statements and switches to READONLY mode.

 

   FIX:

·        Stop and Bring up DB into single user mode

·        Run Vacumm FULL on entire DB (vacuumdb --all)

·        Once done stop ad restart postgres normally