1import { PgClient } from "@effect/sql-pg"
2import { PostgreSqlContainer } from "@testcontainers/postgresql"
3import { Effect, Redacted } from "effect"
4import * as Reactivity from "effect/reactivity/Reactivity"
5import { execFileSync } from "node:child_process"
6import { cpus } from "node:os"
7import { Bench } from "tinybench"
8
9const externalUrl = process.env.PGCLIENT_BENCHMARK_URL
10const resource = externalUrl === undefined
11 ? await new PostgreSqlContainer("postgres:alpine").start().then((container) => ({
12 container,
13 url: container.getConnectionUri()
14 }))
15 : { container: undefined, url: externalUrl }
16const { container, url } = resource
17
18let sink = 0
19
20const multiplex = process.env.PGCLIENT_BENCHMARK_MULTIPLEX === "1"
21
22const run = Effect.gen(function*() {
23 const sql = yield* PgClient.make({
24 url: Redacted.make(url),
25 applicationName: "pgclient-benchmark",
26 maxConnections: 10,
27 ...(multiplex ? { multiplex: true } : {})
28 })
29
30 // A table for the transaction workload to write into.
31 yield* sql.unsafe("DROP TABLE IF EXISTS pgclient_benchmark")
32 yield* sql.unsafe("CREATE UNLOGGED TABLE pgclient_benchmark (value int4)")
33
34 const workloads = [
35 {
36 name: "parameterized SELECT (1 row)",
37 unitsPerOperation: 1,
38 unit: "queries/s",
39 effect: sql.unsafe("SELECT $1::int4 AS value, $2::text AS label", [42, "effect"]).pipe(
40 Effect.map((rows) => rows.length)
41 )
42 },
43 {
44 name: "generate_series (100 rows x 3 columns)",
45 unitsPerOperation: 100,
46 unit: "rows/s",
47 effect: sql.unsafe(
48 "SELECT value, value * 2 AS doubled, value::text AS label FROM generate_series(1, 100) AS value"
49 ).pipe(Effect.map((rows) => rows.length))
50 },
51 {
52 name: "generate_series (100 rows x 20 columns)",
53 unitsPerOperation: 100,
54 unit: "rows/s",
55 effect: sql.unsafe(
56 `SELECT ${
57 Array.from({ length: 20 }, (_, index) => `value + ${index} AS column_${index}`).join(", ")
58 } FROM generate_series(1, 100) AS value`
59 ).pipe(Effect.map((rows) => rows.length))
60 },
61 {
62 // Transactions run their BEGIN, COMMIT, and SAVEPOINT statements through
63 // a different path from the body, so a change to one is invisible here
64 // without a shape that exercises both.
65 name: "transaction (1 INSERT)",
66 unitsPerOperation: 1,
67 unit: "transactions/s",
68 effect: sql.withTransaction(
69 sql.unsafe("INSERT INTO pgclient_benchmark (value) VALUES ($1::int4)", [1])
70 ).pipe(Effect.map((rows) => rows.length + 1))
71 },
72 {
73 name: "20 concurrent parameterized SELECTs",
74 unitsPerOperation: 20,
75 unit: "queries/s",
76 effect: Effect.all(
77 Array.from(
78 { length: 20 },
79 (_, index) => sql.unsafe("SELECT $1::int4 AS value", [index])
80 ),
81 { concurrency: "unbounded" }
82 ).pipe(Effect.map((results) => results.flat().length))
83 }
84 ]
85
86 // Open the pool and prepare the statements before Tinybench starts timing.
87 for (const workload of workloads) {
88 yield* workload.effect
89 }
90
91 for (const workload of workloads) {
92 const bench = new Bench({
93 iterations: 16,
94 time: 2_000,
95 warmupIterations: 8,
96 warmupTime: 500,
97 timestampProvider: "hrtimeNow"
98 })
99
100 bench.add(workload.name, async () => {
101 const result = await Effect.runPromise(workload.effect)
102 sink += result
103 })
104
105 yield* Effect.promise(() => bench.run())
106 console.table(bench.table((task) => {
107 const result = task.result
108 if (result?.state !== "completed") {
109 return { Workload: task.name, State: result?.state ?? "missing result" }
110 }
111 return {
112 Workload: task.name,
113 "operations/s": Math.round(result.throughput.mean),
114 [workload.unit]: Math.round(result.throughput.mean * workload.unitsPerOperation),
115 "latency (ms)": result.latency.mean.toFixed(3),
116 RME: `${result.latency.rme.toFixed(2)}%`,
117 Samples: result.latency.samplesCount
118 }
119 }))
120 }
121}).pipe(
122 Effect.scoped,
123 Effect.provide(Reactivity.layer)
124)
125
126try {
127 const revision = execFileSync("git", ["rev-parse", "--short", "HEAD"], { encoding: "utf8" }).trim()
128 // Results get compared across machines, and the container makes that
129 // especially misleading off Linux, so every run says where it came from.
130 console.log(
131 `PgClient benchmark at ${revision} (${externalUrl === undefined ? "Testcontainers" : "external PostgreSQL"}${
132 multiplex ? ", multiplex" : ""
133 })`
134 )
135 console.log(
136 ` ${process.platform}/${process.arch}, node ${process.versions.node}, ${cpus()[0]?.model ?? "unknown CPU"}`
137 )
138 await Effect.runPromise(run)
139 if (sink === 0) throw new Error("Benchmark did not run")
140} finally {
141 await container?.stop()
142}