draft for nested postgres results

8 files changed, 164 insertions, 0 deletions

diff --git a/content/posts/nested-postgres-results/composite-array-agg.sql b/content/posts/nested-postgres-results/composite-array-agg.sql
new file mode 100644
index 0000000..0def84f
--- /dev/null
+++ b/content/posts/nested-postgres-results/composite-array-agg.sql
@@ -0,0 +1,8 @@
+SELECT
+    accommodation_id,
+    array_agg((date, price)) AS daily_configs
+FROM
+    overrides
+WHERE '[2025-07-01,2025-07-08)'::daterange
+@> date
+GROUP BY overrides.accommodation_id
diff --git a/content/posts/nested-postgres-results/docker-compose.yml b/content/posts/nested-postgres-results/docker-compose.yml
new file mode 100644
index 0000000..7dd182c
--- /dev/null
+++ b/content/posts/nested-postgres-results/docker-compose.yml
@@ -0,0 +1,8 @@
+services:
+  postgres:
+    image: postgres
+    restart: always
+    environment:
+      POSTGRES_HOST_AUTH_METHOD: trust
+    ports:
+      - "5432:5432"
diff --git a/content/posts/nested-postgres-results/index.md b/content/posts/nested-postgres-results/index.md
new file mode 100644
index 0000000..e35adbf
--- /dev/null
+++ b/content/posts/nested-postgres-results/index.md
@@ -0,0 +1,30 @@
+---
+title: ""
+draft: true
+date:
+lastmod:
+description: ""
+categories:
+  - PostgreSQL
+---
+
+I often need to return several sub-rows for each top-level row of a given query - I will provide a case study later. This does not fit the regular SQL model, but PostgreSQL has powerful constructs and operations to help us build such queries. This post's goal is to compare several approaches, both in term of ease of use and performance. Performance is different here than in regular benchmarks, because it will span from the database to the host programming language: Python. More concretely, here are hypothesis I want to test:
+
+1. Postgres composite types array offer better raw time performance that json objects array
+2. Postgres composite types array offer better data compression performance that json objects array
+3. Postgres composite types array offer better performance up to the host language
+  - Conversion between JSON output from postgres result to python objects should be slower that using composite types
+4. Postgres jsonb functions offer better performances than json functions
+
+## Data model and setup
+
+As usual, we'll try to solve some real world problem with a certain data model. Data size will be tweaked to a realistic but challenging size, also to ease benchmarks.
+
+## Aggregation row results
+
+The first estimates show 48ms for composite, 58ms for json array and 68ms for jsonb array. It kind of make sense, jsonb is finally costlier and only used when processing the data.
+**I should compare explain to see what blocks are impacted**
+
+## Python gathering
+
+The python part seem to be low, pure json is still the best. Binary composites are ok.
diff --git a/content/posts/nested-postgres-results/json-array-agg.sql b/content/posts/nested-postgres-results/json-array-agg.sql
new file mode 100644
index 0000000..a7ca29c
--- /dev/null
+++ b/content/posts/nested-postgres-results/json-array-agg.sql
@@ -0,0 +1,11 @@
+SELECT
+    accommodation_id,
+    json_agg(json_build_object(
+        'date', date,
+        'price', price
+    )) AS daily_configs
+FROM
+    overrides
+WHERE '[2025-07-01,2025-07-08)'::daterange
+@> date
+GROUP BY overrides.accommodation_id
diff --git a/content/posts/nested-postgres-results/jsonb-array-agg.sql b/content/posts/nested-postgres-results/jsonb-array-agg.sql
new file mode 100644
index 0000000..91d024c
--- /dev/null
+++ b/content/posts/nested-postgres-results/jsonb-array-agg.sql
@@ -0,0 +1,11 @@
+SELECT
+    accommodation_id,
+    jsonb_agg(jsonb_build_object(
+        'date', date,
+        'price', price
+    )) AS daily_configs
+FROM
+    overrides
+WHERE '[2025-07-01,2025-07-08)'::daterange
+@> date
+GROUP BY overrides.accommodation_id
diff --git a/content/posts/nested-postgres-results/jsonb-array-agg.txt b/content/posts/nested-postgres-results/jsonb-array-agg.txt
new file mode 100644
index 0000000..35ae6bb
--- /dev/null
+++ b/content/posts/nested-postgres-results/jsonb-array-agg.txt
@@ -0,0 +1,33 @@
+    GroupAggregate  (cost=14173.77..16807.65 rows=8725 width=36) (actual time=15.671..34.981 rows=10000 loops=1)
+    Output: accommodation_id, jsonb_agg(jsonb_build_object('date', date, 'price', price))
+    Group Key: overrides.accommodation_id
+    Buffers: shared hit=6442
+    ->  Gather Merge  (cost=14173.77..16545.84 rows=20367 width=13) (actual time=15.641..17.253 rows=20000 loops=1)
+            Output: accommodation_id, date, price
+            Workers Planned: 2
+            Workers Launched: 2
+            Buffers: shared hit=6442
+            ->  Sort  (cost=13173.74..13194.96 rows=8486 width=13) (actual time=13.876..14.122 rows=6667 loops=3)
+                Output: accommodation_id, date, price
+                Sort Key: overrides.accommodation_id
+                Sort Method: quicksort  Memory: 430kB
+                Buffers: shared hit=6442
+                Worker 0:  actual time=13.239..13.513 rows=6391 loops=1
+                    Sort Method: quicksort  Memory: 392kB
+                    Buffers: shared hit=2067
+                Worker 1:  actual time=13.028..13.281 rows=5997 loops=1
+                    Sort Method: quicksort  Memory: 380kB
+                    Buffers: shared hit=1948
+                ->  Parallel Seq Scan on public.overrides  (cost=0.00..12619.99 rows=8486 width=13) (actual time=0.009..13.186 rows=6667 loops=3)
+                        Output: accommodation_id, date, price
+                        Filter: ((overrides.date >= '2025-07-01'::date) AND (overrides.date < '2025-07-08'::date))
+                        Rows Removed by Filter: 326666
+                        Buffers: shared hit=6370
+                        Worker 0:  actual time=0.010..12.581 rows=6391 loops=1
+                        Buffers: shared hit=2031
+                        Worker 1:  actual time=0.008..12.365 rows=5997 loops=1
+                        Buffers: shared hit=1912
+    Planning:
+    Buffers: shared hit=129
+    Planning Time: 0.251 ms
+    Execution Time: 35.310 ms
diff --git a/content/posts/nested-postgres-results/setup.sql b/content/posts/nested-postgres-results/setup.sql
new file mode 100644
index 0000000..a54563b
--- /dev/null
+++ b/content/posts/nested-postgres-results/setup.sql
@@ -0,0 +1,26 @@
+CREATE TABLE accommodations (
+    id serial PRIMARY KEY,
+    name text NOT NULL,
+    default_price numeric(10, 2) NOT NULL
+);
+
+INSERT INTO accommodations (name, default_price)
+SELECT
+    CONCAT('accommodation_', generate_series) AS name,
+    TRUNC(RANDOM() * 100 + 200) AS default_price
+FROM GENERATE_SERIES(1, 10000);
+
+CREATE TABLE overrides (
+    id serial PRIMARY KEY,
+    accommodation_id integer NOT NULL REFERENCES accommodations (id),
+    date date NOT NULL,
+    price numeric(10, 2) NOT NULL
+);
+ALTER TABLE overrides ADD constraint unique_dates UNIQUE (
+    accommodation_id, date
+);
+INSERT INTO overrides (accommodation_id, date, price) SELECT
+    (generate_series / 100) + 1 AS accommodation_id,
+    '2025-01-01'::date + (generate_series % 100) * '3 days'::interval AS date,
+    TRUNC(RANDOM() * 100 + 200) AS price
+FROM GENERATE_SERIES(1, 999999);
diff --git a/content/posts/nested-postgres-results/tests.py b/content/posts/nested-postgres-results/tests.py
new file mode 100644
index 0000000..c1f1506
--- /dev/null
+++ b/content/posts/nested-postgres-results/tests.py
@@ -0,0 +1,37 @@
+from pathlib import Path
+from typing import Generator
+import psycopg
+from pytest_benchmark.fixture import BenchmarkFixture
+import pytest
+
+
+@pytest.fixture(scope="module")
+def connection() -> Generator[psycopg.Connection]:
+    with psycopg.connect(
+        dbname="postgres", user="postgres", password="", host="127.0.0.1", port="5432"
+    ) as connection:
+        yield connection
+
+
+def load(connection: psycopg.Connection, query: bytes, *, binary: bool) -> None:
+    with connection.cursor() as cursor:
+        cursor.execute(query, binary=binary)
+        cursor.fetchall()
+
+
+@pytest.mark.parametrize("binary", [False, True])
+@pytest.mark.parametrize(
+    "query", ["json-array-agg.sql", "jsonb-array-agg.sql", "composite-array-agg.sql"]
+)
+def test_benchmark_load(
+    connection: psycopg.Connection,
+    benchmark: BenchmarkFixture,
+    binary: bool,
+    query: str,
+):
+    benchmark(
+        load,
+        connection,
+        (Path("content/posts/nested-postgres-results") / query).read_bytes(),
+        binary=binary,
+    )