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?

Improve this question
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
    – a_horse_with_no_name
    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.
    – CadentOrange
    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.
    – a_horse_with_no_name
    Mar 12, 2014 at 17:17

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

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

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