Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Citus 12: Schema-based sharding for PostgreSQL

citusdata.com

Post reply on HN
Print view View original

31–40 of 47 posts

Re: Citus 12: Schema-based sharding for PostgreSQL

#31

Post by isoos »

metadat wrote: post #14

Does Oracle support anything like this? Or any other DBMS in widespread use, for that matter? 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!

Depends on the definition of widespread use, but e.g. CockroachDB and YugabyteDB offer scaling of a subset of Postgresql features (and extending on the cluster capabilities).

Nevertheless, Citus is now a promising open source alternative.

Re: Citus 12: Schema-based sharding for PostgreSQL

#32

Post by justinclift »

mslot wrote: post #9

Nice to see this on HN :) 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 featur…

Is there any way to have that automatically create n copies of the data across shards?

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).

Re: Citus 12: Schema-based sharding for PostgreSQL

#33

Post by sokols »

metadat wrote: post #14

Does Oracle support anything like this? Or any other DBMS in widespread use, for that matter? 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!

There is Oracle RAC Sharding, but AFAIK it works only at a partition level.

Re: Citus 12: Schema-based sharding for PostgreSQL

#34

Post by mslot »

justinclift wrote: post #32
mslot wrote: post #9

Nice to see this on HN :) 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 featur…

Is there any way to have that automatically create n copies of the data across shards? 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).

Historically, there has been, but we have found physical replication is ultimately a lot more performant, robust, and tweakable. Citus users & platforms generally use physical replication of each node for high availability.

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.

Re: Citus 12: Schema-based sharding for PostgreSQL

#35

Post by janpio »

__s wrote: post #27

What'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't want to do it again) 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 distrib…

One reasons why some prefer multi tenancy via schemas instead of a tent ant column: Reading a column with the wrong tenant id happens easily, just forget the where in a query. Across schemas, this is much harder to get wrong as a schema has to be explicitly named in a query.

Re: Citus 12: Schema-based sharding for PostgreSQL

#36

Post by mslot »

stevefan1999 wrote: post #29

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…

The answer is mostly the same as for PostgreSQL, since Citus stores data in regular PostgreSQL tables.

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.

Re: Citus 12: Schema-based sharding for PostgreSQL

#37

Post by somat »

hans_castorp wrote: post #7
pickledish wrote: post #2

Hm, question for people a bit more familiar with Postgres -- what is meant by "schema" here? 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)

Schemas are namespaces (actually called that internally in Postgres). 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…

I always vaguely wonder why no one allowed a full hierarchical schema path. I expect it is probably because "the standard sez one level of schema only"

Re: Citus 12: Schema-based sharding for PostgreSQL

#38

Post by mslot »

__s wrote: post #27

What'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't want to do it again) 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 distrib…

> What's advantage over having tenant id as distribution column?

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.

Re: Citus 12: Schema-based sharding for PostgreSQL

#39

Post by krab »

brightball wrote: post #25
metadat wrote: post #14

Does Oracle support anything like this? Or any other DBMS in widespread use, for that matter? 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!

I watched a Citus demo a few months back and it blew me away. I hear upgrades are the big challenge.

Not really. It's comparable to a regular Postgres upgrade.

But you can screw it up - see https://github.com/citusdata/citus/discussions/6934

Re: Citus 12: Schema-based sharding for PostgreSQL

#40

Post by krab »

mariocesar wrote: post #18

I'm having trouble getting CitusDB to work with RDS, even though I'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'm hesitant because I have a lot of resources and infrastructure in AWS and it wouldn't make sense to move the database layer. The idea of having t…

Because you can't install extensions to RDS, I think it can't be done unless AWS chooses to support it.

You can still install Postgres + Citus on EC2 or use some 3rd party service to manage it for you.

Post reply on HN