28.5 分区生命周期

DELETE 500 million expired rows
  -> row locks / WAL / dead tuples / index cleanup / vacuum / replica replay

DETACH one expired partition
  -> catalog and lock operation
      -> standalone table
          -> archive / validate / drop

event_time_semantics: UTC timestamptz
retention: 400 days
retirement_unit: month
late_arrival: 7 days
future_precreate: 3 months
archive_retention: 7 years

partition key
range bounds
timezone
default partition policy
precreate horizon
detach cadence

query predicate
retention cut
maintenance unit

CREATE TABLE app.events (
  event_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE app.events_2026_08
  PARTITION OF app.events
  FOR VALUES FROM ('2026-08-01 00:00:00+00')
             TO   ('2026-09-01 00:00:00+00');

[2026-08-01 00:00Z, 2026-09-01 00:00Z)

generate future partitions
validate exact non-overlapping bounds
create local indexes
apply owner/grants/comments/storage parameters
ANALYZE when populated
alert on last future boundary

SELECT
  parent.relname AS parent,
  child.relname AS partition,
  pg_get_expr(child.relpartbound, child.oid) AS bound
FROM pg_inherits AS i
JOIN pg_class AS parent ON parent.oid = i.inhparent
JOIN pg_class AS child ON child.oid = i.inhrelid
WHERE parent.oid = 'app.events'::regclass
ORDER BY child.relname;

SELECT *
FROM pg_partition_tree('app.events');

CREATE TABLE app.events_2026_08_stage
  (LIKE app.events INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

ALTER TABLE app.events_2026_08_stage
  ADD CONSTRAINT events_2026_08_bound
  CHECK (
    occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
    AND occurred_at < TIMESTAMPTZ '2026-09-01 00:00:00+00'
  );

-- load, cleanse, build indexes, validate

ALTER TABLE app.events
  ATTACH PARTITION app.events_2026_08_stage
  FOR VALUES FROM ('2026-08-01 00:00:00+00')
             TO   ('2026-09-01 00:00:00+00');

silent routing of bad/future data
attach scan/lock cost
DETACH CONCURRENTLY restriction on that parent
data migration before new range attach

ANALYZE app.events_2026_08;
ANALYZE app.events;

row estimate drift
join order change
partition-wise plan quality loss

columns / generated expressions
constraints
indexes / INCLUDE / predicates
storage parameters / fillfactor
tablespace
owner / grants / RLS
publication policy
comments
autovacuum overrides

CREATE TABLE child (LIKE parent);

ALTER TABLE app.events
  DETACH PARTITION app.events_2024_01;

parent no longer routes/scans it
child remains as standalone table
attached child indexes detach from parent indexes
cloned triggers are removed
data remains queryable by standalone name

online dataset
  -> detached immutable dataset
      -> archive
          -> restore validation
              -> deletion

ALTER TABLE app.events
  DETACH PARTITION app.events_2024_01 CONCURRENTLY;

cannot run inside transaction block
not allowed when parent has a default partition
only one partition per parent may be pending detach
foreign-key related tables may acquire SHARE locks
old transactions can prolong the wait

ALTER TABLE app.events
  DETACH PARTITION app.events_2024_01 FINALIZE;

retention cutoff immutable
late-arrival window closed
backfill jobs stopped
application routes no new row to old range
timezone/cutoff reviewed
no open transaction still writes old partition

database/schema/table
partition bound
owner/grants
columns/types/collations
constraints/indexes
row-level security

SELECT
  count(*) AS rows,
  min(event_id) AS min_id,
  max(event_id) AS max_id,
  min(occurred_at) AS min_time,
  max(occurred_at) AS max_time,
  sum(amount) AS amount_sum
FROM app.events_2024_01;

format/version
compression/encryption
object URI
bytes
cryptographic hash
created_at
retention/legal hold

restore target
row/type/constraint verification
logical aggregates/digests
query spot checks
elapsed time
tool versions

COPY
  simple data stream
  schema/privileges not included
  explicit order and format needed

pg_dump table
  schema/data options
  dependency-aware archive
  restore tooling and version policy needed

base backup
  cluster physical recovery
  not a per-partition logical archive

md5(string_agg(all_rows...))

events_2024 rows         10,000
parent total             15,000

manifest
  rows                   10,000
  id range               1..10,000
  amount sum             499,950.00
  logical digest         recorded

DETACH CONCURRENTLY      pass
parent after detach      5,000
standalone               10,000

CSV bytes                547,894
CSV SHA-256              cd7d54e4...a9a41d0d

restore check rows       10,000
restore digest           equal

drop standalone          only after equality

drop before restore validation
row mismatch
digest mismatch
empty archive
parent count mismatch
force cleanup

DELETE FROM app.events
WHERE occurred_at < now() - interval '400 days';

row locks
large transaction / long snapshot
WAL and archive volume
replica replay
dead heap tuples
dead index tuples
autovacuum backlog
relation growth before reuse
rollback/retry cost

WITH victim AS (
  SELECT ctid
  FROM app.events
  WHERE occurred_at < $1
  ORDER BY occurred_at
  LIMIT 10000
  FOR UPDATE SKIP LOCKED
)
DELETE FROM app.events AS e
USING victim AS v
WHERE e.ctid = v.ctid;

row-by-row physical change
  -> partition metadata change

predicate cuts through every partition
legal hold retains arbitrary rows
tenant records mixed in same partition
foreign keys prevent independent detach
late arrivals keep changing old ranges
application directly names leaf tables
default partition contains mixed data

Can an event_id be unique without time?
Who references expired rows?
Should archive preserve FK graph?
Can referenced partitions retire independently?

timestamptz vs timestamp
UTC and business timezone
NOT NULL / CHECK
identity and uniqueness
retention metadata

partition_key:
  column: occurred_at
  type: timestamptz
  canonical_zone: UTC
  null_allowed: false
retention_cutoff_semantics: event_time

partition pruning
parameterized plan behavior
parent/leaf statistics
join estimate
EXPLAIN evidence

representative predicates prune expected leaves
generic/custom plans both reviewed
parent ANALYZE after distribution change
planning time acceptable at partition count

lock compatibility
lock_timeout
preflight holders
expand/contract
canary and rollback
DDL queue behavior

change:
  attach_or_detach: ...
  lock_budget: ...
  transaction_block: false
  old_snapshot_gate: ...
  interrupted_state: ...
  finalize_or_rollback: ...

time bucketing
late/out-of-order events
rollup/downsample
spatial/temporal retention
hot/warm/cold tiers

late arrival horizon
immutable cutoff
rollup completion watermark
archive query contract

precreate
explicit analyze
seal
detach
archive manifest
restore validation
drop
monitor and audit

type/constraint semantics          ch04
  -> plan/pruning/statistics       ch07
      -> safe DDL release          ch11
          -> time-domain policy    ch16
              -> retirement SOP    ch28

CREATE TABLE ops.partition_lifecycle (
  parent_table regclass NOT NULL,
  partition_table regclass,
  range_start timestamptz NOT NULL,
  range_end timestamptz NOT NULL,
  state text NOT NULL CHECK (
    state IN (
      'planned','attached','sealed','detached',
      'archived','restore_verified','dropped'
    )
  ),
  manifest_uri text,
  manifest_sha256 text,
  approved_by text,
  changed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  PRIMARY KEY (parent_table, range_start)
);

partition key and timezone semantics
retention and late-arrival horizon
future partitions precreated
bound gaps/overlaps checked
leaf schema/index/grant consistency
attach CHECK avoids scan where possible
default partition policy
parent explicit ANALYZE
dependency/FK inventory
old snapshot and lock budget
DETACH CONCURRENTLY restrictions
interrupted FINALIZE plan
object/logical/file/restore manifest
drop only after restore proof
online/archive ownership transition