Time-Series Patterns Explained
Time-series data looks like ordinary rows with a timestamp column, but the access patterns around it are different enough to justify different storage decisions.
//
Search across all documentation pages
Time-series data looks like ordinary rows with a timestamp column, but the access patterns around it are different enough to justify different storage decisions.
This page explains why: the write pattern, the partitioning idea, the downsampling idea, and the tension between a row-store transactional engine and an analytical workload, without repeating the concrete DDL and index steps owned by the other pages in this section.
Most transactional tables experience a mix of inserts, updates, and deletes spread across the whole dataset, which is what row-store engines like Postgres are tuned for.
Time-series tables are different because new rows almost always describe "now," existing rows are rarely updated, and old rows are eventually deleted in bulk rather than one at a time.
That pattern is called append-mostly, and it is the single fact that most time-series design decisions trace back to.
Because new data always lands at the end of the timeline, rows that are physically close together in the table also tend to be close together in time, if the table is loaded in time order.
That physical correlation is valuable, because it means "give me last hour's data" can become "give me the last physical chunk of the table" instead of "search the whole table for matching timestamps."
Queries against time-series data also skew heavily toward recent data, with dashboards, alerts, and debugging sessions overwhelmingly asking about the last few hours or days rather than the full history.
A simple analogy is a filing cabinet organized by date instead of by name: pulling this week's folders is fast because they are physically grouped together, while pulling one specific name from an alphabetized cabinet spanning years would mean checking every drawer.
Time partitioning, or chunking, is the mechanism that turns that intuition into an actual physical structure the database can exploit, and its concrete syntax belongs to the basics page in this section.
A single giant table with all history in one heap forces every query, even one asking for "the last hour," to potentially touch storage spread across years of data.
Partitioning by time range splits that one heap into many smaller physical tables, each covering a bounded time window, while still presenting one logical table to queries.
The planner can then perform partition pruning: if a query's time filter only overlaps one or two partitions, every other partition is skipped entirely before any row is read.
This is the mechanical reason time partitioning helps both reads and writes: reads skip irrelevant chunks, and retention deletes become a fast DROP TABLE on an old partition instead of a slow, WAL-heavy row-by-row delete.
Downsampling is a related but distinct idea, and it operates on precision rather than on physical placement.
Raw time-series data is usually collected at high resolution, such as once per second or once per minute, because that resolution matters for recent, operational questions.
That same resolution is rarely useful once the data is a year old, because nobody is debugging a specific second from thirteen months ago, they are looking at trends.
A rollup precomputes coarser summaries, such as hourly or daily averages, so that queries over long time ranges read a small summarized table instead of aggregating millions of raw rows on every request.
-- Conceptually: three grains of the same fact, each cheaper to scan as it coarsens
-- raw: one row per minute, kept for weeks
-- hourly: one row per hour, kept for years (built from raw)
-- daily: one row per day, kept indefinitely (built from hourly)Rollups and partitioning solve different halves of the same problem: partitioning controls which physical chunks a query has to touch, while rollups control how much a query has to compute once it gets there.
Continuous aggregates are the automated version of a rollup, incrementally refreshing a summary table as new raw data arrives instead of recomputing everything from scratch on a schedule, and the concrete mechanics of building one live on this section's dedicated page.
Row-store OLTP engines like Postgres are built around efficient point lookups, updates, and small transactional reads, which is a different optimization target than scanning and aggregating billions of rows.
Analytical, OLAP-style engines are built the opposite way: they favor columnar storage and heavy scan-and-aggregate throughput over fast single-row updates.
Time-series data sits uncomfortably between these two worlds, because it is written like an OLTP workload but frequently queried like an OLAP workload.
Partitioning and rollups are both ways of narrowing that gap without leaving Postgres, by shrinking the amount of raw data any single query has to touch.
At high enough volume or query complexity, that gap can widen past what partitioning and rollups can close inside a row-store engine, which is when teams consider replicating data out to a dedicated OLAP system.
That is a real architectural fork, not a tuning knob, and the decision criteria for taking it belong to the OLAP offload page in this section rather than this conceptual one.
Retention policy is the piece that ties chunking and downsampling together in production: raw data is kept at full resolution for a short window, then either dropped or replaced by its rollup as it ages out.
Observability for a time-series system should track both partition creation lead time, so writes never hit a missing future partition, and rollup freshness, so dashboards never silently serve stale aggregates without anyone noticing.
| Technique | Strength | Weakness | Best Fit |
|---|---|---|---|
| Time partitioning (chunking) | Fast pruning, cheap retention via DROP TABLE | Adds DDL and automation overhead | Any table with strong time-bounded query and retention patterns |
| Downsampling / rollups | Small tables for long-range dashboard queries | Loses precision on historical data | Trend and reporting queries over weeks or years |
| OLAP offload | Purpose-built for heavy scan-and-aggregate workloads | Separate system, replication lag, added operational surface | Analytical workloads that outgrow what Postgres can comfortably serve |
Its writes are almost entirely appends of new "now" data, and its reads skew heavily toward recent time windows, which is different from the mixed read/write/update pattern OLTP tables usually see.
Because new rows land together in time, rows that are physically close in storage also tend to be close in time, and partitioning turns that correlation into a real performance advantage.
DROP TABLE instead of a slow row-by-row delete.No, downsampling replaces fine-grained historical data with a coarser summary, while deletion removes data outright, and systems often do both at different ages.
Because aggregating millions of raw rows on every dashboard load is expensive, while a precomputed hourly or daily rollup answers the same trend question from a much smaller table.
Row-store OLTP engines are optimized for point lookups and updates, while time-series queries often want to scan and aggregate large time ranges, which is closer to an analytical access pattern.
No, partitioning controls which physical chunks a query has to touch, while rollups control how much computation a query has to do once it gets there, and they typically work together.
When query volume or analytical complexity grows past what partitioning and rollups can close inside a row-store engine, which is when teams evaluate offloading to a dedicated OLAP system.
Because aggregates address query cost on existing data, not storage growth, so raw data still needs a plan for how long it stays at full resolution.
No, it mainly helps when queries and retention actually filter or drop by the partition key, and a table without time-bounded access patterns gains little from it.
Partition creation lead time, so future writes never hit a missing partition, and rollup freshness, so dashboards never silently serve stale summaries.
Dropping a partition is a fast metadata operation, while deleting millions of individual rows generates heavy write-ahead log traffic and table bloat for the same end result.
Stack versions: This page is conceptual and not tied to a specific stack version.
Reviewed by Chris St. John·Last updated Jul 15, 2026