Opening a database connection is slow compared to running a small query, and a database can only hold so many connections at once. In ETL jobs with many parallel tasks, both limits show up quickly. A connection pool keeps a small set of connections open and lends them out to the code that needs them.
The cost of connecting
A new connection means a network handshake, authentication, and often TLS setup, and the server allocates memory for it (in Postgres, a whole process for each connection). If a job opens a new connection for each small query, most of its time goes on connecting.
The risk: too many connections
Suppose an extraction job runs 200 parallel tasks, and each opens its own connection to the production Postgres replica, whose max_connections is 100. Tasks fail with "too many connections", and worse, the application sharing that database might also be refused. This is a common way for an ETL job to cause an outage.
Pools
A pool holds, say, 10 connections. Code asks for one, uses it, and returns it. The pool reuses them, and queues requests when all are busy. A good pool also checks that connections are still alive, and replaces broken ones.
Spark and other parallel jobs
In Spark, each executor task that writes to or reads from a database opens its own connection. With foreachPartition, open one connection per partition, not one per row, and close it at the end:
def write_partition(rows):
conn = psycopg2.connect(...)
try:
cur = conn.cursor()
for batch in chunked(rows, 1000):
cur.executemany(sql, batch)
conn.commit()
finally:
conn.close()
df.foreachPartition(write_partition)Limit the number of partitions writing at the same time (for example coalesce to 8) so the total number of connections stays low.
Server-side poolers
A tool such as PgBouncer sits in front of Postgres and multiplexes many client connections onto a few real ones. It is common when many applications and jobs share a database.
Good habits
Always close connections in a finally block or a context manager, so a failure does not leak connections. Set timeouts. Know the connection limit of each source and sink and keep your parallelism under it. Say that you would never point heavy parallel extraction at a primary database.