diff options
Diffstat (limited to 'content/posts/nested-postgres-results')
11 files changed, 409 insertions, 204 deletions
diff --git a/content/posts/nested-postgres-results/composite-array-agg.sql b/content/posts/nested-postgres-results/composite-array-agg.sql deleted file mode 100644 index 0def84f..0000000 --- a/content/posts/nested-postgres-results/composite-array-agg.sql +++ /dev/null @@ -1,8 +0,0 @@ -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/composite-array-agg.txt b/content/posts/nested-postgres-results/composite-array-agg.txt deleted file mode 100644 index eb5c507..0000000 --- a/content/posts/nested-postgres-results/composite-array-agg.txt +++ /dev/null @@ -1,33 +0,0 @@ -GroupAggregate (cost=14206.02..16920.03 rows=8849 width=36) (actual time=17.442..24.779 rows=10000 loops=1) - Output: accommodation_id, array_agg(ROW(date, price)) - Group Key: overrides.accommodation_id - Buffers: shared hit=6442 - -> Gather Merge (cost=14206.02..16702.25 rows=21433 width=13) (actual time=17.433..20.521 rows=20000 loops=1) - Output: accommodation_id, date, price - Workers Planned: 2 - Workers Launched: 2 - Buffers: shared hit=6442 - -> Sort (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.503..14.704 rows=6667 loops=3) - Output: accommodation_id, date, price - Sort Key: overrides.accommodation_id - Sort Method: quicksort Memory: 424kB - Buffers: shared hit=6442 - Worker 0: actual time=13.119..13.313 rows=6171 loops=1 - Sort Method: quicksort Memory: 385kB - Buffers: shared hit=2007 - Worker 1: actual time=13.642..13.847 rows=6437 loops=1 - Sort Method: quicksort Memory: 394kB - Buffers: shared hit=2086 - -> Parallel Seq Scan on public.overrides (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.011..13.689 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.009..12.361 rows=6171 loops=1 - Buffers: shared hit=1971 - Worker 1: actual time=0.010..12.859 rows=6437 loops=1 - Buffers: shared hit=2050 -Planning: - Buffers: shared hit=129 -Planning Time: 0.272 ms -Execution Time: 25.133 ms diff --git a/content/posts/nested-postgres-results/data-model.svg b/content/posts/nested-postgres-results/data-model.svg new file mode 100644 index 0000000..70ddf60 --- /dev/null +++ b/content/posts/nested-postgres-results/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/nested-postgres-results/docker-compose.yml b/content/posts/nested-postgres-results/docker-compose.yml deleted file mode 100644 index 7dd182c..0000000 --- a/content/posts/nested-postgres-results/docker-compose.yml +++ /dev/null @@ -1,8 +0,0 @@ -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 index 3e07017..dbee8af 100644 --- a/content/posts/nested-postgres-results/index.md +++ b/content/posts/nested-postgres-results/index.md @@ -1,39 +1,250 @@ --- -title: "" +title: PostgreSQL aggregation processing speed draft: true -date: +date: 2025-09-29 lastmod: -description: "" +description: Comparison of aggregation function speed, on the database and in a host language 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: +## Key takeaways -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 +**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 -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. +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. + + + +```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. -## Aggregation row results +| 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 -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. +All JSON outputs are ready for output, so they require the less work on the Python side. -To better understand the differences in performance, I want to compare the explain plans that should look very similar. They are indeed composed of the same 4 steps, and only the duration of the last step is changed by the kind of aggregation. +`composite binary` is definitely slower: `psycopg` pays for the conversion in Python `date` and `Decimal`, and then again for converting it back manually. -| Method | Parallel Seq Scan | Sort | Gather Merge | GroupAggregate | -| --------------- | ----------------- | ------- | ------------ | -------------- | -| composite array | 13.7 ms | 1.02 ms | 5.68 ms | 4.26 ms | -| json array | 13.4 ms | 1.03 ms | 3.38 ms | 13.6 ms | -| jsonb array | 13.6 ms | 1.05 ms | 3.51 ms | 23.1 ms | +I don't really know why `composite text` is that slow in both benchmarks. -These results seem in line with my expectations, after updating my ideas on JSONB: this binary format is efficient for storage efficiency and querying the data, but at the cost of encoding and decoding speed. In our case, the output is raw text, so we pay the encoding in JSONB by postgres, and certainly the decoding back by the server or our client. +## Resources +- [Benchmarking script](test.py) -## Python gathering +## TODO -The python part seem to be low, pure json is still the best. Binary composites are ok. +- [ ] Rename folder +- [ ] Resources: postgres json, postgres aggregation, psycopg
\ No newline at end of file diff --git a/content/posts/nested-postgres-results/json-array-agg.sql b/content/posts/nested-postgres-results/json-array-agg.sql deleted file mode 100644 index a7ca29c..0000000 --- a/content/posts/nested-postgres-results/json-array-agg.sql +++ /dev/null @@ -1,11 +0,0 @@ -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/json-array-agg.txt b/content/posts/nested-postgres-results/json-array-agg.txt deleted file mode 100644 index 63bebe7..0000000 --- a/content/posts/nested-postgres-results/json-array-agg.txt +++ /dev/null @@ -1,33 +0,0 @@ -GroupAggregate (cost=14206.02..16973.61 rows=8849 width=36) (actual time=15.958..31.415 rows=10000 loops=1) - Output: accommodation_id, json_agg(json_build_object('date', date, 'price', price)) - Group Key: overrides.accommodation_id - Buffers: shared hit=6442 - -> Gather Merge (cost=14206.02..16702.25 rows=21433 width=13) (actual time=15.939..17.800 rows=20000 loops=1) - Output: accommodation_id, date, price - Workers Planned: 2 - Workers Launched: 2 - Buffers: shared hit=6442 - -> Sort (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.210..14.424 rows=6667 loops=3) - Output: accommodation_id, date, price - Sort Key: overrides.accommodation_id - Sort Method: quicksort Memory: 423kB - Buffers: shared hit=6442 - Worker 0: actual time=13.430..13.637 rows=6367 loops=1 - Sort Method: quicksort Memory: 391kB - Buffers: shared hit=2064 - Worker 1: actual time=13.558..13.776 rows=6247 loops=1 - Sort Method: quicksort Memory: 388kB - Buffers: shared hit=2031 - -> Parallel Seq Scan on public.overrides (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.013..13.396 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.008..12.658 rows=6367 loops=1 - Buffers: shared hit=2028 - Worker 1: actual time=0.018..12.728 rows=6247 loops=1 - Buffers: shared hit=1995 -Planning: - Buffers: shared hit=129 -Planning Time: 0.292 ms -Execution Time: 31.781 ms diff --git a/content/posts/nested-postgres-results/jsonb-array-agg.sql b/content/posts/nested-postgres-results/jsonb-array-agg.sql deleted file mode 100644 index 91d024c..0000000 --- a/content/posts/nested-postgres-results/jsonb-array-agg.sql +++ /dev/null @@ -1,11 +0,0 @@ -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 deleted file mode 100644 index 5936490..0000000 --- a/content/posts/nested-postgres-results/jsonb-array-agg.txt +++ /dev/null @@ -1,33 +0,0 @@ -GroupAggregate (cost=14206.02..16973.61 rows=8849 width=36) (actual time=16.239..41.215 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=14206.02..16702.25 rows=21433 width=13) (actual time=16.215..18.136 rows=20000 loops=1) - Output: accommodation_id, date, price - Workers Planned: 2 - Workers Launched: 2 - Buffers: shared hit=6442 - -> Sort (cost=13206.00..13228.33 rows=8930 width=13) (actual time=14.416..14.629 rows=6667 loops=3) - Output: accommodation_id, date, price - Sort Key: overrides.accommodation_id - Sort Method: quicksort Memory: 424kB - Buffers: shared hit=6442 - Worker 0: actual time=13.574..13.780 rows=6267 loops=1 - Sort Method: quicksort Memory: 388kB - Buffers: shared hit=2037 - Worker 1: actual time=13.768..13.981 rows=6339 loops=1 - Sort Method: quicksort Memory: 391kB - Buffers: shared hit=2050 - -> Parallel Seq Scan on public.overrides (cost=0.00..12619.99 rows=8930 width=13) (actual time=0.024..13.580 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.011..12.728 rows=6267 loops=1 - Buffers: shared hit=2001 - Worker 1: actual time=0.051..12.968 rows=6339 loops=1 - Buffers: shared hit=2014 -Planning: - Buffers: shared hit=129 -Planning Time: 0.274 ms -Execution Time: 41.571 ms diff --git a/content/posts/nested-postgres-results/setup.sql b/content/posts/nested-postgres-results/setup.sql deleted file mode 100644 index a54563b..0000000 --- a/content/posts/nested-postgres-results/setup.sql +++ /dev/null @@ -1,26 +0,0 @@ -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 index c1f1506..3c32db5 100644 --- a/content/posts/nested-postgres-results/tests.py +++ b/content/posts/nested-postgres-results/tests.py @@ -1,37 +1,190 @@ -from pathlib import Path +""" +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 connection() -> Generator[psycopg.Connection]: +def conn() -> Generator[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: +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) - cursor.fetchall() + 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() -@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, + 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) |
