Questions from Our Recent Webinar - Scaling PostgreSQL on Amazon Web Services

In our recent webinar on Scaling PostgreSQL on Amazon Web Services using pg_shard and CitusDB, we received a number of interesting questions, particularly about CitusDB, which transparently shards tables and parallelizes queries across a cluster of PostgreSQL servers. In this post we'll go into a bit more detail to address these.

• Can CitusDB perform distributed joins? Is it fast?
  Yes! CitusDB supports joining distributed tables in several ways. Ideally, your tables are partitioned on the join key and matching partitions are located on the same nodes. In that case CitusDB can directly join overlapping shards on the worker nodes, and this is by far the fastest form of join. When tables are not partitioned on the join key, CitusDB automatically copies or re-partitions one or both tables across the cluster to create pairs of matching partitions that can then be joined by the worker nodes. In the process, a lot of data may be moved to other machines which may take some time. However, the CitusDB query planner intelligently decides the optimal order for the joins, and it performs filtering and projections before the join to minimize the amount of network traffic when possible. Importantly, everything always happens in parallel, using all machines and all their cores. 

  Last year we published benchmarks of CitusDB using TPC-H queries, which include many levels of re-partition jobs. In the benchmarks, we found CitusDB join performance to be comparable and in most cases better than that of Hadoop-based SQL solutions such as Impala or SparkSQL.
   

• In addition to hash-partitioning. Can we do range or list partitioning?
  With CitusDB you can use hash-partitioning, range-partitioning and what we call append-distribution. The latter is a form of range-partitioning especially suitable for time series data. Whenever you add new data, that data becomes a new shard in the distributed table, which allows very fast, scalable ingestion of new data. CitusDB automatically determines the range of values in the partition column for the new shard. It is possible for shards to have overlapping ranges, but this does have certain limitations  (e.g. INSERTs into overlapping shards are not possible).

  CitusDB does not currently support list-partitioning, but it is possible to have a single shard per distribution key by creating ranges which start and end with the same key. CitusDB can support a large number of shards in this way.
   

• How does CitusDB rebalance data across the cluster? 
  CitusDB has data-rebalancing functions that automatically move shards to evenly distribute the data. This is especially useful when adding new machines, or re-adding machines after maintenance. Shards can be thought of as the units of data in a CitusDB cluster. You can transactionally add and remove shards, copy or move them across the cluster, and queries are automatically parallelized across the shards. In addtion, the open source pg_shard extension lets you perform DML commands directly on the shards in a CitusDB  table. 

You can find a full recording of the webinar about Scaling PostgreSQL on Amazon Web Services at the Citus Data webinars page.

• CitusDB

• CitusDB