bcgov / bcgov/lcfs

LCFS - Add query timeout and connection pool monitoring

Open
#4,100 0 comments 0 reactions 0 assignees View on GitHub
Dev Ops Low python Tech Debt
Dominant language
Python
Stars
6
Forks
6
Avg merge
1d 14h
Merged PRs (30d)
80

Description

**Describe the task**
Add query timeout configuration and connection pool monitoring to prevent long-running queries from blocking the connection pool.

**Purpose**
The current database configuration (`pool_size=30, max_overflow=50, pool_timeout=30`) has no per-query timeout. A single slow query can hold a connection indefinitely, and under load, the pool can be exhausted with no visibility into what's happening.

**Acceptance Criteria**
- [ ] Configure a per-query statement timeout (e.g., 30s for normal queries, configurable for reports/exports)
- [ ] Add connection pool utilization logging/metrics
- [ ] Add slow query logging (queries exceeding a configurable threshold)
- [ ] Ensure export/report generation endpoints have appropriate extended timeouts
- [ ] Document configuration in wiki

**Additional context**
- DB config: `backend/lcfs/db/dependencies.py` (lines 21-30)
- Pool settings: `pool_size=30, max_overflow=50, pool_timeout=30`
- No query timeout currently specified
- Raw SQL queries via `text()` bypass ORM logging

Contributor guide

Open the contributing guide

Research direction

Start with backend/lcfs/db/dependencies.py lines 21-30 and review the existing pool settings and raw SQL use via text(). Then trace the report and export endpoints to determine their timeout needs. Done means configurable query and extended report/export timeouts, pool utilization and slow-query logging or metrics, plus the configuration documented in the wiki.

Written by the indexing model from the issue text.

Assessment

Tech stack
python
Domain
backend, databases, observability, performance
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.