DB maintenance commands in PostgreSQL: Analyze, Reindex, Vacuum

Actively updated database tables in PostgreSQL sometimes require maintenance because of table and index bloat, obsolete and suboptimal query plans and sometimes corrupted indexes. Bloated tables and indexes occupy too much of disk space and memory, suboptimal queries and corrupted indexes slow down database queries. As a result, database performance may significantly degrade. PostgreSQL has a few commands to help developers solve those problems: ANALYZE, VACUUM, REINDEX.

ANALYZE

ANALYZE collects statistics about the contents of all or several columns in a table or entire database. These statistics are then used by the query planner to produce efficient execution plans for queries.

  • ANALYZE VERBOSE users; does exactly the same plus prints progress messages.
  • ANALYZE users (id, display_name); collects statistics for id and display_name columns of users table.
  • ANALYZE; collects statistics for all the database tables.

VACUUM

VACUUM reclaims storage occupied by dead records. Dead records in a table are the deleted or the previous versions of updated records. Such records are only marked as unavailable but still occupy disk space, making table scans slower. VACUUM also has an option that tells it to ANALYZE the table.

  • VACUUM VERBOSE users; vacuums users table and prints progress messages.
  • VACUUM FULL users; fully vacuums users table. Other queries cannot access users table while vacuuming is running.
  • VACUUM ANALYZE users; vacuums and analyses users table.
  • VACUUM FULL VERBOSE ANALYZE users; fully vacuums users table and displays progress messages.
  • VACUUM; vacuums all the tables in the database the current user has access to.

REINDEX

REINDEX rebuilds an existing index or all indexes for a table or entire database. This command is helpful when an index becomes corrupted and is not used anymore, or when an index gets bloated after significant changes in the table contents.

  • REINDEX TABLE users; reindexes users table.
  • REINDEX VERBOSE TABLE users; reindexes users table and prints progress messages.
  • REINDEX DATABASE app_production; reindexes all tables in app_production database.

Software engineer, manager since 2002. Engineering management, leadership, software architecture, high-performing teams, professional growth.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store