Multi-Model PostgreSQL in 2026: Working with Graphs, Time Series, and Documents in a Single Database

Introduction: Why One Database Instead of Several Specialized Ones?

In 2026, data architects are increasingly tempted to assemble the "perfect stack" from multiple specialized databases: Neo4j for graphs, InfluxDB or TimescaleDB for time series, MongoDB for documents. In practice, this approach leads to distributed transactions, data duplication, a complex operational model, and exponentially growing DevOps costs.

PostgreSQL in 2026 is a mature multi-model DBMS capable of covering all three scenarios within a single cluster. Recursive CTEs and the ag_catalog extension (Apache AGE) enable graph queries. Native partitioning, window functions, and TimescaleDB-compatible extensions handle time series workloads. JSONB with GIN indexes competes with MongoDB in document storage flexibility. All three models operate under a unified ACID transactional model, with shared backup, monitoring, and role management.

This article is a practical guide for backend developers and architects who want to get the most out of PostgreSQL without unnecessary dependencies.

Graph Data in PostgreSQL

Recursive CTEs: The Foundation of Graph Queries

The most accessible tool for working with hierarchical and graph structures in PostgreSQL is recursive Common Table Expressions (CTEs). They allow traversal of trees and directed graphs without external extensions.

Consider a classic use case: traversing a service dependency graph in a microservices architecture.

-- Service dependency table\nCREATE TABLE service_deps (\n  parent_id INT NOT NULL,\n  child_id  INT NOT NULL,\n  weight    NUMERIC DEFAULT 1.0\n);\n\nCREATE INDEX ON service_deps (parent_id);\n\n-- Recursive traversal: all dependencies of service #1 up to depth 10\nWITH RECURSIVE dep_tree AS (\n  -- Base case\n  SELECT parent_id, child_id, weight, 1 AS depth,\n         ARRAY[parent_id] AS path\n  FROM service_deps\n  WHERE parent_id = 1\n\n  UNION ALL\n\n  -- Recursive step\n  SELECT sd.parent_id, sd.child_id, sd.weight,\n         dt.depth + 1,\n         dt.path || sd.child_id\n  FROM service_deps sd\n  JOIN dep_tree dt ON dt.child_id = sd.parent_id\n  WHERE sd.child_id != ALL(dt.path)  -- cycle protection\n    AND dt.depth < 10\n)\nSELECT child_id, depth, path, weight\nFROM dep_tree\nORDER BY depth, child_id;

Note the cycle protection via ARRAY and the condition sd.child_id != ALL(dt.path) — this is critical for real-world graphs with back edges.

The ltree Extension for Hierarchies

For materialized hierarchies (product categories, organizational structures), the ltree extension is more efficient than recursive CTEs: it stores the tree path as a label and supports indexed subtree queries.

CREATE EXTENSION IF NOT EXISTS ltree;\n\nCREATE TABLE categories (\n  id    SERIAL PRIMARY KEY,\n  path  LTREE NOT NULL,\n  name  TEXT  NOT NULL\n);\n\nCREATE INDEX cat_path_gist ON categories USING GIST (path);\nCREATE INDEX cat_path_btree ON categories USING BTREE (path);\n\n-- Inserting a hierarchy: electronics > smartphones > Android\nINSERT INTO categories (path, name) VALUES\n  ('electronics', 'Electronics'),\n  ('electronics.smartphones', 'Smartphones'),\n  ('electronics.smartphones.android', 'Android'),\n  ('electronics.laptops', 'Laptops');\n\n-- All descendants of 'electronics.smartphones'\nSELECT id, name, path\nFROM categories\nWHERE path <@ 'electronics.smartphones';\n\n-- Pattern search (all direct children of electronics)\nSELECT * FROM categories\nWHERE path ~ 'electronics.*{1}';

Apache AGE: Cypher Queries on Top of PostgreSQL

When full-featured Cypher-style graph queries are needed, the Apache AGE extension (ag_catalog) is the go-to choice in 2026. It stores vertices and edges in PostgreSQL and executes Cypher queries via the cypher() SQL function.

-- Load the extension\nCREATE EXTENSION age;\nLOAD 'age';\nSET search_path = ag_catalog, "$user", public;\n\n-- Create a graph\nSELECT create_graph('social');\n\n-- Create vertices (users)\nSELECT * FROM cypher('social', $$\n  CREATE (:User {id: 1, name: 'Alice'}),\n         (:User {id: 2, name: 'Bob'}),\n         (:User {id: 3, name: 'Carol'})\n$$) AS (v agtype);\n\n-- Create edges (follows)\nSELECT * FROM cypher('social', $$\n  MATCH (a:User {name: 'Alice'}), (b:User {name: 'Bob'})\n  CREATE (a)-[:FOLLOWS]->(b)\n$$) AS (e agtype);\n\n-- Find friends of friends of Alice\nSELECT * FROM cypher('social', $$\n  MATCH (a:User {name: 'Alice'})-[:FOLLOWS*2]->(fof)\n  RETURN fof.name\n$$) AS (name agtype);

Time Series in PostgreSQL

Native Time-Based Partitioning

For time series without external extensions, PostgreSQL offers declarative range partitioning by date. This reduces index size, speeds up period-based queries, and simplifies archiving via DETACH PARTITION.

CREATE TABLE metrics (\n  ts         TIMESTAMPTZ NOT NULL,\n  service_id INT         NOT NULL,\n  metric     TEXT        NOT NULL,\n  value      DOUBLE PRECISION NOT NULL\n) PARTITION BY RANGE (ts);\n\n-- Create monthly partitions\nCREATE TABLE metrics_2026_01\n  PARTITION OF metrics\n  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');\n\nCREATE TABLE metrics_2026_02\n  PARTITION OF metrics\n  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');\n\n-- Index within a partition\nCREATE INDEX ON metrics_2026_01 (service_id, ts DESC);\n\n-- Query: average CPU usage over the last 7 days\nSELECT\n  date_trunc('hour', ts) AS hour,\n  AVG(value)             AS avg_cpu\nFROM metrics\nWHERE metric = 'cpu_usage'\n  AND ts >= NOW() - INTERVAL '7 days'\nGROUP BY 1\nORDER BY 1;

Window Functions for Trend Analysis

Window functions are the primary tool for time series analytics in SQL. They allow computing moving averages, lag/lead values, and running totals without self-joins.

-- 5-point moving average and delta from the previous value\nSELECT\n  ts,\n  service_id,\n  value,\n  AVG(value) OVER (\n    PARTITION BY service_id\n    ORDER BY ts\n    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW\n  ) AS moving_avg_5,\n  value - LAG(value) OVER (\n    PARTITION BY service_id ORDER BY ts\n  ) AS delta\nFROM metrics\nWHERE metric = 'cpu_usage'\n  AND ts >= NOW() - INTERVAL '1 day'\nORDER BY service_id, ts;

Comparison with TimescaleDB

TimescaleDB remains a popular extension in 2026, adding hypertable, automatic chunk-based partitioning, the time_bucket() function, and compression policies. If your workload involves millions of data points per second with aggressive compression and continuous aggregates, TimescaleDB is justified. For workloads up to 100k events/sec, native PostgreSQL partitioning with proper indexes is sufficient — and you avoid an extra dependency.

• Native PostgreSQL: full control, no licensing restrictions, less "magic".
• TimescaleDB Community: time_bucket(), continuous aggregates, chunk compression — faster at very high ingestion rates.
• TimescaleDB Cloud / Timescale: managed service, columnar storage, relevant at petabyte scale.

Documents: JSONB, GIN, and Search Operators

Storing and Indexing Documents

JSONB — PostgreSQL's binary JSON representation — stores data in parsed form, supports indexing of individual keys, and enables full-text search over document contents. Unlike the text-based JSON type, JSONB does not preserve key order or duplicates, but is significantly faster for reads.

CREATE TABLE events (\n  id         BIGSERIAL PRIMARY KEY,\n  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n  payload    JSONB       NOT NULL\n);\n\n-- GIN index for the @> (containment) operator\nCREATE INDEX events_payload_gin ON events USING GIN (payload);\n\n-- Index on a specific key (for queries always targeting one field)\nCREATE INDEX events_user_id ON events ((payload->>'user_id'));\n\n-- Insert events\nINSERT INTO events (payload) VALUES\n  ('{"type": "login", "user_id": "u42", "ip": "1.2.3.4", "tags": ["mobile", "vpn"]}'),\n  ('{"type": "purchase", "user_id": "u42", "amount": 199.99, "items": ["sku-1", "sku-2"]}');\n\n-- @> operator: find all events with type=login\nSELECT id, payload\nFROM events\nWHERE payload @> '{"type": "login"}';\n\n-- Search within a nested tag array\nSELECT id, payload\nFROM events\nWHERE payload @> '{"tags": ["vpn"]}';\n\n-- @@ operator: jsonpath-based search\nSELECT id, payload\nFROM events\nWHERE payload @@ '$.type == "purchase" && $.amount > 100';

Tips for Working with JSONB

• Use GIN with the jsonb_path_ops operator class for the @> operator — it is more compact than the default.
• For frequent lookups on a specific key, create expression B-tree indexes: (payload->>'user_id').
• Avoid storing data with a known, stable schema in JSONB — native columns are faster and more reliable for that.
• Use jsonb_set() for atomic updates of individual fields without rewriting the entire document.

Practical Case Study: IoT Monitoring Platform

Let's look at a real-world scenario: a platform for monitoring industrial devices. The data includes the device network topology (graph), sensor metrics (time series), and configuration documents (JSONB).

Schema

-- 1. Device topology graph\nCREATE TABLE device_topology (\n  parent_device_id INT NOT NULL,\n  child_device_id  INT NOT NULL,\n  link_type        TEXT NOT NULL  -- 'ethernet', 'zigbee', 'mqtt'\n);\n\n-- 2. Device metrics (time series, partitioned by month)\nCREATE TABLE device_metrics (\n  ts        TIMESTAMPTZ NOT NULL,\n  device_id INT         NOT NULL,\n  metric    TEXT        NOT NULL,\n  value     DOUBLE PRECISION NOT NULL\n) PARTITION BY RANGE (ts);\n\nCREATE TABLE device_metrics_2026_q2\n  PARTITION OF device_metrics\n  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');\n\n-- 3. Device configurations (JSONB documents)\nCREATE TABLE device_configs (\n  device_id  INT         PRIMARY KEY,\n  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n  config     JSONB       NOT NULL\n);\n\nCREATE INDEX device_configs_gin ON device_configs USING GIN (config);\n

Combined Query

The following query demonstrates the power of the multi-model approach: it finds all devices in the subtree under gateway #1 where the average temperature over the last hour exceeded a threshold, and whose configuration has alert_enabled set to true.

WITH RECURSIVE subtree AS (\n  SELECT child_device_id AS device_id\n  FROM device_topology\n  WHERE parent_device_id = 1\n\n  UNION ALL\n\n  SELECT dt.child_device_id\n  FROM device_topology dt\n  JOIN subtree s ON s.device_id = dt.parent_device_id\n),\nhot_devices AS (\n  SELECT device_id, AVG(value) AS avg_temp\n  FROM device_metrics\n  WHERE metric = 'temperature'\n    AND ts >= NOW() - INTERVAL '1 hour'\n    AND device_id IN (SELECT device_id FROM subtree)\n  GROUP BY device_id\n  HAVING AVG(value) > 75.0\n)\nSELECT\n  hd.device_id,\n  hd.avg_temp,\n  dc.config->>'firmware_version' AS firmware,\n  dc.config->>'location'        AS location\nFROM hot_devices hd\nJOIN device_configs dc ON dc.device_id = hd.device_id\nWHERE dc.config @> '{"alert_enabled": true}'\nORDER BY hd.avg_temp DESC;

This entire query — graph + time series + document — executes within a single transaction, with a unified execution plan and no network calls between different database systems.

Performance and Limitations

What Works Well

• JSONB + GIN: queries using @> on document sets up to 10 GB execute in milliseconds with proper indexing.
• Partitioning: queries hitting a single partition (partition pruning) are 10–50x faster than a full table scan.
• Recursive CTEs: efficient for trees up to 20–30 levels deep and graphs with hundreds of thousands of edges.

Where Limitations Exist

• Deep graphs with millions of edges: recursive CTEs scale worse than native graph databases (Neo4j, JanusGraph). For graphs with >50M edges, consider Apache AGE or a hybrid approach.
• Very high time series ingestion rates: at >500k points/sec, native partitioning falls behind TimescaleDB with chunk compression. Benchmark your specific workload.
• Full-text search within JSONB: the @@ operator with jsonpath is powerful, but for complex full-text search across nested text fields, consider a dedicated tsvector column or Elasticsearch integration.
• Horizontal write scaling: PostgreSQL scales vertically. For sharding, you need Citus or an external proxy (Pgpool-II, PgBouncer).

Optimization Tips

• Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) to diagnose execution plans for recursive queries.
• For JSONB columns with high cardinality on specific fields, create partial indexes: WHERE (payload->>'type') = 'purchase'.
• Enable enable_partition_pruning = on (default in PostgreSQL 14+) and verify that the plan actually uses partition pruning via EXPLAIN.
• For recursive CTEs on large graphs, consider materializing intermediate results with WITH ... AS MATERIALIZED.
• Tune work_mem for sorts and hash joins in analytical time series queries — the default 4 MB is critically insufficient.

Conclusions and Recommendations

PostgreSQL in 2026 is a fully capable multi-model platform, not just a relational database with JSON support. For most projects, a single PostgreSQL cluster replaces a combination of three specialized systems, eliminating operational complexity and ensuring transactional integrity across all data models.

Add a specialized database only when PostgreSQL demonstrably cannot handle a specific workload — and you have measured this, not merely assumed it.

Practical recommendations for choosing your approach:

1. Start with native PostgreSQL: JSONB + partitioning + recursive CTEs cover 80% of use cases.
2. If you need Cypher queries or a graph with >10M edges — add Apache AGE on top of your existing cluster.
3. If time series ingestion exceeds 100k/sec or you need continuous aggregates — evaluate TimescaleDB as an extension to the same PostgreSQL instance.
4. Do not add MongoDB, Neo4j, or InfluxDB until PostgreSQL has hit a measurable bottleneck.
5. Invest in understanding the PostgreSQL query planner — EXPLAIN ANALYZE and pg_stat_statements should be part of your regular workflow.

Multi-model PostgreSQL is not a compromise — it is a deliberate architectural strategy that in 2026 is backed by a rich extension ecosystem, a mature query planner, and an enormous community. Use its full potential.