5

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?

3
  • I wonder how big your customer table is. We are happily using tables with over 50 million rows. You must have some really good running business if you have more than 50 million customers. If you really, really need this you might want to take a look at Postgres-XC Mar 12, 2014 at 17:08
  • Our problem isn't the customer table as that's fairly small, which is hundreds. The problem is that each customer has a whole host of other tables, and these can be fairly large. See the note about each table in my database being foreign keyed on the customer table. Mar 12, 2014 at 17:14
  • 3
    Please define "fairly large". Unless each and every table goes into hundreds of million of rows (and you have like hundreds of those tables) I don't think adding the complexity of sharding is worth the effort. Mar 12, 2014 at 17:17

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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