PostgreSQL’s table access method (TAM) API lets you run different storage engines per table in the same database. For agentic systems, this means you can store mutable conversation state in heap tables, append-only event logs in columnar tables, and time-series telemetry in hypertables without leaving PostgreSQL or managing cross-database replication.
The TAM API shipped in PostgreSQL 12 (October 2019). Six years later, the ecosystem has stabilized around a few credible options. This is not a drop-in replacement story. The API is a tuple-shaped abstraction designed around the heap and then partially genericized. You will hit constraints. But if your agent infrastructure needs different data access patterns in the same deployment, the tradeoffs are now clear enough to make informed decisions.
Why agentic workflows care about storage engines
Multi-agent systems produce three distinct data shapes:
- Mutable state: Conversation history, agent memory, task queues. Frequent updates, row-level locking, ACID guarantees.
- Append-only logs: Tool calls, decision traces, audit trails. Write-heavy, rarely updated, scanned for analytics.
- Time-series telemetry: Agent performance metrics, token usage, latency histograms. Unbounded growth, time-based queries, automatic rollups.
The default PostgreSQL heap handles mutable state well. It is terrible at analytical scans over append-only logs (full table scans touch every row version). It does not partition time-series data automatically, so your observability tables grow until vacuum chokes.
Pluggable storage engines let you optimize per table. Heap for state, columnar for logs, hypertables for telemetry. All in one database, all queryable with standard SQL, all backed up together.
The TAM API constraint surface
The table access method API defines 38 callbacks in TableAmRoutine. A few are mandatory (scan_begin, tuple_insert, tuple_delete, index_fetch_tuple). Most assume you have a tuple identifier (TID) consisting of a block number and an offset.
This is fine if your storage is page-shaped. It is a problem if you are building a columnar engine that stores data by column, not by row. The TID assumption couples the TAM API to the index access method API. Indexes expect to store TIDs and use them to fetch tuples.
If your columnar engine does not have honest block-and-offset TIDs, you have two options:
- Fake TIDs well enough to fool the index AMs (fragile, breaks on edge cases).
- Maintain a shadow row store with real TIDs (doubles storage, adds write amplification).
Most production columnar TAMs do the latter. This is not optional engineering. It is a constraint of the abstraction.
Storage engine options for agent infrastructure
| Engine | Access Pattern | Use Case | Write Amplification | Vacuum Behavior | Index Support |
|---|---|---|---|---|---|
| Heap (default) | Row-oriented OLTP | Mutable agent state, task queues | Low | Standard MVCC vacuum | Full |
| Columnar (zedstore, Hydra) | Column-oriented OLAP | Append-only event logs, analytics | High (shadow row store) | Column-level, slower | Limited (shadow TIDs) |
| TimescaleDB hypertables | Time-series partitioning | Agent telemetry, metrics | Medium (chunk overhead) | Per-chunk, automatic | Full (distributed) |
| Orioledb | Undo-log MVCC | High-churn transactional state | Low (no bloat) | No vacuum needed | Custom B-tree |
Heap: The default, still the best for mutable state
The heap is PostgreSQL’s original storage engine. It stores rows in 8KB pages, uses multi-version concurrency control (MVCC) to handle concurrent updates, and supports all index types.
For agent state that changes frequently (conversation history, memory updates, task status), heap tables are still the right choice. Row-level locking works. Indexes work. Vacuum works. The failure modes are well understood.
The problem is analytical queries. If you run SELECT COUNT(*) FROM agent_logs WHERE created_at > now() - interval '1 day', PostgreSQL scans every row version in the table, even if most rows are weeks old. For append-only logs, this is wasteful.
Columnar: Compression and scan speed for append-only logs
Columnar engines (zedstore, Hydra) store data by column instead of by row. This gives you:
- Better compression: Similar values in a column compress well (timestamps, agent IDs, status codes).
- Faster analytical scans: If you only need three columns, PostgreSQL only reads those three columns.
- Worse transactional performance: Updates and deletes are expensive because you have to rewrite entire column chunks.
The shadow row store constraint means every insert writes twice: once to the columnar store, once to the shadow heap. This doubles write amplification. For append-only logs (tool calls, decision traces), this is acceptable. For mutable state, it is not.
Failure mode: If an agent queries a columnar table with a row-oriented access pattern (fetching individual rows by ID), the query planner may fall back to the shadow row store or do a full column scan. You will see this in EXPLAIN output as a sequential scan with a high cost estimate.
TimescaleDB hypertables: Automatic partitioning for telemetry
TimescaleDB is an extension, not a TAM, but it solves the same problem for time-series data. It automatically partitions tables into chunks based on time intervals (default: one week). Old chunks can be compressed, moved to cheaper storage, or dropped.
For agent observability (token usage, latency, error rates), hypertables handle unbounded growth without manual intervention. You write to a single table, and TimescaleDB routes inserts to the correct chunk.
Plumbing detail: Hypertables use PostgreSQL’s native partitioning under the hood. Each chunk is a regular table. Indexes are chunk-local. Queries that span chunks use parallel workers. Compression uses columnar storage (via the Timescale-specific compression TAM).
Failure mode: If you query a hypertable without a time filter, PostgreSQL scans all chunks. This is slow. The query planner will warn you with a high cost estimate, but it will not stop you.
Orioledb: Undo-log MVCC for high-churn state
Orioledb is a TAM that replaces PostgreSQL’s MVCC implementation with an undo-log design. Instead of keeping old row versions in the table (which bloats the heap and requires vacuum), Orioledb stores only the current version and keeps undo records in a separate log.
This eliminates bloat for high-churn tables (agent memory that updates every turn, task queues that churn through thousands of jobs). No vacuum needed. No autovacuum storms.
Tradeoff: Orioledb uses custom B-tree indexes. Standard PostgreSQL index types (GIN, GiST, BRIN) do not work. If your agent queries need full-text search or spatial indexes, Orioledb is not an option.
Orchestration flow: Mixing engines in one deployment
A typical multi-agent deployment might use:
- Heap tables for conversation state, agent memory, task queues.
- Columnar tables (zedstore) for tool call logs, decision traces.
- Hypertables (TimescaleDB) for token usage, latency metrics, error rates.
You define the storage engine per table:
-- Heap (default) for mutable state
CREATE TABLE conversations (
id UUID PRIMARY KEY,
agent_id TEXT,
messages JSONB,
updated_at TIMESTAMPTZ
);
-- Columnar for append-only logs
CREATE TABLE tool_calls (
id UUID,
agent_id TEXT,
tool_name TEXT,
input JSONB,
output JSONB,
created_at TIMESTAMPTZ
) USING zedstore;
-- Hypertable for time-series telemetry
CREATE TABLE agent_metrics (
time TIMESTAMPTZ NOT NULL,
agent_id TEXT,
tokens_used INT,
latency_ms INT
);
SELECT create_hypertable('agent_metrics', 'time');Locking behavior: Different storage engines do not lock each other. An agent can insert into a heap table and a columnar table in the same transaction. The transaction coordinator handles the two-phase commit.
Replication: Logical replication works across storage engines. Physical replication (streaming replication) does not care about storage engines at all. Backup tools (pg_dump, pg_basebackup) handle all engines.
Observability and failure modes
Query planner mismatches: If an agent queries a columnar table with a row-oriented pattern, the planner may choose a sequential scan. You will see this in EXPLAIN output:
EXPLAIN SELECT * FROM tool_calls WHERE id = 'some-uuid';
-- Seq Scan on tool_calls (cost=0.00..10000.00 rows=1 width=...)The fix is to add a covering index on the shadow row store or rewrite the query to scan by time range instead of ID.
Vacuum lag: Columnar engines vacuum more slowly than heap tables because they operate on column chunks, not individual rows. If you have mixed workloads, autovacuum may spend more time on columnar tables and starve heap tables. Monitor pg_stat_user_tables.last_autovacuum.
Compression overhead: TimescaleDB compression is a background job. If your agent writes faster than compression can keep up, chunks stay uncompressed and queries slow down. Monitor timescaledb_information.chunks for uncompressed chunks older than your compression policy.
Deployment shape
Most production deployments run a single PostgreSQL instance with multiple storage engines. You do not need separate clusters. The storage engine is a per-table property, not a per-database property.
Scaling: If you need to scale reads, use read replicas. All storage engines replicate via WAL. If you need to scale writes, partition by agent ID or conversation ID and shard across multiple PostgreSQL instances. The storage engine choice is orthogonal to sharding.
Backup strategy: Use pg_basebackup for physical backups (works for all engines) or pg_dump for logical backups (also works for all engines). Columnar tables compress well in backups because the data is already columnar.
Technical Verdict
Use heap tables for mutable agent state (conversations, memory, task queues). The default storage engine is still the best for transactional workloads.
Use columnar tables (zedstore, Hydra) for append-only logs (tool calls, decision traces) that you query analytically. Accept the write amplification from the shadow row store. Avoid columnar tables if you need to update or delete rows frequently.
Use TimescaleDB hypertables for time-series telemetry (token usage, latency, error rates). The automatic partitioning and compression are worth the extension dependency. Avoid hypertables if you need to query without time filters.
Use Orioledb if you have high-churn transactional tables (agent memory that updates every turn) and do not need GIN or GiST indexes. Avoid Orioledb if you need full-text search or spatial queries.
Avoid mixing engines if your deployment is small enough that a single heap database works fine. The complexity is not worth it until you have clear evidence that storage engine choice is a bottleneck.