Database / Supabase Intermediate to Advanced Interview Questions
1. How does Supabase expose custom Postgres functions as callable RPC endpoints?
Any function defined in your Postgres schema is automatically exposed by PostgREST at /rest/v1/rpc/
2. What is the difference between a SECURITY DEFINER and a SECURITY INVOKER Postgres function?
A SECURITY INVOKER function (the default) runs with the privileges of the role that calls it, so any Row Level Security policies on tables it touches still apply exactly as if the caller ran the query directly. A SECURITY DEFINER function instead runs with the privileges of the role that created ...
3. How does pg_cron enable scheduled jobs directly inside a Supabase Postgres database?
pg_cron is a Postgres extension that schedules SQL commands or function calls to run on a cron-style schedule, stored and executed entirely inside the database rather than needing an external scheduler. select cron.schedule( 'nightly-cleanup' , '0 2 * * *' , $$ delete from sessions where expires_...
4. What is the difference between a read replica and the primary database in a Supabase project?
The primary database is the single instance that accepts writes (INSERT, UPDATE, DELETE); a read replica is a continuously updated copy that only serves read (SELECT) queries, kept in sync via Postgres streaming replication. Because replication is asynchronous, a replica can lag the primary by a ...
5. When should you use a read replica versus scaling the primary database's resources?
Scaling the primary (more CPU, RAM, or storage) helps when both reads and writes are the bottleneck, or when the workload doesn't tolerate any replication lag — for example, a checkout flow that must read back data it just wrote. It's also the simpler change, since it doesn't introduce a se...
6. What is the difference between daily backups and Point-in-Time Recovery in Supabase?
Daily backups are full snapshots of the database taken once a day and retained for a set window depending on plan tier; restoring from one returns the database to exactly the state it was in at that snapshot's timestamp, so anything written after it is lost. Daily Backups Point-in-Time Recovery (...
7. How does Supabase's database branching feature isolate schema changes per git branch?
Branching spins up a separate, fully isolated Supabase project (its own Postgres instance, Auth, Storage, and API) seeded from your migration history, tied to a specific git branch rather than shared with production or other branches. When a pull request is opened, a preview branch can be created...
8. When would you choose a preview branch over testing migrations directly in staging?
A shared staging project is a single, persistent environment that every developer's in-flight work competes for, so testing a migration there risks colliding with someone else's half-finished schema change or leaving staging in a broken state for the team. A preview branch makes sense specificall...
9. What is the difference between a custom access token hook and a raw JWT claim?
A raw JWT claim is simply a key-value pair Supabase Auth includes in every issued token by default — things like the user's ID ( sub ), role, and expiry, generated the same way for every user with no customization point. A custom access token hook is a function (Postgres or Edge Function) t...
10. Why should you avoid embedding sensitive business data directly inside JWT claims?
A JWT's payload is only signed, not encrypted, meaning anyone holding the token — including the end user themselves, since it's stored client-side — can decode and read every claim inside it without ever contacting Supabase's servers. Putting something like a user's exact salary, an i...
11. How does Supabase support anonymous sign-ins, and how do you convert one to a permanent account?
Anonymous sign-in creates a real row in auth.users and issues a normal JWT, but without any email, password, or OAuth identity attached — useful for letting a first-time visitor use an app (add items to a cart, save a draft) before committing to creating an account. const { data, error } = ...
12. What is the difference between linking an OAuth identity and creating a brand-new user account?
Creating a brand-new account always produces a fresh row in auth.users with a new, unique user ID and no history. Linking an OAuth identity instead attaches a new sign-in method (say, Google) to an existing user row — the same user ID, same existing data, just an additional way to authentic...
13. How is Storage object access controlled compared to table-level Row Level Security?
Supabase Storage objects are actually tracked as rows in an internal storage.objects table, so access control uses the exact same Row Level Security mechanism as any other table — policies with USING and WITH CHECK expressions — rather than a separate, bucket-specific permission syste...
14. What is the difference between resumable (TUS) uploads and standard uploads in Supabase Storage?
A standard upload sends the entire file in one HTTP request; if the connection drops partway through, the whole upload has to restart from byte zero. A resumable upload, using the open TUS protocol, breaks the file into chunks and tracks how much has already been received, so an interrupted uploa...
15. How does Supabase's image transformation feature resize images without a separate CDN service?
Supabase Storage can transform images on the fly by appending query parameters to the file's URL — requesting a specific width, height, or quality — rather than requiring you to pre-generate and store multiple resized copies of every image. https://project.supabase.co/storage/v1/rende...
16. What is the difference between LISTEN/NOTIFY and Supabase Realtime's Postgres Changes channel?
LISTEN / NOTIFY is a raw Postgres primitive: a connected client issues LISTEN channel_name and then receives any message another session sends via NOTIFY channel_name, 'payload' , over that same direct Postgres connection — it requires holding a persistent database connection and has no bui...
17. What is the difference between schema-per-tenant and RLS-based multi-tenancy?
Schema-per-tenant gives each customer their own Postgres schema (or even database), with identical table structures duplicated per tenant; isolation is structural — a query simply cannot reach another tenant's schema unless explicitly told to. Schema-per-tenant RLS-based (shared schema) Str...
18. Why is EXPLAIN ANALYZE useful before optimizing a slow Supabase query?
EXPLAIN ANALYZE actually runs the query and reports Postgres's real execution plan — which indexes (if any) were used, how many rows were scanned at each step, and where the time was actually spent — rather than guessing based on how the query reads. Without it, a common mistake is ad...
19. What is the difference between JSONB and a fully normalized relational schema in Postgres?
A normalized schema splits data into separate tables connected by foreign keys, with each fact stored once; a JSONB column instead stores a whole nested document as a single value inside one row, queryable with operators like -> , ->> , and @> , and indexable with a GIN index. Normalization enfor...
20. When should you use a JSONB column instead of creating additional relational tables?
JSONB makes sense when the data's shape is genuinely variable or unknown ahead of time — user-defined custom fields, a webhook payload from a third-party service, or app configuration that changes per customer — where forcing a fixed table structure would mean constant migrations just...
21. How does wrapping auth.uid() in a subquery improve Row Level Security performance?
Calling auth.uid() directly inside a policy's USING clause looks harmless, but Postgres treats it as a volatile function call by default, meaning the planner can't assume it returns the same value for every row and may end up re-evaluating it once per row scanned rather than once per query. Wrapp...
22. Why do we use SECURITY DEFINER functions when RLS would otherwise block a needed operation?
Row Level Security is deliberately restrictive by default — a user typically can't see or modify rows outside their own scope. That's correct most of the time, but some operations legitimately need to cross that boundary in a controlled way: incrementing a shared counter, writing an audit l...
23. How does pg_net let Postgres make asynchronous HTTP calls without blocking a transaction?
Calling an external HTTP endpoint synchronously from inside a Postgres transaction is risky: the transaction has to hold its locks and wait for a network round trip that might be slow or fail, tying up a database connection and potentially blocking other queries on the same rows for the duration....
24. Explain the lifecycle of a Point-in-Time Recovery (PITR) backup in Supabase?
PITR starts from a periodic full base backup of the database, taken automatically on a schedule. From that point forward, every change is continuously captured in the Postgres write-ahead log (WAL) and archived off the primary instance as it's generated, rather than waiting for the next scheduled...
25. How does a custom access token Auth Hook let you enrich a user's JWT with custom claims?
Supabase Auth calls a designated hook function (implemented as a Postgres function or an Edge Function) at the moment it's about to issue a new access token, passing in the current claims it was going to include. The hook function can inspect the user, run its own queries, and return a modified c...
26. How does Supabase enforce multi-factor authentication (MFA) at the session level?
Supabase Auth supports MFA (typically TOTP, an authenticator-app code) as an additional factor layered on top of a user's primary sign-in. After enrolling a factor, a session is tagged with an authenticator assurance level (AAL): aal1 for a session that only completed the first factor, and aal2 o...
27. Why doesn't disabling RLS on a table make it invisible in the auto-generated API docs?
PostgREST builds its schema introspection — and by extension the auto-generated API documentation Supabase Studio shows — directly from Postgres's catalog metadata: table names, columns, and types. That introspection happens regardless of whether RLS is enabled, because RLS is an acce...
28. Explain the internal working of Supabase Vault for storing encrypted secrets in Postgres?
Vault is a Postgres extension that stores secrets encrypted at rest using authenticated encryption, backed by a root key that never lives inside the database itself, so a raw database dump or a leaked backup doesn't expose the plaintext secret alongside it. Secrets are inserted through a dedicate...
29. Why should you store third-party API keys in Vault instead of a plain table column?
A plain column holding an API key is stored as ordinary plaintext data: anyone with SELECT access to that table — a teammate with dashboard access, a misconfigured RLS policy, a database backup that leaks — can read the key directly with no extra step. It's also indistinguishable from...
30. When should you choose LISTEN/NOTIFY over Supabase Realtime for internal service communication?
LISTEN/NOTIFY fits when the communicating parties are trusted backend processes that already maintain a persistent Postgres connection — a background worker, a queue processor, or another server-side service — and the goal is a lightweight, low-latency signal (like "a new job was inse...
31. How can you optimize a multi-tenant schema design using Row Level Security instead of schema-per-tenant?
The core optimization is making the tenant filter cheap for Postgres to apply on every query, since every read and write in a shared-schema design passes through an RLS policy keyed on tenant_id . That starts with a composite index leading with tenant_id on every frequently queried table, so the ...
32. How do you troubleshoot a Postgres query that performs well in the SQL editor but slowly through the REST API?
The two paths often aren't running the same actual query, which is the first thing to verify rather than assume. In the SQL editor, you frequently run as a privileged role with no RLS applied; through the REST API, PostgREST runs the query as the authenticated (or anon) role, meaning every RLS po...
33. Explain the execution flow of a private Realtime broadcast channel authorized by Row Level Security?
A private Realtime channel starts the same way a public one does — a client calls supabase.channel('room-42', { config: { private: true } }) and attempts to subscribe — but before any messages flow, the Realtime server checks whether that connection's JWT is authorized for this specif...
34. Why do private Realtime channels need their own RLS-style authorization check?
A Realtime Broadcast or Presence channel isn't backed by a specific table row the way a Postgres Changes event is — there's no underlying SELECT ... WHERE for Postgres's RLS engine to filter, because the "message" being sent might be an arbitrary payload like a cursor position or a chat lin...
35. How does Supabase's connection string differ between the direct connection, session pooler, and transaction pooler?
All three ultimately reach the same Postgres database, but they differ in what sits between the client and Postgres, and in what connection-level features survive that path. Direct Connection Session Pooler Transaction Pooler One dedicated Postgres connection per client Pooled, held for the clien...
36. Why doesn't the transaction pooler support prepared statements the way a direct connection does?
A prepared statement is parsed and planned once, then referenced by name on later executions — but that only works if every subsequent execution reuses the exact same underlying Postgres connection that did the original preparation, since the prepared statement lives in that specific backen...
37. How can you optimize zero-downtime schema migrations for a table already receiving production traffic?
The general strategy is expand-and-contract: make additive, backward-compatible changes first, deploy application code that can handle both the old and new shape simultaneously, then remove the old shape only once nothing depends on it anymore — rather than changing the schema and applicati...
38. What happens when a long-running migration locks a table that's still receiving live writes?
Most schema-altering statements (like ALTER TABLE ) need an ACCESS EXCLUSIVE lock, the strongest lock Postgres has, for at least a brief moment to safely change the table's structure. While that lock is held, every other query trying to read or write that table — including simple SELECTs &m...
39. How does Supabase Studio's SQL editor differ from running migrations through the CLI in a CI pipeline?
Studio's SQL editor executes a statement immediately and directly against the connected project's live database, with no history file created and no automatic record of what ran or when beyond Postgres's own logs — it's built for quick, ad hoc exploration and one-off fixes. The CLI's migrat...
40. Which is better for production schema changes, editing directly in Studio or CLI-managed migrations, and why?
For anything beyond a quick, one-off inspection or an emergency fix, CLI-managed migrations are the better choice for production, because they turn a schema change into an artifact that can be reviewed, tested against a preview branch, and applied identically and repeatably across every environme...
