Citus 12: Schema-based sharding for PostgreSQL

Nice to see this on HN :)<p>The high-level is: You enable a setting and every CREATE SCHEMA creates a new shard. All the tables in the schema will be co-located so you can have efficient joins &amp; foreign keys between the tables.<p>On top of that, you can also have reference tables that are replicated to all nodes, again for fast joins &amp; foreign keys with all schemas.<p>Everything else is about making every PostgreSQL feature work as seamlessly as if there was no sharding. You can still do things like transactions across schemas, create and use custom types, access controls, work with other extensions, use procedures, etc.

Does Oracle support anything like this? Or <i>any other</i> DBMS in widespread use, for that matter?<p>The promise behind this approach to DB sharding has great potential. Simultaneously impressive, novel, and badass.<p>I wish this had been available ten years ago at a few of my startups!

Hm, question for people a bit more familiar with Postgres -- what is meant by &quot;schema&quot; here?<p>My definition is &quot;the columns and column types of a table&quot;, but, that doesn&#x27;t seem to make sense with what they&#x27;re talking about here (&quot;large&quot; and &quot;small&quot; schemas probably aren&#x27;t referring to wide and narrow tables for example, and I don&#x27;t see how sharding by my definition of &quot;schema&quot; could even make sense anyways)

Having used schema based sharding in postgres before, I hope citus has a way around connection pooling and pgbouncer, as if you use pgbouncer transaction pooling your search_path could disappear at any time.

What happens if one node lost their shards due to external event? (e.g. Disk corruption, physcially destroyed like OVH Cloud) I do understand we still have to actively backup but I rather not serve any data than serving wrong data at the time of severe outage. Also I want to see any forward error correction code (FECC) would be implemented in Citus so we can do this on the fly rather than relying on RAID, e.g. RAID10, RAIDZ1, RAIDZ2

love citus! this is useful.<p>one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can&#x27;t be manipulated as a batch, they don&#x27;t provide the isolation &#x2F; access control of databases, and can&#x27;t be manipulated in DML and require stored procedures to manipulate.

It is worth noting that Citus was acquired by Microsoft a few years ago (not a secret, but may not be obvious to everyone), and they have since shifted heavily towards the Azure side of the world.<p>Back when it happened we tried to buy their product and they were not sure if the Citus standalone product was even going to exist, and they refused to demo it even. Odd timing possibly, but it&#x27;s a data point.<p>On the positive side of things, the shard rebalancing was not open source back then iirc, which made the open source version pretty useless. Now it seems to be open source: <a href="https:&#x2F;&#x2F;www.citusdata.com&#x2F;product&#x2F;comparison" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.citusdata.com&#x2F;product&#x2F;comparison</a> -- pretty cool.<p>I&#x27;d still be careful to bank on it as a Citus only customer or open source user.

I&#x27;m having trouble getting CitusDB to work with RDS, even though I&#x27;d really like to use it with AWS. Whenever I try to research how to make it happen, I get stuck in a lot of challenges and end up concluding that it might not be possible. While I could use Azure instead, I&#x27;m hesitant because I have a lot of resources and infrastructure in AWS and it wouldn&#x27;t make sense to move the database layer. The idea of having to do DBA and Ops work on my own EC2 instances is overwhelming. I would appreciate any guidance on how to use RDS with Citus on their documentation. It&#x27;s concerning that Citus might be withholding information about the possibility of using RDS, given that Microsoft is its partner.

What&#x27;s advantage over having tenant id as distribution column? Seems like you make schema name the distribution column. Maybe gross setups where same name function definition varies between schemas (been there done that, don&#x27;t want to do it again)<p>Seems like article only offers ease of use. Guess I&#x27;ve never used microservices enough to consider that use case<p>Couldn&#x27;t the microservice case be handled by having distributed tables with no distribution column? ie today I&#x27;d create a distribution column &amp; fill it with the same value on every row<p>Can one have a reference schema which can be efficiently used alongside every other schema? Guess that&#x27;s public schema with create_reference_table&#x2F;create_distributed_function

Doesn&#x27;t sharding imply horizontal partitioning? Some of their examples show vertical partitioning use cases. Not sure how I feel about that. I can&#x27;t imagine why you would do microservices and host their databases as schemas on the same instance.