What is the main role of a connection pool in database operations?

Connection pool is a set of pre-opened and ready-to-use database connections. Its main role is to improve performance and efficiency when working with a database.

Without a pool, a new connection is created for each request, which is costly in terms of time and resources. A pool allows reusing existing connections, reducing the overhead of establishing and closing connections.

Example in Python using the psycopg2 library and a connection pool:

import psycopg2
from psycopg2 import pool

connection_pool = psycopg2.pool.SimpleConnectionPool(1, 10, user='user', password='pass', host='localhost', database='db')

conn = connection_pool.getconn()
try:
    with conn.cursor() as cur:
        cur.execute('SELECT * FROM table')
        result = cur.fetchall()
finally:
    connection_pool.putconn(conn)

This approach reduces delays and increases the scalability of the application.