Citus 12: Schema-based sharding for PostgreSQL
citusdata.com
citusdata.com
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 & foreign keys between the tables.
On top of that, you can also have reference tables that are replicated to all nodes, again for fast joins & foreign keys with all schemas.
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.
Something like a "min-copies 2" setting, which would ensure the shared data has at least one viable alternative in case of hardware failure (etc).
For instance, the ability to quickly spin up a new replica using a disk snapshot is very useful and only feasible at the server level.
It is still possible to replicate shards (via the citus.shard_replication_factor setting), but it only helps for scaling read throughput, at the cost of lower write throughput.
The promise behind this approach to DB sharding has great potential. Simultaneously impressive, novel, and badass.
I wish this had been available ten years ago at a few of my startups!
But you can screw it up - see https://github.com/citusdata/citus/discussions/6934
Nevertheless, Citus is now a promising open source alternative.
My definition is "the columns and column types of a table", but, that doesn't seem to make sense with what they're talking about here ("large" and "small" schemas probably aren't referring to wide and narrow tables for example, and I don't see how sharding by my definition of "schema" could even make sense anyways)
By large and small we are referring to the amount of data each schema holds currently. They can grow over time and some of them may become very big while others will remain small (storage wise).
> You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data.
So in this respect, each table within a schema indeed already acts like a "shard" of the overall table
Is this enforced? Like, if I create a table "messages" in schema A and a table "messages" in table B, must they have the same columns/column types, or is that just convention
The words get really fuzzy here when you try to get a concrete and ubiquitous definition, though.
Best I can do is to say, in these queries:
use foo;
select id, name from foo.people where id < 10;
Schema is the thing you get from `CREATE SCHEMA`...a namespace of tables/functions/views/etc.
Thank you!
The SQL standard defines a two level namespace hierarchy. A single "instance" of contains multiple catalogs and each catalog contains multiple schemas (and each schema then contains objects like tables, views, types, functions etc).
Many database products use the term "database" instead of "catalog" e.g. in Postgres and SQL Server. But "schema" is used quite uniformly. MySQL's "databases" are in fact "schemas" though.
We worked upstream to have `search_path` properly handled (tracked per client) by pgbouncer.
https://github.com/pgbouncer/pgbouncer/commit/8c18fc4d213ad4...
Check config.md in that commit for a verbose, humanized description.
It's a good idea to set up archival into blob storage / S3 using a tool like WAL-G for disaster recovery purposes, and streaming replication using a tool like Patroni.
(Or use a managed service like Azure Cosmos DB for PostgreSQL)
If a node is down and cannot be quickly recovered then the remaining shards are still available for reads and writes, except for DDLs.
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't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.
A big advantage of schemas over databases is that you can access different schemas over the same connection, which allows much higher density, since connections are a scarce resource in PostgreSQL. You can also have shared tables.
A big advantage over row-based sharding is that you can have variations of the table definitions or a different set of tables altogether in each schema. Plus for the purpose of sharding it's easier to use, since normalized data models complicate row-based sharding.
I would make a partitioned table, and put each partition in its own schema.
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's a data point.
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: https://www.citusdata.com/product/comparison -- pretty cool.
I'd still be careful to bank on it as a Citus only customer or open source user.
There no longer is an enterprise version, what runs on Azure is the exact same Citus that you can run yourself. We even invested in Patroni, to make it easier for the community to self-host Citus with HA setups.
While we obviously want people using Citus on Azure, having Citus as a viable open-source choice is our path to achieving that. I wasn't part of the company when the acquisition happened so can't speak to that, but I can imagine how that could have made sales at the transition time unclear.
Personally I would also like to add, that the team is full of long term open-source contributors. We contribute both to PostgreSQL and projects around it (like pgbouncer). I understand and respect your reservation, but wanted to share my perspective on it.
Would've been great if something like Cruncy Bridge, PgAnalyze and Citus were merged into one company so that you have managed Postgres over the 3 major providers with the best pg tech out the box.
Having your DB and apps in different providers is a stuff up, the latency and data transfer costs killed us, so Citus was a non start (we don't want to manage it ourselves in EC2).
They are offering Citus integration
You can still install Postgres + Citus on EC2 or use some 3rd party service to manage it for you.
Seems like article only offers ease of use. Guess I've never used microservices enough to consider that use case
Couldn't the microservice case be handled by having distributed tables with no distribution column? ie today I'd create a distribution column & fill it with the same value on every row
Can one have a reference schema which can be efficiently used alongside every other schema? Guess that's public schema with create_reference_table/create_distributed_function
Ease of use is definitely the main one. If you're willing to put in the work required to use tenant ID as a distribution column (add it to tables, primary keys, foreign keys, filters, joins), then it's a more scalable approach.
A challenge with sharding by tenant ID is that many applications use a normalized data model, meaning not all tables obviously have a tenant ID column. When you use a schema per tenant on vanilla PostgreSQL, no additional steps are typically required to enable schema-based sharding.
There are some other benefits of schema-based sharding such as custom table definitions, simpler & more versatile access control, and longer term we expect it will be easier to pin a large tenant to a node using schemas, or distribute the tables of ultra-large tenants (by some other dimension). Of course, row-based sharding has other benefits like parallel cross-tenant queries and global DDL.
> Couldn't the microservice case be handled by having distributed tables with no distribution column?
Absolutely. We first implemented the notion of single shard distributed tables with a NULL distribution column, and then built schema-based sharding on top as a convenience layer.
> Can one have a reference schema which can be efficiently used alongside every other schema? Guess that's public schema with create_reference_table/create_distributed_function
Yes, public schema (or other another non-distributed schema) can have reference tables & distributed tables as usual, and tables in distributed schemas can have foreign keys / local joins with reference tables.
You can start with all your microservices sharing a single node, add nodes and have the storage layer distributed horizontally in a way transparent to the services themselves.