brianc
When does node-pg use binary protocol
Hi! I was looking at node-pg-types and noticed it has a separate file for decoding binary streams
But when I set a custom type parser, such as on int8 or timestamptz, this only affects the text protocol. Are there situations where node-pg switches to binary protocol? And in binary protocol, are custom type parsers ignored?
Are there situations where node-pg switches to binary protocol?
I think queries accept a binary: true option? It also used to when a Buffer parameter was passed; not sure if it still does.
And in binary protocol, are custom type parsers ignored?
Custom text type parsers don’t apply in that case, right. You can add custom binary parsers with the three-argument form of setTypeParser.
Thanks. My read of node-pg connection is that it does not use the binary protocol, nor is that option documented anymore!
I was looking at asyncpg and wondering why we didn't have something similar for node...
Oh interesting I haven't looked at the binary protocol in a while, I'll take a look at it and maybe there are some nice perf gains we can get there!
On Fri, Mar 26, 2021 at 10:04 AM Isaac Levy @.***> wrote:
[image: sad perf] https://raw.githubusercontent.com/MagicStack/asyncpg/master/performance.png
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/brianc/node-postgres/issues/2500#issuecomment-808289809, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAMHIJJQECEOH5UNEMMT33TFSPABANCNFSM4ZXAFEIQ .
As far as I can tell binary mode is not supported yet? https://github.com/brianc/node-postgres/blob/6121bd3bb0e0e8ef8ec8ad5d02f59fef86b2f992/packages/pg-protocol/src/parser.ts#L87
Would be extremely interested in keeping up with this development.
To achieve performance similar in the graph probably just requires porting asyncpg to node. They rewrote everything and use native code.
“just” rewriting everything and writing native code probably is WAY out of scope for this project, not to mention insanely time-consuming for the author. Trying to optimize parts, enabling binary protocol where possible, etc, would more likely result in smaller but easier “wins” that are much more realistic to achieve without a full rewrite.
I get that. I wasn't saying it was in the scope of this bug. I was saying we can't expect to reach those performance targets in this project.
Isaac
On Wed, May 5, 2021 at 10:14 AM Jane Jeon @.***> wrote:
“just” rewriting everything and writing native code probably is WAY out of scope for this project, not to mention insanely time-consuming for the author. Trying to optimize parts, enabling binary protocol where possible, etc, would more likely result in smaller but easier “wins” that are much more realistic to achieve without a full rewrite.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/brianc/node-postgres/issues/2500#issuecomment-832722422, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAH26LUSVNTFRJZJLQ3JNBTTMFHDJANCNFSM4ZXAFEIQ .
Was there ever any progress here?
I am wondering specifically around handling of jsonb.
Parsing of large JSON documents is the current bottleneck I am dealing with. It sounds like using binary protocol would help here.
Interestingly, this project claims to use binary protocol:
https://github.com/panates/postgrejs
Binary Wire Protocol: Implements the full binary wire protocol for all PostgreSQL data types, ensuring robust and efficient data handling.
Never heard of it.
Would using pg-native leverage the binary protocol?
No, pg needs to parse and serialize between JavaScript values and binary representations regardless of whether it’s using libpq.
Parsing of large JSON documents is the current bottleneck I am dealing with. It sounds like using binary protocol would help here.
https://github.com/brianc/node-postgres/issues/3467#issuecomment-3530402820 would help determine if that’s the case.