Citus

Written by Onder Kalaci March 20, 2021 Update in October 2022: The Citus managed database service is now available in the cloud as Azure Cosmos DB for PostgreSQL. And as always, the Citus database is also available as open source: you can find the Citus repo on GitHub or download Citus here. One of the big new things in Citus 10 is that you can now shard Postgres on a single Citus node. So in addition to using the Citus extension to Postgres to scale out Postgres across a distributed cluster, you can now also: Try out Citus on a single node with just a few simple commands Shard Postgres on a single Citus node to be “scale-out-ready” Simplify CI/CD pipelines by testing with single-node Citus The Citus 10 release is chock full of new capabilities like columnar storage for Postgres, the open sourcing of the shard rebalancer, as well as the feature we are going to explore here: using Citus on a single node. No matter what type of application you run on top of Citus—multi-tenant SaaS apps, customer-facing analytics dashboards, time-series workloads, high-throughput transactional apps—there is something for everyone in Citus 10. In this post, let’s walk through how to try out Citus on a single node. And let’s dive deep into some of the ways sharding Postgres on a single node can help you. Try out Citus on a single node, with just a few simple commands (a Quick Start guide) My favorite way to get started with Citus is to use a docker container to run Citus on a single node. If you prefer, you could instead download Citus open source packages for both single-node and multi-node Citus installations, too. The docker container includes: Latest version of Postgres, as of time of this writing, PG 13 Latest version of Citus, as of time of this writing, Citus 10.0.3 Citus extension already created via CREATE EXTENSION citus; # run PostgreSQL with single-node Citus on port 5500docker run -d --name citus_coordinator -p 5500:5432 -e POSTGRES_PASSWORD=mypassword citusdata/citus# connect using psql within the Docker containerdocker exec -it citus_coordinator psql -U

More nodes as your application grows. Simplify CI/CD pipelines for your Citus applications The 3rd interesting scenario for using Citus on single node is with your CI/CD pipelines. If Citus is part of your CI/CD workflow, setting up a test environment is so much simpler now. Instead of having two or more database servers, you can setup Citus on a single node which will provide the same experience and coverage. In other words, whatever you can (or cannot) do on multi-node Citus is the same on the new single-node Citus capability. Back before we made single-node Citus a first-class citizen in this Citus 10 release, we had shared a sneak preview of sharding on a single node in our Citus 9.5 blog post. And we received this lovely feedback from Ivan Vyazmitinov, one of our Citus users, just a few weeks later: “A piece of feedback about the new ‘single node cluster’ feature from the blog post: after a little bit of testing we’ve immediately switched all of our integration tests to it. It is literally a lifesaver in terms of complexity of configuration of tests and it also completely eliminated our tests flakiness issue due to errors from unhealthy citus containers on tests’ startup.” Using single-node Citus in the cloud One of the easiest ways run Citus is in the cloud: you can deploy the Hyperscale (Citus) option in the Azure Database for PostgreSQL managed service. For those of you who use Citus as part of the Azure Database for PostgreSQL managed service, the good news is: as of update August 2021 Citus 10 is now available on Citus on Azure with the new single node configuration. More details in Nik’s initial preview post on what’s new and his follow-on post announcing that Citus 10 is GA in Hyperscale (Citus). Citus on a single node opens the door to new possibilities We are very excited to bring you the new Citus on a single node feature. And while “opens the door to new possibilities” may sound lofty, it’s true. Single-node Citus gives you a way to be “scale-out-ready” on

11.0, because everything in the Citus extension is now fully open source! That means that you can now rebalance shards without blocking writes, manage roles across the cluster, isolate tenants to their own shards, and more. All this comes on top of the already massive enhancement in Citus 11.0: You can query your Citus cluster from any node, creating a truly distributed PostgreSQL experience. Keep reading With the 10.1 release to the Citus extension to Postgres, you can now monitor the progress of an ongoing shard rebalance—plus you get performance optimizations, as well as some user experience improvements to the rebalancer, too. Whether you use Citus open source to scale out Postgres, or you use Citus in the cloud, this post is your guide to what’s new with the shard rebalancer in Citus 10.1. And if you’re wondering when you might need to use the shard rebalancer: the rebalancer is used when you add a new Postgres node to your existing Citus database cluster and you want to move some of the old data to this new node, to “balance” the cluster. There are also times you might want to balance shards across nodes in a Citus cluster in order to optimize performance. A common example of this is when you have a SaaS application and one of your customers/tenants has significant more activity than the rest. Keep reading Citus 10.1 is out! In this latest release to the Citus extension to Postgres, our team focused on improving your user experience. Some of the 10.1 fixes are operational improvements—such as with the shard rebalancer, or with citus_update_node. Some are performance improvements—such as for multi-row INSERTs or with citus_shards. And some are fixes you’ll appreciate if you use Citus with lots of Postgres partitions. Given that the previous Citus 10 release included a bevy of new features—including things like columnar storage, Citus on a single node, open sourcing the shard rebalancer, new UDFs so you can alter distributed table properties, and the ability to combine Postgres and Citus tables via support for JOINs between local and distributed tables, and foreign keys between local and reference tables—well, we felt that Citus 10.1 needed to prioritize some of our backlog items, the kinds of things that can make your life easier. This post is your guide to the what’s new in Citus 10.1. And if you want to catch up on all the new things in past releases to Citus, check out the release notes posts about Citus 10, Citus 9.5, Citus 9.4, Citus 9.3, and Citus 9.2. Keep reading It’s been an eventful time for Hyperscale (Citus) lately. If you’re interested in Postgres, distributed databases, and how to handle ever growing needs

Hit 10K. ️New Citus open source features in last 8 monthsPostgres 16 support: Published on the Citus Open Source Blog, this Citus 12.1 release blog post announced the Citus support of Postgres 16 in Citus 12.1, within just one week of the PG16 release. (More details in the Citus 12.1 release notes.) PG16: JSON aggregate support: As of Citus 12.1, Citus now supports and parallelizes the new JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregates. (Code example in the 12.1 release notes.)PG16: DEFAULT in COPY: By using the new DEFAULT option in PG16 with COPY FROM, you can control in which rows you want to insert the default value of a column (vs. inserting a defined, non-default value.) And as of Citus 12.1, this new DEFAULT option is supported and propagated to the nodes in a distributed cluster. (Code example in the 12.1 release notes.)PG16: more DDL propagation: Citus now propagates new CREATE TABLE, VACUUM, and ANALYZE options to worker nodes in the distributed cluster. And according to the 12.1 release notes, Citus can propagate the STORAGE attribute if it is specified when creating a new table. In addition, Citus can now propagate BUFFER_USAGE_LIMIT, PROCESS_MAIN, SKIP_DATABASE_STATS and ONLY_DATABASE_STATS options in VACUUM and/or ANALYZE. ICU collation rule propagation: Prior to Postgres 16, Citus already supported distributed collations. So with the PG16 addition of custom ICU collation rules that can be created using the new “rules” option in CREATE COLLATION, Citus just needed to support the propagain of this new PG16 collation “rules” option. (Details in 12.1 release notes.)Support TRUNCATE triggers on Citus foreign tables: Those of you who care about audit logging were probably pleased to see Postgres 16 add support for TRUNCATE triggers for foreign tables. With Citus 12.1 you can use the new TRUNCATE triggers features with Citus foreign tables too. (More details in the announcement blog post.) Combine query-from-any-node with load balancing: PG16 added a new load balancing feature in libpq that lets you specify load_balance_hosts and set it to random. This new libpq load balancing feature makes it easy to load balance in combination with the Citus query-from-any-node feature. (More details