perf: Configure SQLAlchemy connection pool settings

[YaronZaki]

Problem Statement

quantara/web_app/db/database.py:24 creates the SQLAlchemy engine with create_engine(SQLALCHEMY_DATABASE_URL) using all defaults — no pool_size, max_overflow, pool_recycle, or pool_pre_ping configured. Under load, connections may exhaust or become stale.

Evidence

# quantara/web_app/db/database.py:24
engine = create_engine(SQLALCHEMY_DATABASE_URL)
# No pool_size, max_overflow, pool_recycle, pool_pre_ping configured.

Note: DBConnector.__init__() in crud/base.py:23 creates its own engine too — another unconfigured pool.

Impact

Medium — connection exhaustion under load. Default pool_size is 5, which is insufficient for a web application under concurrent requests. Idle connections not recycled may become stale (PostgreSQL closes idle connections after timeout). Stale connections cause "server closed the connection unexpectedly" errors.

Proposed Solution

Configure engine with: pool_size=10, max_overflow=20, pool_recycle=3600 (1 hour), pool_pre_ping=True. Make pool_size configurable via environment variable.

Acceptance Criteria

• create_engine() configured with pool_size, max_overflow, pool_recycle, pool_pre_ping
• pool_size configurable via DB_POOL_SIZE env var (default 10)
• Both engines (database.py and crud/base.py) configured
• All existing tests pass

File Map

• quantara/web_app/db/database.py:24 — configure pool settings
• quantara/web_app/db/crud/base.py:23 — configure pool settings in DBConnector

Testing Strategy

• Integration: Run concurrent requests, verify no connection exhaustion errors

Security Considerations

No direct security impact. pool_pre_ping=True prevents use of stale connections.

Definition of Done

• Code implemented and peer-reviewed
• Tests written and passing
• PR linked and merged

---

Labels: performance
Priority: Medium
Difficulty: Beginner
Estimated Effort: 0.5h

[snowrugar-beep]

assign me this issue.

[grantfox-oss]

🦊 GrantFox — @snowrugar-beep has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #59)
2. Your PR will be reviewed by the Quantarq maintainers

Good luck! Track your progress on GrantFox.