agentsclimarketplace

Managing postgres connections

Skill pumarogie/claude-postgres-skills/skills/managing-postgres-connections

Guides Postgres connection pooling when applications add instances, connection churn or storms appear, max_connections is exhausted, PgBouncer is introduced, or pgxpool and server pool limits must be sized safely.From its SKILL.md

Install
npx -y skills add pumarogie/claude-postgres-skills --skill managing-postgres-connections

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

  • 2 stars2 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

4.8 KB, as published. Nobody here has run it

Managing Postgres Connections

Overview

A Postgres connection is a server process with memory and scheduling cost. Bound concurrency across all application instances and preserve an operational reserve. Raising max_connections does not create capacity.

Choose the pooling layer

Prefer PgBouncer when many processes share a database: it enforces one server budget. Use an in-process pool such as Go's pgxpool when necessary, but multiply its maximum by every replica and process.

Use this mode decision for PgBouncer:

ModeUseRequired warning
Transaction (default)Stateless web/request workloadsA later transaction may use another backend; session state is not preserved
SessionCompatible fallback for session-dependent codeIdle clients retain server connections, so multiplexing is less efficient
StatementRare, independent autocommit statements onlyCannot run multi-statement transactions; unsuitable for most apps

Transaction pooling breaks assumptions about session-level prepared statements, session-level advisory locks, LISTEN/NOTIFY, session variables, cursors held across transactions, and temporary tables. Use transaction-level locks, SET LOCAL, and transaction-scoped cursors where possible; otherwise use a dedicated session-pooled or direct connection.

For pgx, never assume its implicit prepared-statement cache is compatible with transaction pooling. Either configure and test PgBouncer protocol-level prepared-statement tracking, or disable implicit preparation with a compatible mode such as default_query_exec_mode=exec. Read reference/pgbouncer-pooling-modes.md for the detailed compatibility choices.

Size from the database inward

Always bound the total pool; never maximize it to eliminate waiting. Budget server connections from CPU, memory, storage, and measured concurrency, reserve administration capacity, then divide the remainder among workloads. For ordinary OLTP, start testing near twice the database vCPU count and increase only when measurements show useful I/O wait. Load testing decides the final number.

Calculate the autoscaling ceiling explicitly: replicas × processes per replica × pool max. For example, 40 pods with a maximum of 20 can demand 800 connections. Do not answer that storm by simply raising max_connections: more backends add memory and scheduling pressure; they do not add database capacity. Put PgBouncer in front of replicas when they need to share one bounded server budget.

total application server pools
  + migration/worker pools
  + monitoring
  + operational reserve
  <= usable Postgres connection budget

Pool wait time is backpressure. Brief waiting is safer than saturating CPU, memory, storage, and lock managers. Cap acquisition time so overload fails predictably.

Configure both layers deliberately

  • Keep application connections long-lived and return them promptly after each unit of work.
  • Set in-process maxima so their aggregate cannot bypass the server budget.
  • Give long-running jobs a separate small pool so they cannot starve latency-sensitive traffic.
  • Set idle and lifetime recycling with jitter; synchronized expiry can create a reconnection storm.
  • Monitor active, idle, and waiting clients at the application, pooler, and Postgres layers.
  • Keep transactions short. Pooling cannot compensate for sessions left idle in transaction.

For pgx behind transaction pooling, do not rely on its session-level statement cache unless PgBouncer tracks protocol-level prepared statements. A conservative setting is default_query_exec_mode=exec; test both layers together. Session advisory locks, LISTEN, session variables, and cross-transaction state require session pooling or redesign.

Diagnose saturation

SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;

Also inspect checkout latency and PgBouncer waiters. A full pool with low database utilization suggests leaks, long transactions, or external work inside a transaction.

Common Mistakes

  • Opening a connection per request or retrying connection failures without backoff and jitter.
  • Multiplying a “safe” per-process maximum across autoscaled replicas.
  • Increasing pool size because callers wait, without checking database saturation.
  • Holding a transaction open during HTTP/RPC work.
  • Enabling transaction pooling while depending on session state.

What ships with it: 1 file

3.4 KB alongside SKILL.md

Gives 0 of the 12 instructions most databases sql skills give

Counted across 609 of the 712 authors here whose files we hold, read 2026-09-06

  • Index all foreign key columnsin 26 of 609
  • Use cursor pagination instead of offsetin 25 of 609, across 20 files
  • Use timestamptz for timestampsin 21 of 609
  • Specify columns instead of using select starin 20 of 609, across 10 files
  • Use parameterized queries for all database interactionsin 20 of 609, across 19 files
  • Use Enum for categorical datain 17 of 609, across 7 files
  • Order by frequently filtered columnsin 17 of 609, across 7 files
  • Batch data insertsin 17 of 609, across 7 files
  • Use expand-contract pattern for schema changesin 17 of 609
  • Use materialized views for real-time aggregationsin 16 of 609, across 6 files
  • Partition tables by timein 16 of 609, across 6 files
  • Use smallest appropriate data typesin 16 of 609, across 6 files

Said here and by no other author read

  • bound total connection pools
  • reserve capacity for administration
  • use PgBouncer for shared database access
  • calculate autoscaling ceiling explicitly
  • keep application connections long-lived
  • set idle and lifetime recycling with jitter

Grouped from the skills themselves: near-identical wordings counted once, and counted by distinct author, so one author publishing three of these counts once.

Keep looking

Skills are one crate of 613,373. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.