1import * as PgProtocol from "@effect/sql-pg/PgProtocol"
2import * as PgTypes from "@effect/sql-pg/PgTypes"
3import * as Result from "effect/Result"
4import assert from "node:assert/strict"
5import { Buffer } from "node:buffer"
6import postgres from "postgres"
7import { Bench } from "tinybench"
8
9const postgresSql = postgres({ max: 0 })
10const postgresSerializers = postgresSql.options.serializers
11const postgresParsers = postgresSql.options.parsers
12
13const success = <A, E>(result: Result.Result<A, E>): A => {
14 if (Result.isFailure(result)) throw result.failure
15 return result.success
16}
17
18const makeParameter = (oid: number, value: unknown): PgTypes.Parameter => {
19 const parameter = { oid, value } as PgTypes.Parameter
20 Object.defineProperty(parameter, PgTypes.ParameterTypeId, { value: PgTypes.ParameterTypeId })
21 return parameter
22}
23
24const jsonValue = { active: true, tags: ["effect", "postgres"] }
25const byteaValue = Uint8Array.from({ length: 32 }, (_, index) => index)
26
27const payload = [
28 { oid: PgTypes.OID.int4, value: 42, text: "42" },
29 { oid: PgTypes.OID.bool, value: true, text: "t" },
30 { oid: PgTypes.OID.float8, value: 1234.5, text: "1234.5" },
31 { oid: PgTypes.OID.text, value: "codec benchmark: hello \\ world", text: "codec benchmark: hello \\ world" },
32 { oid: PgTypes.OID.jsonb, value: jsonValue, text: JSON.stringify(jsonValue) },
33 { oid: PgTypes.OID.bytea, value: byteaValue, text: `\\x${Buffer.from(byteaValue).toString("hex")}` }
34] as const
35
36const effectEncoded = payload.map(({ oid, value }) => success(PgTypes.encode(value, oid)))
37const postgresTextParsers = payload.map(({ oid }) => postgresParsers[oid] ?? ((value: string) => value))
38
39const encodeEffect = () => payload.map(({ oid, value }) => success(PgTypes.encode(value, oid)))
40const encodePostgresJs = () => payload.map(({ oid, value }) => postgresSerializers[oid]?.(value) ?? String(value))
41
42const decodeEffect = () => payload.map(({ oid }, index) => success(PgTypes.decode(effectEncoded[index], oid, 1)))
43const decodePostgresJs = () => payload.map(({ text }, index) => postgresTextParsers[index](text))
44
45const normalize = (values: ReadonlyArray<unknown>) =>
46 values.map((value) => value instanceof Uint8Array ? Array.from(value) : value)
47
48assert.deepStrictEqual(normalize(decodeEffect()), normalize(decodePostgresJs()))
49
50const makeDataRow = (fields: ReadonlyArray<string | Uint8Array>): Uint8Array => {
51 const encoded = fields.map((field) => typeof field === "string" ? Buffer.from(field, "utf8") : Buffer.from(field))
52 const length = 4 + 2 + encoded.reduce((total, field) => total + 4 + field.length, 0)
53 const frame = Buffer.allocUnsafe(1 + length)
54 frame[0] = 0x44
55 frame.writeInt32BE(length, 1)
56 frame.writeInt16BE(encoded.length, 5)
57 let offset = 7
58 for (const field of encoded) {
59 frame.writeInt32BE(field.length, offset)
60 offset += 4
61 field.copy(frame, offset)
62 offset += field.length
63 }
64 return frame
65}
66
67const rowsPerParserRun = 100
68const dataRow = makeDataRow(payload.map(({ text }) => text))
69const parserPayload = new Uint8Array(dataRow.length * rowsPerParserRun)
70for (let index = 0; index < rowsPerParserRun; index++) {
71 parserPayload.set(dataRow, index * dataRow.length)
72}
73const parserChunks = Array.from(
74 { length: Math.ceil(parserPayload.length / 64) },
75 (_, index) => parserPayload.subarray(index * 64, (index + 1) * 64)
76)
77const effectSingleParser = PgProtocol.makeParser()
78const effectChunkedParser = PgProtocol.makeParser()
79
80const parseEffectSingle = () => effectSingleParser.push(parserPayload).length
81const parseEffectChunked = () => {
82 let count = 0
83 for (const chunk of parserChunks) {
84 count += effectChunkedParser.push(chunk).length
85 }
86 return count
87}
88assert.equal(parseEffectSingle(), rowsPerParserRun)
89assert.equal(parseEffectChunked(), rowsPerParserRun)
90
91// End to end: JavaScript values in, a complete Bind frame out. This is the
92// comparison the type-encode table cannot make because it includes framing.
93
94const bindEffect = () =>
95 success(PgProtocol.encodeBind({
96 portal: "",
97 statement: "s1",
98 parameters: payload.map(({ oid, value }) => success(PgTypes.encode(value, oid)))
99 }))
100
101// The same job with the values written straight into the frame, which is what
102// a client should use: no array per parameter, no copy out of one.
103const bindParameters = payload.map(({ oid, value }) => makeParameter(oid, value))
104const encodeBindFused = PgProtocol.makeBindEncoder(PgTypes.writeParameter)
105const bindEffectFused = () => success(encodeBindFused({ portal: "", statement: "s1", parameters: bindParameters }))
106
107// Array parameters, where the win is much larger: every element used to be
108// encoded into an array of its own before being copied into the frame.
109const arrayRow = [
110 makeParameter(PgTypes.OID.int4Array, Array.from({ length: 16 }, (_, index) => index)),
111 makeParameter(PgTypes.OID.textArray, Array.from({ length: 8 }, (_, index) => `tag-${index}`))
112]
113const bindArrayEffectFused = () => success(encodeBindFused({ portal: "", statement: "s2", parameters: arrayRow }))
114const bindArrayEffect = () =>
115 success(PgProtocol.encodeBind({
116 portal: "",
117 statement: "s2",
118 parameters: arrayRow.map((parameter) => success(PgTypes.encodeParameter(parameter)))
119 }))
120// End to end: DataRow frames in, JavaScript values out. Each library reads its
121// own wire format, so the frames the binary codec sees carry binary fields.
122const binaryDataRow = makeDataRow(effectEncoded)
123const binaryRowsPayload = new Uint8Array(binaryDataRow.length * rowsPerParserRun)
124for (let index = 0; index < rowsPerParserRun; index++) {
125 binaryRowsPayload.set(binaryDataRow, index * binaryDataRow.length)
126}
127
128// Fields read through the field reader are plain array reads, so unlike the
129// codec calls the other suites make they need somewhere to land to survive.
130let fieldSink: unknown
131
132// The same rows read through a parser that decodes each field where it lies,
133// with no view per column. This is what a client should use: the view is a
134// fixed cost per field, so it dominates exactly when the columns are cheap.
135const binaryColumns = payload.map(({ oid }) => ({ dataTypeOid: oid, format: 1 }))
136const fusedRowParser = PgProtocol.makeParser({ readField: success(PgTypes.makeFieldReader(binaryColumns)) })
137
138const decodeRowsFused = () => {
139 let count = 0
140 for (const message of fusedRowParser.push(binaryRowsPayload)) {
141 if (message._tag !== "DataRow") continue
142 for (let index = 0; index < message.values.length; index++) {
143 fieldSink = message.values[index]
144 }
145 count++
146 }
147 return count
148}
149
150// A wide row of cheap columns, where the view per field is most of the work.
151const wideColumnCount = 20
152const wideOid = PgTypes.OID.int4
153const wideEncoded = Array.from(
154 { length: wideColumnCount },
155 (_, index) => success(PgTypes.encode(index * 7, wideOid))
156)
157const wideRow = makeDataRow(wideEncoded)
158const wideRowsPayload = new Uint8Array(wideRow.length * rowsPerParserRun)
159for (let index = 0; index < rowsPerParserRun; index++) {
160 wideRowsPayload.set(wideRow, index * wideRow.length)
161}
162const wideParser = PgProtocol.makeParser()
163const wideFusedParser = PgProtocol.makeParser({
164 readField: success(PgTypes.makeFieldReader(wideEncoded.map(() => ({ dataTypeOid: wideOid, format: 1 }))))
165})
166
167const decodeWideRows = () => {
168 let count = 0
169 for (const message of wideParser.push(wideRowsPayload)) {
170 if (message._tag !== "DataRow") continue
171 for (let index = 0; index < message.values.length; index++) {
172 const field = message.values[index]
173 if (field !== null) fieldSink = success(PgTypes.decode(field, wideOid, 1))
174 }
175 count++
176 }
177 return count
178}
179
180const decodeWideRowsFused = () => {
181 let count = 0
182 for (const message of wideFusedParser.push(wideRowsPayload)) {
183 if (message._tag !== "DataRow") continue
184 for (let index = 0; index < message.values.length; index++) {
185 fieldSink = message.values[index]
186 }
187 count++
188 }
189 return count
190}
191
192const effectRowParser = PgProtocol.makeParser()
193const decodeRowsEffect = () => {
194 let count = 0
195 for (const message of effectRowParser.push(binaryRowsPayload)) {
196 if (message._tag !== "DataRow") continue
197 for (let index = 0; index < message.values.length; index++) {
198 const field = message.values[index]
199 if (field !== null) success(PgTypes.decode(field, payload[index].oid, 1))
200 }
201 count++
202 }
203 return count
204}
205
206assert.equal(decodeRowsEffect(), rowsPerParserRun)
207assert.equal(decodeRowsFused(), rowsPerParserRun)
208assert.equal(decodeWideRows(), rowsPerParserRun)
209assert.equal(decodeWideRowsFused(), rowsPerParserRun)
210assert.ok(bindEffect().length > 0)
211assert.deepStrictEqual(Array.from(bindEffectFused()), Array.from(bindEffect()))
212assert.deepStrictEqual(Array.from(bindArrayEffectFused()), Array.from(bindArrayEffect()))
213
214// Arrays are the one place a value's cost is paid per element, so they get a
215// suite of their own.
216const int4ArrayLength = 16
217const int4ArrayValue = Array.from({ length: int4ArrayLength }, (_, index) => index * 1000)
218const int4ArrayEncoded = success(PgTypes.encode(int4ArrayValue, PgTypes.OID.int4Array))
219// postgres.js has no array parser to compare against: it leaves array columns
220// as their text.
221const decodeArrayEffect = () => success(PgTypes.decode(int4ArrayEncoded, PgTypes.OID.int4Array, 1))
222
223assert.deepStrictEqual(decodeArrayEffect(), int4ArrayValue)
224
225// Column names are the one place the parser decodes text, and the existing
226// payload has none: every field of a `DataRow` stays raw bytes.
227const columnNames = ["id", "email", "created_at", "balance", "metadata", "is_active"]
228const makeRowDescription = (names: ReadonlyArray<string>): Uint8Array => {
229 const encoded = names.map((name) => Buffer.from(name, "utf8"))
230 const length = 4 + 2 + encoded.reduce((total, name) => total + name.length + 1 + 18, 0)
231 const frame = Buffer.allocUnsafe(1 + length)
232 frame[0] = 0x54
233 frame.writeInt32BE(length, 1)
234 frame.writeInt16BE(names.length, 5)
235 let offset = 7
236 for (const name of encoded) {
237 name.copy(frame, offset)
238 offset += name.length
239 frame[offset++] = 0
240 frame.writeInt32BE(0, offset)
241 frame.writeInt16BE(0, offset + 4)
242 frame.writeInt32BE(23, offset + 6)
243 frame.writeInt16BE(4, offset + 10)
244 frame.writeInt32BE(-1, offset + 12)
245 frame.writeInt16BE(1, offset + 16)
246 offset += 18
247 }
248 return frame
249}
250
251const descriptionsPerRun = 100
252const rowDescription = makeRowDescription(columnNames)
253const descriptionPayload = new Uint8Array(rowDescription.length * descriptionsPerRun)
254for (let index = 0; index < descriptionsPerRun; index++) {
255 descriptionPayload.set(rowDescription, index * rowDescription.length)
256}
257const effectDescriptionParser = PgProtocol.makeParser()
258const parseDescriptionEffect = () => effectDescriptionParser.push(descriptionPayload).length
259
260assert.equal(parseDescriptionEffect(), descriptionsPerRun)
261
262// `numeric` is the one builtin whose text is built a digit group at a time, and
263// the existing payload has no column of that type either.
264// Neither `pg` nor postgres.js has anything to compare against here: both hand
265// back the text PostgreSQL sent without looking at it, while the binary codec
266// has to build that text out of base-10000 digit groups.
267const numericValues = ["0.00", "12345.6789", "-1", "9".repeat(20) + "." + "1".repeat(10), "NaN"]
268const numericEncoded = numericValues.map((value) => success(PgTypes.encode(value, PgTypes.OID.numeric)))
269const decodeNumericEffect = () => numericEncoded.map((bytes) => success(PgTypes.decode(bytes, PgTypes.OID.numeric, 1)))
270
271assert.deepStrictEqual(decodeNumericEffect(), numericValues)
272
273const codecRowsPerRun = 100
274let sink: unknown
275
276const batch = (run: () => unknown, rows = codecRowsPerRun) => () => {
277 let value: unknown
278 for (let index = 0; index < rows; index++) {
279 value = run()
280 }
281 sink = value
282}
283
284const options = {
285 iterations: 64,
286 time: 1_000,
287 warmupIterations: 16,
288 warmupTime: 250,
289 timestampProvider: "hrtimeNow" as const
290}
291
292const runSuite = async (
293 name: string,
294 rowsPerRun: number,
295 tasks: ReadonlyArray<readonly [name: string, run: () => unknown]>,
296 shape = `${payload.length} columns per row`
297) => {
298 const bench = new Bench(options)
299 for (const [taskName, run] of tasks) {
300 bench.add(taskName, run)
301 }
302 await bench.run()
303 console.log(`\n${name} (${shape})`)
304 console.table(bench.table((task) => {
305 const result = task.result
306 if (result?.state !== "completed") {
307 return { Implementation: task.name, State: result?.state ?? "missing result" }
308 }
309 return {
310 Implementation: task.name,
311 "Rows/s": Math.round(result.throughput.mean * rowsPerRun),
312 "Latency (ns/row)": (result.latency.mean * 1_000_000 / rowsPerRun).toFixed(2),
313 RME: `${result.latency.rme.toFixed(2)}%`,
314 Samples: result.latency.samplesCount
315 }
316 }))
317}
318
319await runSuite("type encode", codecRowsPerRun, [
320 ["@effect/sql-pg binary", batch(encodeEffect)],
321 ["postgres.js text", batch(encodePostgresJs)]
322])
323
324await runSuite("type decode", codecRowsPerRun, [
325 ["@effect/sql-pg binary", batch(decodeEffect)],
326 ["postgres.js text", batch(decodePostgresJs)]
327])
328
329await runSuite("int4[] decode", codecRowsPerRun, [
330 ["@effect/sql-pg binary", batch(decodeArrayEffect)]
331], `${int4ArrayLength} elements per array`)
332
333await runSuite("Bind frame from JavaScript values", codecRowsPerRun, [
334 ["@effect/sql-pg binary, value sink", batch(bindEffectFused)],
335 ["@effect/sql-pg binary, encoded parameters", batch(bindEffect)]
336])
337
338await runSuite(
339 "Bind frame from array parameters",
340 codecRowsPerRun,
341 [
342 ["@effect/sql-pg binary, value sink", batch(bindArrayEffectFused)],
343 ["@effect/sql-pg binary, encoded parameters", batch(bindArrayEffect)]
344 ],
345 "int4[16] and text[8] per row"
346)
347
348await runSuite("DataRow frames to JavaScript values", rowsPerParserRun, [
349 ["@effect/sql-pg binary, field reader", decodeRowsFused],
350 ["@effect/sql-pg binary, view per field", decodeRowsEffect]
351])
352
353await runSuite("DataRow frames to JavaScript values, wide rows", rowsPerParserRun, [
354 ["@effect/sql-pg binary, field reader", decodeWideRowsFused],
355 ["@effect/sql-pg binary, view per field", decodeWideRows]
356], `${wideColumnCount} int4 columns per row`)
357
358await runSuite("numeric decode", codecRowsPerRun, [
359 ["@effect/sql-pg binary", batch(decodeNumericEffect)]
360], `${numericValues.length} values per row`)
361
362await runSuite("protocol RowDescription parser", descriptionsPerRun, [
363 ["@effect/sql-pg", parseDescriptionEffect]
364], `${columnNames.length} columns per description`)
365
366await runSuite("protocol DataRow parser, one chunk", rowsPerParserRun, [
367 ["@effect/sql-pg", parseEffectSingle]
368])
369
370await runSuite("protocol DataRow parser, 64-byte chunks", rowsPerParserRun, [
371 ["@effect/sql-pg", parseEffectChunked]
372])
373
374if (sink === undefined || fieldSink === undefined) {
375 throw new Error("Benchmark did not run")
376}
377
378await postgresSql.end()