Schemas and Connections

Configure Postgres schema usage in Sync Streams/Rules queries, including wildcard schemas for schema-per-tenant setups, and connect to high-availability replicas.

Schemas (Postgres)

When no schema is specified, the Postgres public schema is used for every query. A different schema can be specified as a prefix:

-- Note: the schema must be in double quotes
SELECT * FROM "other"."assets"

Wildcard Schemas (Postgres)

Wildcard schemas require Sync Streams and PowerSync Service v1.24.0 or later. They are currently only supported for Postgres connections.

Use % as a wildcard in the schema name to match tables with the same name across multiple schemas. "%" matches every schema, and a prefix such as "tenant_%" matches every schema whose name starts with tenant_. The wildcard can only be the last character of the schema name. Postgres system schemas (pg_* and information_schema) are never matched.

Combine a wildcard schema with the schema() function, which returns the schema each row was replicated from, to filter rows by schema. This supports schema-per-tenant databases (a single database with one identical schema per tenant): one stream covers every tenant schema, and each client syncs only its own tenant’s data, resolved from a JWT claim.

config:
  edition: 3

streams:
  work_orders:
    query: SELECT * FROM "%".work_orders WHERE work_orders.schema() = auth.parameter('tenant_schema')

In this example, rows are grouped into a bucket per schema, and each client syncs only the bucket matching the tenant_schema claim in its JWT. Rows from all matched schemas sync into a single client-side table, named after the table in the query (work_orders here).

Each matched table must be part of the PowerSync publication. Tables that are not in the publication are skipped.

High Availability / Replicated Databases (Postgres)

When the source Postgres database is replicated, for example with Amazon RDS Multi-AZ deployments, specify a single connection with multiple host endpoints. Each host endpoint will be tried in sequence, with the first available primary connection being used.

For this, each endpoint must point to the same physical database, with the same replication slots. This is the case when block-level replication is used between the databases, but not when streaming physical or logical replication is used. In those cases, replication slots are unique on each host, and all data would be re-synced in a fail-over event.

Multiple Separate Database Connections (Planned)

This feature will be available in a future release. See this item on our roadmap.

In the future, it will be possible to configure PowerSync with multiple separate source database connections, where each connection is concurrently replicated.

You should not add multiple connections to multiple replicas of the same database — this would cause data duplication. Only use this when the data on each connection does not overlap.

It will be possible for each connection to be configured with a “tag”, to distinguish these connections in Sync Rules. The same tag may be used for multiple connections (if the schema is the same in each).

By default, queries will reference the “default” tag. To use a different connection or connections, assign a different tag, and specify it in the query as a schema prefix. In this case, the schema itself must also be specified.

-- Note the usage of quotes here
SELECT * FROM "secondconnection.public"."assets"