LayerbaseLayerbase

What Is a Time-Series Database?

QuestDBInfluxDBTime Series

You have data with timestamps. Server metrics, sensor readings, application logs, financial ticks. Every row has a created_at or recorded_at column, and your queries almost always include a WHERE timestamp > ... clause.

PostgreSQL can store all of it. So can MySQL. Any relational database handles timestamps just fine. But at some point, you start noticing that your aggregation queries are slow, your table is growing faster than you expected, and you're writing the same date_trunc + GROUP BY pattern over and over. That's when the question comes up: do I need a specialized database for this?

Sometimes the answer is no. Sometimes it's absolutely yes. Here's how to tell the difference.

Contents

What Makes Time-Series Data Different

Time-series data has a distinct shape that separates it from typical application data. Understanding these patterns helps explain why specialized databases exist.

Append-mostly writes. You almost never update an old sensor reading or go back and change a metric from yesterday. Data arrives, gets written, and stays as-is. This is fundamentally different from a users table where you're constantly updating emails, passwords, and preferences. Time-series workloads are write-heavy and update-light.

Queries always filter by time range. "Show me the last hour of CPU usage." "What was the average temperature between 2pm and 6pm?" "Give me all error rates from the past 7 days." The time column is in virtually every query. General-purpose databases can index a timestamp column, but they don't organize storage around it.

Data ages out. Per-second metrics from two years ago are rarely useful at their original resolution. You might want hourly averages from last year, but you don't need 31 million individual data points. Time-series data has a natural lifecycle: high resolution when fresh, summarized as it ages, eventually deleted.

Write volumes can be enormous. A single server reporting CPU, memory, disk, and network metrics every 10 seconds generates 34,560 data points per day. A fleet of 1,000 servers generates 34 million. Add application-level metrics (request latency, error rates, queue depths) and you're looking at hundreds of millions of rows per day. That's not an edge case. That's a normal monitoring setup.

What a Time-Series Database Does Differently

A time-series database (TSDB) is built around the patterns above. It's not a general-purpose database with a timestamp index bolted on. The storage engine, query optimizer, and data lifecycle tools are all designed for time-ordered, append-mostly data.

Time-based partitioning. Data is physically organized by time intervals (hourly, daily, weekly). When you query "the last 24 hours," the database reads only the partitions that contain that range. It doesn't scan your entire 500GB table to find yesterday's rows.

Compression for repetitive patterns. Timestamps in time-series data are highly regular. If a sensor reports every 15 seconds, the database can store the start time and interval instead of every individual timestamp. Numeric values from the same source tend to be close together, which compresses well with delta or XOR encoding. Real-world compression ratios of 10:1 or better are common.

Built-in downsampling and retention. Instead of writing cron jobs to aggregate old data and delete raw rows, a TSDB handles it natively. Keep raw data for 7 days. Roll it up to 1-minute averages for 90 days. Delete everything older than a year. Set it once and forget about it.

Optimized write path. Time-series writes are sequential (data arrives roughly in order) and don't need the transaction overhead of a general-purpose database. TSDBs exploit this with write-ahead logs, batched ingestion, and lock-free append operations that handle millions of writes per second.

Time-bucketed aggregation as a first-class operation. "Average value per 15-minute window" is not an afterthought. It's a core operation with dedicated syntax. In QuestDB, it's SAMPLE BY 15m. In InfluxDB, it's aggregateWindow(every: 15m, fn: mean). Compare that to PostgreSQL's date_trunc('minute', ts) + (EXTRACT(minute FROM ts)::int / 15) * interval '15 minutes'.

When PostgreSQL Is Enough

PostgreSQL is a remarkably capable database, and plenty of time-series workloads run fine on it. You probably don't need a TSDB if:

  • Write volume is moderate. Hundreds or low thousands of writes per second. PostgreSQL handles this without breaking a sweat, especially with proper indexing and partitioning.
  • Your data fits on one server. If your total dataset is tens of gigabytes and growing slowly, PostgreSQL's native table partitioning (available since version 10) gives you many of the benefits of time-based partitioning.
  • You don't need sub-second query latency on large ranges. If "show me the last month of hourly averages" taking 2 seconds is acceptable, PostgreSQL will get it done.
  • Your team already knows PostgreSQL. There's real value in not adding another database to your stack. One more database means one more thing to monitor, back up, upgrade, and debug at 2am.
  • You only need time-series alongside other data. If your app is primarily a CRUD application that also happens to store some metrics, keeping everything in PostgreSQL avoids the complexity of running two databases and joining across them.

The TimescaleDB extension deserves a mention here. It adds time-series capabilities (automatic partitioning, continuous aggregates, compression, retention policies) to PostgreSQL. If you want TSDB features without leaving PostgreSQL, it's a strong option.

When You Need a Dedicated TSDB

The signs are usually obvious when you hit them:

  • High write throughput. Tens of thousands of writes per second and up. You're running a fleet of servers, a network of IoT devices, or a trading platform with tick-by-tick data. PostgreSQL starts struggling with write amplification and index maintenance at these volumes.
  • You need automatic retention and downsampling. Manually managing data lifecycle with cron jobs and DELETE queries gets brittle fast. If you're writing scripts to aggregate old data into summary tables and purge raw rows, a TSDB does this out of the box.
  • Time-bucketed aggregation is your primary query pattern. If most of your queries are "average X per Y-minute window, grouped by Z," a TSDB makes these trivial to write and fast to execute. Writing date_trunc and GROUP BY for the hundredth time is a signal.
  • Data measured in billions of rows. At this scale, compression and time-partitioned storage aren't nice-to-haves. They're the difference between a database that fits on reasonable hardware and one that doesn't.
  • You need real-time dashboards with sub-second latency. Monitoring dashboards that refresh every few seconds and query across millions of recent data points. TSDBs are optimized for exactly this access pattern.

Your Options

Two time-series databases worth looking at, each with a different philosophy:

QuestDB extends SQL with time-series primitives. If your team writes SQL, QuestDB is immediately productive. SAMPLE BY 15m for time-bucketed aggregation, LATEST ON for the most recent value per group. It speaks the PostgreSQL wire protocol, so you connect with any PG client library. No new query language to learn.

InfluxDB takes a different approach entirely. Its own data model (measurements, tags, fields), its own query language (Flux), and deep integration with the Telegraf metrics collection agent. The learning curve is steeper, but the monitoring ecosystem around it is mature and well-tested.

For a detailed comparison with runnable code, see QuestDB vs InfluxDB. For getting-started guides with full TypeScript projects:

Managed Time-Series Databases with Layerbase Cloud

QuestDB and InfluxDB are both available on Layerbase Cloud if you want a managed time-series database instead of a local development server. Create QuestDB when you want SQL and PostgreSQL-compatible connections. Create InfluxDB when you want the Influx data model, Flux, and the Telegraf ecosystem.

Use SpinDB or Layerbase Desktop when you want to experiment locally. Use Layerbase Cloud when you want a hosted endpoint your application or dashboard can reach from anywhere.

Try It: QuestDB with SAMPLE BY

The fastest way to see the difference is to run a query. SpinDB will have QuestDB running in seconds. (What is SpinDB?)

bash
npm i -g spindb
spindb create quest1 -e questdb --start

Connect and create a table with some sample data:

bash
spindb connect quest1
sql
CREATE TABLE IF NOT EXISTS metrics (
  host SYMBOL,
  cpu_usage DOUBLE,
  mem_usage DOUBLE,
  ts TIMESTAMP
) timestamp(ts) PARTITION BY HOUR WAL;

INSERT INTO metrics VALUES ('web-1', 45.2, 62.1, '2026-03-14T10:00:00Z');
INSERT INTO metrics VALUES ('web-2', 72.8, 58.3, '2026-03-14T10:00:05Z');
INSERT INTO metrics VALUES ('web-1', 47.1, 63.4, '2026-03-14T10:15:00Z');
INSERT INTO metrics VALUES ('web-2', 68.5, 59.1, '2026-03-14T10:15:05Z');
INSERT INTO metrics VALUES ('web-1', 51.3, 65.7, '2026-03-14T10:30:00Z');
INSERT INTO metrics VALUES ('web-2', 71.2, 57.8, '2026-03-14T10:30:05Z');
INSERT INTO metrics VALUES ('web-1', 44.9, 61.2, '2026-03-14T10:45:00Z');
INSERT INTO metrics VALUES ('web-2', 69.4, 60.5, '2026-03-14T10:45:05Z');

Now run a time-bucketed aggregation:

sql
SELECT
  host,
  avg(cpu_usage) as avg_cpu,
  avg(mem_usage) as avg_mem
FROM metrics
SAMPLE BY 30m
ALIGN TO CALENDAR;
text
host   | avg_cpu | avg_mem
-------|---------|--------
web-1  |   46.15 |  62.75
web-2  |   70.65 |  58.70
web-1  |   48.10 |  63.45
web-2  |   70.30 |  59.15

That's SAMPLE BY 30m. Two words instead of a date_trunc expression, a GROUP BY clause, and an ORDER BY. Change it to 1h for hourly, 1d for daily, 5m for five-minute buckets. The database handles the rest.

Compare that to the PostgreSQL equivalent:

sql
SELECT
  host,
  date_trunc('hour', ts)
    + (EXTRACT(minute FROM ts)::int / 30) * interval '30 minutes'
    AS bucket,
  avg(cpu_usage) AS avg_cpu,
  avg(mem_usage) AS avg_mem
FROM metrics
GROUP BY host, bucket
ORDER BY bucket, host;

Same result. More typing. And at scale, SAMPLE BY runs faster because QuestDB's storage engine is built for exactly this operation.

To manage your instance:

bash
spindb stop quest1     # Stop QuestDB
spindb start quest1    # Start it again
spindb list            # See all your database instances

SpinDB manages 20+ database engines from one CLI. Run QuestDB for time-series alongside PostgreSQL for your app and Redis for caching. If you prefer a GUI, Layerbase Desktop is available for macOS. If you want a hosted time-series database, create QuestDB or InfluxDB on Layerbase Cloud.

Something not working?