Efficiency of opening PostgreSQL database connections

Question

We have a PostgreSQL database to store our C++ application's data, and we use libpqxx to connect to it.

Currently, we open a new pqxx::connection for each transaction we'd like to run. In deployment, we expect to execute at most probably about four or five dozen transactions per minute, and our application is going to be running 24x7x365.

According to the PostgreSQL architectural fundamentals,

...[the PostgreSQL server process] starts ("forks") a new process for each connection.

That sounds to me like our method of opening a new pqxx::connection for every transaction is really inefficient, since we are indirectly spawning a few dozen new processes every minute. Is this something that we should really be worried about?

I see here on the PostgreSQL wiki that PostgreSQL does not, itself, maintain a pool of client connection processes, so it would seem that we do indeed need to worry about it. If so, is there a "proper" way to keep pqxx::connection objects around indefinitely so that a new process isn't being forked every time I need to connect to the database? Keep in mind that my application needs to run all day, every day, so it would be unacceptable for my TCP connections to drop after a long period of time.

Answer 1

What you're doing is inefficient, but not drastically so. PostgreSQL's fork cost is low on a unix platform; backends are fairly cheap to create and destroy.

The setup, authentication etc does take time, so you'll increase your transaction latencies.

It would be preferable to use a connection pool, either in-app or in a proxy like pgbouncer. That said, for "a few dozen connections per minute" I wouldn't be too worried unless you're facing load issues. It's ugly, but it's just not that bad.

TCP connections don't just "drop" after some set period though. Unless you're behind some resource-limited stateful NAT router or firewall they can remain idle indefinitely. If you are you just need to enable TCP keepalives. There is no real reason not to hold a connection open as long as you like.

In any case, your application should already have retry logic for connection loss because backends can die due to admin action, a PostgreSQL server crash recovery and restart, bugs, server resource exhaustion causing OOM, etc. Never, ever fire and forget a transaction. Your app should remember the full state required to redo the whole xact from the start until it receives confirmation that the xact has committed from the DB. If there's a failure at any step it should be able to reconnect and retry.