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?

CadentOrange
  • 733
  • 1
  • 7
  • 10
  • 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 '14 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 '14 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 '14 at 17:17

1 Answers1

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

Nick Lashinsky
  • 31
  • 3