supabase / supavisor / 29055230766
77%

DEFAULT BRANCH: main
Ran
Jobs 2
Files 130
Run time 1min
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst
coverage: 77.22%. Remained the same
29055230766

push

github

web-flow 
docs: explain citext + pgbouncer=true slowdown and its remedy (#1074)

I looked into #300 and the short version is this isn't a Supavisor bug.
The
slowdown is on Prisma's side, and it happens even with no pooler in the
path.

Refs #300

## What kind of change does this PR introduce?

Documentation only. No change to proxy behavior.

## What is the current behavior?

If you've got `citext` columns (or any extension type) and you're using
Prisma
with `pgbouncer=true`, queries in transaction mode are way slower than
the same
table with plain `text` columns that's what #300 is about. The docs
don't
explain it, and the FAQ still says prepared statements only work in
session
mode, which isn't true anymore.

Here's what's actually going on. `citext`'s type OID gets assigned when
you run
`CREATE EXTENSION`, so unlike `text` the client doesn't know it up
front. With
`pgbouncer=true`, Prisma throws away its statement and type caches on
every
transaction (`DEALLOCATE ALL`), so it has to look the OID back up with
`SELECT ... FROM pg_type` once per citext column, every single
transaction.
Add latency and those round trips are basically the whole query time. I
confirmed it's not us two ways: it reproduces hitting Postgres directly
with no
pooler, and profiling the client/db handlers with `:eprof` during the
slow
query shows under 1% of the time is actually spent in Supavisor. We're
just
passing the round trips through.

## What is the new behavior?

The fix is already in Supavisor, it just wasn't documented  turn on the
`named_prepared_statements` flag and drop `pgbouncer=true`, and Prisma
keeps its
caches so the lookups happen once instead of every transaction. Session
mode
works too.

- `docs/orms/prisma.md` documents the flag as the alternative to
`pgbouncer=true`, plus a section on why extension-typed columns get slow
and
  how to avoid it.
- `docs/faq.md`  fixes the stale "session mode only" answer.
- `test/integration/prepared_statements_test.exs` — new test ... (continued)

2644 of 3424 relevant lines covered (77.22%)

54302.01 hits per line

Coverage Regressions

Lines Coverage ∆ File
2
77.27
 -1.52% lib/supavisor.ex
Jobs
ID Job ID Ran Files Coverage
1 run-integration - 29055230766.1 130
42.2
 GitHub Action Run
2 run-tests - 29055230766.2 130
75.76
 GitHub Action Run
Source Files on build 29055230766