pg-partsmith

PostgreSQL partition lifecycle management with extensible hooks.

A single library that covers the full PostgreSQL partition lifecycle: creating partitions ahead of time, detaching expired ones, and dropping orphans — with a middleware system for injecting custom logic at each step.

Features

• Async and sync — pg_partsmith.aio on the SQLAlchemy async engine, pg_partsmith.sync on the classic sync engine
• Full lifecycle — create ahead, detach expired, drop orphans in one call
• Nested partitioning — RANGE(time) → HASH(column) and → LIST(column) trees, reconciled towards the configured shape on every run
• Time-free tables — HASH or LIST roots with a fixed partition set, managed by the same reconciler
• Composite keys — multi-column partition keys, with trailing columns bounded by MINVALUE
• Encoded partition keys — partition by time even when the key is a UUIDv7 or another sortable id
• Extensible hooks — 6 hook points (before/after create, detach, drop)
• Multiple strategies — hourly, daily, weekly, monthly, quarterly, yearly + fully custom
• Distributed locking — PostgreSQL advisory locks (built-in) or Redis
• Schema-aware — multi-schema support, independent of search_path
• Safe by default — refuses to drop tables not managed by this library
• Type-safe — full mypy compliance with Pydantic models
• Well-tested — 90%+ coverage with real PostgreSQL via testcontainers

Installation

pip install pg-partsmith

# With Redis distributed locks
pip install "pg-partsmith[redis-locks]"

Requirements: Python 3.11+, PostgreSQL 15+

Quick start

from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine

from pg_partsmith import (
    MonthPeriodCalculator,
    PartitionGranularity,
    PartitionStrategy,
    PartitionType,
    TablePartitionConfig,
)
from pg_partsmith.aio import (
    PartitionLifecycleService,
    PartitionMaintainer,
    PostgresAdvisoryLockManager,
    PostgresMetadataProvider,
    PostgresPartitionRepository,
)

engine = create_async_engine("postgresql+asyncpg://user:pass@host/db")

config = TablePartitionConfig(
    schema="public",
    table_name="events",
    partition_type=PartitionType.RANGE,
    partition_strategy=PartitionStrategy.TIME_BASED,
    partition_column="created_at",
    granularity=PartitionGranularity.MONTH,
    create_ahead_count=3,  # current month + next 2
    retention_count=12,
)


async def run_maintenance(engine: AsyncEngine) -> None:
    service = PartitionLifecycleService(
        repo=PostgresPartitionRepository(engine),
        metadata=PostgresMetadataProvider(engine),
        locks=PostgresAdvisoryLockManager(engine),
        period_calculator=MonthPeriodCalculator(),
    )
    maintainer = PartitionMaintainer(service)
    result = await maintainer.run_maintenance_safe(config)
    if result.success:
        print(
            f"created={result.created_count} "
            f"detached={result.detached_count} "
            f"dropped={result.dropped_count}"
        )
    else:
        print(f"error={result.error}")

Transaction semantics — every DDL operation (CREATE, ATTACH, DETACH, DROP) runs in its own connection and commits immediately. Use AsyncEngine, not AsyncSession. A subpartitioned branch is built detached and attached last, so an interrupted run leaves an unreachable table rather than a live partition that cannot route part of its keyspace.

Timezones — everything is UTC by default. For calendar partitions in a business timezone pass the same zone to both sides: MonthPeriodCalculator(tz=ZoneInfo("Europe/Moscow")) and PostgresPartitionRepository(engine, ddl_timezone="Europe/Moscow") — the service refuses a mismatched pair, so names and real bounds cannot silently drift apart. Hourly granularity is UTC-only (DST makes local hour names ambiguous).

Cancellation semantics — run_maintenance_safe() (and maintain_partitions()) always returns MaintenanceResult, including on asyncio.CancelledError.

Sync usage

Every class in pg_partsmith.aio has a synchronous twin in pg_partsmith.sync with the same name and API — built on the classic SQLAlchemy Engine instead of AsyncEngine:

from sqlalchemy import create_engine

from pg_partsmith import MonthPeriodCalculator
from pg_partsmith.sync import (
    PartitionLifecycleService,
    PartitionMaintainer,
    PostgresAdvisoryLockManager,
    PostgresMetadataProvider,
    PostgresPartitionRepository,
)

engine = create_engine("postgresql+psycopg2://user:pass@host/db")

service = PartitionLifecycleService(
    repo=PostgresPartitionRepository(engine),
    metadata=PostgresMetadataProvider(engine),
    locks=PostgresAdvisoryLockManager(engine),
    period_calculator=MonthPeriodCalculator(),
)
maintainer = PartitionMaintainer(service)
result = maintainer.run_maintenance_safe(config)

Hooks and custom lock managers implement the sync protocols from pg_partsmith.sync (plain methods instead of coroutines). Two behavioural differences from the async package:

• ddl_timeout_seconds is enforced server-side via PostgreSQL statement_timeout (per statement) rather than client-side around the whole operation.
• The Redis lock renews its TTL from a background thread; on renewal failure it logs a warning but cannot cancel the running maintenance (the TTL bounds a stale holder).

Nested partitioning

A time partition can itself be a partitioned table. Add a subpartition spec and each period is split along a second dimension:

from pg_partsmith import HashSubpartitionSpec

config = TablePartitionConfig(
    schema="public",
    table_name="events",
    partition_type=PartitionType.RANGE,
    partition_strategy=PartitionStrategy.TIME_BASED,
    partition_column="created_at",
    granularity=PartitionGranularity.WEEK,
    create_ahead_count=3,
    retention_count=12,
    subpartition=HashSubpartitionSpec(column="tenant_id", modulus=4),
)

events
├── events__2026_w35            PARTITION BY HASH (tenant_id)
│   ├── events__2026_w35__h0
│   ├── events__2026_w35__h1
│   ├── events__2026_w35__h2
│   └── events__2026_w35__h3
└── events__2026_w36 …

The two dimensions stay separate: time is the lifecycle dimension (create-ahead, retention, detach, drop, hooks all operate on the whole week) and the nested one is the distribution dimension. retention_count=12 keeps twelve weeks, not twelve leaves.

ListSubpartitionSpec splits a period into named value sets instead (RANGE(time) → LIST(region)), with an optional DEFAULT catch-all; the strategies also nest into each other.

Maintenance converges the actual tree towards the configured one on every run: missing buckets are created, a converged tree costs zero DDL, and anything that cannot be repaired safely — a bucket set at an older modulus, a partition from a previous policy, a shape the config does not describe — is left intact and reported through MaintenanceResult.issues. See the subpartitioning guide.

Tables partitioned without a time dimension

A table divided only by tenant or region has a fixed set of partitions — nothing is created ahead and nothing ages out. Configure it with root_layout:

config = TablePartitionConfig(
    table_name="issue_index",
    partition_type=PartitionType.HASH,
    partition_strategy=PartitionStrategy.HASH_BASED,
    partition_column="organization_id",
    root_layout=HashSubpartitionSpec(column="organization_id", modulus=16),
)

Such a table needs no period calculator, and maintenance is purely reconciliation: missing partitions are created, nothing is ever pruned.

Partitioning by an encoded key

When the partition key is a time-sortable identifier rather than a timestamp — a UUIDv7, a ULID, an epoch bigint — a boundary codec keeps the lifecycle reasoning in calendar periods while the DDL speaks the key's own language:

from pg_partsmith.boundaries import UUIDv7BoundaryCodec

codec = UUIDv7BoundaryCodec()

service = PartitionLifecycleService(
    repo=PostgresPartitionRepository(engine),
    metadata=PostgresMetadataProvider(engine, boundary_codec=codec),
    locks=PostgresAdvisoryLockManager(engine),
    period_calculator=WeekPeriodCalculator(boundary_codec=codec),
)

Partition names, create-ahead, and retention are unchanged; only the FOR VALUES FROM … TO … literals become UUIDs. Codecs are bidirectional, so retention and is_partition_closed keep working. See the boundary codec guide.

Multi-schema databases

If your database uses multiple schemas, set schema in TablePartitionConfig. The library schema-qualifies all catalog queries, DDL statements, and lock namespaces — behaviour becomes independent of search_path.

Orphan partitions

After detach, the repository writes a COMMENT marker on the detached table. Only marker-tagged tables are eligible for dropping, making cleanup safe even if the database contains similarly named tables not managed by this library.

Set marker_prefix explicitly on both PostgresPartitionRepository and PostgresMetadataProvider to ensure consistent orphan marker recognition across deployments.

Marker semantics — the COMMENT marker means "this library owns cleanup of this table". It survives pg_dump/restore (comments are dumped by default), so a restored copy of a marked table is again eligible for dropping. When repurposing such a table, clear its comment (COMMENT ON TABLE ... IS NULL) or restore with --no-comments.

DEFAULT partition reconciliation

When creating a new partition, if the DEFAULT partition contains rows belonging to the new range, pg-partsmith automatically:

1. Detects the conflict (CheckViolationError 23514)
2. Moves conflicting rows from DEFAULT to the new partition
3. Retries ATTACH PARTITION

The reconciliation is atomic and logged at INFO level.

TIMESTAMPTZ boundary semantics

For TIMESTAMP WITH TIME ZONE partition keys, PostgresPartitionRepository runs SET LOCAL TimeZone='UTC' before ATTACH PARTITION (ddl_timezone="UTC" default). Set ddl_timezone=None to disable this enforcement.

Safe drops

drop_partition() refuses to drop tables not tagged as orphans. To override:

repo = PostgresPartitionRepository(engine, drop_allow_unmanaged=True)

An attempt to drop an unmanaged table raises UnmanagedPartitionDropError.

Hooks (middleware)

from pg_partsmith.aio import BasePartitionLifecycleHooks, PartitionLifecycleService
from pg_partsmith.entities import PartitionInfo, TablePartitionConfig


class KafkaNotifyHooks(BasePartitionLifecycleHooks):
    def __init__(self, producer: KafkaProducer) -> None:
        self._producer = producer

    async def after_create(self, config: TablePartitionConfig, partition: PartitionInfo) -> None:
        await self._producer.send("partition.created", {"name": partition.name})

    async def before_drop(self, table_name: str, partition_name: str) -> None:
        await export_to_cold_storage(table_name, partition_name)


service = PartitionLifecycleService(
    repo=repo,
    metadata=metadata,
    locks=locks,
    period_calculator=calculator,
    hooks=[KafkaNotifyHooks(producer)],
)

Hook points

before_* exceptions abort the operation. after_* exceptions are logged but do not affect result.success.

Extensibility

from pg_partsmith.aio import PostgresPartitionRepository


class AuditedPartitionRepository(PostgresPartitionRepository):
    async def drop_partition(self, partition_name: str) -> None:
        await self._audit_log.record("drop", partition_name)
        await super().drop_partition(partition_name)

Lock managers

PostgreSQL advisory locks (default)

from pg_partsmith.aio import PostgresAdvisoryLockManager

locks = PostgresAdvisoryLockManager(engine, prefix="myapp")

Pool sizing — advisory locks hold a dedicated connection for the duration of maintenance. Ensure your pool has spare capacity, or use a separate AsyncEngine for the lock manager (a pool of 1 will deadlock).

Redis distributed locks

pip install "pg-partsmith[redis-locks]"

from redis.asyncio import Redis
from pg_partsmith.aio import RedisDistributedLockManager

locks = RedisDistributedLockManager(
    redis_client=Redis.from_url("redis://localhost"),
    prefix="myapp:partitioner",
    ttl_seconds=300,
)

Period strategies

from pg_partsmith.strategies import BasePeriodCalculator
from pg_partsmith.entities import Period


class QuarterPeriodCalculator(BasePeriodCalculator):
    def current_period(self) -> Period: ...
    def format_partition_name(self, table_name: str, period: Period) -> str: ...
    def parse_partition_name(self, partition_name: str) -> Period | None: ...
    def get_boundaries(self, period: Period) -> tuple[str, str]: ...

Scheduler integration

from pg_partsmith.aio import maintain_partitions

scheduler.add_job(
    maintain_partitions,
    "cron",
    hour=2,
    kwargs={"maintainer": maintainer, "config": config},
)

API reference

pg_partsmith

Entities — Period, PartitionInfo, TablePartitionConfig, MaintenanceResult, MaintenanceIssue, MaintenanceIssueStep

Topology — HashSubpartitionSpec, ListSubpartitionSpec, ListGroup, SubpartitionSpec, PartitionNode, RangeBounds, HashBounds, ListBounds, DefaultBounds, PartitionBounds, SubpartitionBounds

Reconciliation — SubpartitionPlan, SubpartitionAction, SubpartitionReconcileResult, TopologyFinding, TopologyReason, plan_subpartitions

Boundary codecs — RangeBoundaryCodec, UUIDv7BoundaryCodec

Helpers — qualify, split_qualified_name

Enums — PartitionType, PartitionGranularity, PartitionStrategy

Exceptions — PartitionError, PartitionAlreadyExistsError, PartitionNotFoundError, PartitionAttachedError, PartitionDetachInProgressError, InvalidPartitionConfigError, LockAcquisitionError, DropRetryExhaustedError, UnmanagedPartitionDropError, PartitionTopologyError, UnsupportedCapabilityError

Protocols — PeriodCalculator, TimezoneAwareCalculator, DdlTimezoneAware, BoundaryDecoder

Strategies — BasePeriodCalculator, HourPeriodCalculator, DayPeriodCalculator, WeekPeriodCalculator, MonthPeriodCalculator, QuarterPeriodCalculator, YearPeriodCalculator, get_period_calculator

pg_partsmith.aio

Protocols — PartitionRepository, PartitionMetadataProvider, LockManager, SubpartitionRepository, NestedPartitionMetadata, CompositeKeyRepository, CompositeKeyMetadata

Hooks — BasePartitionLifecycleHooks

Service — PartitionLifecycleService

PostgreSQL — PostgresPartitionRepository, PostgresMetadataProvider

Lock managers — PostgresAdvisoryLockManager, RedisDistributedLockManager

Orchestration — PartitionMaintainer, maintain_partitions

pg_partsmith.sync

Synchronous mirror of pg_partsmith.aio — same names, same layout, plain methods built on the sync SQLAlchemy Engine.

Migrating from a hand-rolled partitioner

The migration guide covers the traps: retention is a count, not a distance (old_distance + 1); legacy detached partitions are adopted with repo.adopt_partition(...) instead of disabling safe-drop via drop_allow_unmanaged; writers that need one specific partition use service.ensure_partition(config, period); scheduled ticks isolate step failures with maintain_lifecycle(..., continue_on_error=True) → result.issues; export pipelines check metadata.is_partition_closed(name, settle_seconds=...) before finalizing; data that predates the create-ahead window gets partitions via service.ensure_partitions(config, periods).

For a worked example of adopting a RANGE(UUIDv7) → HASH(tenant) event store — including a non-uniform bucket history and custom partition names — see Example: an event store with TIME → HASH.

Development

make install          # uv sync --group dev
make check            # ruff + mypy
make test-unit        # unit tests (no Docker)
make test-integration # integration tests (Docker required)
make test             # all tests with coverage
make docs-serve       # local docs preview

See CONTRIBUTING.md for the full guide.

License

Apache 2.0