Skip to content
Storing high-frequency meter data: plain Postgres, TimescaleDB, or something else?
question1 min read102 viewsSep 19, 2026

Storing high-frequency meter data: plain Postgres, TimescaleDB, or something else?

Emma Lindqvist@emmalindqvist

September 19, 2026 · 1 min read

#4 in SELECT * Anonymous

102 views3 comments

We are getting more charger and meter data than our current setup was designed for. Right now it is about 40,000 devices sending a reading every 15 seconds, so roughly 230 million rows per day, into a single Postgres table with a B-tree index on (device_id, ts). Inserts are still fine, but queries over a week for one site are getting slow and the table is huge.

Typical queries:

  • last 24 hours for one device, at full resolution
  • 15 minute averages per site for the last 30 days
  • daily totals per region for reporting

We only need raw data for 90 days, after that the aggregates are enough. I am looking at TimescaleDB (hypertables, continuous aggregates, compression) vs native partitioning plus our own rollup jobs vs moving to ClickHouse. Has anyone run something similar? I would prefer to stay close to Postgres, because the rest of our stack is there.

Accepted answer

Lin Wei#2 in SELECT * Anonymous3 likes

we run something similar for clickstream (bigger rows, less often). my opinion: if you want to stay in postgres, timescale is the easy path for exactly these 3 query - hypertable on ts, chunk interval 1 day - continuous aggregate for 15 min per device, then a second one on top for daily per site (hierarchical cagg work now) - compression on chunks older than 2-3 days, segment by device_id, order by ts. we got around 10x smaller - retention policy to drop raw after 90 days, keep the caggs native partitioning + BRIN on ts also ok but you end up rebuilding half of timescale with cron job. clickhouse is great for analytics but then you operate 2 database

View in the thread

#postgres#time-series#data-engineering#energy

Written by

Emma Lindqvist

Software engineer at a climate tech company in Stockholm, building tools that shift EV charging to when the grid is cleaner. Python, time series, cold-water swimming.

View profile

Get the best of #select-star-anonymous weekly

One email each Monday with the most useful posts. No spam; unsubscribe in one click.

Comments

3

Sign in to join the conversation.

Sign in to comment

Accepted answer

Lin Wei@linwei#2 in SELECT * Anonymous
we run something similar for clickstream (bigger rows, less often). my opinion: if you want to stay in postgres, timescale is the easy path for exactly these 3 query - hypertable on ts, chunk interval 1 day - continuous aggregate for 15 min per device, then a second one on top for daily per site (hierarchical cagg work now) - compression on chunks older than 2-3 days, segment by device_id, order by ts. we got around 10x smaller - retention policy to drop raw after 90 days, keep the caggs native partitioning + BRIN on ts also ok but you end up rebuilding half of timescale with cron job. clickhouse is great for analytics but then you operate 2 database
3 net votes
Emma Lindqvist@emmalindqvist#4 in SELECT * Anonymous
This is extremely helpful, thank you. Compression segmented by device_id is exactly what I was missing. We will prototype the hierarchical aggregates this week.
1 net votes
Arjun Mehta@arjunmehta#1 in SELECT * Anonymous
+1 for staying in Postgres if the team knows it, familiarity is worth a lot at 3am. One small thing: define the daily aggregates in the time zone you report in, otherwise the daily totals per region will be shifted by some hours and finance will ask you why
0 net votes