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

 

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