Database Management · Advanced level

Advanced level · Module 1: PostgreSQL Administration · Lesson 4 of 4

Maintenance and Monitoring

Keep the server healthy with VACUUM and ANALYZE, monitor its activity and plan upgrades.

In this lesson you will

  • Explain VACUUM, ANALYZE and autovacuum
  • Monitor connections, locks, size and slow queries

Key concepts

MVCC and dead rowsVACUUM and ANALYZEpg_stat_activityminor and major upgrades

The lesson

PostgreSQL keeps old row versions for concurrent transactions (MVCC). VACUUM cleans up dead rows and ANALYZE updates the statistics the planner uses. Autovacuum does both automatically; check it keeps up on busy tables.

Monitor connections and running queries with pg_stat_activity, locks with pg_locks, database and table sizes and slow queries (the pg_stat_statements extension). Disk space running out is a common cause of outages.

Apply minor version updates regularly for security fixes. Major upgrades need planning and testing with pg_upgrade or dump and restore.

Step by step

  1. Check table bloat and autovacuum activity.
  2. List running queries with pg_stat_activity.
  3. Enable pg_stat_statements and find the slowest queries.
  4. Write a weekly maintenance checklist.

Practice activity

Create the monitoring queries and weekly maintenance checklist for the health programme database.

Expected result: A checklist with queries that report connections, size, slow queries and vacuum status.

Check your understanding

  1. What does ANALYZE do?

    Show answer

    Updates the statistics the query planner uses to choose plans.

  2. Which view shows currently running queries?

    Show answer

    pg_stat_activity.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.