Managed Supabase project generating ~125k rollbacks/min from PostgREST with almost no application traffic

[uyung99]

I am seeing extremely high transaction/rollback churn on a managed Supabase project even when application traffic is negligible.

Environment

• Managed Supabase Cloud project
• PostgreSQL 17
• PostgREST 14.5
• Project status remains ACTIVE_HEALTHY
• Normal application usage is currently very low

What I observed

During a bounded 43.304-second read-only observation window:

• PostgREST request-context initialization increased by 70,708 calls
• estimated PostgREST transaction-init rate: ~97,974/min
• transaction commit rate: ~52.7/min
• transaction rollback rate: ~125,124/min

The dominant statement pattern was PostgREST request-context initialization using set_config(...), including request role/context fields.

The observed connections were associated with:

• PostgREST 14.5
• authenticator
• anon

The activity appears to occur before an identifiable application business RPC/query is executed.

Application traffic comparison

Our actual business/RPC calls are only in the tens of cumulative calls and are orders of magnitude smaller than the PostgREST initialization activity.

The application does have normal polling:

• operator participants/feedback: every 15 seconds
• authenticated learner presence: every 20 seconds
• HITL status: every 15 seconds

However, during a separate five-minute application/Sites observation window, visible application requests were zero while the database churn continued.

No application retry loop was found.

Additional observation

The churn appears to be bursty/intermittent, not continuously flat.

A later 10-second sample showed zero new commits/rollbacks, but over approximately 64 minutes the cumulative rollback counter still increased by about 7.8 million.

So short quiet samples can occur even while the longer-period transaction counter grows extremely quickly.

What I have NOT changed

I have intentionally not:

• upgraded compute
• changed database schema
• added indexes
• killed database sessions
• reset statistics
• changed application polling
• modified application runtime behavior

I am trying to identify the source before applying any workaround.

Question

Has anyone seen a managed Supabase/PostgREST instance generate this kind of request-context initialization / rollback churn with almost no corresponding application traffic?

In particular, I would like to understand:

1. what can cause PostgREST to repeatedly initialize request context and then roll back before a business query/RPC is reached;
2. whether this can originate from the managed PostgREST service itself;
3. what additional safe diagnostics would best distinguish a platform/runtime issue from an application-triggered request source.

Supabase support ticket already opened: SU-474601.

No credentials, API keys, tokens, or private project identifiers are included here.

[somuai]

This high rollback rate (~125k/min) accompanied by PostgREST request-context initialization (set_config) and virtually no application traffic is a classic signature of PostgREST handling a flood of read requests or 4xx error responses (typically HTTP 404 probes from external scanners or aggressive health checks).

Here is an explanation of the internal mechanics, why this happens, and the exact queries to isolate the traffic source.

---

1. Why PostgREST Initializes Context and Immediately Rolls Back

In PostgREST's request lifecycle:

1. Every incoming HTTP request opens a PostgreSQL transaction (BEGIN).
2. PostgREST executes transaction-local context settings:

   SELECT set_config('role', 'anon', true),
          set_config('request.jwt.claims', '...', true),
          set_config('request.headers', '...', true);

3. PostgREST then matches the requested URL path and method against its in-memory schema cache.
4. Crucial architectural behavior: By design, PostgREST terminates transactions with ROLLBACK for:
   • All GET and HEAD requests (to prevent transaction ID / XID consumption in Postgres).
   • All client error responses (400 Bad Request, 401 Unauthorized, 404 Not Found / PGRST200, 405 Method Not Allowed).
5. If an HTTP request arrives for a path that does not exist in the exposed schema (e.g. external vulnerability bots scanning for /.env, /wp-login.php, or random endpoints, or an uptime monitor hitting an unmapped /health endpoint), PostgREST executes the set_config(...) block, immediately determines that the resource does not exist, returns HTTP 404, and terminates the transaction with ROLLBACK.
6. In PostgreSQL statistics (pg_stat_database), this directly increments xact_rollback while leaving xact_commit unchanged.

---

2. Can This Originate from the Managed Service Itself?

While possible (e.g. an internal service health check or a stuck connection pooler probe), in the vast majority of hosted Supabase cases this churn is external ingress traffic.

Because every Supabase project has a public API URL (https://<project-ref>.supabase.co/rest/v1/) and a public anon key, automated internet crawlers and botnets frequently sweep project endpoints with high-concurrency scans. Even though these requests fail immediately with 404, each request still completes an entire BEGIN -> set_config -> ROLLBACK cycle inside Postgres.

---

3. Safe Diagnostics to Identify the Exact Source

You can identify the exact IP, User-Agent, and requested URLs without modifying database state or restarting services:

A. Query Edge Logs in the Supabase Dashboard

Go to Dashboard -> Logs -> API Logs or Log Explorer and run:

select
  timestamp,
  metadata.request.method as method,
  metadata.request.path as path,
  metadata.response.status_code as status,
  metadata.request.headers.cf_connecting_ip as client_ip,
  metadata.request.headers.user_agent as user_agent,
  count(*) as request_count
from edge_logs
where timestamp > now() - interval '1 hour'
group by 1, 2, 3, 4, 5, 6
order by request_count desc
limit 50;

Look for:

• A high volume of 404 or 400 status codes.
• Specific scanner paths or repetitive User-Agent strings (e.g., bot scanners, uptime monitors, or external scripts).

B. Check Active Database Sessions

Run this in the SQL Editor to inspect live PostgREST transactions as they execute:

select
  pid,
  usename,
  client_addr,
  application_name,
  state,
  query,
  wait_event_type,
  wait_event,
  now() - state_change as duration
from pg_stat_activity
where usename in ('authenticator', 'anon')
   or application_name like '%postgrest%'
order by state_change asc;

---

4. Resolution

• If external bot/crawler traffic: Configure Cloudflare or your WAF in front of the project with rate limiting or IP blocking rules.
• If an external uptime monitor / health check loop: Ensure the probe target points to a valid exposed endpoint with appropriate headers rather than hitting a non-existent path.
• If Postgres resources remain healthy: Because these read/rollback transactions consume minimal CPU and zero transaction IDs, they do not cause transaction ID wraparound or vacuum degradation.

If this analysis helps clarify the rollback spikes and diagnostic path, please feel free to mark this as the accepted answer.

[uyung99]

We ran a synchronized read-only observation window from 2026-09-16 05:33:20.531865 UTC to 05:34:38.350308 UTC (77.82 seconds).
During that exact window:

• xact_rollback increased by 147,075 (~113.4k/min)
• xact_commit increased by 73 (~56/min)
• API Gateway / edge_logs requests: 0
• EXPECTED_VERSION_CONFLICT current=11 expected=10: 7,782
• total postgres_logs: 7,783
  The version-conflict loop is real and explains at most about 5.3% of the observed rollback count if treated as one rollback per logged conflict.
  That leaves approximately 139k rollbacks in the same 77.82-second window (~107k/min) that are not represented by API Gateway ingress and are not represented by one-per-transaction Postgres error logs.
  This substantially weakens the external scanner / HTTP flood explanation for this project. Could you help identify what managed PostgREST / internal service path could generate this volume of rollbacks without corresponding edge_logs entries?

[somuai]

That 77.8-second synchronized observation window is extremely revealing. The fact that edge_logs recorded 0 requests while xact_rollback increased by 147,075 (~1,900 rollbacks/sec) and postgres_logs captured 7,782 occurrences of EXPECTED_VERSION_CONFLICT current=11 expected=10 completely shifts the diagnosis.

Here is the exact architectural breakdown of why this bypasses edge_logs, how the ~100/sec logged conflict correlates with the ~1,900/sec rollback rate, and how to pinpoint the source process immediately.

---

1. Why edge_logs = 0 Rules Out the API Gateway (Kong)

On hosted Supabase:

• edge_logs captures HTTP traffic passing through the Kong API Gateway (https://<project-ref>.supabase.co/rest/v1/..., /auth/v1/..., /storage/v1/...).
• Wire-protocol PostgreSQL connections (libpq) connecting directly to db.<project-ref>.supabase.co on port 5432 (direct/session mode) or port 6543 (Supavisor transaction pooler) completely bypass Kong. They produce zero entries in edge_logs.
• Similarly, internal platform daemons (such as Realtime or internal replication workers) communicating with PostgreSQL over the host/VPC network do not traverse Kong.

This confirms the traffic is arriving over direct Postgres/pooler connections, not the public HTTP REST gateway.

---

2. The EXPECTED_VERSION_CONFLICT Smoking Gun

EXPECTED_VERSION_CONFLICT is not a native PostgreSQL kernel error code (Postgres uses SQLSTATE codes like 40001 or 23505). It is an application-level Optimistic Concurrency Control (OCC) error string emitted when an UPDATE or RPC checks an expected version number and finds it stale.

7,782 logged errors in 77.82 seconds is precisely ~100 error logs per second.

Why does this show 147,075 rollbacks (~1,900/sec) if only 7,782 errors were logged?

1. Unlogged rollbacks / Tight retry loops: In optimistic concurrency patterns, clients or stored procedures frequently attempt an update, encounter rows_affected = 0 (or a conflict), issue a ROLLBACK, and immediately retry in a tight loop. Only a fraction (e.g. final attempt or when an explicit RAISE EXCEPTION is triggered) writes to postgres_logs, while every single aborted attempt increments PostgreSQL's internal xact_rollback counter.
2. Subtransactions (SAVEPOINT): If a client or ORM wraps operations in nested transactions or exception blocks (BEGIN ... EXCEPTION WHEN ...), rolled-back savepoints in some drivers/poolers increment rollback statistics.
3. Log throttling: Under extreme volume (>1,000 events/sec), PostgreSQL and host log forwarders apply log suppression/sampling to prevent disk exhaustion.

---

3. Immediate Diagnostic SQL Queries

Run these read-only diagnostic queries in the SQL Editor to pinpoint the exact client, IP, function, and table involved:

A. Identify the Client IP, Process Name, and State

Check live backend connections to see who is connected via direct/pooler connections:

select
  pid,
  usename,
  application_name,
  client_addr,
  client_port,
  backend_start,
  state,
  query,
  now() - state_change as duration
from pg_stat_activity
where backend_type = 'client backend'
  and state != 'idle'
order by duration desc;

Look at client_addr (will reveal whether the connection originates from an external server IP, a serverless provider, or internal Supavisor 127.0.0.1) and application_name.

B. Find the Stored Procedure or Trigger Emitting the Conflict

Search catalog definitions for where EXPECTED_VERSION_CONFLICT is defined:

select
  n.nspname as schema_name,
  p.proname as function_name,
  pg_get_userbyid(p.proowner) as owner,
  p.prosrc as source_code
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosrc ilike '%EXPECTED_VERSION_CONFLICT%';

C. Inspect pg_stat_statements for the Dominant Query Pattern

Identify the exact SQL statement executing millions of calls:

select
  query,
  calls,
  rows,
  round(total_exec_time::numeric, 2) as total_exec_ms,
  round(mean_exec_time::numeric, 2) as mean_exec_ms
from pg_stat_statements
order by calls desc
limit 10;

D. Identify Which Table Is Taking the UPDATE/Conflict Churn

select
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_del
from pg_stat_user_tables
order by n_tup_upd desc
limit 10;

---

4. Summary & Next Step

The source is an application worker, background job, or client script connecting directly via port 5432/6543 that is stuck in a rapid-fire optimistic concurrency retry loop on a specific record (version 11 vs expected 10). Query B and C above will identify the exact function and table in seconds.

[uyung99]

Hi Supabase Support,

I have a new synchronized read-only observation that further narrows the issue.

The rollback storm is still active.

In a synchronized ~31.9-second window:

xact_rollback increased by 65,703
xact_commit increased by 20
the dominant PostgREST request-context set_config(...) statement increased by 65,496 calls

This is approximately:

123.6k rollbacks/min
123.2k PostgREST context initializations/min

So in this window, PostgREST request-context initialization tracked the rollback increase at roughly 99.7%.

Live pg_stat_activity during the storm showed local:

usename = authenticator
application_name = PostgREST 14.5
client_addr = ::1

and the active RPC was repeatedly:

public.synthetic_authority_append(...)

with the paired backend also observed in ABORT / idle-in-transaction cycles.

This materially narrows the problem:

the dominant churn appears to be a PostgREST request loop around synthetic_authority_append, not normal application business traffic.

One important nuance: client_addr = ::1 only proves the PostgREST-to-Postgres connection is local. It does not identify the upstream caller that is continuously feeding the RPC into PostgREST.

Earlier synchronized Logs Explorer windows showed 0 Edge/API Gateway requests while the rollback storm was active, so the remaining question is:

What upstream process/client can continuously invoke this RPC through managed PostgREST while normal Edge/API ingress logs show zero requests?

I have intentionally not killed sessions, revoked the function, reset stats, or changed schema/configuration yet, in order to preserve evidence.

Historical reference: SU-474601
Current ticket: SU-475687

Thank you.

[uyung99]

Hi Supabase Support,

We have now completed a read-only caller-identification pass and a reversible causal isolation test.

The most important new evidence is:

public.synthetic_authority_append(...) was created as a synthetic RPC harness on 2026-09-07 around 11:42 UTC.
pg_net was enabled shortly afterward for a concurrency proof.
Exactly one PostgreSQL statement issued two net.http_post() requests to that RPC.
Both requests timed out after approximately 5 seconds.
pg_net has only three request IDs in total: two POSTs and one later GET.
The pg_net request queue is empty.
Therefore pg_net was not continuously generating ~100k–126k requests/min.
Nevertheless, PostgREST 14.5 continued producing approximately 100k–126k request-context / rollback transaction cycles per minute for about nine days.
Live database activity showed authenticator / PostgREST 14.5 / ::1 executing the synthetic RPC path.
Closing local browser, Codex, Base44, Sites and local app surfaces did not reduce the storm.
No recurring pg_cron job, DB trigger recursion, GitHub CI/deployment caller, Base44 caller, or visible Sites caller was found.
When EXECUTE on public.synthetic_authority_append was revoked from anon, authenticated, and service_role, both rollback rate and PostgREST request-context rate immediately dropped to 0/min.

This establishes that PostgREST-callable exposure of this RPC was causally necessary for the storm, but it does not identify the internal mechanism that sustained the requests.

The strongest remaining hypothesis is a stuck/replayed/pathological managed PostgREST request lifecycle triggered after the original timed-out server-side HTTP requests, although we cannot prove that mechanism from database-level evidence alone.

Could you please investigate whether PostgREST 14.5 or the managed Supabase request stack can retain/replay/re-enter an RPC request after the originating HTTP client times out or disconnects?

We are intentionally keeping this synthetic RPC disabled and will not re-enable it merely for diagnosis.

Ticket: SU-475687
Historical reference: SU-474601

[somuai]

Your causal isolation test—confirming that revoking EXECUTE on public.synthetic_authority_append instantly collapsed both the rollback rate and the PostgREST set_config rate from ~125,000/min to 0/min—provides the definitive proof.

Here is the exact internal mechanism in PostgREST that explains how two timed-out pg_net calls sustained a 125k/min rollback storm for 9 days, why edge_logs recorded 0, and why revoking EXECUTE killed the loop immediately.

---

1. The PostgREST Internal Transaction Retry Loop

PostgREST is written in Haskell and uses the hasql / hasql-transaction library on top of the Warp HTTP server:

1. Automatic Transaction Retries on Serialization / Concurrency Errors:
   In PostgREST 14.x, PostgREST contains internal transaction-retry logic designed for concurrency. When an RPC or transaction encounters a serialization failure (SQLSTATE 40001) or concurrency conflict, PostgREST’s transaction runner does not simply return an error to the client immediately—it automatically retries the transaction internally in an unthrottled loop.
2. Disconnected Client / Timeout Disconnect Trap:
   When pg_net sent the two net.http_post() requests and timed out after 5 seconds, the client connection closed. However, inside PostgREST:
   • The Haskell worker thread was already running the transaction retry loop.
   • If the RPC logic repeatedly raised an exception or concurrency error (such as your EXPECTED_VERSION_CONFLICT at version 11 vs 10), the loop condition never satisfied.
   • Because client socket disconnection checks were not terminating the active in-memory retry loop in that code path, PostgREST continued to execute BEGIN -> set_config(...) -> EXECUTE RPC -> ROLLBACK as fast as the CPU and local socket (::1) could cycle—clocking ~2,000 executions per second (~120,000/min).
3. Known PostgREST Upstream Issue:
   This infinite retry behavior on concurrency errors was an active issue in PostgREST where transactions could spin indefinitely on replicas or conflicting states. In PostgREST issue tracking and changelogs, this is documented under unbounded retries on 40001 / transaction conflict conditions.

---

2. Why edge_logs Was 0

• When pg_net initially executed net.http_post(), it communicated either directly with PostgREST on http://localhost:3000 (or http://127.0.0.1:3000 / ::1) or via internal network routing.
• Once the initial request reached PostgREST, no new HTTP requests were ever sent across the wire. The ~125,000 executions/min were not 125,000 incoming HTTP requests; they were internal transaction retry iterations executed by the single stuck PostgREST worker process directly against Postgres via ::1.
• Because no HTTP requests were arriving at the Kong API Gateway, edge_logs accurately recorded 0.

---

3. Why Revoking EXECUTE Instantly Terminated the Storm

When you ran REVOKE EXECUTE ON FUNCTION public.synthetic_authority_append FROM anon, authenticated, service_role:

1. Postgres invalidated permissions in the catalog.
2. On the next millisecond iteration of the retry loop, PostgREST attempted to execute the RPC under the authenticator / anon role and received:
   42501: permission denied for function synthetic_authority_append
3. A permission error (42501) is a fatal, non-retryable error (unlike concurrency conflicts). PostgREST immediately broke out of the internal retry loop, terminated the worker thread, and released the connection.
4. With the worker loop broken, both the set_config context initializations and the transaction rollbacks dropped instantly to zero.

---

4. Recommendation for Supabase Support (Ticket SU-475687)

You can share this exact diagnosis with the Supabase engineering team handling ticket SU-475687:

• Core issue: Unbounded internal transaction retry loop in PostgREST 14.5 (hasql-transaction runner) when an RPC encounters repeated concurrency conflicts after client timeout/disconnection.
• Trigger: Two pg_net POST requests to a concurrency-checking RPC that timed out after 5s.
• Evidence: 125k/min rollback storm with 0 edge logs, local connection client_addr = ::1, and immediate 100% cessation upon revoking EXECUTE (triggering non-retryable 42501).
• Resolution on platform: Ensure PostgREST is updated to the version containing bounded retry limits on transaction failures, or configure client disconnect cancellation (statement_timeout / cancellation on socket close).

[uyung99]

We have now found official Supabase and upstream PostgREST documentation matching our incident exactly.

Supabase’s troubleshooting article states that custom SQLSTATE 40001 in an RPC can cause infinite retries in PostgREST 14, and that the bug is fixed in PostgREST 16.

Our affected project was observed running PostgREST 14.5.

This matches our incident:

one synthetic RPC request path
repeated 40001
~125k rollback/request-context cycles per minute
no corresponding new edge requests
immediate cessation when RPC execution was revoked.

We are keeping the synthetic RPC disabled.

Please confirm:

whether this managed project can be moved from PostgREST 14.5 to the fixed PostgREST 16 version, and
whether Supabase recommends replacing our application-level expected-version conflict code from 40001 to PT409 or another non-retryable code before the RPC is ever re-exposed.

Ticket: SU-475687.

[somuai]

Glad to hear the official Supabase and upstream PostgREST documentation confirmed the exact 40001 infinite retry behavior.

Here are the direct answers to your two questions:

---

1. Moving the Managed Project from PostgREST 14.5 to PostgREST 16

On hosted Supabase, the PostgREST version is bundled with the platform image / PostgreSQL release:

• How to upgrade: In the Supabase Dashboard, navigate to Project Settings → Infrastructure. If an infrastructure/database upgrade is available, applying it will deploy the updated platform image containing the newer PostgREST version (which bounds transaction retries).
• Support-assisted upgrade: Because you have active ticket SU-475687, ask the support engineer to schedule an image upgrade / platform maintenance for your project ref to roll forward to the release containing PostgREST 16.

---

2. Replacing the Expected-Version Conflict Code (40001 → PT409 or P0001)

Yes, strongly recommended.

In standard PostgreSQL:

• 40001 (serialization_failure) is strictly meant for engine-level transactional serialization anomalies at SERIALIZABLE or REPEATABLE READ isolation levels. Middleware (PostgREST, hasql, and many ORM connection poolers) treats 40001 as a transient, automatically retryable engine condition.
• Using 40001 for business-logic optimistic concurrency control (OCC) is what deceived the PostgREST retry runner into treating the version conflict as a transient retryable failure.

Recommended Replacement:

1. Map to HTTP 409 Conflict:
   PostgREST supports custom error mapping via the PT prefix. Raising PT409 in your PL/pgSQL function:

   RAISE EXCEPTION 'EXPECTED_VERSION_CONFLICT current=% expected=%', current_v, expected_v
     USING ERRCODE = 'PT409';

   PostgREST will return HTTP 409 Conflict directly to the HTTP client, treats the transaction as aborted without retrying, and cleanly terminates.
2. Alternative Standard Exception:
   Use standard P0001 (raise_exception) or 23514 (check_violation). Both are non-retryable and return HTTP 400 with the error message in the JSON payload.

If these clarifications fully resolve the investigation for your project, please feel free to mark this as the accepted answer so it can assist other Supabase users encountering the same issue.

[Concepto505]

Before adding another hypothesis, two things about the measurement, because I think the numbers as stated are doing some of the confusing.

1. set_config calls are not transactions, and the ratio between them is not 1.

PostgREST sets several request-context settings per request. If the ~97,974/min "transaction-init rate" was derived from pg_stat_statements.calls on the set_config statement, it is inflated by however many settings PostgREST writes per request on 14.5 — the real request rate could be several times lower. Worth pinning down before sizing the problem:

select calls, query
from pg_stat_statements
where query ilike '%set_config%'
order by calls desc limit 5;

If one statement carries all the settings, calls ≈ requests. If there are several rows moving in lockstep, divide.

The transaction count itself is unambiguous, though — take deltas of xact_commit + xact_rollback from pg_stat_database, which is what you did, and trust that over any statement-derived estimate.

2. The commit:rollback ratio is the actual anomaly, and it rules a lot out.

Per the PostgREST transaction docs, a request ends in COMMIT unless the transaction fails or db-tx-end is configured to roll back. You are seeing roughly 125,000 rollbacks against 52.7 commits. That is not a busy API. That is essentially every transaction failing, which means one of exactly three things:

• something is raising an error on nearly every request, and it will be in the Postgres logs;
• db-tx-end is set to rollback (unlikely on managed Supabase, but it is a one-line check and it would explain the ratio perfectly and harmlessly);
• the rollbacks are not finished PostgREST requests at all.

That third one is the case people skip, and it is the one that fits "no application traffic" best: a backend that disconnects with a transaction open increments xact_rollback without ever logging an error. Connection churn — pool recycling, a supervisor restarting workers, health probes that open and drop — manufactures exactly this signature: high rollbacks, near-zero commits, request-context statements at the top of pg_stat_statements, and a completely silent error log.

The bisection that settles it, and it is two queries plus one log view:

Postgres error log	API gateway log	Conclusion
errors at ~the rollback rate	requests present	real failing requests — read the error
errors at ~the rollback rate	no requests	something calling the DB directly as authenticator, not through the gateway
quiet	no requests	connection churn, not requests. Look at connection lifetime, not queries
quiet	requests present	db-tx-end, or requests ending without commit

For the third row specifically:

select count(*), state, application_name,
       min(now() - backend_start) as youngest,
       max(now() - backend_start) as oldest
from pg_stat_activity
where usename in ('authenticator','anon')
group by state, application_name;

Sample that a few times a second during a burst. If youngest keeps resetting to near zero, connections are being created and destroyed rather than reused, and the rollbacks are a symptom of the churn rather than of anything your application did.

One note on the external-scanner theory above: I would want evidence before accepting it. PostgREST resolves an unknown path against its in-memory schema cache and answers 404 without opening a database transaction, so path-scanning from outside should not move xact_commit/xact_rollback at all. A scanner hitting a real table with a malformed filter would — but that produces log errors, which puts you in row one of the table above, not in the silent case you are describing. Worth checking rather than assuming, in either direction.