Partitioned Tables (Postgres)

Partitioned tables and wildcard table name matching

For partitioned tables in Postgres, each individual partition is replicated and processed using Sync Streams (or legacy Sync Rules).

To use the same queries and same output table name for each partition, use % for wildcard suffix matching of the table name:

Sync Streams

streams:
  user_todos:
    queries:
      # Wildcard matches all user partition tables (e.g. users_2024, users_2025)
      - SELECT * FROM "users_%" WHERE id = auth.user_id()
      # Wildcard matches all todo partition tables (e.g. todos_2024, todos_2025)
      - SELECT * FROM "todos_%" AS todos WHERE user_id = auth.user_id()

Sync Rules (Legacy)

  by_user:
    # Use wildcard in a parameter query
    parameters: SELECT id AS user_id FROM "users_%"
    data:
      # Use wildcard in a data query
      - SELECT * FROM "todos_%" AS todos WHERE user_id = bucket.user_id

The wildcard character can only be used as the last character in the table name.

When using wildcard table names, the original table suffix is available in the special _table_suffix column. This works the same way in both Sync Streams and Sync Rules:

Sync Streams

streams:
  active_todos:
    query: SELECT * FROM "todos_%" AS todos WHERE _table_suffix != 'archived'

Sync Rules (Legacy)

SELECT * FROM "todos_%" AS todos WHERE _table_suffix != 'archived'

When no table alias is provided, the original table name is preserved.

publish_via_partition_root on the publication is not supported — the individual partitions must be published.