Repository navigation
Performance after postgres 4: pg is not the fastest client anymore #3856
Description
On October 29 postgres 4 was released, it rewrites everything below the public API. In the postgres-benchmarks linked in our README, pg and postgres 3 were about equal. With postgres 4, pg is now 41% slower overall and pg-native 45%.
Median latency of postgres relative to pg (lower means postgres is faster), from the CI runs of the benchmark:
| Rows | Sequential, postgres 3 | Sequential, postgres 4 | Pipelined x10, postgres 3 | Pipelined x10, postgres 4 |
|---|---|---|---|---|
| 1 | 0.95 | 0.84 | 1.04 | 1.01 |
| 100 | 0.96 | 0.65 | 1.03 | 0.83 |
| 500 | 1.01 | 0.51 | 1.03 | 0.54 |
The gap grows with the number of rows, so the difference is mostly in how rows are read, not in the protocol or in the pool. The release notes confirm it: int2, int4, oid and bool are decoded straight from the bytes, rows are decoded by hand, and a row reader is compiled for each result shape.
Open pull requests that could help, most relevant first. The numbers come from each PR, they are not measured together.
| PR | What | Possible impact |
|---|---|---|
| #3855 | Read integers, booleans and text straight from the row bytes | Probably the most relevant: 22-50% lower latency on 100 and 500 rows, no change on 1 row. Integers do not pass from pg-types anymore, so it also helps who has pg-types 2.2.0 |
| #3773 | Decode short ascii values without buffer.toString() |
+12% to +33% on 100 and 500 rows in our CI benchmark. Same cost of #3855: after it the client does not use BufferReader.string() for rows, so this must move on the new path |
| #3814 | pg-native: read all the values of a result in one call | 52-60% less time to build a result on 100 and 500 rows. Needs brianc/node-libpq#144 |
| #3753 | Cache field parsers and prebuilt rows per query shape | Neutral with 5 columns. With 20 and 100 columns +5% to +12% on 1 row sequential, but -9% on 1 row pipelined with 100 columns. Touches the same code of #3855 |
I propose to evaluate #3855 first, then to rebase #3773 on it. With 1 row sequential postgres stays about 20% faster, this is not row decoding and no PR here explains it, so it needs more investigation.
Activity