Sure, I can talk through that. It's very "in the weeds" and is definitely the sort of thing we prefe
Sure, I can talk through that. It's very "in the weeds" and is definitely the sort of thing we prefer to avoid making our users have to deal with, but I find it interesting.
So all of this has to do with Bind messages (https://www.postgresql.org/docs/current/protocol-message-formats.html) and how you handle the parameter values for each bound parameter. There are two formats available,
Binary format avoids needing text parsing, is smaller on the wire, and can in some scenarios be more precise. However, you generally need to get the actual format codes for each type from the server. To do this, postgres.js structures all new prepared statements (and therefore most new queries), as a Parse-Describe-Flush sequence, which will get back the ParameterDescription containing the binary format codes for each parameter. It then uses that information to construct the Bind message, and sends a followup Bind-Execute-Sync sequence, to get the actual results.
Node-postgres just builds the whole thing in one shot, as a Parse-Bind-Execute-Sync sequence. This pulls back the results in a single round-trip, at the cost of making the origin DB do some extra work handling the Bind message, and with the risk of some loss of precision in types like
Which is most appropriate, as always, depends
So all of this has to do with Bind messages (https://www.postgresql.org/docs/current/protocol-message-formats.html) and how you handle the parameter values for each bound parameter. There are two formats available,
binarytextBinary format avoids needing text parsing, is smaller on the wire, and can in some scenarios be more precise. However, you generally need to get the actual format codes for each type from the server. To do this, postgres.js structures all new prepared statements (and therefore most new queries), as a Parse-Describe-Flush sequence, which will get back the ParameterDescription containing the binary format codes for each parameter. It then uses that information to construct the Bind message, and sends a followup Bind-Execute-Sync sequence, to get the actual results.
Node-postgres just builds the whole thing in one shot, as a Parse-Bind-Execute-Sync sequence. This pulls back the results in a single round-trip, at the cost of making the origin DB do some extra work handling the Bind message, and with the risk of some loss of precision in types like
floattimestampWhich is most appropriate, as always, depends
PostgreSQL Documentation
53.7. Message Formats # This section describes the detailed format of each message. Each is marked to indicate that it can …
