PostgREST stops serving while Postgres stays healthy — one idle authenticator connection precedes each outage (4x in 23 days)

[marcioror]

What we're seeing

Four times in 23 days, our production app went down because the Data API (PostgREST) stopped serving requests while Postgres itself stayed perfectly healthy. Only a Restart project brings it back.

Longest outage: 10 hours 4 minutes. It started at 01:40 UTC — the middle of the night locally — which is how it got that long.

We're on Free, region sa-east-1, Postgres 17.6. We have an external monitor probing four layers separately every 5 minutes, so the timeline below is measured, not reconstructed.

The part we think matters: one stale authenticator connection

We've now caught this twice, and it looks like a signature.

During an outage, connecting directly to Postgres (which worked fine throughout):

usename        | application_name | state | count | age of state_change
authenticator  | PostgREST        | idle  |   1   | 3 days 21:26:29

One authenticator connection, idle for almost four days — instead of a healthy pool. And it dated to roughly 16 minutes after a previous short outage had cleared.

The first time we checked, the same query showed one authenticator connection idle for 7 days 23 hours, dating to the restart that ended the outage before it.

So the pattern seems to be: PostgREST recovers → is left holding a single idle connection instead of a pool → days later it stops serving entirely.

After a fresh restart, the same query shows three connections, ages 10–40s. That looks healthy.

A cheap way to tell this apart from "Supabase is down"

This was the most useful thing we found, and it's fast to check on any project:

Request	Result during the outage
GET /rest/v1/ without apikey	401 in 0.05s
GET /auth/v1/health	401 in 0.07s
GET /storage/v1/version	200 in 0.06s
GET /rest/v1/<any table> with a valid key	hangs, 0 bytes after 25s

TLS handshake completed in 40ms in every case.

So the edge and Kong are healthy and fast — unauthenticated requests get rejected before reaching PostgREST, which is why they answer instantly. Only requests that actually route into PostgREST hang.

If you're debugging something similar: an instant 401 on /rest/v1/ does not mean your Data API is fine. We learned that the hard way — an earlier outage went unnoticed for three days because we were probing an endpoint that never touches the database.

What we ruled out

• Database load — 14 of 60 connections in use, 0 idle in transaction, 0 waiting on locks, select 1 instant.
• Our code — no deploy for 13 days before the last outage; the running image was byte-identical before, during and after.
• Our infrastructure — all containers up and healthy for 12 days straight, zero restarts, 2.4 GB RAM free, 35% disk.
• Our network — TLS handshakes in 40ms during the outage; unrelated HTTPS endpoints answered in under 200ms.
• PostgREST vs direct connection — our background worker uses a direct Postgres connection and logged zero errors across the entire 10-hour outage. Only Data API traffic failed.

What the failure looks like from the app

This is worth flagging because it sends you chasing the wrong thing. Our app's logs filled with:

Timed out acquiring connection from connection pool.

…roughly 457 times an hour, while every container reported healthy and the database was idle. It reads like a client-side pool bug. It isn't — the pool drains as a consequence. We lost hours on that before checking the Data API separately.

After the restart, the errors briefly change to Could not query the database for the schema cache. Retrying. and then stop on their own. No container restart needed.

Questions

1. Has anyone else seen PostgREST come back from a restart holding a single idle authenticator connection instead of a pool? Is that a known failure mode, or are we misreading pg_stat_activity?
2. Is there anything configurable — on PostgREST or on the Postgres side — that would make it recover on its own instead of needing a manual project restart?
3. Is there any way to be alerted by Supabase when the Data API stops serving? Project health in the dashboard showed green throughout.
4. Does anyone have a better early-warning signal than what we're using? We'd rather detect the stale-connection state days ahead than detect the outage.

What we did in the meantime

Since the last outage began at 01:40 local night and nobody was awake to press the button, we automated it: our monitor now calls

POST https://api.supabase.com/v1/projects/{ref}/restart

when — and only when — a direct database connection succeeds and an authenticated query to a real table fails. It's guarded with a 1-hour cooldown, a cap of 2 attempts per outage and 3 per day, and it escalates by email instead of retrying forever if restarting doesn't help.

It works, and it turns a 10-hour outage into about 7 minutes. But we'd much rather not be restarting our own database on a timer, so we're posting this in case the underlying issue is fixable — or in case someone recognises the pattern.

We also filed a support ticket for this — SU-466952 — but we're on the Free plan, so there's no guaranteed response, and we'd rather the findings be public anyway in case they help someone else. If anyone from the team wants to correlate this with the actual project logs, that ticket has the full details.

Happy to provide more diagnostics.

[wonjangcloud9]

Excellent write-up. I think the mechanism is explainable from PostgREST's documented behaviour — but I also think the signal you've identified as the precursor is a false one, which matters most for Q4.

Q1 — the single idle authenticator connection is normal

That connection is almost certainly not a pool connection at all. It's the LISTEN connection.

db-channel-enabled defaults to true and db-channel defaults to pgrst. PostgREST holds that listener open for the lifetime of the process — it has to, since LISTEN is session state. That's exactly why the docs tell you to disable it when you're behind a transaction-mode pooler, which would break it.

Now put that next to db-pool-max-idletime, which defaults to 30 seconds. On an idle project, pool connections are released half a minute after the last request. So the resting state of a healthy, quiet PostgREST is exactly what you measured: one long-lived authenticator connection, aged however long since the process started, and no pool.

Which also explains your "healthy" reading — three connections aged 10–40s right after a restart is just the pool warming up from your own probe traffic. Wait 30 seconds without traffic and it will drop back to one.

So: not a precursor, and you aren't misreading pg_stat_activity — you're reading it correctly and the number is just benign. The age matching "16 minutes after the previous outage cleared" is the process start time from that recovery, not a connection that went stale.

The hang is the schema cache, and your own logs name it

The detail that ties it together is in the schema cache docs:

requests will wait until the schema cache reload is done

That is the behaviour you measured. Requests that reach PostgREST hang rather than erroring, and hang well past db-pool-acquisition-timeout (default 10s) — which rules out pool exhaustion, since that path returns 504 instead of blocking. Meanwhile unauthenticated /rest/v1/ is rejected at Kong before it ever reaches PostgREST, so it stays fast. Your table matches this exactly, line for line.

And the log line you see on the way out is the same subsystem:

Could not query the database for the schema cache. Retrying.

So the shape is: PostgREST loses its schema cache, fails to rebuild it, and parks in the retry loop — with every incoming request blocking behind the reload rather than failing fast. It clears on restart because the process rebuilds the cache from scratch.

What I can't tell you from the outside is why the rebuild fails for hours, given your database was demonstrably fine. That part genuinely needs someone with the project logs.

Q2 — recovery without restarting the project

Try NOTIFY before the restart API. From a direct connection — which you've proven keeps working throughout, since your background worker never logged an error:

NOTIFY pgrst, 'reload schema';

That's the documented way to force the reload, and it's enormously cheaper than a project restart. Worth wiring into your monitor as the first escalation step, with the restart as the fallback if a probe still fails 30–60s later.

The honest caveat: this only helps if the listener connection is still alive and the reload can succeed. If the listener itself is gone, the NOTIFY lands in an empty channel and nothing happens — silently. So keep the restart path, just demote it to step two.

Q3 — alerting

Project health in the dashboard doesn't probe the Data API as a client, which is why it stayed green. There's nothing on the Supabase side that alerts specifically on "PostgREST stopped serving", so an external probe like yours is the answer. The important part is the one you already found the hard way: the probe has to be an authenticated request against a real table, because anything that Kong can answer alone tells you nothing.

Q4 — a better early-warning signal

Given Q1, the connection count won't give you lead time — it looks identical before and during. I'd stop watching it; it will produce false alarms on every quiet night.

What does carry signal:

• Probe latency, not just success. A schema cache reload makes requests wait. A probe that normally returns in 60ms starting to take seconds while the database is idle is the same failure in its early stage.
• Correlate with DDL. Reloads are triggered by schema changes, so if your outages follow migrations, extension changes, or anything else that fires the reload, that narrows the cause considerably. Four events in 23 days is few enough to check each against your migration history.
• Watch the Postgres logs for the retry line itself rather than inferring from connections — it's the first-party symptom, and it appears before your users notice.

One last thing worth adding to the report: db-pool defaults to 10, and you measured 14 of 60 connections in use with the pool drained — so connection limits aren't in play here either. That rules out one more thing the thread will otherwise suggest.

[marcioror]

You were right on Q1, and I can now show it from the other direction: a long-idle authenticator connection coexists with a completely healthy API.

Measured with the Data API demonstrably working (a table read returning 200 in 0.098s, seconds earlier):

usename        | state | age of backend_start | age of state_change
authenticator  | idle  | 2 days 03:30:57      | 2 days 03:30:56
authenticator  | idle  | 00:01:26             | 00:01:26      ← my own probe traffic
authenticator  | idle  | 00:01:26             | 00:01:26
authenticator  | idle  | 00:01:26             | 00:01:26
authenticator  | idle  | 00:00:27             | 00:00:27
...

One connection aged two days, plus a handful of seconds-old ones from the probes I had just fired — exactly the resting shape you described. So the "7 days 23 hours" figure in my original post was process uptime since the previous recovery, not a stale connection, and the "three connections aged 10–40s after a restart" was the pool warming from my own checks. I've stopped watching that number, and I'd encourage anyone who found this thread through the title to do the same.

I've also wired NOTIFY pgrst, 'reload schema' into the monitor as the first escalation step, with the project restart demoted to step two if a probe still fails afterwards. Thank you — that alone turns a ~1 minute outage into something much cheaper, when it works.

---

What the project logs show, now that I've actually looked

I hadn't realised the log explorer is reachable from the Management API (/v1/projects/<ref>/analytics/endpoints/logs.all?sql=...), so I'd never queried edge_logs or postgres_logs. Doing that changed the shape of the problem completely.

This is not four outages in 23 days. It is a continuous degradation that never stops, and the outages are just its tail.

Twenty consecutive hours, grouped by hour:

requests/hour	~485, essentially constant
504 per hour	between 1% and 9% — present in every single hour
mean origin_time	955–2814 ms, never below ~950
requests over 3s	25–143 per hour

For context on what is being served: the database is 35 MB, the largest table has 13.7k rows, most have fewer than 100, and several of the slow ones have zero. There are no end users — this is an idle installation whose only traffic is its own background jobs.

A single-row select should not average a second and a half.

Things I was able to rule out, with measurements rather than argument

I assumed for a while that this was self-inflicted — my own scheduler firing eleven cron endpoints at second :00 of every fifth minute. It turned out to be worth fixing but not the cause:

Concurrency is not the limit. Firing N identical trivial requests simultaneously, from an idle client:

concurrent	slowest
1	68 ms
4	94 ms
8	182 ms
16	271 ms

From inside the app container the same test measures 25–63 ms at 16 concurrent. origin_time for those requests was logged as 7–64 ms. The API handles sixteen simultaneous readers without breaking a sweat, minutes before and after the slow periods.

More load is not slower. One minute carrying 77 requests had a worst origin_time of 2,648 ms and zero 504s. A different minute carrying 29 requests peaked at 12,201 ms with a timeout. The heavier minute was four times faster.

It is not the database. The same queries over a direct Postgres connection measure 7–8 ms, consistently, including during the slow periods — which is also why our background worker (direct connection) has never once failed while the Data API was timing out.

It is not the client. Load average on the client host sits at 0.05–0.38 on 2 vCPU, including at the exact moments requests take ten seconds. The host is asleep.

It is not DDL of ours. No application code issues DDL at runtime, and pg_cron is not installed on this project.

What I did change, and what it did

I staggered my own cron burst (0–40s offsets instead of all at :00). The effect is real but partial:

• mean origin_time went from 1,017 ms → 320 ms
• quiet minutes now show 10–600 ms, some as low as 10 ms
• the 504 rate did not measurably change

So the burst was costing latency, and removing it exposed that the timeouts were never about the burst.

The question I'm left with

What makes origin_time reach 10–12 seconds for a single-row read on a 35 MB idle database, when sixteen simultaneous copies of that same read measure 64 ms a few minutes later, the database answers in 7 ms throughout, and the client is idle?

Your schema-cache explanation still fits better than anything else I can construct — requests waiting rather than erroring is exactly the shape, and it would explain why the slowness is bursty in a way that has nothing to do with load. What I can't see from outside is how often a reload is actually happening. Two questions, if anyone knows:

1. Is there any client-visible way to observe schema cache reload frequency? Nothing in postgres_logs names it for me at LOG level, and I'd rather measure than infer. If reloads are happening constantly, that alone would explain the whole distribution.
2. On Free, is there anything that would cause PostgREST to reload or re-establish periodically — instance recycling, idle handling, scale-to-zero behaviour — independent of DDL?

Happy to run any specific query or probe against this project and post the numbers. At this point I have the logging access and the monitoring in place to answer fairly precise questions, which I didn't when I opened the thread.

[wonjangcloud9]

Thanks for going back and measuring — and for saying the connection count was a dead end. That is the useful kind of correction, and it saves the next person the three days it cost you.

Two things, in order of what I would check first.

Your 504s probably have a name, and it is one query away

PostgREST returns 504 for exactly one internal condition: PGRST003 — "The request timed out waiting for a pool connection to be available." The timeout is db-pool-acquisition-timeout, which defaults to 10 seconds.

Your worst measured origin_time is 12,201 ms. That is a 10-second wait plus overhead, not a slow query.

The response body of a 504 carries the code, so capture one from your monitor and look. If it says PGRST003, the story is not "the Data API is slow" — it is "requests are queueing for a connection that PostgREST does not have free", and almost everything else you measured falls into place:

• Direct Postgres stays at 7–8 ms throughout, because it never touches PostgREST's pool. This is also why your background worker never failed during the outages.
• 16 concurrent trivial reads take 64 ms, because the pool happened to be free at that moment.
• The 29-request minute peaked at 12,201 ms while the 77-request minute peaked at 2,648 ms. Under a queueing model this is expected: what matters is whether something was holding a slot, not how many requests arrived. db-pool defaults to 10, so a handful of slow requests is enough to put everything behind them.

The measurement you have not made yet: group by path

Every number you have posted is aggregated across all traffic, including your eleven cron endpoints. "A single-row select should not average a second and a half" assumes the mean describes single-row selects — but the mean describes whatever your background jobs are doing, and you benchmarked the single-row select separately and got 68 ms.

Same log explorer query, one extra clause:

select path, count(*), avg(origin_time), max(origin_time),
       countif(status_code = 504) as timeouts
from edge_logs
group by path
order by avg(origin_time) desc

If a small number of paths carry the tail and the 504s, you have your answer, and it is one you can fix. If the slowness is uniform across paths including trivial ones, that kills the queueing theory and is worth knowing just as much.

Your Q1 — you were looking in the right tool, wrong table

PostgREST does not log to postgres_logs. It has its own source, postgrest_logs, which is documented as separate from both Postgres and the API gateway. The schema cache emits these at info level:

• Schema cache loaded N Relations, N Relationships, N Functions...
• Schema cache queried in N milliseconds

If postgrest_logs has no such lines, the service is running above info level and no query will surface them — at which point the counter is the fallback. PostgREST exposes pgrst_schema_cache_loads_total (with SUCCESS/FAIL labels) and pgrst_schema_cache_query_time_seconds on its admin server, which is not reachable on hosted Supabase. Supabase does publish its own Prometheus endpoint at https://<ref>.supabase.co/customer/v1/privileged/metrics (Basic auth, username service_role, password a sb_secret_... key). The docs describe it as Postgres series and do not mention pgrst_*, so I cannot promise it is there — but it is one curl, and if the counter is exposed, scraping it twice ten minutes apart answers your question exactly rather than by inference.

Your Q2 — and an argument against the obvious suspect

Free is Nano compute with shared, burstable CPU, and free projects pause only after seven days with no requests, which is not you. I do not know of any documented periodic PostgREST recycling on Free, and I would not assert one.

But it is worth noticing that your own data argues against CPU starvation, which is where that question usually leads. PostgREST and Postgres run on the same instance. If the instance were throttled, the direct connection would show it too — and yours sits at 7–8 ms through the slow periods. Whatever is happening is specific to the PostgREST path, not to the machine.

Where that leaves my earlier explanation

I want to separate two things, because I think I conflated them and you should not inherit that.

The schema cache story fits your original outages well: requests hanging rather than erroring is documented reload behaviour, and your own recovery log said Could not query the database for the schema cache. Retrying.

It does not explain a steady 1–9% 504 rate across twenty consecutive hours. A reload loop that persistent would be visible in ways it is not. So I would now guess you have two different problems that happen to share a symptom, and the 504 code will tell you which one you are looking at on any given request.

[marcioror]

Update: the degradation has a clock signature, and it is not my traffic.

First — thank you for citing the docs on every claim rather than asserting. It meant I could check each one against my own project instead of taking it on trust, and that is exactly how this got unstuck. Two of the things below contradict your last message, and one contradicts mine.

Sampling a trivial authenticated read (select=id&limit=1, service_role, 35 MB database, no end users) every 2–3 seconds and bucketing by second within the minute:

second of minute	mean
:00–:04	816 ms
:15–:19	125 ms
:40–:44	86 ms
:55–:59	72 ms

Monotonic decay, ~11x across the minute.

It is not my own traffic

My first assumption was my scheduler — five cron endpoints fire in the first 8 seconds of every minute. So I tested it instead of arguing it: stopped the scheduler for 6 minutes, which cut my application's Data API traffic from 70 requests to 7.

	:00–:09	:10–:59	ratio
scheduler running (n=820)	570 ms	117 ms	4.9x
scheduler running (n=79)	721 ms	97 ms	7.4x
scheduler stopped (n=113)	636 ms	90 ms	7.0x

Unchanged with my traffic removed. Overall mean barely moved: 182 ms → 177 ms.

It is not the schema cache

pgrst_schema_cache_loads_total stayed at 62 across the entire 7-minute correlation run, while 6 of those 7 minutes showed the spike. Separately, over the process lifetime the rate is ~1.1 reloads/hour (62 loads / 55.4 h of process uptime, measured against pg_stat_activity). At 6.35 s per reload that is ~7 s of blocking per hour — 0.2% of the time, which cannot produce a 3.5% 504 rate. Your instinct to separate the outages from the continuous degradation was right, and my own earlier explanation does not cover the continuous part.

It is not the database

pg_stat_statements, accumulating for 43 days:

• my product's queries: 0.01–0.52 ms mean, across hundreds of thousands of calls
• the most expensive statements in the entire database are PostgREST's own schema cache catalog queries — SELECT name FROM pg_timezone_names at 345.5 ms mean over 385 calls (133 s total), and the recursive base_types CTE at 88.7 ms over 387 calls

Postgres answers my query in 0.19 ms and PostgREST takes 816 ms to deliver it.

PGRST003 is ruled out

pgrst_db_pool_timeouts_total is 0. Not one, ever, on this process. So the 504s are not pool acquisition timeouts — they are the gateway giving up on the upstream. db_pool_max=10, available=4, waiting=2, steady throughout.

The metrics endpoint does exist on Free

You said you could not promise it — it is there, and it exposes pgrst_*. Two caveats for anyone following:

• It is genuinely live, not cached: the JWT cache counter moved 26,151 → 27,232 in 31 minutes.
• Its gauges refresh slowly. I sampled every 5 s and got unusable garbage (alternating 0% and 100% CPU) before I realised why.

One number I still cannot explain: pgrst_schema_cache_query_time_seconds = 6.35, while the catalog SQL it runs sums to well under a second of database time.

Where this leaves it

Something contends with PostgREST at the top of every minute on an otherwise idle Free project, and it is not the client, the schema cache, the connection pool, or the database. I have no visibility into what runs on the instance itself, so I cannot take it further from outside.

Two asks:

1. Can anyone else on Free reproduce this? One authenticated single-row read every 2 seconds for ten minutes, then group by second-within-minute. It takes one shell loop, and if the shape shows up on an unrelated project it is not something about mine.
2. If anyone from Supabase sees this — the question is whether anything scheduled on the instance (metrics collection, log shipping, health probes, backup coordination) lands on the minute boundary and competes with PostgREST on Nano compute.