You own dozens of time-partitioned tables across several services. Design the automation that keeps them healthy: creating future partitions, expiring or archiving old ones. What must the job guarantee, how do you monitor it, and which failure modes do you plan for?
answer
- declarative config + reconcile loop, not increments
- idempotent, one partition per transaction, advisory-lock singleton
- lock_timeout + retry on every DDL
- alert on runway, not on job exit code
- adopt pg_partman-style tooling, own the policy
basics
~20 sMake it declarative and data-driven: a config row per table (interval, premake count, retention, archive action). The job must be idempotent, singleton-locked, short-transactioned, and lock-timeout bounded with retries. Monitor runway, partition counts and the last successful run, and prefer a proven implementation such as pg_partman over bespoke scripts.
solid answer
~60 sTreat it as a control loop, not a cron script. Configuration lives in a table (one row per partitioned table: interval, how many periods to premake, retention period, archive versus drop, index and privilege template). The worker reconciles reality to that configuration on every run. Guarantees it must provide: idempotency, so a rerun after a crash is harmless; one creation or drop per transaction, so a failure halfway leaves a valid state; a singleton guard such as an advisory lock so two schedulers cannot race; `lock_timeout` on every DDL with bounded retries, so a maintenance step never becomes a lock-queue outage; and templating so new partitions inherit indexes, constraints, storage settings, privileges and statistics targets. Monitoring is about state, not job exit codes: runway per table (time until the newest bound), oldest partition age versus policy, partition count and total size, and time since the last successful reconcile. Page on runway, because a scheduler that stops firing produces no error at all. Failure modes to plan for: skipped runs, clock and time-zone drift at boundaries, retention dropping data still needed, unbounded partition counts hurting planning, and archive steps that fail silently.
go deeper
Know that partitions must be created ahead of time and old ones removed, usually by a scheduled job, and that someone has to watch it.
Describe an idempotent job with configuration per table, one partition per transaction, and templating so new partitions get indexes and grants.
Add operational hardening: advisory-lock singleton, lock_timeout with retries, runway and partition-count monitoring, archive-before-drop verification, and time-zone-safe boundaries.
Argue the whole control loop: declarative policy, reconcile semantics, build-versus-adopt, blast-radius limits on destructive steps, and the partition-count cost curve across a fleet of tables.
## Framing: a reconciler, not a script The naive version is a nightly shell script per table that runs `CREATE TABLE ... PARTITION OF` for tomorrow and `DROP TABLE` for the oldest day. It works until it does not: someone adds a table and forgets the script, a service is restored from backup with a gap, a run is skipped and the next run creates only one day. The durable design is a reconciler that reads a declarative policy and makes reality match it, so a run is a convergence step rather than an increment. **Policy table**, one row per partitioned table: partition interval (day/week/month), premake count, retention period, end-of-life action (drop, detach-and-archive, or move to cheap storage), and a template reference for indexes, constraints, storage parameters and grants. **Reconcile loop**, per table: compute the set of partitions that should exist over the window `[now - retention, now + premake]`, diff against the catalog, create what is missing, and apply the end-of-life action to whatever falls out of the window. ## Guarantees the worker must provide - **Idempotency.** Every action is create-if-absent or drop-if-present. A crashed run followed by a retry must be a no-op for work already done. - **Small transactions.** One partition per transaction. A long transaction holding DDL locks across many tables is a self-inflicted outage, and a partial failure must still leave a valid schema. - **Singleton execution.** Two schedulers firing concurrently (a duplicated cron, a failed-over node) must not race on the same DDL. An advisory lock held for the duration of the run is the cheap guard. - **Bounded lock waiting.** Every DDL sets a short `lock_timeout` and retries with backoff and jitter. Without it, one maintenance step waiting for an exclusive lock puts every subsequent query in the lock queue behind it: the classic maintenance-induced outage. - **Templating.** New partitions must arrive complete: indexes matching the parent's, check constraints, fillfactor and other storage settings, privileges (which are not inherited when granted directly on siblings), and an ANALYZE so the planner is not working from empty statistics. - **Auditability.** Log every created and removed partition with row counts and sizes, and keep a dry-run mode. Deleting data on a schedule deserves the same review as any destructive migration. ## Monitoring that actually catches failures The dominant real-world failure is not an erroring job, it is a job that stopped running: a disabled cron, a migrated host, a container without the scheduler sidecar. Alert on observable state: - **Runway** per table: hours until the newest partition's upper bound. Page when it drops below several times the run interval. This single metric catches skipped runs, stalled schedulers, and misconfigured intervals. - **Retention drift**: age of the oldest partition versus policy, which catches a drop step that has been failing on a lock for a week. - **Partition count and total relations**: planning time, catalog size and lock-table pressure all grow with partition count; a runaway premake or a too-fine interval shows up here first. - **Catch-all partition row count**, if one exists: it must be zero. - **Time since last successful reconcile**, exported by the worker itself. ## Failure modes to name - **Boundary and time-zone bugs.** Bounds are literal timestamps; a session in a different zone, or a daylight-saving transition, can create a gap or an overlap exactly at midnight. Standardise on UTC bounds and test the rollover. - **Late-arriving data.** Events replayed after their partition was dropped will fail to insert. Decide explicitly whether they are dropped, dead-lettered, or written to a rescue table. - **Retention removing data someone still needs.** Retention is a product decision with legal weight in both directions (regulatory retention minimums and deletion obligations). Make the policy visible in configuration, require review to change it, and keep the archive path if there is any doubt. - **Blocking DDL.** Covered above: lock timeouts, quiet windows, and concurrent detach where available. - **Too many partitions.** Planning cost, memory per plan, lock acquisition per partition and catalog bloat all scale with partition count. Choose the coarsest interval the retention policy allows, and revisit when tables grow. - **Archive that silently fails.** If the flow is detach, export, drop, verify the export before the drop, and alert on detached tables that were never archived. ## Build versus adopt A proven implementation already encodes most of this: pg_partman keeps a configuration table per partitioned parent, premakes partitions ahead of time, applies retention with either drop or detach, uses a template table for attributes that are not inherited, and runs from a background worker or an external scheduler. The principal-level answer is usually "adopt the standard tool, own the monitoring and the retention policy", with a bespoke reconciler justified only by a requirement the tool does not cover.
- A team proposes hourly partitions for a table with 18 months of retention. What is your reaction?That is roughly 13,000 partitions, which pushes cost into query planning, lock acquisition, catalog size and per-plan memory, and makes every maintenance operation touch far more relations. Ask what the hourly granularity buys: if it is retention granularity, daily is almost always enough; if it is pruning for hot recent data, a coarser interval plus an index on the key usually performs better. If sub-day granularity is genuinely required, consider a coarse interval for old data and a finer one only for the recent window.
- How do you make a scheduled retention job safe against deleting data the business still needs?Keep retention in reviewed configuration rather than in code, so changing it is a visible, approved change. Prefer detach-then-archive over drop where storage cost allows, and verify the archive succeeded before dropping. Add a guard that refuses to remove more than a configured number of partitions or bytes in one run, run in dry-run mode first on new tables, and log every removal with row counts so the action is auditable.
saying these in an interview costs you the question
- Alerting only on job failure, so a scheduler that stopped firing goes unnoticed until inserts fail.
- Doing all creates and drops in one long transaction holding DDL locks across many tables.
- Running maintenance DDL with no lock timeout, turning a fast operation into a lock-queue outage.
- Assuming new partitions inherit everything, so privileges or statistics are missing on the newest, hottest data.
- Writing a bespoke reconciler when an established partition-maintenance extension already covers the requirements.