Pg_shard – PostgreSQL extension to scale out real-time reads and writes

Hey, Jason from Citus Data here (one of pg_shard's authors).

Yes: if you have high availability needs, PostgreSQL's streaming replication feature can mitigate this failure risk by providing a hot standby for your master; otherwise, regular backups of the master can suffice (pg_shard's metadata is stored in regular PostgreSQL tables).

See our documentation page for more answers to questions like this: http://citusdata.com/docs/pg-shard

Huge fan of Citus Data. Their column store for postgres is really useful for building data warehouses:

https://github.com/citusdata/cstore_fdw

Nice work. But I wonder how they handle table alterations, I couldn't see they mentioned on the docs. Is it possible at all? If it is, since pg_shard doesn't support transactions, what if alteration fails ?

At work we're in the midst of rolling out a sharded Postgres platform based on http://www.craigkerstiens.com/2012/11/30/sharding-your-datab..., with the sharding implemented at the application level. The biggest piece of complexity in that post is around designing the sharding in such a way that you can gracefully add more shards later.

Having read the pg_shard readme, it's not clear to me how it addresses that issue. I'd need to have a really clear idea how to handle scaling my cluster before committing to a sharding solution.

We'll update our FAQ with a detailed answer to this question.

As a summary, the user specifies the shard and replication count as they are sharding their table. For example, if you have 4 nodes, you may pick 256 as the initial shard count. That way, you'll have ample room to grow as you add new nodes to your cluster.

When you pick 256 shards over 4 worker nodes, pg_shard will create them in a round-robin fashion. Shard #1 will go to node A and B, shard #2 will go to node B and C, and so forth. This has the advantage that when one of the worker nodes fail, the remaining 3 nodes evenly take the additional work. Also, when you add a new node to the cluster, you can gradually rebalance some of the shards by moving them to the new node.