We have a Postgres database on AWS, with a table that will contain millions (but not tens of millions) of rows. Primary key is a single column. Data access is going to be basically uniformly distributed among the data rows, and it's going to be simple SELECT statements by primary key, so basically we get either one row or none as the result data set.
Our client is written in Node.js using the node-postgres module. We are using connection pooling. We would like to do anything we reasonably can to execute our SELECTs as fast as possible; would prepared statements or stored functions be a good idea in this scenario? What would be, on paper, faster?
Edit: we are currently handling connection pooling via the pg-pool module that comes with node-psotgres itself, and pick a new connection from the pool with every request. I am not sure whether the work done by the server when preparing a statement, or a stored function, is lost when the connection is handed back to the pool.
You might optimize for PostgreSQL query performance on Node.js a couple ways with the node-postgres module, but with the use cases you describe, I feel like neither of them would offer significant performance benefits. The general tl;dr: is to benchmark, benchmark, benchmark.
Using prepared statements and a connection pool are not mutually exclusive, but the query plans for prepared statements are only cached on a per-connection basis. As long as those connections remain active (as long as the Pool itself is not closed), the cached query plans remain intact and associated with the connection that executed them. In the worst-case scenario, multiple connections perform the planning stage for a given prepared query once each.
Prepared statements may confer some performance benefits, since node-postgres does skip the query planning phase for them after first execution (source, JavaScript, source, native bindings).
And speaking of the native bindings, the author of the node-postgres module also maintains a Node.js-compatible set of bindings to the native C libpq library in the package node-pg-native. If your deployment environment supports a compatible compiler, they might be worth investigating.
The documentation warns that there may be edge case incompatibilities between the JavaScript client and the native bindings, so be sure to take those into consideration before you make such a transition to ensure nothing else in your application breaks.