Two maintenance commands, ANALYZE and VACUUM, are central to keeping PostgreSQL fast. ANALYZE refreshes the row-count and distribution statistics that the planner relies on for cost estimates. It runs automatically after autovacuum, but a manual ANALYZE table_name is often worthwhile after very large UPDATE/DELETEs or partition swaps. The depth of sampling is controlled by default_statistics_target (default 100); raising this to 1000 or more for high-cardinality columns where the planner consistently underestimates can materially improve plan quality. On partitioned tables, statistics live per partition, and on PostgreSQL 14+ also on the parent—ANALYZE should be run after partition operations to keep the planner from going stale. Stale stats are a frequent cause of plan instability: the same query may flip between plans as autovacuum/ANALYZE updates estimates. For situations where a stable plan is essential and the planner's choices are unreliable, the server-level plan_cache_mode can be set to force_generic_plan or force_custom_plan, and judicious use of prepared statements will keep a plan cached server-side.
VACUUM reclaims dead tuples left behind by UPDATE/DELETE and updates the visibility map, without blocking readers or writers. VACUUM FULL is more aggressive: it rewrites the table and returns space to the operating system, but it takes an ACCESS EXCLUSIVE lock and is inappropriate on a busy system. pg_repack is the practical alternative that rewrites a table online. Bloat is the symptom of insufficient vacuuming—dead tuples and free space inflate reads—and can be detected with the pgstattuple extension or by inspecting pg_stat_user_tables. Index bloat is similarly easy to spot with pgstattuple on the index, or with queries that compare current index size against an expected size from row count. Run REINDEX CONCURRENTLY for index bloat during a maintenance window; it cannot run inside a transaction, but it rebuilds the index in parallel and swaps it in with minimal disruption and requires only extra disk space.
HOT (Heap-Only-Tuple) updates are a significant performance win when updates fit on the same page and do not modify indexed columns, since no index entry needs to change. Autovacuum itself can be tuned: the setting autovacuum_vacuum_scale_factor (and the per-table storage parameter) controls the dead-tuple threshold that triggers a run; for hot, write-heavy tables, lower values make vacuum more responsive. Live progress is visible in pg_stat_progress_vacuum. Long-running transactions can block autovacuum entirely because the vacuum worker cannot reclaim tuples visible to any active snapshot; idle-in-transaction sessions are a common cause, found via SELECT * FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)'). Note that ANALYZE alone may not be enough after a huge UPDATE: although it catches the stats up, the pages themselves remain bloated, so a follow-up VACUUM to update the visibility map is often warranted. A broader concern is transaction wraparound: PostgreSQL's 32-bit transaction IDs wrap around at roughly four billion, and the database will shut down to protect data if autovacuum falls behind on freezing. Monitor with SELECT datname, age(datfrozenxid) FROM pg_database, warn above about 1.5 billion, and treat anything approaching 2 billion as urgent.