Citus and pg_partman: Creating a scalable time series database on Postgres (opens in new tab)

That's a problem with AWS RDS PostgreSQL, not with Citus and TimescaleDB.

The Citus Cloud service is similar to RDS, but for Citus clusters. Every cluster runs on AWS in its own VPC and it lets you peer with the VPC. It also provides auto-failover, PITR, forking, read-only followers and scaling out without any downtime.

AWS RDS has tons of other problems. It's perfect for developement, but for production usage it might bite you.

The answer to that is to use Redshift which is a proper distributed column-oriented data warehouse. Partion on time and let the database do the rest.

Using standard Postgres with sharding for OLTP workloads is great but there are better options for OLAP, especially if you’re using managed services. Also there is the Citus cloud offering if you want to stay with Postgres.

Thanks for mentioning bitemporal databases, I hadn't heard of anything like that before. Time-based mutable facts are so hard to represent well.

I think their definition of time-series database fits the common usage I've seen everywhere: the data has a time dimension and is append-only/immutable (well, ok, you can mutate the data in a postgresql table, but nobody's forcing you to).

Given the choice between selecting a specialized time-series only database or using a time-series pattern in your existing postgresql database, postgresql is often (usually?) the more pragmatic choice. That's what we do at mixrank with time-series tables approaching the 100 billions of rows.

> I doesn't even really seem to me you necessarily want to partition on time since your load distribution is going to be terrible.

I think that's not accurate. The tables mentioned in the post are first sharded/distributed on `repo_id`. Later, each shard is also partitioned on time dimension (i.e., `created_at `). Thus, the load should be distributed proportionally with the activity for each `repo_id`.

In general I'd expect native partitioning to have similar performance characteristics as TimescaleDB. On the insert side lot of the benefit comes from partitions having small indexes, on the delete side from the ability to drop partitions quickly, and on the select side from skipping partitions based on filters.

Postgres 10 partitioning does have a few limitations and inefficiencies that will be resolved in Postgres 11. Partitioning is the most actively developed area of postgres.

Note that Citus shards across multiple nodes, and can then partition on disk using native partitioning, which is automated by pg_partman. TimescaleDB so far only works on a single node.

Citus can also run parallel, distributed SQL queries, perform distributed transactions, and build rollups tables in parallel, and is used in Postgres clusters with up to a petabyte of data.

From everything I've seen on it, Citus makes horizontally scaling a multi-tenant database a transparent operation. This is ideal just because so many applications emphasize recency in the data that you don't want rarely accessed, 3 year old information cluttering up your indexes that are looking up things from this month 99% of the time.

Timescale seems to me more about the analytics side of things, focusing on ingestion speed and aggregation. Correct me if I'm wrong somebody.

Craig from Citus here. From our cursory looks it does look like they could work together, though personally we've not tried it and I'm not aware of users that have (though it's entirely plausible some have already). What you'd have to do is setup timescale on each of your distributed nodes and tables. You'd still talk to Citus as the primary point and it would re-write and push the queries down to the nodes which could then leverage timescale.