RPC calls via POST are ~15-20x slower than via GET, with no corresponding cost visible in Postgres

[sascha-kaleidoscode]

Environment

• PostgREST image: public.ecr.aws/supabase/postgrest:v14.14
• Postgres: 17.6 (aarch64)
• Managed via Supabase CLI 2.109.1 (local dev stack), but reproduced against
  a standalone PostgREST container pointed at the same database with a
  minimal config, independent of Supabase's Kong/GoTrue layer.
• PGRST_DB_POOL at default (10).

Summary

Calling the same stable SQL function via POST /rpc/<fn> (JSON body) is
consistently ~15-20x slower than calling it via GET /rpc/<fn> (query
string params) - even for a trivial function whose entire body is a single
count(*). PostgREST's own Server-Timing header attributes the entire gap
to its internal transaction phase, but pg_stat_statements for the exact
same request shows Postgres itself only spending a small fraction of that
time on the actual statement. Two plausible causes (JWT/JWKS verification
cost, RLS-defeated generic-plan costing in Postgres's prepared-statement
cache) were investigated in depth and both ruled out with direct evidence.

Reproduction

Function used (also reproduced with a much simpler function, same result -
not specific to this one):

create or replace function public.search_media_assets(
  p_mime_filter text default 'all',
  p_name_query text default '',
  p_tag_ids uuid[] default '{}',
  p_tour_ids uuid[] default '{}',
  p_untagged_only boolean default false,
  p_limit int default 60,
  p_offset int default 0
) returns table(...)
language sql stable
as $$ ... $$;

grant execute on function public.search_media_assets to authenticated;

The table it queries has row-level security enabled with policies that
reference current_setting('request.jwt.claims').

GET:

GET /rpc/search_media_assets?p_mime_filter=all&p_name_query=&p_tag_ids=%7B%7D&p_tour_ids=%7B%7D&p_untagged_only=false&p_limit=60&p_offset=0
Authorization: Bearer <JWT, role=authenticated>

POST (equivalent):

POST /rpc/search_media_assets
Authorization: Bearer <same JWT>
Content-Type: application/json

{"p_mime_filter":"all","p_name_query":"","p_tag_ids":[],"p_tour_ids":[],"p_untagged_only":false,"p_limit":60,"p_offset":0}

Measured timing (curl, several repeated runs, consistent every time)

Call shape	Time
Trivial authenticated GET on a plain table (no RPC)	3-6ms
RPC via GET (query string)	65-88ms
RPC via POST (JSON body)	1.0-2.5s

Also reproduced with a second, much simpler function (a single
select count(*) from ... where ..., no joins) - same POST-specific
slowdown, ruling out query complexity as the cause.

Server-Timing breakdown (PGRST_SERVER_TIMING_ENABLED=true)

POST: jwt;dur=0.3, parse;dur=0.5, plan;dur=0.0, transaction;dur=1243.9, response;dur=0.0
GET:  jwt;dur=0.0, parse;dur=0.1, plan;dur=0.1, transaction;dur=67.7,   response;dur=0.0

jwt/parse/plan are near-zero in both cases. The entire gap is
attributed to the transaction phase.

Cross-checked against Postgres directly - no matching cost found

pg_stat_statements reset immediately before a single, isolated POST
request. That request took 1247ms end-to-end (per curl and per PostgREST's
own Server-Timing). The only statement pg_stat_statements recorded for
it:

calls=1, total_exec_time=17.79ms, query="WITH pgrst_source AS (SELECT ...
LATERAL json_to_record(...) ... ) SELECT ... FROM pgrst_source ..."

No other statement, no waiting locks (select count(*) from pg_locks where not granted returned 0), no blocking/idle-in-transaction session found in
pg_stat_activity at the time. Postgres has no record of spending anywhere
near 1.2s on anything related to this request.

Ruled out

1. JWT verification cost / JWKS-format secret. Tested with the project's
   real ES256-signed token AND a hand-minted HS256 token asserting identical
   claims - both equally slow via POST (~1.3-1.5s), both fast via GET. Also
   tested against a fresh throwaway PostgREST container configured with a
   flat single-string PGRST_JWT_SECRET instead of a JWKS {"keys":[...]}
   array (which is what the original setup used) - still slow via POST,
   still fast via GET. A trivial authenticated GET on an unrelated table
   with the same JWT was 3-6ms, so JWT/role-switching overhead in general
   is not the cause.

2. RLS defeating Postgres's prepared-statement plan cache. This looked
   very promising initially: RLS policies here reference session GUCs the
   planner can't resolve at prepare time, and manually running the query via
   PREPARE/EXECUTE in psql showed the 6th execution (when Postgres
   evaluates switching from a custom to a generic plan) spiking to 1192ms of
   planning time alone, vs 48-58ms for each of the first 5 executions.
   Setting plan_cache_mode = force_custom_plan eliminated that spike
   cleanly in isolation (7 executions, all 47-65ms planning, no jump).
   However, applying this for real (alter role authenticator set plan_cache_mode = force_custom_plan, confirmed active via show plan_cache_mode on a fresh session, PostgREST restarted to establish new
   connections under the new default) made no difference to real POST
   requests through PostgREST. Also tried PGRST_DB_PREPARED_STATEMENTS=false
   (disables PostgREST's use of server-side prepared statements entirely,
   sidestepping plan caching altogether) - still slow via POST. So this
   is a real, independently reproducible Postgres behavior, but not the
   cause of the POST/GET gap.

What's left unexplained

Given jwt/parse/plan are all near-zero and the actual SQL statement
Postgres executes takes ~18ms per pg_stat_statements, the remaining
~1.2s+ appears to be spent somewhere in PostgREST's own request-handling
path for POST-with-JSON-body RPC dispatch specifically - possibly connection
acquisition from PostgREST's internal pool, though this wasn't reproducible
via black-box HTTP testing alone (no lock contention, no other concurrent
load on the test database, fresh containers).

Ask

Is this expected/known behavior for POST-body RPC dispatch, or worth
investigating further? Happy to provide more detail, run additional tests
against a specific hypothesis, or share the throwaway-container setup used
to reproduce this outside of Supabase's stack entirely if useful.

[wolfgangwalther]

Could you clarify whether this adheres to our AI policy?