I have a Postgres database that has grown to the size where it is no longer feasible to store everything on a single database node. There is a Customer table in my schema where each row represents a (surprise!) customer. Every other table in my database is foreign keyed on this customer table and I would like to shard my database along customer ranges. For instance, I want customers with an id of 1 – 100 to go to database node A, 101 – 200 to go to database node B, and so on.
I've found information on table partitioning but I've found little else that shows me how I would enable database partitioning in Postgres.
What are my options for sharding a database in Postgres? If sharding is not possible, what are my alternatives?
Best Answer
To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard
It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application.
https://github.com/citusdata/pg_shard