Do you have a benchmark?

[punkpeye]

This looks like a very well written client with binary protocol support.

I discovered it after searching for node.js clients that support binary protocol.

brianc/node-postgres#2500

I am wondering if you've made any benchmarks?

My use case involves dealing with very large JSONB files.

I would love to understand if it would be worthwhile migrating to postgrejs as a way of solving these issues.

[punkpeye]

All my attempts at benchmarking show that postgrejs is significantly slower than pg. I am wondering what's the tradeoff?

[erayhanoglu]

Hello.

The JSONB binary format is not decoded by any driver. No driver sends JSONB in ​​binary format. All use JSONB in ​​text format.

So why isn't there a driver that decodes binary JSONB?

1. The server-side JSONB binary format is an internal implementation, not an API.
   • The PostgreSQL team does not accept the JSONB binary format as a public protocol.
   • Subject to change; there are no guarantees.
   • Therefore, no driver wants to be dependent on it.

2. The JSONB binary format is very complex.
   • JsonbContainer
   • JsonbValue
   • header bit-fields
   • offset tables
   • key/value pointer indices
   • internal numeric, boolean, and scalar encoding

3. Text JSON is much more portable.
   • JSON can be parsed in all languages.
   • Binary formatting would require a significant amount of code to be written in the driver.

PostgreJS transfers number, boolean, float4, float8, numeric, date, timestamp int2, int4, int8, box, circle, etc.. data types as binary.

By the way can you share me you benchmark code?

[dougg0k]

Any benchmark vs pg, pg-native and porsager/postgres?

https://github.com/porsager/postgres-benchmarks

https://github.com/nigrosimone/postgres-benchmarks

[stephenh]

I also came here intrigued by a new/modern driver implementation, particularly with support for binary format, but afaict it seems like postgrejs is slower across the board:

It seems like some of this is from extra queries that are issued behind the scenes, i.e. rollbackOnError adding SAVEPOINT queries (which I turned off), but also UNLISTEN * and ROLLBACK are being issued when the benchmark code isn't doing anything with listening or rollbacks (there are no errors, so no needed to rollback).

I.e. here are the queries seen by pg_stat_statements from doing a BEGIN+INSERT+COMMIT:

num=1 sql=UNLISTEN *
num=1 sql=INSERT INTO author (id, first_name, last_name, email) VALUES ($1, $2, $3, $4), ($5, $6, $7, $8), ($9, $10, $11, $12), ($13, $14, $15, $16), ($17, $18, $19, $20), ($21, $22, $23, $24), ($25, $26, $27, $28), ($29, $30, $31, $32), ($33, $34, $35, $36), ($37, $38, $39, $40)
num=1 sql=BEGIN
num=1 sql=COMMIT
num=1 sql=ROLLBACK

My guess is removing these unneeded statements would speed things up, but even then it seems like there is more perf optimization needed before postgrejs can complete with either node-pg or postgres.js -- disclaimer I'm not benchmarking "insert 100mb/1gb of data" or something which is maybe where the binary protocol shines, and postgrejs claim of super-fast starts being true?

https://github.com/joist-orm/joist-benchmarks