Custom configuring your Postgres settings on Citus Cloud

Written by Craig Kerstiens
April 17, 2018

Postgres has long been a reliable database for keeping your data safe, and it is used in a variety of flexible ways. Because of the many flexible ways it can be used (ranging from embeded devices to data warehousing to large transactional system) it also comes with a lot of knobs to configure it. Part of our approach in providing a fully managed database as a service is configuring Postgres to be production ready from the moment you click a provision, which is what you get with Citus Cloud.

Over time though we have seen a need for more flexibility to tune and customize configurations to your specific needs. Part of this flexibility is in supporting the rich feature set of Postgres features such as JSONB, rich indexing, and more. Part is supporting a broad set of extensions such at HyperLogLog, pg_partman, TopN, PostGIS, and more. And today we're excited to support custom configuration of your Citus clusters on Citus Cloud to enable even broader flexibility.

You'll find the new customization tab within your Citus console. Custom configurations that do not require a restart of the Postgres server are supported. Additionally we support other customization of Postgres such as connection limits (which do require a restart) by reaching out to us directly. You can read more in our docs on how to customize your cluster today.

Let's take a quick walkthrough of just a few of the settings we've seen value in tweaking:

  • log_min_duration_statement (in milliseconds) to log any query that runs for longer than your specified time
  • log_temp_files is handy to notice when queries spill to disk, by monitoring this you could then tune the following setting.
  • work_mem is that is allowed to be used for a single query within Postgres. It is of note that for queries that parallelize in Citus one query may spawn 8 others to a data node, if you have questions in tuning work_mem when in doubt feel free to ask us.
  • log_statement is useful if you need a clear record anytime DDL changes are run against your database.
  • max_connections need more than the standard 300 database connections? Even with pgbouncer in place? We can customize this for you and it'll be persisted within your cluster

The ability to customize your PostgreSQL config should leave you more enabled than ever when it comes to keeping your app performant and scaling. If you have any questions on how to keep things performing and scaling let us know as we're always happy to chat.

Craig Kerstiens

Written by Craig Kerstiens

Former Head of Cloud at Citus Data. Ran product at Heroku Postgres. Countless conference talks on Postgres & Citus. Loves bbq and football.