clover's git

the-knife

1	# PostgreSQL benchmarks
2
3	## PgClient
4
5	`PgClient.ts` measures complete queries through `PgClient`, including Effect execution, pool checkout, the PostgreSQL
6	wire protocol, and result decoding. It covers a parameterized one-row query, a 100-row result at three and at twenty
7	columns, a single-statement transaction, and 20 concurrent queries through a ten-connection pool. The transaction is
8	there because `BEGIN`, `COMMIT`, and `SAVEPOINT` reach the connection through a different path from the statements
9	between them, so a change to that path is invisible to every other workload here. Its numbers are dominated by the
10	commit, so read it for regressions rather than for throughput. The two row-count-matched widths are there because
11	per-column work and per-row work scale differently: a change that barely moves a three-column result can dominate a
12	twenty-column one. A PostgreSQL Testcontainer starts automatically, so Docker is the only external
13	requirement.
14
15	Run it from the repository root:
16
17	```sh
18	nix develop -c pnpm --filter @effect/sql-pg benchmark:client
19	```
20
21	Set `PGCLIENT_BENCHMARK_MULTIPLEX=1` to run the same workloads on a multiplexed pool, where concurrent statements are
22	pipelined rather than sent one at a time. It only changes the concurrent workload; the sequential ones submit one
23	statement at a time and have nothing to pipeline.
24
25	To use an existing PostgreSQL server instead, set `PGCLIENT_BENCHMARK_URL` to its connection URI. Keep the same server,
26	Node version, and machine load when comparing revisions.
27
28	Two numbers from different machines are not comparable, and on anything but Linux the container is the reason. Docker
29	runs the server inside a virtual machine on macOS and Windows, so every round trip crosses a virtualised network and
30	the workloads that wait on one - the single-row query and the transaction - measure that boundary as much as they
31	measure the client. Point `PGCLIENT_BENCHMARK_URL` at a natively installed server there, or at a unix socket
32	(`postgres:///postgres?host=/tmp`), and compare two revisions against the same server rather than comparing platforms.
33
34	The benchmark file is deliberately valid against both the working branch and `main`. Since `main` does not contain the
35	new file yet, pipe it from the benchmark branch while checked out on `main`:
36
37	```sh
38	BENCHMARK_REF=origin/eff-854/pg-connection-startup
39	nix develop -c pnpm install
40	git show "$BENCHMARK_REF:packages/sql/pg/benchmark/PgClient.ts" \
41	| nix develop -c pnpm --dir packages/sql/pg exec node --input-type=module
42	```
43
44	This runs the benchmark source from `BENCHMARK_REF`, but resolves `@effect/sql-pg` from the current checkout.
45
46	## PgCodec
47
48	This benchmark compares the `@effect/sql-pg` binary codecs with the native codec paths used by `postgres.js`. It runs
49	entirely in one Node process. It does not open a database connection or include socket, TLS, query, or server latency.
50
51	Run it from the repository root:
52
53	```sh
54	nix develop -c pnpm --filter @effect/sql-pg benchmark:codec
55	```
56
57	Every suite but one uses the same six-column semantic row: `int4`, `bool`, `float8`, `text`, `jsonb`, and `bytea`;
58	`int4[] decode` uses a 16-element `int4` array instead, because an array is where a value's cost is paid per element.
59	Each sample processes 100 rows. `@effect/sql-pg` uses its binary wire representation and `postgres.js` uses its normal
60	text-oriented codecs, so the byte layouts differ even where the values match.
61
62	## The end-to-end suites
63
64	`Bind frame from JavaScript values` and `DataRow frames to JavaScript values` measure the native codec end to end:
65	JavaScript values in, a complete `Bind` frame out, and `DataRow` frames in, JavaScript values out.
66
67	The `Bind` suite runs `@effect/sql-pg` twice. `value sink` is `PgProtocol.makeBindEncoder(PgTypes.writeParameter)`,
68	which writes each value straight into the frame; it is what a client should use. `encoded parameters` is
69	`PgProtocol.encodeBind` over `PgTypes.encode` output, which allocates an array per parameter and copies it into the
70	frame. Both produce the same bytes.
71
72	`Bind frame from array parameters` is the same comparison for a row of two arrays, a 16-element `int4[]` and an
73	8-element `text[]`. Arrays are where writing values in place pays most, because the old path encoded every element
74	into an array of its own before copying all of them into the frame. Its rows are per parameter row, not per element,
75	so they are not comparable with the six-column suites.
76
77	## The component suites
78
79	The remaining suites split that work up, which is useful for finding hot spots but not for ranking the libraries:
80
81	- `type encode` and `type decode` compare the public codec paths for the shared scalar types.
82	- `int4[] decode` only measures `@effect/sql-pg`; `postgres.js` ships no array parser and leaves an array column as
83	text.
84	- The two parser suites feed a block of 100 `DataRow` frames to the native parser, first as one buffer and then in
85	64-byte chunks. `postgres.js` is absent from those tables because its protocol parser is private and fused to
86	live connection and query state. Wrapping that state machine in a fake socket would measure client orchestration as
87	well as parsing, so it would not be an equivalent offline parser benchmark. Its exported serializers and parsers are
88	still included in the encode and decode tables.
89
90	Tinybench warms each task for 250 ms and measures it for one second. Results depend on the Node version, CPU, power
91	management, and other local load. Compare results from the same machine and runtime rather than treating one run as a
92	portable score.