Configuration reference#

pgpartix reads one YAML file or every .yaml and .yml file in a directory. The same configuration drives both phases of the lifecycle, run by a single command, pgp-run-lifecycle:

• its make phase generates DDL for partitions that should exist;
• its expire phase generates DDL for partitions whose upper bounds have passed the retention window.

Neither phase applies the generated lifecycle migration to the source database. They inspect the PostgreSQL database and write reviewable SQL files for the repository's normal migration process.

Minimal configuration#

---
lifecycle:
  directory: migrations/partitions
  partition:
    naming:
      template: "{parent_table_schema}__{parent_table_name}__YYYY_MM"
    interval: 1 month
    retention:
      interval: 12 months
  tables:
    - schema: public
      name: transactions

lifecycle.tables is required. Partition creation is skipped for a table when partition.interval is omitted; when partition.interval is present, partition.naming.template must also be present, globally or on that table. Expiration produces no SQL when retention.interval is omitted or null.

The output directory must exist before the command runs. The partitioned tables, configured schemas, tablespaces, and optional template tables must also exist in the database being inspected.

Always quote naming, description, and output filename template values that include {...} placeholders, as shown above. YAML treats a leading { as the start of a flow mapping, so an unquoted value such as template: {parent_table_schema}__{parent_table_name}__YYYY_MM fails to parse as the intended string.

Inheritance and overrides#

Options under lifecycle apply to all tables. A table can override description, output.file.naming.template, or individual partition options.

lifecycle:
  partition:
    interval: 1 month
    retention:
      interval: 12 months
      detach_first: true
      detach_concurrently: true
  tables:
    - schema: public
      name: transactions
    - schema: public
      name: audit_events
      partition:
        interval: 1 week
        retention:
          interval: 26 weeks
          detach_only: true
          detach_first: false
          detach_concurrently: false

Overrides are resolved per field, including explicit false values. In the example, audit_events inherits unrelated defaults but disables the global detach settings.

Top-level lifecycle options#

Table entries#

Output filenames#

output.file.naming.template supports the following placeholders:

The descriptions themselves support {parent_table_schema} and {parent_table_name}. These are resolved before the description is inserted into the filename.

lifecycle:
  description:
    make: "Make partitions for {parent_table_schema}_{parent_table_name}"
  output:
    file:
      naming:
        template: "V{integer:4}__{description}.{direction}.sql"

Multiple successful tables may target one filename, depending on the provided output.file.naming.template. pgpartix formats each table's SQL independently and appends the successful results in configuration order. A failed table does not discard successful output and does not contribute partial SQL. The command still exits nonzero and reports every failed table so automation remains visibly unhealthy.

Partition creation options#

These options are consumed by the make phase of pgp-run-lifecycle.

• Setting partition.interval without a resolvable partition.naming.template fails with partition name template is required when partition interval is specified.
• A zero interval fails with interval cannot be zero.
• partition.past and partition.future must be nonnegative.

Partition naming#

Partition names support {parent_table_schema}, {parent_table_name}, and PostgreSQL to_char date/time patterns. Common patterns include:

Use a naming pattern whose precision distinguishes every configured interval. A monthly interval with only YYYY, for example, would produce duplicate names.

Start timestamps and existing partitions#

• NOW starts from the current timestamp in lifecycle.timezone. This is the default, so omitting partition.start_timestamp entirely is enough to get started.
• An RFC 3339 value provides a stable explicit start point.
• LATEST_PARTITION uses the upper bound of the most recent attached partition and fails clearly when no such partition exists.

Whenever the target table already has an attached partition, its upper bound always overrides partition.start_timestamp, regardless of which of the above three forms was configured. This means a fixed RFC 3339 value or NOW only matters for the table's first run; every subsequent run picks up from the latest existing partition instead, so generation always moves forward from where partitions were last created and never re-evaluates or rewinds the start point. For both NOW and an explicit RFC 3339 value, the make phase checks for an existing latest partition first and logs which one it used, so this override is always visible rather than silent; the configured value is only actually used when the table has no partitions yet.

Supported parent tables#

Current generation and expiration support:

• PostgreSQL 14 or newer;
• single-column RANGE partitioning;
• partition keys of date, timestamp, timestamptz, integer, bigint, or UUIDv7-compatible uuid values.

List/hash partitioning, multicolumn partition keys, and other key data types are rejected with table-specific errors.

Template tables and object replication#

template selects a relation whose objects should be reproduced on newly generated partitions:

tables:
  - schema: public
    name: transactions
    template:
      schema: public
      name: transactions_template

It may also be one of:

• CURRENT_PARTITION: the partition containing the current time;
• LATEST_PARTITION: the attached partition with the greatest upper bound;
• DEFAULT_PARTITION: the parent's default partition.

pgpartix replicates template constraints, indexes, and user triggers that are not already represented by the parent table. Trigger enabled/disabled state is preserved. NOT NULL attributes are part of the table's logical data model and are inherited through PostgreSQL partitioning rather than replicated as separate template constraints.

The selected template must exist. Symbolic sources fail with a useful message when no matching partition exists.

Storage parameters#

partition:
  storage:
    mode: merge
    parameters:
      fillfactor: 80
      autovacuum_enabled: true
      toast.vacuum_index_cleanup: false

inherit and merge are most useful with template. Storage parameter names and values are rendered into PostgreSQL WITH (...) clauses; PostgreSQL remains the authority on whether a parameter is valid for the generated relation.

Index configuration#

partition:
  index:
    naming:
      template: "{partition_name}_{index_keys}_{index_type}_idx{ordinal}"
    tablespace: fast_indexes

partition.index.tablespace moves replicated indexes to an existing tablespace. When partition.index.naming.template is null or omitted, PostgreSQL chooses names for replicated indexes. A custom template supports:

• {parent_table_schema} and {parent_table_name};
• {partition_schema} and {partition_name};
• {index_keys};
• {index_type}, such as btree;
• {ordinal}, empty for the first equivalent key set and numbered for additional ones.

Unique, partial, expression, and storage-parameter-bearing indexes are preserved from the template definition.

Constraint naming#

partition:
  constraint:
    naming:
      template: "{partition_name}_{constraint_keys}_{constraint_suffix}{ordinal}"
      primary_key:
        template: "{partition_name}_pkey"
      unique_key:
        template: "{partition_name}_{constraint_keys}_key{ordinal}"
      foreign_key:
        template: "{partition_name}_{constraint_keys}_fkey{ordinal}"
      check:
        template: "{partition_name}_{constraint_keys}_check{ordinal}"
      exclusion:
        template: "{partition_name}_{constraint_keys}_excl{ordinal}"

The generic naming.template is the fallback for a type without its own template. The primary-key default is {partition_name}_pkey; other types fall back to the generic template.

Available placeholders are:

• {parent_table_schema} and {parent_table_name};
• {partition_schema} and {partition_name};
• {constraint_keys};
• {constraint_suffix}: pkey, key, fkey, check, or excl;
• {ordinal}, empty for the first matching constraint and numbered thereafter.

The supported type keys are primary_key, unique_key, foreign_key, check, and exclusion.

Trigger naming#

partition:
  trigger:
    naming:
      template: "{partition_name}_{event_timing}_{trigger_event}_{trigger_function_name}{ordinal}"

When partition.trigger.naming.template is null, it defaults to {partition_name}_{event_timing}_{trigger_event}_{trigger_function_name}{ordinal}. A custom template supports:

• {parent_table_schema} and {parent_table_name};
• {partition_schema} and {partition_name};
• {event_timing}, such as BEFORE, AFTER, or INSTEAD OF;
• {trigger_event}, such as INSERT or UPDATE OR DELETE;
• {trigger_function_schema} and {trigger_function_name};
• {ordinal}, empty for the first trigger sharing the same timing, event, and body, and numbered for additional ones.

Trigger enabled/disabled state is preserved regardless of naming.

Expiration options#

These options are consumed by the expire phase of pgp-run-lifecycle.

Mode precedence is:

1. detach_only: true generates detach statements only.
2. Otherwise, detach_first: true generates detach followed by drop.
3. Otherwise, pgpartix generates direct DROP TABLE statements.

detach_concurrently has an effect only when a detach statement is generated.

PostgreSQL does not support DETACH PARTITION IF EXISTS. With idempotent: true, pgpartix can guard the parent table and drop statements, but detach operations remain only partially idempotent if a partition has already been detached outside the generated migration.

Connection environment#

The lifecycle command reads:

The command defaults these values to postgres, postgres, localhost, 5432, and postgres respectively for the disposable local cluster. Local-cluster users do not need to define them. External databases must override every value explicitly; see Database environment.

Pass a directory to -c to process multiple configuration files in sorted filename order.

Failure and publication behavior#

Each table is generated and formatted independently in a staging directory. At the end of a run:

• SQL from successful tables is published, including successful tables sharing one output file;
• failed and partially formatted table output is discarded;
• existing unrelated migrations remain untouched;
• every failed table and its PostgreSQL error are reported;
• the command exits with status 1 when any table failed.

This gives local and automated runs useful partial progress without disguising failures. Successful migrations can be reviewed or published while the nonzero exit status still identifies tables requiring intervention.

Complete example#

See config.sample.yaml for a current, annotated configuration containing global defaults, table overrides, naming templates, storage behavior, template replication, and expiration modes. The schema is available at schema.