0

As a hypothetical example, say I wanted to shard a PostgreSQL database for a multi-tenant setup. Each of the tenants has an ID that is stored as a foreign key in a column on every record of every table. Is it possible to then shard the database by the tenants' ID for each record and allow querying by the shard?

Say there are four physical servers, all with one shard, and that there are four tenants:

  • Server 1 - Shard 1 - Tenant 1's data
  • Server 2 - Shard 2 - Tenant 2's data
  • Server 3 - Shard 3 - Tenant 3's data
  • Server 4 - Shard 4 - Tenant 4's data

Would it then be possible to allow an application such as a web framework, knowing what tenant it is serving, to then connect to and query (including reading and writing) the respective server with the respective shard?

Improve this question
1
  • You might want to look at Citus which is an extension which facilitates sharding - "Citus gives you the Postgres you love, plus the superpower of distributed tables. 100% open source. Now with schema-based and row-based sharding—plus Postgres 16 support!" - might be worth a look for your use-case? But, unless you've masses of data per customer, why go to the expense of a machine per client? That seems like overkill to me!
    – Vérace
    Commented Mar 2 at 21:32

1 Answer 1

Reset to default
1

Would it then be possible to allow an application such as a web framework, knowing what tenant it is serving, to then connect to and query (including reading and writing) the respective server with the respective shard?

Yes, but it has nothing to do with Postgres. You have N independent Postgres instances, so your application is free to connect to any (or all) of them.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.