Database Maintenance

1
Database Maintenance

• Karen Tian
• ktian_at_Stanford.EDU

2
Overview

• Q How long can we keep running?
• Our goal is to minimize downtime
• Do maintenance while running
• Challenges
• Very large database a few TB
• Transaction intensive applications
• Currently we have SUMS and DRMS tables in the
  same DB
• SUMS tables insert, update, and delete
• Must vacuum to reclaim disk space
• DRMS tables tables insert only, no update or
  delete, except for transient records
• Static tables, need to vacuum to prevent XID
  wraparound

3
Routine database maintenance tasks

• Base backup and WAL file backup
• Vacuum
• Reindex
• PostgreSQL upgrade

4
Base backup and WAL file backup

• Base backup
• pg_start_backup()
• file system snapshot
• pg_stop_backup()
• snapshot dump to tape
• Plan on weekly base backup
• The longer the interval between base backups, the
  longer it takes to process xlog to catch up
• These base backup along with appropriate WAL
  files can make a consistent DB snapshot, perhaps
  useful for other purpose.
• Archive WAL files
• Clean up WAL files after a base backup

5
Vacuum

• Purpose
• Recovering disk space
• Updating planner statistics
• Preventing transaction ID wraparound failures
• Obstacle
• Long running transaction prevents vacuum from
  deleting dead rows
• Currently SUMS and DRMS tables reside in the
  same DB. DRMS module may start long running
  transaction that prevents vacuum from removed
  dead rows in SUMS tables.
• Plans
• Switch to warm stand-by and perform vacuum on the
  primary
• Separate SUMS from DRMS, i.e., run them off
  different PostgreSQL server instances.

6
Reindex

• Why?
• Index might become corrupted
• Index might become bloated
• A freshly-constructed index is faster because
  logically adjacent pages are usually also
  physically adjacent in a newly built index
• Impact
• Locks out writes but not reads of the index's
  parent table
• Takes an exclusive lock on the specific index
  being processed, which will block reads that
  attempt to use that index

7
PostgreSQL upgrade

• The internal data storage format is subject to
  change between major releases
• Require backing up data and restore it on the new
  server
• Must take the DB server down and switch to
  stand-by