Connection pooling in Flask - Performance & Optimization

Performance: Connection pooling

HIGH IMPACT

Connection pooling affects how quickly the backend can handle database requests, impacting server response time and user experience.

Managing database connections efficiently in a Flask app

Flask

from flask import Flask
def create_app():
    from sqlalchemy import create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker

    app = Flask(__name__)
    engine = create_engine('sqlite:///database.db', pool_size=5, max_overflow=10)
    db_session = scoped_session(sessionmaker(bind=engine))

    @app.route('/')
    def index():
        data = db_session.execute('SELECT * FROM table').fetchall()
        return str(data)

    @app.teardown_appcontext
    def shutdown_session(exception=None):
        db_session.remove()

    return app

Reuses a pool of persistent connections, reducing connection setup time and server resource use.

📈 Performance GainReduces request latency by tens of milliseconds; lowers CPU and memory usage under load.

Managing database connections efficiently in a Flask app

Flask

from flask import Flask
import sqlite3

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('database.db')
    return conn

@app.route('/')
def index():
    conn = get_db_connection()
    data = conn.execute('SELECT * FROM table').fetchall()
    conn.close()
    return str(data)

Opening and closing a new database connection on every request causes high latency and resource overhead.

📉 Performance CostBlocks request handling for tens to hundreds of milliseconds per connection; increases server load under traffic.

Performance Comparison

Pattern	DOM Operations	Reflows	Paint Cost	Verdict
No connection pooling (new connection per request)	N/A	N/A	Increases server response time delaying paint	[X] Bad
Connection pooling with SQLAlchemy engine	N/A	N/A	Faster server response enables quicker paint	[OK] Good

Rendering Pipeline

Connection pooling reduces backend wait time for database responses, speeding up server response that affects browser rendering start.

→Server Processing

→Network Request

→First Paint

⚠️ BottleneckServer Processing (waiting for DB connection and query execution)

Core Web Vital Affected

INP

Connection pooling affects how quickly the backend can handle database requests, impacting server response time and user experience.

Optimization Tips

1Always use connection pooling to reuse database connections and reduce latency.

2Avoid opening and closing DB connections on every request to prevent server overload.

3Monitor backend response times in DevTools Network panel to verify pooling effectiveness.

Performance Quiz - 3 Questions

What is the main performance benefit of using connection pooling in a Flask app?

A Reduces latency by reusing existing database connections

B Increases the number of database connections opened per request

C Blocks rendering until all connections are closed

D Improves CSS rendering speed

DevTools: Network

How to check: Open DevTools Network panel, reload page, and check server response times for API/database calls.

What to look for: Look for reduced backend response times and faster time-to-first-byte indicating efficient DB connection reuse.

Practice

1. What is the main benefit of using connection pooling in a Flask application?

easy

A. It reuses database connections to improve performance.

B. It automatically creates new database tables.

C. It encrypts all database queries for security.

D. It reduces the size of the Flask application code.

Solution

1. Step 1: Understand connection pooling purpose

   Connection pooling keeps database connections open and reuses them instead of opening new ones each time.

2. Step 2: Identify the benefit in Flask apps

   This reuse reduces connection overhead and speeds up database access, improving app performance.

3. Final Answer:

   It reuses database connections to improve performance. -> Option A

4. Quick Check:

   Connection pooling = reuse connections = better speed [OK]

Hint: Pooling means reusing connections to speed up queries [OK]

Common Mistakes:

• Thinking pooling creates tables automatically
• Confusing pooling with encryption
• Believing pooling reduces app code size

2. Which of the following is the correct way to set the maximum pool size in Flask-SQLAlchemy?

easy

A. app.config['SQL_POOL_LIMIT'] = 10

B. app.config['POOL_MAX_SIZE'] = 10

C. app.config['SQLALCHEMY_POOL_SIZE'] = 10

D. app.config['MAX_POOL'] = 10

Solution

1. Step 1: Recall Flask-SQLAlchemy config keys

   The correct key to set pool size is 'SQLALCHEMY_POOL_SIZE'.

2. Step 2: Match the option with correct key

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 uses 'SQLALCHEMY_POOL_SIZE' correctly with value 10.

3. Final Answer:

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 -> Option C

4. Quick Check:

   Pool size config = SQLALCHEMY_POOL_SIZE [OK]

Hint: Look for 'SQLALCHEMY_POOL_SIZE' to set pool size [OK]

Common Mistakes:

• Using incorrect config keys like POOL_MAX_SIZE
• Confusing pool size with max connections
• Misspelling config keys

3. Given this Flask-SQLAlchemy setup:

app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 10

What is the maximum number of simultaneous database connections allowed?

medium

A. 15

B. 10

C. 5

D. 20

Solution

1. Step 1: Understand pool size and max overflow

   Pool size is 5 connections kept open. Max overflow allows 10 extra temporary connections.

2. Step 2: Calculate total max connections

   Total max = pool size (5) + max overflow (10) = 15 connections.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   Max connections = pool size + overflow = 15 [OK]

Hint: Add pool size and max overflow for max connections [OK]

Common Mistakes:

• Ignoring max overflow and choosing pool size only
• Adding incorrectly or mixing values
• Confusing max overflow as total limit

4. You set app.config['SQLALCHEMY_POOL_SIZE'] = 'five' in your Flask app. What will happen when you run the app?

medium

A. The app will run normally with pool size 5.

B. A TypeError or ValueError will occur due to wrong type.

C. The pool size will default to 10 silently.

D. The app will ignore the setting and use no pool.

Solution

1. Step 1: Check the type of pool size value

   Pool size must be an integer. Setting it as string 'five' is invalid.

2. Step 2: Identify error on invalid type

   Flask-SQLAlchemy or underlying SQLAlchemy will raise a TypeError or ValueError when parsing this config.

3. Final Answer:

   A TypeError or ValueError will occur due to wrong type. -> Option B

4. Quick Check:

   Wrong type config causes error, not silent fallback [OK]

Hint: Pool size must be integer, strings cause errors [OK]

Common Mistakes:

• Assuming string numbers work fine
• Expecting silent default fallback
• Ignoring error messages on startup

5. You want to optimize a Flask app for many users by adjusting connection pooling. Which combination of settings is best to handle 50 simultaneous users smoothly?

hard

A. SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0

B. SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10

C. SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5

D. SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40

Solution

1. Step 1: Calculate total connections needed

   To handle 50 users, total connections should be at least 50.

2. Step 2: Evaluate each option's total connections

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40: 10 + 40 = 50 (meets requirement)
   SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0: 50 + 0 = 50 (meets but no overflow flexibility)
   SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5: 5 + 5 = 10 (too low)
   SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10: 20 + 10 = 30 (too low)

3. Step 3: Choose best option for flexibility

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 allows a smaller base pool with high overflow, balancing resource use and capacity.

4. Final Answer:

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 -> Option D

5. Quick Check:

   Pool + overflow ≥ users; flexible overflow is best [OK]

Hint: Sum pool size and overflow to cover all users [OK]

Common Mistakes:

• Choosing too small total connections
• Ignoring overflow flexibility
• Setting pool size too high without overflow