Postgres.js with default settings doesn'
Postgres.js with default settings doesn't play very nicely with Hyperdrive at the moment. We're planning a release to roll out in the next couple weeks that will help that significantly.
In short, postgres.js uses named prepared statements by default and that can cause several additional round trips depending on your query pattern.
For right now if you want to try testing again using node-postgres for best performance that's the best bet. You can also use
prepare: false when setting up your connection with postgres.js to help ameliorate some of the performance penalty.
As to your use of transactions, that does mean you're likely not going to have great cache hit rates, but shouldn't cause issues otherwise.8 Replies
For right now if you want to try testing again using node-postgres for best performance that's the best bet. You can also use prepare: false when setting up your connection with postgres.js to help ameliorate some of the performance penalty.Tangentially (unrelated to Hyperdrive), I'd love to know more about node-postgres vs postgres.js. With
prepare: false, should we expect comparable performance between the two libraries, or are there other pros / cons of each library?From our perspective, the main differences are:
* node-postgres uses text format in its describe messages, so it doesn't need to do the initial *parse-describe-flush" sequence for type inference. This saves a roundtrip that we cannot cache today
* Node-postgres doesn't use prepared statements, which currently error out on reuse causing an additional two round trips
* Unfortunately, node-postgres only works with the old node-compat flag, locking it out from use with pages and in general causing some issues. I believe there's been some conversations with the maintainer to try and address this but unfortunately I'm not read up on those
Overall after our next release the intention is that performance should be comparable between them, with a slight edge to postgres.js in the situation of repeating the same query on the same connection multiple times, and a slight edge to node-postgres for one-off queries.
Aside, would you mind sharing the Hyperdrive id you used for testing? If you're using prepare: false and still seeing really bad latency I'd be curious to look a bit more. (Note Hyperdrive id is not a secret and is safe to share here or in DM's)
node-postgres uses text format in its describe messages, so it doesn't need to do the initial *parse-describe-flush" sequence for type inference. This saves a roundtrip that we cannot cache todayInteresting. To be clear, even if we select
prepare: false, does postgres.js incur more roundtrips per query?Yes. Part of the upcoming release is caching that first block of messages with the types, but even then it's an extra round trip the first time a query is sent.
I see. And if I understanding correctly, that penalty is not incurred for subsequent queries on that connection? (What is not clear to me is how the library keeps track of this when there may be multiple connections in its internal connection pool ...)
Correct, postgres.js will send Bind-Execute-Sync messages re-using the prepared statement and the inferred types.
In terms of how exactly postgres.js implements that, I assume something along the lines of a hashmap of which connections have which information, similar to how most connection poolers have solved this. Their code is open source, you could check. PgBouncer had a PR doing similar work last November you could see as well for an alternative approach.
Got it. Thank you for the explanation! 🙏
Unknown User•7mo ago
Message Not Public
Sign In & Join Server To View