Written by Audience: platform / data engineers wiring a Python service to a Snowflake Postgres instance.
On this page
This is the connection-layer chapter of our migration of an online transaction processing (OLTP) workload onto Snowflake Postgres. Pooling on Snowflake Postgres does not follow standard psycopg3 practice, so it gets a dedicated article.
Four decisions make up the layer, and the rest of this piece takes them in order: authenticate with a static service-account password rather than a minted token, hold one connection per thread rather than pool them, keep them alive with three mechanisms that all run on the calling thread, and leave Snowflake’s own PgBouncer switched off.
Each request thread caches one connection for the life of the process, and the worker thread has its own. Nothing sits in between, so no background thread owns a connection either.
Connection auth: a static password, not a token bridge
There are two separate auth problems in a Snowflake-hosted app, and conflating them costs you a week. User to app (which pages and endpoints a person may reach) stays with Snowflake role-based access control (RBAC) and SHOW GRANTS OF ROLE. App to Postgres is just a database connection, and it should be boring.
We use a static password for an application service account, injected at deploy time via GitHub Secrets.
Why not the token bridge. We deliberately did not use GENERATE_POSTGRES_ACCESS_TOKEN_FOR_USER(). Those tokens last 15 minutes and require a live Snowpark session to mint. Both properties fight the connection model below: a connection cached on a thread outlives any single request, so something has to notice the token expiring and re-authenticate mid-flight. That is a refresh loop, a clock-skew bug and a new failure mode, bought in exchange for credentials that were never the weak link. For a service account that only talks to its own database, a static password rotated on demand is simpler and more reliable:
ALTER POSTGRES INSTANCE <name> RESET ACCESS FOR 'application';
Token bridges earn their keep when a human’s identity has to reach the database. This one does not: the person is already authorised at the application edge.
One Snowflake Postgres connection per thread
The driver is psycopg3 (psycopg[binary,pool]). The [pool] extra is on the dependency only because we evaluated psycopg_pool early and did not ship it. The layout that shipped is simpler: one connection per thread, cached in threading.local(), lazily reconnected when it goes stale.
# Sketch of a per-thread connection manager
class PGConnectionManager:
_local = threading.local() # per-thread cache
_worker_connection: psycopg.Connection | None = None # dedicated worker conn
@classmethod
def get_connection(cls) -> psycopg.Connection:
conn = cls._ensure_healthy(getattr(cls._local, "conn", None), ...)
if conn is not None:
return conn
new_conn = cls._new_connection() # SELECT 1 + exp-backoff
cls._local.conn = new_conn
return new_conn
Why the connection count stays bounded. This works because of how the request path is shaped. The FastAPI endpoints are sync, so Starlette runs them in a threadpool. Every pool thread that ever serves a request keeps its own connection alive for the life of the process, and the threadpool is bounded, so the connection count is bounded with it. Workers run in their own thread against a separate dedicated connection.
The FastAPI lifespan handler does nothing on startup. Connections are created on first use, not at boot, so a Postgres blip during a deploy does not stop the container coming up. On shutdown it closes every cached connection:
@asynccontextmanager
async def lifespan(app: FastAPI):
yield
close_pg() # closes every thread-local + worker connection
Three details that keep the connections alive
A cached connection is only useful if it is still open. Three things inside _new_connection() and _ensure_healthy() carry that weight:
- Health-check before reuse. A cached connection may have been silently dropped, and an idle SSL timeout on the Snowflake side is a real thing rather than a theoretical one. Every
get_connection()runsSELECT 1on the cached connection; on failure it is closed and a fresh one opened. This is the single most important line in the module. Without it the first request after an idle period fails, which is exactly the request a human is watching. - Exponential-backoff reconnect (2 s, 4 s, 8 s) for transient network blips. Worth the thirty-odd lines: without it, one half-second Postgres hiccup ripples into every concurrent endpoint at once, because every thread retries in the same instant.
- Proactive recycling by age.
PG_CONNECTION_MAX_AGE_SECONDS(default 72 000, or 20 hours) caps how long a connection sticks around even when it looks healthy. This catches slow-drift problems that aSELECT 1still passes.
Note what is absent: no keepalive thread, no reaper, no background task. Recycling happens on the next checkout, on the thread that wants the connection. That is deliberate, and the next section explains why.
One case these three do not cover: a single long-running read. The managed-Postgres proxy silently drops connections that go quiet, and a server-side cursor streaming a large result set is quiet at the TCP level for as long as the application is busy processing. A nightly export doing an hour-long cursor read died at roughly the one-hour mark until we set keepalives on that connection: keepalives=1 keepalives_idle=30 keepalives_interval=10 keepalives_count=5. The request path never hits this, because no request holds a connection open that long, which is exactly why it took a while to find.
Why we skipped pooling, client-side and in Snowflake Postgres
Client-side. psycopg3’s ConnectionPool is a good default for most stacks, and this is not most stacks. The workload is fewer than 20 concurrent internal users, sync endpoints, one worker thread plus the request threadpool. The peak working set sits well under the instance’s connection ceiling, so the pool would be managing a scarcity that does not exist. Two populations of connection, one per request thread and one dedicated to the worker, is enough.
The deciding factor was not performance. psycopg_pool runs a background maintenance thread, and we wanted none: every reconnect and every recycle happens on the thread that asked for the connection. When something goes wrong there is one place it can have gone wrong, and no timer to reason about.
If your stack is async-first with hundreds of concurrent requests, invert this advice: use psycopg_pool.AsyncConnectionPool and wire it through the FastAPI lifespan. That is the standard pattern and we are not arguing against it.
Server-side. Snowflake Postgres separately offers a built-in PgBouncer in transaction mode, opt-in via CREATE EXTENSION snowflake_pooler. We do not enable it. Transaction pooling breaks anything relying on session state surviving across transactions, most notably server-side prepared statements. psycopg3 binds client-side by default so it is safe out of the box, but if you flip that, set prepare_threshold=0 before you turn the pooler on.
Two pooling layers, neither of them used. That is a defensible answer more often than the pooling literature suggests.
Local development, and a health check that does not crashloop
The same module drives local development. A LOCAL_DB=true environment variable flips the connection string to a Docker Postgres on a non-standard port with sslmode=disable. Same code path, same retry logic, no test-only shim, which means the reconnect behaviour is exercised on a laptop long before it matters in production.
We added /health/db to run a SELECT 1 against the connection manager, and then deliberately kept it out of the SPCS readiness probe. A brief Postgres blip should not crashloop the container; /health, a basic liveness check, is the probe. The database health endpoint is for humans and dashboards, not for the orchestrator.
FloreData leads the platform team at a market research company. The connection layer described here is running in production.
The Snowflake Postgres series: the business case · the migration write-up · cross-VPC networking · the psycopg3 connection layer · the migration script and cutover · mirroring back into Snowflake.
