Partitioned Tables (Postgres)

Partitioned tables and wildcard table name matching

For partitioned tables in Postgres, each individual partition is replicated and processed using Sync Rules.

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

  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:

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.