You can store timestamps in PostgreSQL. You can add a created_at
column, index it, partition by month, and query time ranges. It works. For
moderate volume (a few million rows, a handful of metrics), it works well enough
that adding another database to your infrastructure would cost more than it saves.
So why does Prometheus exist? Why do companies pay for InfluxDB Cloud when they already have a database? The answer isn't that PostgreSQL engineers didn't think of time ranges. It's that time-series data has structural properties that general-purpose databases aren't designed for, and those structural mismatches compound as data volume grows.
What makes time-series data different
The write pattern is almost always append-only. A CPU metric is recorded at timestamp T, then T+15s, then T+30s. Nobody updates the reading at T+15s after the fact. There are no UPDATE statements. Historical data is immutable. This is different from a user table, where you update an email address or increment a counter. Time-series data grows in one direction at a predictable rate.
Queries are almost always time ranges. "Show me CPU usage for host web-01 over the last hour." "Give me p99 request latency for the payments service over the last 7 days." The time dimension is the primary access pattern. Queries that don't involve a time range are the exception.
Retention is time-based. Raw metrics from 18 months ago aren't useful. You keep 15-second resolution data for 7 days, hourly averages for 6 months, and daily summaries for 2 years. The database needs to expire old data automatically and optionally downsample it (replace raw samples with aggregates). General-purpose databases don't do this natively.
These aren't just access pattern preferences. They describe the shape of the data in a way that makes specialized storage tractable.
What general-purpose databases do poorly
The problems with storing time-series data in PostgreSQL aren't obvious at small scale. They emerge gradually, and by the time they're painful, you have a lot of data that you'd rather not migrate.
Index maintenance is the first friction point. Every insert into a PostgreSQL
table updates every index on that table. A B-tree index on timestamp
must be kept sorted; each new row requires finding the right position in the
tree and inserting there. At low insert rates, this is fast. At high insert rates
(millions of metric samples per second across thousands of time series), index
maintenance becomes a significant fraction of total write latency. PostgreSQL's
B-tree is optimized for random access patterns, not sequential append.
Table partitioning by time helps. PostgreSQL supports declarative partitioning: you define monthly or daily partitions, and each partition is a separate physical table with its own indexes. New inserts go into the current partition, which has a small, hot index. Range queries across a time range touch only the relevant partitions. This is genuinely useful and closes much of the gap at moderate scale. But partitioning requires setup, and the automation around creating new partitions and dropping old ones is something you have to build yourself.
Retention via DELETE is the most persistent problem. Removing data from a PostgreSQL table doesn't immediately reclaim space: it marks rows as dead and leaves them in the heap. A background process called autovacuum eventually cleans them up, but until then, the space is occupied and table scans have to skip over dead rows. Deleting 30 days of metrics at once generates a large cleanup workload that competes with live queries. At scale, this is a recurring operational problem.
What a TSDB optimizes for
A time-series database is built around the specific properties above. The differences aren't magic: they're engineering tradeoffs that optimize for append-heavy writes, time-range reads, and time-based expiration, at the cost of everything else.
Storage is columnar per metric rather than row-based. Rather than storing rows of
(timestamp, metric_name, value), a TSDB stores each metric as a
separate column-like stream. Prometheus calls these "time series" and identifies
them by a metric name plus a set of labels (key-value pairs like
host="web-01", region="us-east"). All values for a given time series
are stored together, sorted by time. This makes time-range reads for a single
metric sequential, optimal for both disk and CPU cache.
Indexing is on labels, not values. Prometheus and InfluxDB index the label dimensions separately from the data. A query like "give me all CPU metrics for hosts in us-east" resolves the label filter to a set of time series IDs, then reads those series directly.
Retention and downsampling are built in. Prometheus's storage engine handles retention natively: old blocks are deleted as units, not row by row. Thanos and Cortex (distributed Prometheus systems) add downsampling, automatically computing 5-minute and 1-hour rollups from raw data and serving queries from the appropriate resolution. This happens without any application code.
The compression story
The compression ratios available for time-series data are striking, and they matter because storage at scale is not free.
Facebook's Gorilla paper (2015) described a compression scheme designed specifically
for time-series data and reported 1.37 bytes per sample, down from 16 bytes for a
naive (int64 timestamp, float64 value) pair. That's roughly 12x
compression. Prometheus's chunk format achieves similar ratios.
The mechanism: timestamps compress as deltas-of-deltas. If your scrape interval is exactly 15 seconds, consecutive timestamps differ by exactly 15,000 milliseconds. The delta is constant. The delta-of-delta is zero. Zero compresses to a handful of bits. Small variations from the exact interval (15,001ms, 14,998ms) produce small non-zero deltas that still encode in a few bits.
Float values compress with XOR encoding. XOR the current value with the previous value; if the metric is slowly changing (CPU usage going from 42.3% to 42.4% to 42.6%), the XOR result is mostly zeros in the high bits. Leading zeros compress trivially. Large sudden changes produce larger XOR results, but those are the exception.
Together, these schemes turn 16 bytes per sample into roughly 1-2 bytes. At a billion samples per day (not unusual for a mid-sized infrastructure monitoring system), that's the difference between 16 GB/day and 1-2 GB/day. At 3 years of retention, it's the difference between 17 TB and roughly 1 TB. That's real money and real operational burden.
When you don't need one
A TSDB is not always the right answer. The operational cost of running another database (learning it, upgrading it, backing it up, debugging it at 2am) is real. Adding Prometheus to your stack makes sense when you already need it for monitoring. Adding InfluxDB as a data store for your application's analytics requires a stronger justification.
If your write volume is millions of events per day rather than billions, PostgreSQL with monthly partitions and a cron job that deletes old partitions is a legitimate solution. You lose 12x compression, you trade native downsampling for a scheduled job that materializes aggregates, and you get a simpler operational picture.
If your queries are not purely time-range-based (you want joins against other tables, full-text search over event descriptions, aggregations that group by non-time dimensions), a TSDB is the wrong tool. TSDBs don't do joins. They don't have query planners that can reason across tables. They're extremely good at one thing.
TimescaleDB is a PostgreSQL extension that adds time-series optimizations to Postgres: automatic time-based partitioning (chunks), columnar compression for cold data, and continuous aggregates that materialize rollups automatically. It's a genuine middle ground: you keep SQL, keep your existing PostgreSQL tooling, and get most of the storage and query performance benefits of a purpose-built TSDB. For teams already on Postgres, it's often the right call.
The question to ask
A TSDB makes sense when you have high-cardinality timestamped data, your queries are predominantly time ranges, and you have retention requirements that make bulk expiration a regular occurrence. If your volume is moderate, your queries cross dimensions a TSDB can't handle, or the operational overhead of another system outweighs the benefit, stay on PostgreSQL.
The decision isn't "which is better." It's "which mismatches between storage model and access pattern am I willing to live with." General-purpose databases are flexible because they don't optimize for any one pattern. Specialized databases are fast because they do. Whether your workload is specialized enough to justify the narrow tool is the only question worth answering.
Back to writing