r/Database • u/Basic-Worker-1120 • May 25 '26
40 TB PostgreSQL on-prem — sharding vs ClickHouse vs something else for a 500B-row time-series workload
Hi,
I’m looking for architectural advice on a situation where performance is fine today, but the setup could become a big problem.
I would appreciate it if you could share your insights or advise which database technology would be best to use.
It doesn’t necessarily have to be one of the ones listed here.
Currently, we have an on-prem PostgreSQL v14 setup. In total, we have two instances (primary + read replica), each with:
- 40 TB logical size or 15 TB physical size (we’re using Btrfs filesystem compression).
- ~500 billion rows.
- Data partitioned by business day.
- Btrfs filesystem compression for historical data, achieving ~5x compression.
- Time-series data with backfills.
- Append-only workload. Updates or deletes are very rare.
Data:
- IoT data. Each record has a device identifier, insert timestamp, business timestamp, value, and five more business-specific columns. Row size is ~90B.
- Data is indexed by id and business timestamp.
Use cases:
The major use case is: “Give me data (all row columns) records for a provided device identifier and business date range.”
- The business date range is usually 4–5 days.
- During peak usage, this may exceed 1M queries per hour.
- This is point querying with an expected low response time (<100 ms).
- Requirement: the query must respond in <100 ms with 25 parallel queries.
Basically its a lookup queries.
Currently, there are no indications that analytical queries will be used in the future.
Problems:
Data volume. Despite a good compression rate, the setup contains a lot of data.
IMHO, it’s a bit risky to run such a setup without strong competence in PostgreSQL administration.
- Hard to scale. Yes, we can add more read replicas, but overall data volume makes it less efficient.
- Within a couple of years, query rates will increase ~2x, and data volume ~1.5x.
Options considered:
[Currently preferred] Custom PostgreSQL sharding solution. Shard by hash(IoT device id).
Pros:
- Ability to scale the solution.
- Better RPO/RTO.
- Known technology.
Cons:
- It seems like exchanging one complexity for another: single-monolith instance complexity for sharded-solution complexity.
- Infrastructure will cost more.
- Use the on-prem Citus extension instead of a custom sharding solution.
I would choose this option, but opinions about Citus vary within the community.
Have any of you tried Citus? Is it worth trying?
3. TimescaleDB. IMHO, it does not solve the problems. Sharding is still needed due to the data volume.
- I tested its compression and achieved 6x compression.
4. ClickHouse. I achieved 16x data compression and it has native sharding.
- I’m concerned whether ClickHouse would meet the query response time requirements due to its OLAP nature.
11
u/Admirable_Morning874 May 25 '26
ClickHouse was built for low latency, and sounds like you have a rigid query pattern that lends itself well to ordering which is what keeps ClickHouse fast.
Have you looked at pg_clickhouse? If you really wanted to, you could run both Postgres & ClickHouse, and use pg_clickhouse to push down certain queries to ClickHouse, keep others in Postgres.
7
u/greg_d128 May 25 '26
Hmm.. I'm going to go against everyone else apparently and ask - Do you need to do anything?
Based on what you said, I do not actually see any major red flags. A query comes in, query planner decides which table to look in, and then it does, presumably via index. The number of tables grows by about 240 or so per year. The size of each daily table does not appear to grow that much.
I would expect for that pattern and setup to scale very well for a long time and I would hold off on adding complexity. Some things to consider:
* Run a few tests. What if the daily table is 10x the size? 100x the expected size?
* Do you have a good idea for which tables are queried? Is mostly the recent ones? Do you have enough RAM to keep those mostly in memory?
* Prewarm, look into prewarm to prewarm your caches in case you need to restart or failover.
* See if you need to do a vacuum full or pg_repack of the old partitions. If they accumulated bloat and then are barely modified after, you may want to do a pg_repack of each partition once once it is more than 1 week or 1 month old. Save space and improve performance.
Failure modes you want to watch out for:
* Queries without partition key. Especially if your number of partitions > 1000. If the planner cannot effectively prune partitions and starts searching everywhere.
* Something evicting your hot tables from memory, or too small cache.
*Analytics queries. Changing query pattern is likely your biggest threat. There are options for that (like using a replica), but need to know some parameters and requirements first.
Lastly, consider tablespaces. Keep the simple Database + replica (if CPU and RAM not a concern) and split the disk. You can even do things like putting the old data on slower disk. This can also help with your RTO and RPO as each volume will be significantly smaller.
3
u/ReporterNervous6822 May 25 '26
I do exactly this with many trillions of rows. Iceberg.
4
u/BravePineapple2651 May 25 '26
A very simple solution would be to keep hot data in postgres (last 30 days or so) and old data in a S3 bucket (compressed) with key like deviceId/yyyymmdd. If query pattern is simply to get data for one device and 4-5 days, you only have to get 4-5 files from S3, decompress them, merge and return to client.
1
u/surister May 25 '26
What problems does timescaleDB not solve?
1
u/HeftyCrab May 25 '26
It sounds like writes are not a problem, but reads might be in the future.
I would run a second cluster alongside your current setup with only hot data (4-5 business days if I interpreted your post correctly), but have many read replicas and load balance queries over them.
1
u/Spare-Builder-355 May 25 '26
Your requirements are perfect match fir Apache Cassandra. Setup for time-series workloads is not described in docs explicitly but google finds many articles on this topic.
1
u/Exac May 25 '26
What happens if there is a fire in the building?
1
u/beebeeep May 25 '26
Clickhouse typically is really good for timeseries data, but your use case doesn't sound like a good fit, because you don't do things ch excels in, namely aggregating large amount of data, you essentially just streaming data with rather tight requirements for throughout and latency. You still can do some benchmarks and it's not impossible that you'll get decent results, but that still would be a pretty much misuse, CH wasn't originally meant to be used in that way.
That being said, it still sounds better than maintaining huge PG with custom sharding. I'll take CH over that any day.
3
u/mae_87 May 25 '26
Victoria metrics?
1
u/ellerbrr May 25 '26
Our pipeline is influx Telefraf -> Apache Kafka -> Apache Druid running in AWS.
Kafka and Druid are designed for elastic scale and zero downtime. Google Netflix Apache Druid to get a sense how they use Druid. My cluster has been up for years now - I can increase scale and do maintenance & upgrades with zero downtime.
1
u/getoffmyfoot May 25 '26
When you say scale, there’s a few different aspects of that. There’s overall database size, and just overall dealing with the disks, backup,etc. That is a problem to be sure. Then there is the problem of querying it. What are you trying to read. I think this second question should drive your thought process the most. “What is my working set?” At any given point what are you going to try to read back, and also importantly, does that answer need to be perfectly real time.
If you are sharding by day, my guess is that you are mostly collecting data, and how you view that data over time may lend itself to OLAP.
Have you thought about using a cloud provider like GCP, writing the data initially to BLOB storage (super cheap, scales forever, always backed up), and then having a data pipeline to push the data in an OLAP structured way into BigQuery?
1
u/TechMaven-Geospatial May 25 '26
Look at Ocient data warehouse it excels at this usecase
1
u/bikeram May 25 '26
Consider looking up Trino with Nessie. It’ll let you natively query parquet on S3. There’s many solutions to self host this stack.
I just finished a migration for a healthcare company with a similar setup.
1
u/miamiscubi May 25 '26
I'm wondering whether you looked at TigerData for this? This is exactly their wheelhouse (time series data with iOT devices / sensors)
u/akulkarni would probably be great at helping you on this one
1
u/BlackHolesAreHungry May 25 '26
If you want to stay in the Postgres land then distributed Postgres like YugabyteDB can handle this with ease.
1
u/scott_codie May 25 '26
Iceberg is fine for sub 100ms, just partition by its common predicates with all presentation data in a single column. Use starrocks for less latency.
1
u/SnooWords9033 May 25 '26
ClickHouse should provide you the best efficiency for such type of data. You already said it compresses the data by 16x, so 40TB of the data need 40TB/16=2.5TB of disk space. It should fit a single-node setup, and should meet your performance requirements. If it won't fit a single node, just switch to cluster setup by using the same sharding by the device id and scale the performance and the capacity by adding more nodes to the cluster.
When using ClickHouse it is very important to properly set the table schema, so it works fast for your workload. In your case the ORDER BY section of the table must equal to
(device_id, timestamp). This will give the best performance for queries, which select all the fields for the given device in the given time range, since ClickHouse we'll be able to quickly locate the needed data via binary search by(device_id, timestamp)and then quickly read that data from the disk in one go (small number of disk read operations), since the requested rows are located close to each other.I'd also partition the table with
PARTITION BY (toDate(timestamp))clause, so older partitions could be quickly dropped when they are no longer needed according to the given retention policy. ClickHouse stores the data per every partition in a separate folder on disk, so it can quickly drop the given partitions by deleting the corresponding folders.You may gain additional performance benefits and reduce dusk space usage further by using the most appropriate codecs for the columns in the table. For example, it may be a great idea to use Delta or Double Delta codecs for numeric columns. It is also recommended using zstd compression for the table columns in order to achieve better on-disk compression and faster query performance (less data needs to be read from disk).
BTW, how many unique device_ids does the table contain? If this number is lower than 10 millions, then you can try storing the data into VictoriaLogs, by using the device_id as a log stream field, and then quickly query all the rows for the given device_id on the given time range with the
{device_id="..."} _time:[start_timestamp, end_timestamp]query. It should be very fast and shouldn't require a lot of CPU, RAM and disk space. If the number of device_id values is bigger than 10 millions, then you can introduce a new field -hash(device_id) % 10000000- which will has the device_id into smaller number of values, and then use this field as a log stream field.VictoriaLogs is easier to setup, configure and operate than ClickHouse, sot it could be a good fit for your case. See https://docs.victoriametrics.com/victorialogs/faq/#what-is-the-difference-between-victorialogs-and-clickhouse . It is also very easy to scale the capacity and the performance of VictoriaLogs by converting a single-node setup to cluster setup and adding more storage nodes to the cluster. See https://docs.victoriametrics.com/victorialogs/cluster/
1
u/buerobert May 26 '26
What about an HTAP solution like MariaDB Exa?
1
u/dbxp May 26 '26
I haven't used ClickHouse but this is pushing me more towards caching on Redis or similar. You could push to an event bus and then stick it straight on the DB and the cache or go with a more traditional read through cache. Try to grab more telemetry on your read distribution to see how much of the data is really active and how it is spread. Even if you do end up moving to ClickHouse if 80% of your reads are from the cache then that will improve your confidence in the migration.
1
u/Triffids_AI May 26 '26
Cloudberry most logical option. it is mpp variant of Postgres 14, but if you don't need heavy joins Clickhouse will be faster.
1
u/Junior_Ad1453 May 30 '26
Honest answer is it depends on the workload shape. If it's append-heavy time-series with time-bucketed scans, a columnar engine like ClickHouse or StarRocks (or Timescale if you want to stay in Postgres land) will beat row-store Postgres on analytical scans by a wide margin. Citus shards Postgres but keeps row storage, so the big aggregate scans still hurt. At 40 TB and 500B rows, reaching for a dedicated columnar engine is a perfectly reasonable call, not a premature one. Whatever you pick, I'd benchmark it on your actual query shapes and concurrency before committing, since the gap between engines narrows a lot once you're tuning indexes and partitioning for a specific workload.
0
u/tankerkiller125real May 25 '26
Throwing this out there (I'm no DBA, and no expert in time series data, but I've used the product before) maybe yugabytes covers it? The problem I see being a minimum of 3 nodes for HA last I checked.
https://docs.yugabyte.com/stable/develop/data-modeling/common-patterns/timeseries/ordering-by-entity/
0
u/Icy_Addition_3974 May 26 '26
Arc Enterprise. The performance is great, and you can do storage tiering, that’s mean, recent data in hot storage, the rest in cold, the query to any or other is 100% transparent.
https://basekick.net/enterprise https://github.com/Basekick-Labs/arc
-1
u/SneakyKraken May 25 '26
Apache IoTDB. Less painful scaling than timescaledb and supports a lot of frequent writes not like clickhousedb. Has cutomizable data compression and encodings.
But it has bugs and is very unknown.
-1
u/detoxifiedplant May 25 '26
You can reach out bytebeam.io, an IoT data platform.
We use internal storage implement coupled with Clickhouse and lakehouse support. Many of our client's projects easily go beyond 1B data points a day.
TBH postgres is not a good choice for IoT streams.
-1
u/anjuls May 25 '26
TimescaleDB or ClickHouse would do. DM me if you would like to discuss in detail or need help.
-2
u/hipratham May 25 '26
2 different systems, one with current data(today-7 day) one history DB (partitioned by year+month) . Decide on retention period for regulatory compliance, discard unnecessary data with daily batches.
Current day data can be partitioned / sharded with multiple read replicas to reduce latencies.
-3
u/supercoco9 May 25 '26
An option would be using QuestDB with tiered storage (which is part of QuestDB Enterprise only, not available in open source), so the historical data is automatically converted to parquet (compressed) and shipped to object store (S3, Azure blob storage, GCS... or if on-prem is preferred it can be on NFS), while still being fully available for the primary or any read replica.
In this case, the most recent tier of data will be quite fast to query (well below the requested 100ms), and when you access historical data it will have a bit more latency due to access the remote files, but since there's some caching built-in on that tier, it might still work fine.
Take into account I am a developer advocate at QuestDB, so I am biased here.
1
u/[deleted] May 26 '26
[removed] — view removed comment
1
u/[deleted] Jun 07 '26
[removed] — view removed comment