Sharded Databases

Sharding is often used in Postgres to handle higher data volumes.

PowerSync cannot replicate foreign tables, but does have other options available to support sharded databases.

The primary options are:

1. Use a separate PowerSync instance per database.

2. Add a connection for each database in the same PowerSync instance.

Where feasible, using separate PowerSync instances would give better performance and give more control over how changes are rolled out, especially around sync rule reprocessing.

Some specific scenarios:

1. Different tables on different databases

This is common when separate "services" use separate databases, but multiple tables across those databases need to be synchronized to the same users.

Use a single PowerSync instance, with a separate connection for each source database. Use a unique connection tag for each source database, allowing them to be distinguished in the sync rules.

2a. All data for any single customer is contained in a single shard

This is common when sharding per customer account / organization.

In this case, use a separate PowerSync instance for each database.

2b. Most customer data is in a single shard, but some data is in a shared database

If the amount of shared data is small, still use a separate PowerSync instance for each database, but also add the shared database connection to each PowerSync instance using a separate connection tag.

3. Only some tables are sharded

In some cases, most tables would be on a shared server, with only a few large tables being sharded.

For this case, use a single PowerSync instance. Add each shard as a new connection on this instance — all with the same connection tag, so that the same sync rules applies to each.