kianiadee/fleetanalytics_mcp
1
0
Fork 0
Code Issues Pull requests 2 Projects Releases Packages Wiki Activity Actions

infra(pgbouncer): transaction-pooling front for timescale_db #2

Open
kianiadee wants to merge 1 commit from infra/pgbouncer into main
pull from: infra/pgbouncer
merge into: kianiadee:main
Conversation 0 Commits 1 Files changed 6 +271
kianiadee commented 2026-06-19 20:44:55 +00:00
Owner
Copy link

Summary

Stack-wide connection pooler to fix the too many connections errors on tracksolid_db (max_connections=100). About nine services each hold a persistent pool — several as the postgres superuser, idle for hours — so load peaks exhaust the 100-connection ceiling.

PgBouncer multiplexes many client connections onto a small fixed set of real backends (~25 with the shipped sizing), so the DB connection count no longer scales with the number of app pools.

Scope: this is shared infrastructure, only parked in the analytics-MCP repo because that's where the investigation happened. It likely belongs in the backend/ingestion repo — noted in the README.

What's included

  • pgbouncer.ini — transaction pooling, auth_query pass-through auth, bounded pool sizes (default_pool_size=20 + reserve_pool_size=5, one database ⇒ ≤25 backends), idle/lifetime recycling.
  • auth_setup.sql — pgbouncer_auth login role + SECURITY DEFINER pgbouncer.user_lookup() so per-app passwords aren't hand-maintained in a userlist.
  • docker-compose.yml — the service; join the existing DB network, healthcheck + log rotation.
  • userlist.txt.example + .gitignore — keep the auth verifier out of git.
  • README.md — deploy steps, incremental cutover (migrate the heavy superuser pools first), SHOW POOLS operations, sizing rule, and the transaction-pooling caveats.

Important caveats (in the README)

  • Transaction mode breaks session-scoped features (server-side prepared statements, cross-transaction SET, advisory locks, LISTEN/NOTIFY, WITH HOLD cursors). Per-connection options GUCs are ignored — pin them at the role level instead.
  • The analytics MCP is a minor consumer and sends options= GUCs + set_session(readonly=True). Recommended path: leave the MCP connecting directly and pool only the heavy superuser apps. (analytics_ro already has its read-only/timeout GUCs pinned at the role level, so correctness holds either way.)

Related (already applied)

idle_session_timeout=5min was set on analytics_ro and dashboard_ro to reap their idle pooled connections immediately — independent of this PR.

Not done

Nothing is deployed; these are config files only. Deploy is manual per the README, and each service is cut over incrementally.

🤖 Generated with Claude Code

## Summary Stack-wide connection pooler to fix the `too many connections` errors on `tracksolid_db` (`max_connections=100`). About nine services each hold a persistent pool — several as the **postgres superuser**, idle for hours — so load peaks exhaust the 100-connection ceiling. PgBouncer multiplexes many client connections onto a **small fixed set** of real backends (~25 with the shipped sizing), so the DB connection count no longer scales with the number of app pools. > **Scope:** this is shared infrastructure, only *parked* in the analytics-MCP repo because that's where the investigation happened. It likely belongs in the backend/ingestion repo — noted in the README. ## What's included - **`pgbouncer.ini`** — transaction pooling, `auth_query` pass-through auth, bounded pool sizes (`default_pool_size=20` + `reserve_pool_size=5`, one database ⇒ ≤25 backends), idle/lifetime recycling. - **`auth_setup.sql`** — `pgbouncer_auth` login role + SECURITY DEFINER `pgbouncer.user_lookup()` so per-app passwords aren't hand-maintained in a userlist. - **`docker-compose.yml`** — the service; join the existing DB network, healthcheck + log rotation. - **`userlist.txt.example` + `.gitignore`** — keep the auth verifier out of git. - **`README.md`** — deploy steps, **incremental cutover (migrate the heavy superuser pools first)**, `SHOW POOLS` operations, sizing rule, and the **transaction-pooling caveats**. ## Important caveats (in the README) - Transaction mode breaks session-scoped features (server-side prepared statements, cross-transaction `SET`, advisory locks, `LISTEN/NOTIFY`, `WITH HOLD` cursors). Per-connection `options` GUCs are ignored — pin them at the **role** level instead. - **The analytics MCP** is a *minor* consumer and sends `options=` GUCs + `set_session(readonly=True)`. Recommended path: leave the MCP connecting **directly** and pool only the heavy superuser apps. (`analytics_ro` already has its read-only/timeout GUCs pinned at the role level, so correctness holds either way.) ## Related (already applied) `idle_session_timeout=5min` was set on `analytics_ro` and `dashboard_ro` to reap their idle pooled connections immediately — independent of this PR. ## Not done Nothing is deployed; these are config files only. Deploy is manual per the README, and each service is cut over incrementally. 🤖 Generated with [Claude Code](https://claude.com/claude-code)
kianiadee added 1 commit 2026-06-19 20:44:56 +00:00 
The DB is at max_connections=100 with ~9 services each holding persistent pools
(several as the postgres superuser, idle for hours), so peaks hit "too many
connections". PgBouncer multiplexes many client connections onto a small fixed
set of backends, bounding DB connections regardless of how many app pools exist.

Adds (stack-wide infra, parked in this repo for now — see README scope note):
- pgbouncer.ini: transaction pooling, auth_query pass-through, bounded pool sizes
- auth_setup.sql: pgbouncer_auth role + SECURITY DEFINER pgbouncer.user_lookup()
  so per-app passwords aren't hand-maintained
- docker-compose.yml: the service (join the existing DB network)
- userlist.txt.example + .gitignore: keep the auth verifier out of git
- README.md: deploy steps, incremental cutover (superuser apps first), and the
  transaction-pooling caveats — including the MCP-specific note (rely on role-level
  GUCs; simplest to leave the minor MCP direct and pool the heavy superuser apps)

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
This pull request can be merged automatically.
This branch is out-of-date with the base branch
You are not authorized to merge this pull request.
View command line instructions

Checkout

From your project repository, check out a new branch and test the changes.
git fetch -u origin infra/pgbouncer:infra/pgbouncer
git checkout infra/pgbouncer

Merge

Merge the changes and update on Forgejo.
git checkout main
git merge --no-ff infra/pgbouncer
git checkout main
git merge --ff-only infra/pgbouncer
git checkout infra/pgbouncer
git rebase main
git checkout main
git merge --no-ff infra/pgbouncer
git checkout main
git merge --squash infra/pgbouncer
git checkout main
git merge --ff-only infra/pgbouncer
git checkout main
git merge infra/pgbouncer
git push origin main
Sign in to join this conversation.
Reviewers
No reviewers
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: kianiadee/fleetanalytics_mcp#2
No description provided.
Delete branch "infra/pgbouncer"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?