Added charts & python snippets

2 files changed, 155 insertions, 25 deletions

diff --git a/content/posts/postgres-aggregation-speed/index.md b/content/posts/postgres-aggregation-speed/index.md
index 2029dd0..0f1efee 100644
--- a/content/posts/postgres-aggregation-speed/index.md
+++ b/content/posts/postgres-aggregation-speed/index.md
@@ -143,13 +143,31 @@ They look pretty similar.
 
 I run these five queries with `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)`, and I can note their total time, alongside the different nodes duration in the plan.
 
-| Query          | Total  | `Seq Scan` | `HashAggregate` |
-| -------------- | ------ | ---------- | --------------- |
-| `composite`    | 80 ms  | 39 ms      | 40 ms           |
-| `json array`   | 120 ms | 37 ms      | 86 ms           |
-| `jsonb array`  | 150 ms | 35 ms      | 112 ms          |
-| `json object`  | 160 ms | 40 ms      | 120 ms          |
-| `jsonb object` | 220 ms | 40 ms      | 180 ms          |
+{{< chart type="bar" data=`{
+  "labels": ["composite", "json array", "jsonb array", "json object", "jsonb object"],
+  "datasets": [{
+    "label": "Seq Scan (ms)",
+    "data": [39, 37, 35, 40, 40],
+    "backgroundColor": "rgba(16, 185, 129, 0.6)",
+    "borderColor": "rgba(16, 185, 129, 1)",
+    "borderWidth": 1
+  }, {
+    "label": "HashAggregate (ms)",
+    "data": [40, 86, 112, 120, 180],
+    "backgroundColor": "rgba(245, 158, 11, 0.6)",
+    "borderColor": "rgba(245, 158, 11, 1)",
+    "borderWidth": 1
+  }]
+}` options=`{
+  "scales": {
+    "x": { "stacked": true },
+    "y": { "stacked": true, "beginAtZero": true, "title": { "display": true, "text": "Time (ms)" }
+    }
+  },
+  "plugins": {
+    "title": { "display": true, "text": "PostgreSQL Query Performance" }
+  }
+}` >}}
 
 - `Seq Scan` is stable for all queries, which makes sense because the same data is required.
 - `HashAggregate` are different because of the choice of data processing
@@ -191,14 +209,71 @@ I need to fetch the data and expose it in meaningful Python types. The expected
 
 This format may give a boost to `composite binary`, because the driver converts the binary in this exact format. Otherwise I need to manually convert the data. I use the `array` version of all `json` over `object`, because object are costlier the create and read.
 
-| Test                 | total  | postgres | python |
-| -------------------- | ------ | -------- | ------ |
-| `composite binary`   | 220 ms | 80 ms    | 140 ms |
-| `json array text`    | 230 ms | 120 ms   | 110 ms |
-| `json array binary`  | 230 ms | 120 ms   | 110 ms |
-| `jsonb array binary` | 260 ms | 150 ms   | 110 ms |
-| `jsonb array text`   | 270ms  | 150 ms   | 120 ms |
-| `composite text`     | 310 ms | 80 ms    | 230 ms |
+I use pytest and pytest-benchmark to write the benchmark. Here is a sample of the code I have to write, but the full file is available at the end.
+
+```py
+def load_any_json_array_json(
+    conn: Connection, query: bytes, *, binary: bool
+) -> JSONOut:
+    with conn.cursor() as cursor:
+        cursor.execute(query, binary=binary)
+        rows = cursor.fetchall()
+
+    return [
+        (
+            row[0],
+            [{"date": item[0], "price": item[1]} for item in row[1]],
+        )
+        for row in rows
+    ]
+
+#...
+
+@pytest.mark.benchmark(group="json out")
+class TestJSONOut:
+    @pytest.mark.parametrize("binary", [False, True])
+    @pytest.mark.parametrize(
+        "expr",
+        [JSON_OBJECT_EXPR, JSONB_OBJECT_EXPR],
+        ids=["json object", "jsonb object"],
+    )
+    def test_json_object(
+        self,
+        conn: Connection,
+        benchmark: BenchmarkFixture,
+        binary: bool,
+        expr: str,
+    ):
+        benchmark(
+            load_any_json_object_json, conn, BASE_QUERY.format(expr), binary=binary
+        )
+```
+
+{{< chart type="bar" data=`{
+  "labels": ["composite binary", "json array text", "json array binary", "jsonb array binary", "jsonb array text", "composite text"],
+  "datasets": [{
+    "label": "PostgreSQL (ms)",
+    "data": [80, 120, 120, 150, 150, 80],
+    "backgroundColor": "rgba(59, 130, 246, 0.6)",
+    "borderColor": "rgba(59, 130, 246, 1)",
+    "borderWidth": 1
+  }, {
+    "label": "Python (ms)",
+    "data": [140, 110, 110, 110, 120, 230],
+    "backgroundColor": "rgba(245, 158, 11, 0.6)",
+    "borderColor": "rgba(245, 158, 11, 1)",
+    "borderWidth": 1
+  }]
+}` options=`{
+  "scales": {
+    "x": { "stacked": true },
+    "y": { "stacked": true, "beginAtZero": true, "title": { "display": true, "text": "Time (ms)" } }
+  },
+  "plugins": {
+    "title": { "display": true, "text": "Python Processing Performance" }
+  }
+}` >}}
+
 - JSON parsing seems stable across all runs
 - Composite binary seems a little slower, but not by much
 - Composite text is much slower
@@ -224,14 +299,32 @@ I have to fetch the data and format it as JSON. I'm not pushing it to the point
 
 This time, I use JSON objects, as this is the shape of the data transmitted. It should give a boost to all JSON queries, while composite still requires processing.
 
-| Test                  | total  | postgres | python |
-| --------------------- | ------ | -------- | ------ |
-| `json object binary`  | 220 ms | 160 ms   | 60 ms  |
-| `json object text`    | 230 ms | 160 ms   | 70 ms  |
-| `jsonb object binary` | 270 ms | 220 ms   | 50 ms  |
-| `composite text`      | 280 ms | 80 ms    | 200 ms |
-| `jsonb object text`   | 280 ms | 220 ms   | 60 ms  |
-| `composite binary`    | 280 ms | 80 ms    | 200 ms |
+{{< chart type="bar" data=`{
+  "labels": ["json object binary", "json object text", "jsonb object binary", "composite text", "jsonb object text", "composite binary"],
+  "datasets": [{
+    "label": "PostgreSQL (ms)",
+    "data": [160, 160, 220, 80, 220, 80],
+    "backgroundColor": "rgba(59, 130, 246, 0.6)",
+    "borderColor": "rgba(59, 130, 246, 1)",
+    "borderWidth": 1
+  }, {
+    "label": "Python (ms)",
+    "data": [60, 70, 50, 200, 60, 200],
+    "backgroundColor": "rgba(245, 158, 11, 0.6)",
+    "borderColor": "rgba(245, 158, 11, 1)",
+    "borderWidth": 1
+  }]
+}` options=`{
+  "scales": {
+    "x": { "stacked": true },
+    "y": { "stacked": true, "beginAtZero": true, "title": { "display": true, "text": "Time (ms)" }
+    }
+  },
+  "plugins": {
+    "title": { "display": true, "text": "JSON API Performance" }
+  }
+}` >}}
+
 - All `json` processing look the same
 - `composite` in much slower in Python, regardless of binary or text
 
@@ -242,9 +335,12 @@ All JSON outputs are ready for output, so they require the less work on the Pyth
 I don't really know why `composite text` is that slow in both benchmarks.
 
 ## Resources
+
 - [Benchmarking script](test.py)
+- [Postgres JSON functions](https://www.postgresql.org/docs/current/functions-json.html)
+- [Postgres aggregation functions](https://www.postgresql.org/docs/current/functions-aggregate.html)
+- [Psycopg](https://www.psycopg.org/)
 
 ## TODO
 
-- [ ] Resources: postgres json, postgres aggregation, psycopg
-- [ ] Charts > tables
+- [ ] Real case study
diff --git a/layouts/shortcodes/chart.html b/layouts/shortcodes/chart.html
new file mode 100644
index 0000000..69689cd
--- /dev/null
+++ b/layouts/shortcodes/chart.html
@@ -0,0 +1,34 @@
+{{- $id := printf "chart-%d" (now.UnixNano) -}}
+{{- $type := .Get "type" | default "bar" -}}
+{{- $data := .Get "data" -}}
+{{- $options := .Get "options" | default "{}" -}}
+
+<div class="chart-container mb-5">
+  <canvas id="{{ $id }}"></canvas>
+</div>
+
+<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
+<script>
+  document.addEventListener('DOMContentLoaded', function() {
+    const ctx = document.getElementById('{{ $id }}').getContext('2d');
+    new Chart(ctx, {
+      type: '{{ $type }}',
+      data: {{ $data | safeJS }},
+      options: Object.assign({
+        responsive: true,
+        maintainAspectRatio: false,
+        plugins: {
+          legend: { position: 'top' }
+        }
+      }, {{ $options | safeJS }})
+    });
+  });
+</script>
+
+<style>
+  .chart-container {
+    position: relative;
+    height: 400px;
+    width: 100%;
+  }
+</style>