Skip to content
Tutorials Center
My learning

Topics

Lesson 59 of 90 ยท Extensions and Ecosystem

Time-Series, Queues and Caching in PostgreSQL

Use PostgreSQL for time-series data, SKIP LOCKED job queues, unlogged cache tables and scheduled jobs with pg_cron, and learn when to add other tools.

A popular rule of thumb says "use PostgreSQL until it hurts". It is popular because PostgreSQL is surprisingly good at jobs that people hand to specialised systems: storing millions of sensor readings, running a background job queue, keeping a quick cache, and running scheduled maintenance. Using one database for all of that means fewer moving parts, transactions that span your data and your jobs, and one backup to restore.

This lesson shows the PostgreSQL features behind each of those four jobs and, just as important, where they stop being the right tool. The PostgreSQL features (partitioned tables, BRIN indexes, SKIP LOCKED, unlogged tables, pg_cron, TimescaleDB) need a real server, so those blocks are read-only. Two live editors rebuild the ideas in SQLite: bucketing readings by hour and showing why a BRIN index works, then a job queue that claims work with UPDATE ... RETURNING.

Time-series data

Time-series data is a stream of timestamped measurements: sensor readings, request logs, stock prices, metrics. It has a recognisable shape: rows are appended in time order, almost never updated, queried by time range, and eventually thrown away.

SQL
CREATE TABLE readings (
    sensor_id integer          NOT NULL,
    ts        timestamptz      NOT NULL,
    value     double precision NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE readings_2026_03 PARTITION OF readings
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE readings_2026_04 PARTITION OF readings
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

Partitioning by time is the single most useful technique. Queries with a time filter only touch the partitions that can contain matching rows (this is called partition pruning), indexes stay small because each partition has its own, and deleting old data is instant: instead of a huge DELETE that leaves dead rows for vacuum to clean up, you drop a whole partition.

SQL
-- Retention: remove everything from March in one cheap step
ALTER TABLE readings DETACH PARTITION readings_2026_03;
DROP TABLE readings_2026_03;

Declarative Partitioning and Managing Partitions in Production cover the details, including creating partitions automatically.

Bucketing: downsampling by time

Raw readings are too many to chart, so you group them into buckets. date_trunc rounds a timestamp down to a unit (hour, day), and date_bin (PostgreSQL 14+) rounds to any interval:

SQL
SELECT date_trunc('hour', ts) AS hour,
       avg(value) AS avg_value,
       min(value), max(value)
FROM readings
WHERE ts >= now() - interval '1 day'
GROUP BY hour
ORDER BY hour;

-- Fifteen-minute buckets
SELECT date_bin('15 minutes', ts, timestamptz '2026-01-01') AS bucket,
       count(*)
FROM readings
GROUP BY bucket;

SQLite has no date_trunc, but strftime does the same job, so you can practice the grouping live. The next editor generates 600 readings, one every ten minutes, and groups them by hour and by day:

SQL Hourly buckets and BRIN block ranges

Press Run (or Ctrl + Enter) to see the result here.

BRIN: tiny indexes for ordered data

The last query in that editor is a model of a BRIN (Block Range INdex). A B-tree stores an entry for every row. A BRIN index stores just a minimum and maximum per range of table pages. Look at the ts columns in the result: each block covers its own consecutive time window, and the windows do not overlap. A query for one day can skip every block whose range does not include it. Now look at noise: every block spans nearly 0 to 999, so its min and max rule out nothing, and a BRIN index on that column would be useless.

SQL
CREATE INDEX readings_ts_brin ON readings USING brin (ts) WITH (pages_per_range = 32);

That is the rule for BRIN: it only helps when the physical order of rows correlates with the column's values, which is exactly what happens when you append timestamped rows. In return it is tiny, often thousands of times smaller than a B-tree, and cheap to maintain on heavy inserts. It is a rough filter, not a precise one: PostgreSQL still reads every page in a matching range. BRIN, Hash and SP-GiST Indexes goes deeper.

Time-series toolkit in plain PostgreSQL

Partition by time Pruning and instant retention.
BRIN index Tiny index for append-only data.
date_trunc / date_bin Bucket readings for charts.
Rollup tables Store hourly and daily summaries.

Keep raw data for weeks, summaries for years.

1 / 2

TimescaleDB in one paragraph

TimescaleDB is a third-party extension that automates the techniques above. You turn a table into a hypertable (SELECT create_hypertable('readings', 'ts');) and it creates and manages time partitions called chunks for you. It adds columnar compression of old chunks, continuous aggregates (materialized rollups that refresh incrementally), and retention policies you set with one function call. It is a strong choice for heavy ingest or long histories, but check two things first: whether your host offers it (many managed services do not), and which of its features sit under a different license than the PostgreSQL license. For moderate volumes, plain partitioning plus BRIN is often all you need. See Extensions for how extension availability works.

A job queue with SKIP LOCKED

A background job queue needs three things: workers that never take the same job twice, jobs that are not lost when a worker crashes, and decent throughput. A plain table with a status column can do it, if you claim jobs correctly.

The naive approach, SELECT the first queued job and then UPDATE it, has a race: two workers can select the same row. Adding FOR UPDATE fixes duplication but makes workers queue behind each other, because the second worker blocks on the first one's locked row. The answer is FOR UPDATE SKIP LOCKED: a worker takes the first row that is not currently locked by anybody else and skips the rest.

SQL
CREATE TABLE jobs (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload    jsonb       NOT NULL,
    status     text        NOT NULL DEFAULT 'queued',
    attempts   integer     NOT NULL DEFAULT 0,
    started_at timestamptz
);
CREATE INDEX jobs_queued_idx ON jobs (id) WHERE status = 'queued';   -- partial index

-- Claim one job atomically
WITH next AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET status = 'running', attempts = attempts + 1, started_at = now()
FROM next
WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;

Each worker runs that statement in a loop. Two workers running it at the same moment each get a different row; neither waits. When the work is finished, the worker sets status = 'done' (or deletes the row). Because the claim and the work's own database changes can happen in one transaction, a crash rolls the claim back and the job returns to the queue automatically. Locks, Row Locking and Deadlocks explains the locking behind this, and Project: Job Queue with SKIP LOCKED builds a complete queue.

SKIP LOCKED needs real row locks held by concurrent sessions, so it cannot run in the browser. What does run is the state machine around it: claim, finish, and recover stuck jobs. SQLite supports UPDATE ... RETURNING, so a single worker can claim jobs one at a time exactly like the real query, just without the concurrency guarantee.

SQL Claiming jobs and recovering stuck ones

Press Run (or Ctrl + Enter) to see the result here.

The recovery query is a visibility timeout: a job that has been running too long is assumed dead and made available again. Notice that job 2 keeps attempts = 1, so a real queue can stop retrying after a few attempts and mark the job failed.

Where does a PostgreSQL queue stop being a good idea? It is a fine fit for thousands of jobs per second and for work that must be consistent with your data. If you need fan-out to many consumers, replay of old messages, or sustained rates far beyond that, a dedicated broker such as Kafka, RabbitMQ or a cloud queue service is the better tool. For waking workers up without polling, see Event Triggers, LISTEN and NOTIFY.

Caching with unlogged tables

Every normal table write goes first into the write-ahead log (WAL), which makes it crash-safe and lets replicas copy it. For data you can afford to lose, such as a cache or a staging area, that logging is wasted effort. An unlogged table skips the WAL:

SQL
CREATE UNLOGGED TABLE page_cache (
    cache_key  text PRIMARY KEY,
    body       jsonb       NOT NULL,
    expires_at timestamptz NOT NULL
);

-- Read through the cache
SELECT body FROM page_cache WHERE cache_key = 'home' AND expires_at > now();

-- Write, replacing any old entry
INSERT INTO page_cache (cache_key, body, expires_at)
VALUES ('home', '{"hello":"world"}', now() + interval '5 minutes')
ON CONFLICT (cache_key) DO UPDATE
    SET body = EXCLUDED.body, expires_at = EXCLUDED.expires_at;

-- Remove expired entries now and then
DELETE FROM page_cache WHERE expires_at <= now();

Writes to unlogged tables are noticeably faster, but the trade-offs are real:

  • After a crash (or an immediate shutdown) the table is emptied. A clean shutdown keeps it.
  • Unlogged tables are not replicated to standbys, and are not usable on a read replica.
  • Indexes on them are unlogged too, and a backup tool may not include their contents.
  • You can switch with ALTER TABLE page_cache SET LOGGED or SET UNLOGGED, but changing to LOGGED rewrites the table.

Use them for caches, session-like data you can rebuild, and bulk-load staging tables (see Bulk Loading and COPY). If you need sub-millisecond lookups at very high rates, an in-memory store such as Redis is still faster; an unlogged table is simply the cheapest cache you can start with, because it is already there.

Scheduling with pg_cron

Time-series tables need new partitions and cleanup; caches need purging; summaries need refreshing. Instead of a separate cron server, the third-party pg_cron extension runs SQL on a schedule from inside the database. It must be listed in shared_preload_libraries and needs a restart, and the cron.database_name setting tells it where its job table lives.

SQL
CREATE EXTENSION pg_cron;

-- Every night at 03:30 (standard five-field cron syntax)
SELECT cron.schedule(
    'purge-cache',
    '30 3 * * *',
    $$DELETE FROM page_cache WHERE expires_at <= now()$$
);

-- Every hour, refresh a summary table
SELECT cron.schedule(
    'hourly-rollup',
    '0 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_readings$$
);

SELECT jobid, jobname, schedule FROM cron.job;               -- what is scheduled
SELECT status, start_time, return_message
FROM cron.job_run_details ORDER BY start_time DESC LIMIT 5;  -- how it went
SELECT cron.unschedule('purge-cache');

Because each job is just a SQL command, anything you can write in a transaction can be scheduled, including a call to a stored procedure. Remember that a schedule lives in the database: it is included in backups and moves with the data, but a restored copy on a test server will start running the same jobs unless you remove them. Many managed services offer pg_cron (check the allowlist), and some have their own scheduler instead.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is partitioning a time-series table by month good for data retention?

    Dropping or detaching a partition is a metadata operation. A big DELETE would write dead rows that vacuum has to clean up afterwards.
  2. 2. When is a BRIN index effective?

    BRIN stores the min and max of each block range. If values are ordered across blocks, whole ranges can be skipped. For random values every range overlaps and the index cannot exclude anything.
  3. 3. What does FOR UPDATE SKIP LOCKED do in a job-claiming query?

    SKIP LOCKED lets concurrent workers each claim a different job without blocking each other.
  4. 4. What happens to an unlogged table after a crash?

    Unlogged tables bypass the write-ahead log, so PostgreSQL cannot recover their contents after a crash and truncates them. They are also not replicated.
  5. 5. What must be configured before pg_cron can schedule jobs?

    pg_cron is a third-party extension that hooks into the server at startup, so it has to be preloaded and the server restarted. Managed services usually do this for you.

Summary

  • Time-series: partition by time for pruning and instant retention, bucket with date_trunc or date_bin, keep rollup tables, and add a tiny BRIN index on append-only timestamps.
  • TimescaleDB automates chunking, compression and continuous aggregates; check host support and licensing, and skip it when plain partitioning is enough.
  • Queues: claim jobs with FOR UPDATE SKIP LOCKED ... LIMIT 1 inside an UPDATE ... RETURNING, add a visibility timeout and an attempt counter, and use a partial index on queued rows.
  • Caching: UNLOGGED tables are faster to write but are emptied after a crash and are not replicated.
  • pg_cron runs scheduled SQL from inside the database; it needs shared_preload_libraries and a restart.
  • Reach for Kafka, Redis or a dedicated time-series database only when volume or features outgrow these tools.

Next: EXPLAIN and EXPLAIN ANALYZE, where you learn to read what PostgreSQL really does with your queries.