Hans-Juergen Schoenig: Citus: Sharding your first table

Citus is a capable sharding solution for PostgreSQL. It solves a ton of scalability issues: these can be addressed using a sharding approach. We at CYBERTEC have used Citus for some time and can wholeheartedly recommend it (check out our services to find out more). Since the need for PostgreSQL sharding is constantly growing, we thought we’d share some of our knowledge. Here’s how to shard a table from scratch. Setting up CitusDB Most people run Citus directly by firing up a couple of containers which already contain a working version of the solution. This is a good way to get started; it helps to shave off some of the overhead associated with installation and configuration. Running a Citus Docker container Here’s how to run a Citus Docker container: # run PostgreSQL with Citus on port 5500 docker run -d --name citus -p 5500:5432 -e POSTGRES_PASSWORD=mypassword citusdata/citus # connect using psql within the Docker container docker exec -it citus psql -U postgres # or, connect using local psql psql -U postgres -d postgres -h localhost -p 5500 This method will provide you with a working solution in minutes. Configuring Citus manually However, if you want to get your hands dirty and find out what’s going on behind the scenes, you can also configure PostgreSQL manually to run Citus. After installing the binaries, it is necessary to load the Citus library directly at startup. The way to do that is to add the citus extension to shared_preload_libraries in postgresql.conf: shared_preload_libraries = 'citus,pg_stat_statements' # (change requires restart) I found it super useful to also add pg_stat_statements to the variable, in order to monitor performance as professionally as possible. Note that “citus” has to be the first extension in the list. Otherwise, the server won’t start. Adding worker nodes Once this is done, we can provide a set of database instances which we’ll run. In my case I prepared 5 empty instances with the following setup: A coordinator: loca[...]