PostgreSQL Autovacuum and Bloat: MVCC, Freezing, Wraparound and Per-Table Tuning

A complete guide to the maintenance work that decides whether your PostgreSQL ages well or quietly degrades: why MVCC produces bloat by design, what VACUUM actually does (and why it does not shrink your table), the autovacuum trigger formula behind 80% of the problems, three levels of detection with pg_stat_user_tables, pgstattuple and pg_stat_progress_vacuum, per-table tuning instead of global, the cost budget that throttles your vacuum to 39 MB/s, HOT updates and fillfactor to prevent rather than clean up, and the full transaction ID wraparound runbook without dropping into single-user mode. Covers the four blockers that make VACUUM run without removing a single tuple, what changed in PostgreSQL 17 (TidStore) and 18 (eager freezing and autovacuum_vacuum_max_threshold), remediation with REINDEX CONCURRENTLY, pg_repack and pg_squeeze, a Python monitoring script, Prometheus alerts, eight recurring mistakes, a production checklist and an FAQ. With production-ready SQL, Python, YAML and configuration. Expanded edition: the invisible bloat of TOAST tables and their own toast.-prefixed parameters, replicas and the real cost of hot_standby_feedback with recovery conflicts, orphaned slots and PostgreSQL 18 idle_replication_slot_timeout, index bloat from the inside with bottom-up deletion, pgstatindex, REINDEX CONCURRENTLY and INDEX_CLEANUP OFF for emergencies, partitioned tables with cold-partition freezing and the parent ANALYZE autovacuum never runs, autovacuum on managed services (rds.adaptive_autovacuum, autovacuum_worker_slots and the worker memory arithmetic), and a full worked example going from 41 GB to 9 GB with no maintenance window. August 2026 expansion: the visibility map as VACUUM’s second job and the heap fetches that quietly degrade index-only scans, append-only tables with autovacuum_vacuum_insert_threshold and COPY FREEZE, catalog bloat from temp tables and DDL, parallel VACUUM and what it actually parallelizes, major upgrades with pg_upgrade and the loss of the cumulative statistics that trigger autovacuum, what is coming in PostgreSQL 19 (priority scoring with pg_stat_autovacuum_scores, autovacuum_max_parallel_workers and the new REPACK CONCURRENTLY command), and a pgbench test bench to validate your configuration before production does it for you.

56 minutes read

Josué Puig

2026-08-18

2 views

Verificando acceso...

Loading comments...

Related Resources

Guía

PREMIUM

API Errors with Problem Details (RFC 9457): One Standard Format, Stable Types, and Clients That Stop Parsing Strings

A practical guide to standardising your API errors with Problem Details (RFC 9457): the anatomy of type, title, status, detail and instance, extension members, validation with multiple errors, centralised FastAPI implementation, a TypeScript client that branches on type, security (what not to reveal), OpenAPI documentation, contract tests, common mistakes and an adoption checklist. With code in Python, TypeScript, YAML and HTTP. Expanded edition of 12 October 2026: the type catalog as a contract (stable URIs, about:blank, the IANA registry and evolution rules), a problem registry in code that generates the docs, domain exceptions and a central FastAPI handler with headers and localization, a robust TypeScript client with retries, Retry-After and jitter, per-type metrics and alerts, migrating without breaking clients, Spring, ASP.NET and Node, async errors, batches and idempotency, a case study, FAQ and glossary. Expansion of October 16, 2026: the boundaries almost nobody covers: errors generated by gateways and proxies (nginx and Envoy local_reply_config), a client that survives intermediary HTML responses, errors in the middle of an SSE or NDJSON stream with a terminal frame and resume cursor, partial failures in batches, disciplined design of extension members with extra="forbid", linting with Spectral, fuzzing with Schemathesis, client tests with MSW and a runbook for spikes in one problem type.

Guía

PREMIUM

API Versioning and Contract Testing: Safe Changes, OpenAPI in CI, Pact, and Retiring Endpoints with Sunset

A practical guide to changing your APIs without breaking the people who consume them: why compatibility rules invert between the request and the response —and why widening a returned enum breaks clients even though you are "only adding"—, the tolerant reader pattern in Pydantic with an escape hatch and a metric, OpenAPI generated from the code and diffed on every pull request with oasdiff (including the git diff --exit-code step without which the whole check is theatre), consumer-driven contract testing with Pact: type matchers instead of literal values, well-designed provider states, version selection with deployed_or_released, and the gate that actually makes it safe, can-i-deploy paired with record-deployment. It also covers rolling this out without stopping the factory using pending and WIP pacts, bi-directional contracts when the provider is a third party, the three versioning strategies with their real operational costs, why versioning the whole API for a single endpoint guarantees nobody migrates, retiring versions with the Deprecation (RFC 9745) and Sunset (RFC 8594) headers plus the migration Link, the per-consumer metric without which no sunset date is ever met, brownouts returning 410 Gone before the final shutdown, and the BACKWARD, FORWARD and FULL compatibility modes for event schemas. With production-ready code in Python, YAML, Bash and PromQL, eight recurring mistakes, a production checklist, FAQ and glossary. It also extends the contract beyond the happy path: errors with RFC 9457 (problem+json), cursor pagination and defaults as part of the contract, the expand/contract pattern for renaming a field across database and API with no maintenance window, governance with Spectral, buf breaking for gRPC and Protobuf, GraphQL schema evolution with @deprecated and real per-field usage, and semantic versioning of generated SDKs. It also covers the contracts that never show up in the schema and break just as hard: outbound webhooks with the version pinned on the subscription, HMAC signing with a timestamp window and key rotation, Idempotency-Key for safe POST retries, the RateLimit and RateLimit-Policy headers —and why lowering a limit is a breaking change—, and OAuth scopes, the blind spot no OpenAPI diff will ever catch.

Guía

PREMIUM

Architecture Decision Records (ADRs): Document the Why Behind Your Technical Decisions

A practical guide to recording architecture decisions with ADRs: what they are and are not, when to write one, a template covering context, alternatives and consequences, a lifecycle with statuses and supersession, and automation with a creation script and a CI validator. With Bash, Python and YAML code, common mistakes and a checklist. Expanded edition of October 2, 2026: writing context with evidence, alternatives without straw men and a weighted decision matrix with sensitivity analysis, Nygard, MADR 4 and Y-statement templates, the advice process with roles, CODEOWNERS and a PR template, the boundary between ADRs, RFCs, design docs and runbooks, ADRs as executable architecture with import-linter and fitness tests, generated index and supersession graph, scheduled reviews with Review-by, two complete annotated ADRs, ADRs at scale in monorepos and multi-repo setups, adoption through retroactive ADRs, ADRs as context for coding assistants, health metrics, advanced mistakes, FAQ and glossary.