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

At work we&#x27;re in the midst of rolling out a sharded Postgres platform based on <a href="http://www.craigkerstiens.com/2012/11/30/sharding-your-database/" rel="nofollow">http:&#x2F;&#x2F;www.craigkerstiens.com&#x2F;2012&#x2F;11&#x2F;30&#x2F;sharding-your-datab...</a>, 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.<p>Having read the pg_shard readme, it&#x27;s not clear to me how it addresses that issue. I&#x27;d need to have a really clear idea how to handle scaling my cluster before committing to a sharding solution.

It&#x27;s great to see this released as open source (LGPL) -- looks like a really useful extension.<p>I&#x27;ve always been a fan of using PostgreSQL wherever possible, and this extends &quot;where possible&quot;. As a YC S11 batchmate especially, I&#x27;m really proud of all the great contributions Citus Data has made, and what a useful tool CitusDB is (a bunch of other YC companies use it).

Huge fan of Citus Data. Their column store for postgres is really useful for building data warehouses:<p><a href="https://github.com/citusdata/cstore_fdw" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;citusdata&#x2F;cstore_fdw</a>

I wonder how it compares to the slightly more well established Postgres-XL.<p>There is definitely a different approach being taken as Postgres-XL has a supervisor&#x2F;loadbalancer, and pg_shard seems like every node is capable of doing all actions.<p>Excited to see it evolve.

Having a master node which delegates all the queries sounds like a single point of failure. Could this be avoided by having a failover master?

I don&#x27;t see any documentation around schema migrations. How is that normally solved on the Petabyte level?

If you want more details about pg_shard, have a look to this blog post.<p><a href="http://www.databasesoup.com/2014/12/whats-this-pgshard-thing.html" rel="nofollow">http:&#x2F;&#x2F;www.databasesoup.com&#x2F;2014&#x2F;12&#x2F;whats-this-pgshard-thing...</a><p>It explains a bit more what it does and doesn&#x27;t

Comparing this a bit with Postgres-XL, how mindful of data locality do I have to be when querying? Looks like this drops right in for existing apps, but I&#x27;d be concerned about long-term performance if I didn&#x27;t tailor my app code.

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

Wondering if any CitusData folks can speak to whether this is being used for production workloads anywhere yet. If so, could you speak to the size&#x2F;throughput of those deployments?

We have a sharding mechanism in our PHP framework and also Node as well, which can actually split shards which become too &quot;hot&quot; as determined by you. The whole system is online during splitting and only a small part if the system goes offline for 1 second before the final switchiver. No need to pre shard in the beginning, it actually splits according to usage later, into an unlimited number of shards.

Awesome work, I should give this a spin.<p>One of the issues I can see already is being able to support existing applications, especially ones that have transaction heavy workflows. I have the same issue with Postgres XC, supporting transactions, but not supporting savepoints.<p>But this looks like a completely different use case for postgres, as a sort of pseudo-noSQL type db.

This is another evidence of how extensible and flexible is PostgreSQL. Great work!

Very cool to see this open sourced.<p>Any advice on migration process? Transferring a high write-throughput postgres instance to a multi-pg deployment with pg_shard feels pretty daunting.

Gotta love how they opensource bits of their product.