Migrations to add indexes and realtime support

[menonsamir]

Postgres doesn't automatically index foreign keys, so we add them manually. This speeds up child-to-parent queries significantly, which may help mitigate timeout issues.

I have tested the migrations locally, and confirmed they increase use of indexes (from ~50% on the games table to ~90%). You can compute statistics about index usage on a local database with npx supabase inspect db index-usage --db-url postgresql://postgres:postgres@localhost:54322/postgres.

[vercel[bot]]

Someone is attempting to deploy a commit to a Personal Account owned by @cmnord on Vercel.

@cmnord first needs to authorize it.

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
jep	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Dec 22, 2023 11:51pm

[cmnord]

Can you add more context in the commit message (& copy to PR description)? If I look back at the commit later, I'll ask "why did we make this change?". The reason for this change is that:

• Postgres doesn't automatically index foreign keys
• Indices will improve the performance of game lookups & joins, which were timing out on Vercel

[cmnord]

Also since we don't have a staging Supabase environment, the only testing is unfortunately local. Can you confirm that the migration works (and maybe improves performance?) locally?

[cmnord]

Another real SWE question would be how long the migration takes to run locally if you pulled down a copy of the full db, but I don't think that's (a) going to be an issue with our DB size and (b) really something I know how to handle if it was too long 😛

[menonsamir]

Ok, updated.

[cmnord]

Migration testing results:

Before: running with the prod credentials

$ npx supabase inspect db index-usage

       TABLE NAME     │ PERCENTAGE OF TIMES INDEX USED │ ROWS IN TABLE
  ────────────────────┼────────────────────────────────┼────────────────
    clues             │                              0 │        529930
    categories        │                             97 │        113051
    room_events       │                             85 │         55906
    games             │                             99 │          8713
    rooms             │                             92 │          1565
    ...

Clues at 0% is bad!

$ npx supabase inspect db seq-scans
Connecting to remote database...

          NAME        │  COUNT
  ────────────────────┼──────────
    clues             │ 1244849
    ...
    categories        │  100196
    room_events       │   59210
    games             │   14349
    rooms             │   13211
    ...

Many sequential scans on clues, categories, and room_events!

Testing migration locally

Applied the migration locally and ran again (unfortunately blowing away my local database):

$ npx supabase inspect db index-usage --local

        TABLE NAME     │ PERCENTAGE OF TIMES INDEX USED │ ROWS IN TABLE
  ─────────────────────┼────────────────────────────────┼────────────────
    room_events        │                             91 │            10
    ...
    rooms              │                              8 │             1
    ...
    clues              │                             99 │             0
    ...
    categories         │                             91 │             0
    games              │                              5 │             0
    ...

I upgraded Supabase and this command broke. Not totally sure what's going on here locally. seq-scans also fails. I think this is a Supabase issue. Going to push the migration.