citus | Distributed PostgreSQL as an extension | Database library

Disclaimer: I am the Software Engineer working on Citus engine that opened a PR for supporting UDFs in column defaults.

In the post you shared in the question gen_random_uuid() UDF is used as a column default. This is not yet supported on Citus.

I suggest you use bigserial.

I also want to note that some of the statements in the blog are not correct for Citus. For example:

So if you use sharding, where you distribute your data across several databases, you cannot use a sequence.

In Citus, you can create distributed sequence objects where each worker node will use a disjoint subset of the allowed values of the sequence. The application sees a single sequence on the coordinator node, while the shards have their own sequences with different start values.

(You could use sequences defined with an INCREMENT greater than 1 and different START values, but that might lead to problems when you add additional shards.)

Citus shifts the start of a bigserial by node_group_id * 2^48 and it means that you are limited to a maximum of 2^18 shards that is practically unlimited. You will run into bigger issues when you have petabytes of data in a single table, or a quarter of a million shards, and this limitations here will not really affect your cluster.

PS: My work on the UDF column defaults is on hold now, as some recent code changes will make the solution cleaner, and easier to maintain. We did not yet ship the changes in a released version. https://github.com/citusdata/citus/pull/5149

I've used citus for a multi-tenant service and distributing tables by the tenant id worked pretty well.

The overall architecture that you described makes sense but I'm not sure if someone from outside or at least without some details can give you much more to work with. Sending events through Kafka and processing them to be stored somewhere is a pretty standard way of working with events these days so nothing crazy is happening there.

There seem to be no limitations to scale out in terms of the number of nodes but what you should keep in mind is that how you set your shards count from the start. re-balancing will lock your tables and can take a while to finish so you want to keep it as small and easy to process as you can. Take a look here for more details: https://docs.citusdata.com/en/v10.2/faq/faq.html#how-do-i-choose-the-shard-count-when-i-hash-partition-my-data

GIN indexes are supported as they used it in their examples: https://docs.citusdata.com/en/v10.2/use_cases/multi_tenant.html?highlight=GIN#when-data-differs-across-tenants

Also, note that you won't have failover support in the community version. You have to go with the enterprise version which supports failover and also allows you to rebalance the tables without locking the entire table.

I wrote a simple service to handle the failover which you can use: https://github.com/Navid2zp/citus-failover

I changed type to Full load it worked so it is not working for ongoing replication Citus Cloud service don't support it.

Looks like a temporary issue on repository side. I just tried it and it works:

1. to use all worker I need to run select_distributed_table/select_reference _table. is that copy the all data to all workers (for example 1TB of data became 16 TB for 16 workers)?

Reference tables are copied across the whole cluster and distributed tables are sharded across the worker nodes.

If you ran the following queries on a Citus cluster with 16 workers for tables with 16 GB of data

citus can I use join if I use citus ?

Yes, you can use joins with Citus.
https://docs.citusdata.com/en/v10.0/develop/reference_sql.html#joins.

Most of the joins between different Citus table types are just supported, but some of them requires enabling repartition joins.
https://docs.citusdata.com/en/v10.0/develop/api_guc.html?highlight=enable_repartition_joins#citus-enable-repartition-joins-boolean

As of Citus 10, joins between local tables and distributed tables are also supported.
https://docs.citusdata.com/en/v10.0/develop/api_guc.html?highlight=enable_repartition_joins#citus-local-table-join-policy-enum

And yes are they performant ?

Citus joins tables very efficiently when tables are co-located.
https://docs.citusdata.com/en/v10.0/develop/reference_sql.html#co-located-joins

For the time being, it is not possible to shard a table on PostgreSQL without dropping the self referencing foreign key constraints, or altering them to include a separate and new distribution column.

Citus places records into shards based on the hash values of the distribution column values. It is most likely the case that the hashes of parent and child id values are different and hence the records should be stored in different shards, and possibly on different worker nodes. PostgreSQL does not have a mechanism to create foreign key constraints that reference records on different PostgreSQL clusters.

Consider adding a new column tenant_id and adding this column to the primary key and foreign key constraints.

Found the answer: This is done through master_copy_shard_placement https://citus-doc.readthedocs.io/en/latest/develop/api_udf.html#master-copy-shard-placement Enterprise is not required.

Former Citus/current Microsoft employee here, chiming in with some advice.

Citus shards are based on integer hash ranges of the distribution key. When a row is inserted, the value of the distribution key is hashed, the planner looks up what shard was assigned the range of hash values that that key falls into, then looks up what worker the shard lives on, and then runs the insert on that worker. This means that the customers are divided up across shards in a roughly even fashion, and when you add a new customer it'll just go into an existing shard.

It is critically important that all distributed tables that you wish to join to each other have the same number of shards and that their distribution columns have the same type. This lets us perform joins entirely on workers, which is awesome for performance.

If you've got a super big customer (100x as much data as your average customer is a decent heuristic), I'd use the tenant isolation features in advance to give them their own shard. This will make moving them to dedicated hardware much easier if you decide to do so down the road.

The shard_max_size setting has no effect on hash distributed tables. Shards will grow without limit as you keep inserting data, and hash-distributed tables will never increase their shard count under normal operations. This setting only applies to append distribution, which is pretty rarely used these days (I can think of one or two companies using it, but that's about it).

I'd strongly advise against changing the citus.shard_count to 3000 for the use case you've described. 64 or 128 is probably correct, and I'd consider 256 if you're looking at >100TB of data. It's perfectly fine if you end up having thousands of distributed tables and each one has 128 shards, but it's better to keep the number of shards per table reasonable.