PostgreSQL Table Partitioning: Range, List, Hash, and Zero-Downtime Retention

Learn how to partition large tables in PostgreSQL: when it pays off (and when it does not), RANGE, LIST and HASH partitioning, partition pruning and how to verify it with EXPLAIN, indexes and unique keys on partitioned tables, automation with pg_partman and pg_cron, data retention with DETACH CONCURRENTLY, and how to migrate an existing table with zero downtime. With production-ready SQL and the mistakes that silently defeat partitioning. Expanded edition: partition key selection and multi-tenant, statistics and parent ANALYZE, BRIN versus B-tree, foreign keys, locks and interrupted DETACH (FINALIZE), planning cost and prepared statements, partition-wise joins, tablespaces and cold archiving, logical replication, monitoring and alerting, pg_partman 5 and template tables, batched backfills with Python, TimescaleDB and Citus, pruning tests in CI, vacuum and wraparound, ORMs, incident runbook, a full case study, FAQ, and a glossary.

35 minutes read

2026-07-31

0 views

Verificando acceso...

Loading comments...

Related Resources

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.

Guía

PREMIUM

asyncio in Production: Never Block the Event Loop — TaskGroups, Cancellation and Bounded Concurrency

A practical asyncio guide for Python services in production: why blocking the event loop degrades the whole process without raising a single exception, how to catch it by measuring loop lag and with Python 3.14 introspection, structured concurrency with TaskGroup and handling ExceptionGroup via except*, the task the garbage collector makes vanish, timeouts with a deadline budget propagated across services, correct cancellation with cleanup and shield, bounded concurrency with semaphores and backpressured queues, synchronization primitives, and what changes with eager tasks, python -m asyncio pstree and free-threading. With production-ready code and a deployment checklist.

Guía

PREMIUM

Cache-Aside in Production: TTLs, Invalidation, and How to Prevent Cache Stampedes

The complete guide to the cache-aside pattern with Redis: jittered TTLs, correct invalidation, and the three defenses against cache stampedes (distributed lock, single-flight, and XFetch). Expanded with stale-while-revalidate, fail-open and circuit breakers, two-tier caching with RESP3 invalidation, delayed double delete and CDC, hot keys, eviction and memory management, observability with Prometheus, testing, choosing an engine (Redis, Valkey, Memcached), and a complete TypeScript implementation. With production-ready code in Python and TypeScript.