8000 Initial commit · malthe/postgres-benchmarks@14f8722 · GitHub
[go: up one dir, main page]
Skip to content

Commit 14f8722

Browse files
porsagerporsager
committed
Initial commit
0 parents  commit 14f8722

File tree

11 files changed

+1148
-0
lines changed

11 files changed

+1148
-0
lines changed

‎.gitignore

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
node_modules

‎README.md

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
# Postgres Library Benchmarks for Node.js
2+
3+
This is a set of benchmarks focusing on the performance of Postgres client libraries for Node.js. The benchmarks are primarily direct selects of values to measure the input-output performance and not the Performance of postgres data fetching.
4+
5+
> NB. In daily usage it is very likely that this difference doesn't matter as much since the time spent by the client library is negligable compared to the query time itself.
6+
7+
Currently benchmarked libraries are
8+
9+
- [postgres](https://github.com/porsager/postgres)
10+
- [pg-promise](https://github.com/vitaly-t/pg-promise)
11+
- [pg](https://github.com/brianc/node-postgres)
12+
- [pg-native](https://github.com/brianc/node-pg-native)
13+
- [slonik](https://github.com/gajus/slonik)
14+
15+
## Results
16+
17+
These are the results from running the benchmarks on a Macbook Pro 2,9 GHz Quad-Core Intel Core i7 with a default Postgres 12 installation and Node 12.4.0.
18+
The time is the average of 5 rounds, running the queries 10,000 times after some warmup rounds.
19+
20+
client | select | select_arg | select_args | select_where
21+
:--------- | -------------: | -------------: | -------------: | -------------:
22+
postgres | 82ms (5.1x) | 91ms (5.3x) | 226ms (3.6x) | 229ms (5.0x)
23+
pg-promise | 331ms (1.3x) | 368ms (1.3x) | 605ms (1.3x) | 771ms (1.5x)
24+
pg | 292ms (1.4x) | 473ms (1.0x) | 772ms (1.1x) | 1070ms (1.1x)
25+
pg-native | 419ms (1.0x) | 478ms (1.0x) | 816ms (1.0x) | 1138ms (1.0x)
26+
slonik | 10466ms (0.0x) | 10904ms (0.0x) | 11190ms (0.0x) | 11107ms (0.0x)
27+
28+
29+
> Slonik is left out of the chart because I'm probably using it wrong and the numbers seem too far off.
30+
31+
![results chart](results.png)
32+
> lower is better
33+
34+
## Query descriptions:
35+
36+
#### select
37+
38+
```sql
39+
select 1 as x
40+
```
41+
42+
#### select_arg
43+
44+
```sql
45+
select $1 as x
46+
47+
-- $1 is just 1
48+
```
49+
50+
#### select_args
51+
```sql
52+
select
53+
$1 as int,
54+
$2 as string,
55+
$3 as timestamp,
56+
$4 as null,
57+
$5 as boolean,
58+
$6 as bytea,
59+
$7 as json
60+
61+
--$1 = 1337
62+
--$2 = 'wat'
63+
--$3 = new Date()
64+
--$4 = null
65+
--$5 = false
66+
--$6 = Buffer.from('awesome')
67+
--$7 = "[{ "some": "json" }, { "array": "object" }]"
68+
```
69+
70+
#### select_where
71+
72+
```sql
73+
select * from pg_catalog.pg_type where typname = $1
74+
75+
--$1 = 'bool'
76+
```
77+

‎index.js

Lines changed: 93 additions & 0 deletions
< 2364 td data-grid-cell-id="diff-e727e4bdf3657fd1d798edcd6b099d6e092f8573cba266154583a746bba0f346-empty-4-0" data-selected="false" role="gridcell" style="background-color:var(--diffBlob-additionNum-bgColor, var(--diffBlob-addition-bgColor-num));text-align:center" tabindex="-1" valign="top" class="focusable-grid-cell diff-line-number position-relative left-side">
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
const queries = [
2+
'select',
3+
'select_arg',
4+
'select_args',
5+
'select_where'
6+
]
7+
, clients = [
8+
'postgres',
9+
'pg-promise',
10+
'pg',
11+
'pg-native',
12+
//'slonik'
13+
]
14+
, warmup = 3
15+
, iterations = 10000
16+
, rounds = 5
17+
, variance = false
18+
19+
const series = (fn, xs) => {
20+
const results = []
21+
return xs.reduce((acc, x) => acc.then(() => fn(x).then(r => results.push(r))), Promise.resolve({})).then(() => results)
22+
}
23+
24+
series(test, clients).then(results => {
25+
results.forEach(x => {
26+
x.queries = Object.values(x.queries).filter(q => q.times).map(q => {
27+
q.times = q.times.slice(warmup).map(x => x[0] + x[1] / 1e9)
28+
const avg = (q.times.reduce((a, b) => a + b) / q.times.length)
29+
, sorted = q.times.slice().sort((a, b) => a - b)
30+
31+
return {
32+
time: avg,
33+
variance: 1 / sorted[0] * (sorted[sorted.length - 1] - sorted[0]) * 100
34+
}
35+
})
36+
})
37+
38+
queries.forEach((q, i) =>
39+
results.map(x => x.queries[i]).sort((a, b) => b.time - a.time).forEach((x, i, xs) => {
40+
x.diff = xs[0].time / x.time
41+
})
42+
)
43+
44+
console.log([pad(1, 10, ' ')('client')].concat(queries.map(pad(0, 14, ' '))).join(' | '))
45+
console.log([pad(1, 10, '-')(':')].concat(queries.map(x => ':').map(pad(0, 14, '-'))).join(' | '))
46+
results.forEach(x =>
47+
console.log(
48+
[pad(1, 10, ' ')(x.client)].concat(
49+
x.queries.map(x =>
50+
x.time.toFixed(3) + 's' + ' (' + x.diff.toFixed(1) + 'x)'
51+
).map(pad(0, 14, ' '))
52+
).join(' | ')
53+
)
54+
)
55+
56+
console.log(['client'].concat(queries).join(';'))
57+
results.forEach(x =>
58+
console.log([x.client].concat(x.queries.map(x => x.time.toFixed(3))).join(';').replace(/\./g, ','))
59+
)
60+
61+
process.exit()
62+
})
63+
64+
function pad(p, n, c) {
65+
return x => {
66+
while (x.length < n)
67+
x = p ? x + c : c + x
68+
return x
69+
}
70+
}
71+
72+
async function test(clientName, done) {
73+
return new Promise(async resolve => {
74+
const client = await Promise.resolve(require('./' + clientName))
75+
const q = queries.flatMap(x => Array(rounds + warmup).fill(x))
76+
77+
async function run(x) {
78+
x.start = process.hrtime()
79+
let j = 0
80+
for(let i = 0; i < iterations; i++)
81+
x().then(() => ++j === iterations && next(x))
82+
}
83+
84+
function next(x) {
85+
x && (x.times = (x.times || []).concat([process.hrtime(x.start)]))
86+
q.length
87+
? setTimeout(() => run(client.queries[q.pop()]), 100)
88+
: Promise.resolve(client.end()).then(() => resolve({ client: clientName, queries: client.queries }))
89+
}
90+
91+
next()
92+
})
93+
}

0 commit comments

Comments
 (0)
0