Moved post to better named folder

3 files changed, 444 insertions, 0 deletions

diff --git a/content/posts/postgres-aggregation-speed/data-model.svg b/content/posts/postgres-aggregation-speed/data-model.svg
new file mode 100644
index 0000000..70ddf60
--- /dev/null
+++ b/content/posts/postgres-aggregation-speed/data-model.svg
@@ -0,0 +1,4 @@
+<?xml version="1.0" standalone="no"?>
+<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">
+<svg version="1.1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 855 208" width="855" height="208"><!-- svg-source:excalidraw --><metadata></metadata><defs><style class="style-fonts">
+      @font-face { font-family: Virgil; src: url(data:font/woff2;base64,d09GMgABAAAAAA40AAsAAAAAFowAAA3lAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAABmAAgQQRCAqkGJpvCzIAATYCJANgBCAFgxwHIBvwECMRZnVqQfYXCebhOOV7oJdecsTimLweBEbs/95+nt/cn3sf75G5ZqmjhEXr6EWxDlgUU5c2ZseMHx1Wk//hAAfzWt6y3IinGg8hl+l/c5b21WpZkExmOVkizqYIl6t967OuX5Lt8I7i0MC2TbJAZCgEwNWlfJRCpYKcv3iQStvvlFbsCx0G/ynNJOPBbp/Ql3Rq1EkCCDGKimyOBVEvdzUENJyOlgAJgOfhMgDIgiaZBFgAUfCyIwYkhcsYCBkQcphbnme3gxGUMED0vZ0HpEVBfQDAL7bH0Pv4daI7+0pJEkHCGaT4aeA2qgABEMAAJSSBCfKgFuqhGVoxug8GvN5ARo2BDhj7Pe297b3mPSnUok1oaqiHwFWRV1jwKgDp5UAAihdwfw8XaAAGewyCjsKhzhx+gURSJo3QGpIlUaUGeUWZwj/UYfdTqiRae6MjqbpyW3LuUIhnoYHBA7DApbJXLF8voNY80cFQMewOlj3618/OmLSojvLacqkFN25geIFds7aqdH/OVTrz9985p077ixTi3YwdZaDh4WYjMgTiLeESRACGzZqB2BBV38NguVSupHLiAgyKl9u4cpuxBeYFBBGejL5O5zdqpwURF56OaE8ZOAMGmLZHYHU0QCS6RxNK0Dds4RP/ZndJl74q619/fd9Mg+lk68hyt0PH0PpK5sZB7cM777BdiPEC4i00TDelzC8zJMgOQQxv+aTl3rLk8wwsS0fR0Ljacns/Q+bN8NDJUikDGTisnt7epthtxIuiJTBpkfgsbhLR1RJKAuutdyGUIaW95Icu4oueH++7Tv1k7f3F5PJOqX5/1+10Q/tqd0l3roddLelfVI6wvtFZbFd8MrXharEofAwdPOB30CWX6ZKZpjh8O25DiJ81EVZuIs5SdnKlspnmVH2ClReJq0T4HoGmYZ/3oowhDELW+FMx8U/ukqWyF0wHNQoRjl4PqvNokHyd8mPpYew3o/1aO/rJniXxHT0eWTxuP2aXDyEliHFjPzzQg4URJXQxLXEiWi3O0eNlKji4o7hPOott+FrSxA36BTVkTmIGKQvqM6yPeGEYx+HUNp3eAA+oHgtMmVCZLAxvjEdIhvGGp6Ho5MFyEzqy3LyRMSpx1gaN5Uk+Nk2wjzgRd7sFztp/p6yc5qwHlnvwWjK7nm67jvge2wfA8uAjABDuJNJCPH1t1jchwazYNvajTARRMpmH/DMKw95Y7XvVLyjB2oSIHZYpS4+e0bD2pUwSJciVN4isXfWbxK54Brg2sV7aK02kcy7+P7+M+BKbFIURLSCafrVKxPVrqvdX4HydhXm35/THrhdaKwzST9nMouGSP4veiPl+BjQNx2RQkxfNwIIOj2G8HYwY0AGsQypN05I4ArAGBmmaZcS7v5elKOMt3CnKKXPo2PBRnD7hWd+wPUO9Z6rfZ0opjlRrSmRjoWhXDR/DI3k6OU2TQNZoj7olUzpMXb/OriMe74mLCGCgH1mPRtemS2LqZJ0hdZUTyJAmopsH3MGgATB4/tcT74+1ES/E+BYq0cFBstOyIyNCP8OXgpSSJuQkTlpPi+LmSsRVdT0v9Ta+ct4mxur3d123uiXe2z5gvVFeC0PqjrTG+EbGrzSzwvcgc+ROFs52SqrMHu7FcNh6jPgmVaZhQhOSvYDWtqmUpcxbXLifS6qL3Qcw0BqDR67GW66+jVedaLD+wbQZlUrO/IBGK3dApQyorLNtj5mV+hu6WRETLlD7cXvDhuF4B+tBhLFej+1GNWuv6BLOFANGOW65oEQ228CTnReHYWCNlsq/arkgofJ3UmcKcaREi/jj9vJKvGRqD6yFIb02/0a0f/CstzWYrgUTpjJNuUD1mRqiq1D0DOUyeNLw/lwyRHyR6vdnyjPMKX+tykoh1c3GKKb8LmmxtC6ny9QG0TXs5RFAx7shBnBBAnUl7d2anp0EwYXaMlNtU8dUmpNBj94zTLVMIS4EWSLiJQJxhlS6KOVbnDaO/3/hTh4i/aRzJQPkjCiIliA+tvNShzRMFkrX0p0bGN5gTWHh9Knzudntj8f/QBrRKzLHzaWMMv0RFe3y1GQB969GuhrZBjFv5lXPGp9q+LOMtaoYrbDOka2VsDk/U1Hnmpp2C/aEih0f15Q+7d36eJPWZnkhqUIe1O2eSkjOA0VmKvihgRRCuMF3LQ+Lll6fHY7YaO9v+ugcYQbJKBKvu7OTxz359/gMzoe3hsCCKbfSPO7zE016HZTqXgRbvtGXu0E9PyC9YcCjQtWyqcvLZf6UxaozIh1DWoYIBEiLAmpD0l3VOdqCoGz7nK44cfWIWT2B8QROteLmwalXHkXwImzv1f84IDe852f9UF2au2TQV++OaEe79pYcydFIN6bmgNzs804x0pN2NuUmHx7lO87zN0upSpdyOy5WJnGdaUINx0WmEiTumLqf4SCnCl6RcQzOPFi9uHdbRpN1y88mRcTCti/yiqUqpLKd1TSFLE3n59GFresS3vlmgsFZHbMtprfpxRLBPjwyGW1ScDwRDclmw4wSl0rqGyb8HE10phMD9JhMWvTysuNuGvCvdS32yGpH/GEcDnZ1pQ5fIY79mhiz88+eB72fdGQT9eodT1O2dIoGfJy3yZQ/Hzd+0CHOrA968xLIfXRm3vShXI5pO2adKNAHn3BGg8N9Mlj+rC3etkmGiEEkY9Rt5E720FPO3C/wy9LK+VQXFc5pDwvKCTMCzsKjSv39jJmBf8fSySNrVScpx2vJcFJ2dFrG9WGu4c5Xh15Q+eDyT6ss9nMRH1baNbSSUkucPObXEMoQjJgsY2+FiVguI3TLtMvp9WeZ4QnmbYtv/aYsJRz6jPAR1X+N6B6SSn+aQfMwQlEqR2X0e7InX+H7pQvFQS5zaHe5dEpokGBJ0nEK1copdiVbfMz7eUWk+Fl3trGspvFERFowkwzd984O5/9FCmuSsbipNCfsj1sO3kxe6607q8SlH68eo4ZHsl+7dEuE3KePQTIS/Kew6d/kBC99PtsTOSQVWSD7m3q56Lx12bMQAjKEVNaYRF5vBjs5Sp81OymdlVxYcj03Pg0oBuf8lVO2n0dnWJOTM/a5zgm/DlOaJbVB8zcQgp/1x00zwux0Fi0gocWmXiiYQxX3f6P5QyS2x1d0rjf6AFdT8xNyLN1ZeDCqrDRR2Mrql+HIhQLmjvQ//egOehndN+DPiJvvSzu7P9YyQkYhPCqyzbwDuJB1hM2+SwcmWzso8w/isog0FhdVS1pRJjdR1XJlM3doWCIfRtLR+eBW1moeDD4oizzzRBJf6wxNyRn84vCKzCuMclR6OWB9BKf/WpG368A87UTcyDQUWgeEG/NEtlAUnf3vcvxNaim7DRvAWhdunk/bURe2CT9KknGWgphvio8tEOtye7ZaLwRlsFcGkEQiQdIElvvK621qJgv1QmlLbtxX9s+uCBjb25a0HwtMKxblE1eR71/KForlI3I1i0xVEYfS0+oZ4D+jo03V+lf/q11rUjPnpA5H2w+mdk7yf+J06xmq1ksZuerO+8fa1SNLZBkJdjbv2tqgmCKdRpbgWXt3egj/3CGywi8CDKz7d8Q2Ln15b6A+/Q1n8n9dY0K+KojZSGPiWcH0tqSmZWvjjrrdX5VcFI5IY+1mgMsXFVQxrcurYwcKs/xT/O74mi33vpyzc7lfBskMhlB2+/crDYn24aKYaEI+yvGYNz7G58sPGHrjp6eKU+cGjFGXtMsuZm6dz+X9PfmLTWCyiT8NT4j6O2DHUSNbplke3afdlGN2BmYl2hs+8qM6TRbxi6qwQhIHEw8Mx6196dKCmWT+/RO9rz7/ZOaz74/nYxqLk1E1Nlv88X1hT/DkybD7PMoPyCqxmGhKZsDRhq9/sCSKyzh6l6gZbWiR7dCFo1IXeoINXx01fbhzP0yrf2YXHBUsYds5ETLLYV/DfeZH+keO46YZtv7WiWm0XsmhKx9+m2+smxCf4+hRTmZ/WBn+0alwRLcwALb6GBPePUgnaHXbecEvwrCmiZndpX6UyKbIcn38qqH0iG+J3z9P/Qg1i2SsYi6leyoOMUz7HPfM1XEIGg4LTxoV+bThJVua+eRGyPQlG2NJAuQHQ+43/sb65K2JyPJtkwbdPfgw9E1V1+fHRtZRc0KuqviUOsNjdNhqNqT/a94lPINWCq8ZhMfPmgMEDxTp9+rDjIFQQmvN8uS5zAkryh84S35YrpX1lzBoPcsFc/anFNYbCawnG2ssgzVJxTRhgdImikMEVllNw9PNQZG+ntH3pbI5wkFlwyfRLzW+L67ke1ZkZ1r9BCwsuznPUqz4etTRrXnrAAAAEAAQANybcaNr+SlPGGziDwCAr9o77wEA/LTtv+3en9+8pP1FmwQADMCE9N+kSIYNuL0V+n91PgWyedBj6kPBhJcAC392JiLJ+wdeD2L0FJg4ETAOBRH6zfsTegI8+BpIRIdsPAhq9DHIsQpk6F9QoEOFvSDg4VjvhzgBRmA2CHAU8AEBgAK+AwoI+xEgl7GTDhVAAKIxhbAHrmAEHDiBMfBgFyagBByYBjJIwiQEgj9QMsAUtRXGQvOfrqyFywInUG+5fmw3cqJxZHeXUu/VDLpESxpPdHme3T5dSJ4yRDI10QVRSAaQXKlmx8vaXdKSSEu3e7AKrcqSlvHKkY2RSg4GT1Y2DALKp0EvVGVe/44x9h3kxmfQq5Sbxk4DnVI2dXjdypUy4yrgTJAT+mRomlQg3HgkAAAA); }</style></defs><g stroke-linecap="round" transform="translate(10 19) rotate(0 194.5 84.5)"><path d="M32 0 C133.85 -1.08, 234.71 2.63, 357 0 C379.26 2.23, 389.42 7.98, 389 32 C384.07 57.47, 384.74 83.56, 389 137 C391.15 155.49, 378.32 168.75, 357 169 C267.91 171.07, 182.04 169.27, 32 169 C7.7 166.21, -2.84 154.75, 0 137 C-3.71 97.39, -0.84 66.92, 0 32 C-0.42 10.27, 14.02 3.54, 32 0" stroke="none" stroke-width="0" fill="#ffffff"></path><path d="M32 0 C116.43 -1.28, 198.9 -2.34, 357 0 M32 0 C154.69 0.55, 277.86 0.02, 357 0 M357 0 C376.7 1.94, 387.09 10.94, 389 32 M357 0 C379.09 1.85, 390.46 10.37, 389 32 M389 32 C387.78 53.38, 390.08 75.63, 389 137 M389 32 C389.74 55.2, 388.53 79.21, 389 137 M389 137 C387.84 157.96, 377.45 168.34, 357 169 M389 137 C389.99 156.82, 376.62 169.33, 357 169 M357 169 C282.68 166.47, 210.85 167.68, 32 169 M357 169 C263.35 170.32, 171.39 170.44, 32 169 M32 169 C8.99 169.59, 1.67 158.69, 0 137 M32 169 C11.44 168.41, -1.58 160.42, 0 137 M0 137 C0 107.4, 0.57 77.57, 0 32 M0 137 C-0.17 98.1, 0.91 59.57, 0 32 M0 32 C1.1 9.89, 9.37 0.57, 32 0 M0 32 C-0.44 10.95, 12.51 -0.35, 32 0" stroke="#1e1e1e" stroke-width="2" fill="none"></path></g><g transform="translate(15 24) rotate(0 147.89166259765625 62.5)"><text x="0" y="17.619999999999997" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    </text><text x="0" y="42.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    Accommodation</text><text x="0" y="67.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">        id: serial</text><text x="0" y="92.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">        name: text</text><text x="0" y="117.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">        default_price: numeric</text></g><g stroke-linecap="round" transform="translate(509 10) rotate(0 168 94)"><path d="M32 0 C91.75 -1.05, 151.95 -4.48, 304 0 C326.19 -1.07, 337.64 13.1, 336 32 C336.55 66.86, 337.36 104.81, 336 156 C338.86 178.11, 324.27 184.41, 304 188 C228.91 189.6, 156.41 187.16, 32 188 C14.13 185.67, -0.17 176.83, 0 156 C-3.06 115.84, -4.52 73.69, 0 32 C3.48 11.88, 9.25 3.09, 32 0" stroke="none" stroke-width="0" fill="#ffffff"></path><path d="M32 0 C87.83 -1.61, 144.2 -3.06, 304 0 M32 0 C140.64 2.1, 248.34 1.72, 304 0 M304 0 C323.54 0.24, 334.55 9.53, 336 32 M304 0 C323.07 1.61, 337.82 12.57, 336 32 M336 32 C337.69 81.28, 335.98 127.69, 336 156 M336 32 C337.66 74.47, 336.29 117.33, 336 156 M336 156 C336.6 177.89, 323.91 188.89, 304 188 M336 156 C337.03 175.53, 326.72 186.07, 304 188 M304 188 C200 186.76, 94.28 189.24, 32 188 M304 188 C213.89 189, 122.17 188.9, 32 188 M32 188 C9.82 189.17, 0.12 176.1, 0 156 M32 188 C9.18 189.69, -1.24 175.21, 0 156 M0 156 C-2.19 128.48, -1.88 105.07, 0 32 M0 156 C0.79 110.16, -0.34 61.57, 0 32 M0 32 C1.06 9.41, 11.25 -1.48, 32 0 M0 32 C1.1 8.44, 8.54 -1.95, 32 0" stroke="#1e1e1e" stroke-width="2" fill="none"></path></g><g transform="translate(514 15) rotate(0 147.1666717529297 75)"><text x="0" y="17.619999999999997" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    </text><text x="0" y="42.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    Override</text><text x="0" y="67.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    id: serial</text><text x="0" y="92.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    accommodation_id: integer</text><text x="0" y="117.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    date: date</text><text x="0" y="142.62" font-family="Virgil, sans-serif, Segoe UI Emoji" font-size="20px" fill="#1e1e1e" text-anchor="start" style="white-space: pre;" direction="ltr" dominant-baseline="alphabetic">    price: numeric</text></g><g stroke-linecap="round"><g transform="translate(549 104) rotate(0 -177.5 -9.5)"><path d="M-0.87 0.64 C-59.89 -2.48, -295.31 -16.11, -354.4 -19.46 M0.88 -0.06 C-58.22 -2.96, -295.48 -15.11, -355.04 -18.17" stroke="#1e1e1e" stroke-width="2" fill="none"></path></g><g transform="translate(549 104) rotate(0 -177.5 -9.5)"><path d="M-331.14 -25.5 C-336.75 -22.64, -345.24 -22.34, -355.04 -18.17 M-331.14 -25.5 C-337.8 -24.29, -343.71 -22.29, -355.04 -18.17" stroke="#1e1e1e" stroke-width="2" fill="none"></path></g><g transform="translate(549 104) rotate(0 -177.5 -9.5)"><path d="M-332.01 -8.43 C-337.28 -10.3, -345.53 -14.75, -355.04 -18.17 M-332.01 -8.43 C-338.39 -11.63, -344.07 -14.05, -355.04 -18.17" stroke="#1e1e1e" stroke-width="2" fill="none"></path></g></g><mask></mask></svg>
\ No newline at end of file
diff --git a/content/posts/postgres-aggregation-speed/index.md b/content/posts/postgres-aggregation-speed/index.md
new file mode 100644
index 0000000..29756e8
--- /dev/null
+++ b/content/posts/postgres-aggregation-speed/index.md
@@ -0,0 +1,250 @@
+---
+title: PostgreSQL aggregation processing speed
+draft: true
+date: 2025-09-29
+lastmod:
+description: Comparison of aggregation function speed, on the database and in a host language
+categories:
+  - PostgreSQL
+---
+
+## Key takeaways
+
+**JSON is more efficient than JSONB when only reading data**
+
+JSONB is useful for data processing, when filtering data on the database. But in this test, the higher cost of creating JSONB is not worth it, and there is also a price to convert it back to text.
+
+**Use `composite binary` for Python data processing**
+
+If you need to compute further in Python, the raw speed of composite binary on Postgres and the OK part in Python makes it the fastest to get data in processable data structures. You can even cast the output in a typed `NamedTuple` and won't need manual parsing
+
+## The problem
+
+Most of the time, the rows of a SQL query matches the domain: a list of user data, a list of product data. But sometimes, there is a sub list for each item: each user's top articles.
+
+When a single sub list is required, several rows can be returned by joins for each root item: several rows for each user. The data is then merged in the host language.
+
+But when there are several sub lists, there is an issue with high cardinality: each user has 15 articles, and 30 comments, and that makes 450 rows for each user, with a lot of duplicated data.
+
+In this case, there are list aggregation functions to generate sub lists at the row level. There are several available, such as `json_agg` and `array_agg`.
+
+**When should you use one or the other?**
+
+## Data model and setup
+
+I use a simple data model, and I can tweak the size of the dataset so that the benchmark is interesting.
+
+The model is an `Accommodation`, that has a default price for rental, and then many `Override` that would allow to specify different prices along the year. There are 10 000 accommodations and 1 000 000 overrides.
+
+![Data model](content/posts/postgres-aggregation-speed/data-model.svg)
+
+```sql
+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);
+```
+
+## Postgres query engine performance
+
+The first question is: **what is the fastest query?**
+
+The three data types used are:
+- `composite`, which is like a type safe tuple in any programming language
+- `json`, which represent a JSON value and is stored as text
+- `jsonb`, also represents a JSON value, but stored in an optimised format for querying
+
+Let's take a look at the five queries:
+
+**Composite query**
+
+```sql
+SELECT
+    accommodation_id,
+    array_agg((date, price)) AS daily_configs
+FROM overrides
+WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+GROUP BY overrides.accommodation_id
+```
+
+**JSON object query**
+
+```sql
+SELECT
+    accommodation_id,
+    json_agg(json_build_object(
+        'date', date,
+        'price', price::text
+    )) AS daily_configs
+FROM overrides
+WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+GROUP BY overrides.accommodation_id
+```
+
+**JSON array query**
+
+```sql
+SELECT
+    accommodation_id,
+    json_agg(json_build_array(date, price::text)) AS daily_configs
+FROM overrides
+WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+GROUP BY overrides.accommodation_id
+```
+
+**JSONB object query**
+
+```sql
+SELECT
+    accommodation_id,
+    jsonb_agg(jsonb_build_object(
+        'date', date,
+        'price', price::text
+    )) AS daily_configs
+FROM overrides
+WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+GROUP BY overrides.accommodation_id
+```
+
+**JSONB array query**
+
+```sql
+SELECT
+    accommodation_id,
+    jsonb_agg(jsonb_build_array(date, price::text)) AS daily_configs
+FROM overrides
+WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+GROUP BY overrides.accommodation_id
+```
+
+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          |
+
+- `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
+- `composite` is the fastest
+- `json` is faster than `jsonb`
+- `array` is faster than `object` 
+
+I thought that JSONB was faster than JSON, but I now understand it is faster for things like filtering data. But in this case, I pay for encoding in the first place, and there is no gain to expect. Only when the source column is JSONB encoded, there is no cost for encoding: it is paid on write.
+
+Array being faster than objects make sense: there is less structure, the data is easier to create. I think there are places where each kind can be useful.
+
+I wasn't sure if Postgres output type (binary or not) would impact the results, but after a quick benchmarks it looks like not.
+
+## Server side
+
+The previous part shows that `composite` is the fastest to execute on the database. But what really matters is the moment when the data is ready to use in the host language. There are two main use cases:
+- **Host processing:** Once the data is ready, I need to apply an algorithm to it: I have a function that can compute the precise pricing for a stay, and then I filter by a maximum price.
+- **JSON output:** The data does not need processing, it is sent back to a client in JSON shape
+
+So each use case may have a unique best solution.
+
+For this experiment, I use Python with `psycopg`, which is a Postgres driver for Python that supports low level configurations. One configuration is `text` versus `binary` output, and applies to each query.
+
+### Python processing
+
+I need to fetch the data and expose it in meaningful Python types. The expected format looks like: `list[tuple[str, list[tuple[date, Decimal]]]]`
+
+```py
+[
+	(
+		"ab18fc69-2f7d-4331-a9d2-615a7957a143",
+		[
+			(date(2025, 9, 28), Decimal("200.50")),
+			...
+		],
+	)
+]
+```
+
+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 |
+- JSON parsing seems stable across all runs
+- Composite binary seems a little slower, but not by much
+- Composite text is much slower
+
+Composite is not much faster, which can be odd. If `psycopg` uses pure python for data transformation, doing it by hand is not slower: the [NumericLoader](https://github.com/psycopg/psycopg/blob/c9e013d4441d9c9ae5bed61ce8fa561dcb0b9dae/psycopg_c/psycopg_c/types/numeric.pyx#L440) implementation uses optimised Python, but relies on the `Decimal` constructor. The speed on the database makes it the best choice still.
+
+Composite text is very slow. Even tuples need to be parsed manually. I don't get why JSON isn't as slow: it needs to be parsed as array too. The [CompositeLoader](https://github.com/psycopg/psycopg/blob/c9e013d4441d9c9ae5bed61ce8fa561dcb0b9dae/psycopg/psycopg/types/composite.py#L243) is pure Python, while Python's own JSON is certainly optimised C.
+
+### JSON API
+
+I have to fetch the data and format it as JSON. I'm not pushing it to the point i keep it as strings, but it might be the better answer. I'm only interesting in aggregations, so I only format the sub list. The format looks like :`list[tuple[str, list[dict[str, str]]]]`
+
+```py
+[
+    (
+        "ab18fc69-2f7d-4331-a9d2-615a7957a143",
+        [
+	        {"date": "2025-09-28", "price": "200.50"}
+	    ],
+    )
+]
+```
+
+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 |
+- All `json` processing look the same
+- `composite` in much slower in Python, regardless of binary or text
+
+All JSON outputs are ready for output, so they require the less work on the Python side.
+
+`composite binary` is definitely slower: `psycopg` pays for the conversion in Python `date` and `Decimal`, and then again for converting it back manually.
+
+I don't really know why `composite text` is that slow in both benchmarks.
+
+## Resources
+- [Benchmarking script](test.py)
+
+## TODO
+
+- [ ] Rename folder
+- [ ] Resources: postgres json, postgres aggregation, psycopg
\ No newline at end of file
diff --git a/content/posts/postgres-aggregation-speed/tests.py b/content/posts/postgres-aggregation-speed/tests.py
new file mode 100644
index 0000000..3c32db5
--- /dev/null
+++ b/content/posts/postgres-aggregation-speed/tests.py
@@ -0,0 +1,190 @@
+"""
+uv run pytest --benchmark-columns=mean,stddev,iqr
+"""
+
+from decimal import Decimal
+from datetime import date
+from typing import Generator
+import psycopg
+from psycopg import Connection
+from psycopg.abc import ConnectionType
+from pytest_benchmark.fixture import BenchmarkFixture
+import pytest
+
+
+@pytest.fixture(scope="module")
+def conn() -> Generator[Connection]:
+    with psycopg.connect(
+        dbname="postgres", user="postgres", password="", host="127.0.0.1", port="5432"
+    ) as connection:
+        yield connection
+
+
+BASE_QUERY = """SELECT accommodation_id, {} AS daily_configs
+    FROM overrides
+    WHERE '[2025-07-01,2025-08-01)'::daterange @> date
+    GROUP BY overrides.accommodation_id"""
+COMPOSITE_QUERY = BASE_QUERY.format("array_agg((date, price))")
+JSON_OBJECT_EXPR = "json_agg(json_build_object('date', date, 'price', price::text))"
+JSONB_OBJECT_EXPR = "jsonb_agg(jsonb_build_object('date', date, 'price', price::text))"
+JSONB_ARRAY_EXPR = "jsonb_agg(jsonb_build_array(date, price::text))"
+JSON_ARRAY_EXPR = "json_agg(json_build_array(date, price::text))"
+
+type PythonOut = list[tuple[str, list[tuple[date, Decimal]]]]
+type JSONOut = list[tuple[str, list[dict[str, str]]]]
+
+
+def load_any_json_array_python(
+    conn: Connection, query: bytes, *, binary: bool
+) -> PythonOut:
+    with conn.cursor() as cursor:
+        cursor.execute(query, binary=binary)
+        rows = cursor.fetchall()
+
+    return [
+        (
+            row[0],
+            [(date.fromisoformat(item[0]), Decimal(item[1])) for item in row[1]],
+        )
+        for row in rows
+    ]
+
+
+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
+    ]
+
+
+def load_any_json_object_python(
+    conn: Connection, query: bytes, *, binary: bool
+) -> PythonOut:
+    with conn.cursor() as cursor:
+        cursor.execute(query, binary=binary)
+        rows = cursor.fetchall()
+
+    return [
+        (
+            row[0],
+            [
+                (date.fromisoformat(item["date"]), Decimal(item["price"]))
+                for item in row[1]
+            ],
+        )
+        for row in rows
+    ]
+
+
+def load_any_json_object_json(
+    conn: Connection, query: bytes, *, binary: bool
+) -> PythonOut:
+    with conn.cursor() as cursor:
+        cursor.execute(query, binary=binary)
+        return cursor.fetchall()
+
+
+def load_composite_text_python(conn: Connection) -> PythonOut:
+    with conn.cursor() as cursor:
+        cursor.execute(COMPOSITE_QUERY, binary=False)
+        rows = cursor.fetchall()
+
+    return [
+        (
+            row[0],
+            [(date.fromisoformat(item[0]), Decimal(item[1])) for item in row[1]],
+        )
+        for row in rows
+    ]
+
+
+def load_composite_text_json(conn: Connection) -> JSONOut:
+    with conn.cursor() as cursor:
+        cursor.execute(COMPOSITE_QUERY, binary=False)
+        rows = cursor.fetchall()
+
+    return [
+        (row[0], [{"date": item[0], "price": item[1]} for item in row[1]])
+        for row in rows
+    ]
+
+
+def load_composite_binary_python(conn: Connection) -> PythonOut:
+    with conn.cursor() as cursor:
+        cursor.execute(COMPOSITE_QUERY, binary=True)
+        return cursor.fetchall()
+
+
+def load_composite_binary_json(conn: Connection) -> JSONOut:
+    with conn.cursor() as cursor:
+        cursor.execute(COMPOSITE_QUERY, binary=True)
+        rows = cursor.fetchall()
+
+    return [
+        (
+            row[0],
+            [{"date": item[0].isoformat(), "price": str(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
+        )
+
+    def test_composite_binary(self, conn: Connection, benchmark: BenchmarkFixture):
+        benchmark(load_composite_binary_json, conn)
+
+    def test_composite_text(self, conn: Connection, benchmark: BenchmarkFixture):
+        benchmark(load_composite_text_json, conn)
+
+
+@pytest.mark.benchmark(group="python out")
+class TestPythonOut:
+    @pytest.mark.parametrize("binary", [False, True])
+    @pytest.mark.parametrize(
+        "expr",
+        [JSON_ARRAY_EXPR, JSONB_ARRAY_EXPR],
+        ids=["json array", "jsonb array"],
+    )
+    def test_json_array(
+        self,
+        conn: Connection,
+        benchmark: BenchmarkFixture,
+        binary: bool,
+        expr: str,
+    ):
+        benchmark(
+            load_any_json_array_python, conn, BASE_QUERY.format(expr), binary=binary
+        )
+
+    def test_composite_binary(self, conn: Connection, benchmark: BenchmarkFixture):
+        benchmark(load_composite_binary_python, conn)
+
+    def test_composite_text(self, conn: Connection, benchmark: BenchmarkFixture):
+        benchmark(load_composite_text_python, conn)