Postgresql – How to improve performance on PostgreSQL when using multiple concurrent processes

Looking at the query plans, the query optimizer is pretty much begging you to add useful indexes to the tables. Until the query plans are robust, forget about looking at locks and I/O.

To make solid suggestions for indexes, you pretty much need to give us a picture of the schema, all the queries, and all existing indexes on the tables. I would not take the missing index suggestions exactly as they are presented. They undoubtedly will help, but they may not be the best thing to do (the suggestions aren't usually... intelligent... for lack of a better word).

The parallelism operators mean that the query used multiple CPUs to process the query in parallel. This is only desirable when you absolutely need SQL Server to do a bunch of work -- in this case, the problem is that you're asking SQL Server to do too much work and it's doing the best it can to satisfy the query in the shortest possible time.

With a small number of rows being returned from each query with what appears to be highly selective predicates, I would expect these queries to fly with useful indexes applied (and no parallelism would be needed).

Just in the visible area, it looks like queries 9 and 10 are virtually the same for ~35% total cost apiece; relatively speaking, the table scan of Transactions should go to pretty much zero. Depending on what you're doing, you may be able to combine those two queries into one.

If that's not enough speed, there are other techniques involving precomputed aggregates, but if you're kinda-sorta-almost okay with 500ms, then you won't need to go this far with just a robust set of base query plans. If all the queries are highly selective, indexes alone should easily get you into the < 5 ms range.

Finally, consider adding useful indexes to your temp tables. A nested loops join with a table scan on the bottom input can be quite expensive depending on the data involved.

I think you see a false dichotomy that does not exist.

It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful for other reasons:

• Block until a connection is available rather than return an immediate max_connections exceeded error;
• You can switch the pool target server if you fail-over to a standby without having to reconfigure applications;
• You can limit application database workers to lower than max_connections, so you can still make reporting and maintenance connections as a non-superuser.

Additionally, if suitable for your application you can use transaction-level pooling to greatly increase the number of clients that can be served by your server.

I would not try to keep a strict 1:1 mapping from Apache workers to PostgreSQL workers, personally. If you've got (say) 16 cores and good I/O on your PostgreSQL box you might want something like 16-20 active PostgreSQL workers for optimal performance. You're almost certain to want more Apache workers than that, since they'll be kept busy by things like:

• persistent HTTP connections from idle clients;
• Unresponsive or very slow clients;
• Intentional DoS connections;
• Network interruptions between client and server; etc

If possible, consider a transaction-pooling design with short-lived transactions instead.