Begin with the query you need to serve
“Should we use a time-series database?” is difficult to answer without the workload. “We need the latest 100 events for one account, within a date range, while retaining an auditable history” is a design problem that can be examined.
This walkthrough uses a simplified event-history schema. It is a reproducible design example, not a claim about a production performance result. Its purpose is to establish what to measure before introducing another datastore.
Revised September 13, 2026.
Match the index to the access pattern
CREATE TABLE account_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_id uuid NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL
);
CREATE INDEX account_events_recent
ON account_events (account_id, occurred_at DESC, id DESC);
The account identifier leads the index because the query first selects an account. Time and ID then provide a stable ordering. The ID breaks ties between events with the same timestamp.
SELECT id, occurred_at, payload
FROM account_events
WHERE account_id = $1::uuid
AND occurred_at >= $2::timestamptz
ORDER BY occurred_at DESC, id DESC
LIMIT 100;
The placeholders are parameters supplied by the application. The index is useful for this access pattern; it is not a promise that every query over the table will be fast. A cross-account aggregation presents a different problem.
Measure realistic distributions
Load representative data into an isolated test database. Include a few large accounts, many small accounts, uneven timestamps and realistic payload sizes. Uniform synthetic rows can hide the skew that makes a production query difficult.
Use EXPLAIN (ANALYZE, BUFFERS) on the read query to inspect actual rows, time and buffer activity. ANALYZE executes the statement, so treat it differently from a plan-only inspection, particularly for writes. PostgreSQL's documentation explains the output and its instrumentation overhead.
Measure the query under concurrent reads and ingestion. An improvement measured on a warm, idle database may disappear when writes and other queries compete for I/O. Record the dataset, hardware, concurrency and cache conditions with the result.
Retention is part of the design
An event history grows even when the current screen only displays 100 rows. Decide which records must remain individually queryable, which can be aggregated and which can expire.
For a time-partitioned table, retiring an old partition can simplify retention compared with repeatedly deleting large row ranges. The additional partition management, key constraints and query patterns still need to be evaluated. Partitioning is an operating choice, not a universal performance switch.
Late-arriving events make the distinction between event time and ingestion time important. If reports must be corrected when old events arrive, define how long aggregates remain open to revision. A faster database cannot choose that policy for the product.
A second system needs an ownership rule
A search index or analytics store can be a derived view of records owned by Postgres. That is different from allowing two databases to independently own the same facts.
Specify how the derived system catches up after an outage, handles deletions and rebuilds from its source. Decide whether a user must immediately see their latest write in search. Eventual consistency may be entirely acceptable for discovery and unacceptable for a permissions check.
Extensions broaden the available choices: pgvector, for example, supports exact and approximate vector search. Approximate indexes trade some recall for speed, and filtering can affect the returned candidates. Those properties need workload-specific evaluation; the existence of an extension does not settle the database decision.
Define the exit condition
Write down the condition that would justify moving: an unacceptable latency distribution, sustained resource contention, a query capability you cannot support economically, or a recovery requirement the design cannot meet.
Then compare alternatives with the same workload and include synchronisation, backup and operating effort. Postgres earns its place when it satisfies the requirement at an acceptable total cost. The useful habit is to make the requirement visible before expanding the architecture.