ENHANCEMENT: Use database connection pooling instead of per-request connections

[Senthil455]

Description

The application creates a new database connection for every request and closes it at the end. This is inefficient for a web application and will not scale well under concurrent load.

Current Code

def get_db_connection():
    database_url = os.environ.get('DATABASE_URL')
    if database_url:
        conn = psycopg2.connect(database_url, sslmode='require')
    else:
        conn = psycopg2.connect(host='localhost', database='postgres', user='postgres', password='4321')
    return conn

# Called at the start of every route
conn = get_db_connection()
cur = conn.cursor()
# ... do work ...
cur.close()
conn.close()

Impact

• Connection overhead - TCP handshake + auth for every request (5-20ms per connection)
• Poor scalability - PostgreSQL has a connection limit (default ~100); under load, connections may be exhausted
• Resource waste - creating and tearing down connections is CPU-intensive
• No connection reuse - even for the same user making sequential requests

Recommended Enhancement

1. Use psycopg2.pool.ThreadedConnectionPool or psycopg2.pool.SimpleConnectionPool:

   from psycopg2.pool import ThreadedConnectionPool
   
   pool = ThreadedConnectionPool(
       minconn=2, maxconn=10,
       dsn=database_url
   )
   
   def get_db_connection():
       return pool.getconn()
   
   def return_db_connection(conn):
       pool.putconn(conn)

2. Alternatively, use Flask's g object to manage connection per request:

   from flask import g
   
   def get_db():
       if 'db' not in g:
           g.db = pool.getconn()
       return g.db
   
   @app.teardown_appcontext
   def close_db(error):
       if hasattr(g, 'db'):
           pool.putconn(g.db)