Skip to content

Instantly share code, notes, and snippets.

@sanp

sanp/vacuuming.md

Last active September 27, 2017 07:49
Show Gist options
  • Save sanp/5835e7ef7d18ed894df403c1ecf97ba0 to your computer and use it in GitHub Desktop.
Save sanp/5835e7ef7d18ed894df403c1ecf97ba0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
vacuuming.md

Vacuuming

  • Postgres uses an MVCC (Multiversion concurrency control) model (as opposed to table locking)

    • When an update/transaction is happening, a new snaphot of the data is created
    • Whenever you query data, you're seeing a snapshot of the data as it was at a certain time in the past.

  • So: when you run an update, it's essentially doubling the size of the table, because a new snapshot is being created.

  • After the update, the old snapshot is essentially dead space: obsolete records.

  • Postgres doesn't release that dead space back to the OS after an update

    • It's inefficient to continually grow and shrink tables

  • Vacuuming marks that space as dead and ready for re-use

  • Vacuuming is garbage collection

    • Normal vacuuming: Reclaims the dead space and makes it available for reuse by the same table/object.
    • VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system

  • Postgres does autovacuuming for routine maintainence (not full)

    • So if you're regularly updating a table, you may want to vacuum full it

More Reading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment