LCFS - Add query timeout and connection pool monitoring
- 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
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