Database / Supabase Intermediate to Advanced Interview Questions

Any function defined in your Postgres schema is automatically exposed by PostgREST at /rest/v1/rpc/<function_name>, callable via POST with the function's arguments as a JSON body — no separate route registration is required.

create function calculate_discount(price numeric, percent numeric)
returns numeric as $$
  select price - (price * percent / 100);
$$ language sql;

From the client, this becomes supabase.rpc('calculate_discount', { price: 100, percent: 10 }). RPC is the natural escape hatch when a query needs logic that a plain table filter can't express — conditional aggregation, multi-step calculations, or anything that should run as one atomic unit inside Postgres rather than round-tripping data to the client and back.

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 it, regardless of who calls it.

This makes DEFINER functions useful for controlled privilege escalation — letting an authenticated user trigger an action (like inserting an audit log row) that their own RLS policy wouldn't otherwise permit — but they need careful review since a bug in one effectively grants elevated access to anyone who can call it.

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_at < now() $$
);

Because the job runs inside Postgres, it can call any function you've already written, including one that uses pg_net to fire an outbound webhook, letting you chain a scheduled trigger into an Edge Function without a separate cron service. It's well suited to housekeeping tasks like pruning expired rows, refreshing a materialized view, or recalculating aggregates on a schedule, and jobs can be listed or unscheduled with cron.job and cron.unschedule().

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 small amount, meaning a read issued immediately after a write on the primary might not yet reflect that change if it hits a replica. Read replicas exist to offload read-heavy traffic (dashboards, reporting queries, high-traffic public pages) away from the primary, and can also be placed in a different region to reduce read latency for geographically distributed users.

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 second connection target for your application to reason about.

A read replica makes more sense when reads vastly outnumber writes and the write path itself isn't the constraint — a content site serving thousands of read-only page views per write, or an internal analytics dashboard hammering the same tables users are actively writing to. Routing that read traffic to a replica keeps it from competing with production writes for the primary's resources, at the cost of accepting eventual consistency for anything read from the replica.

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.

PITR instead continuously archives the database's write-ahead log (WAL), so a restore can target any specific second within the retention window — useful when an incident (like a bad migration or an accidental mass-delete) happened at a known time and you want to recover to just before it, rather than losing an entire day's writes.

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 automatically, applying that branch's pending migrations to a clean environment so a schema change can be tested end-to-end — including RLS policies and seed data — before it's merged. Because each branch is isolated, one developer's in-progress migration can't accidentally corrupt another developer's branch or the shared staging project, and the branch can simply be discarded if the PR is abandoned.

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 specifically when a migration is risky, exploratory, or still being iterated on — it gives one developer or one PR a disposable environment to break, roll back, and retry without affecting anyone else, and it can be torn down the moment the PR merges or closes. Staging still earns its place as the final, shared checkpoint that mirrors production configuration closely enough to catch integration issues a solo branch wouldn't surface, such as how a new migration interacts with other recently merged changes.

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) that Supabase Auth calls right before issuing a token, letting you inject additional claims based on your own data — for example, looking up a user's subscription tier or organization ID and adding it to the JWT so that value is available to Row Level Security policies and the client without an extra database round trip on every request.

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 internal risk score, or another user's private data into a claim effectively hands it to the client in plain sight, even if your UI never displays it. Claims are best limited to identifiers and flags that are safe to expose (a role name, an organization ID, a subscription tier), while anything genuinely sensitive should stay server-side and be fetched through an authorized query when actually needed.

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 } = await supabase.auth.signInAnonymously();

Because the anonymous session already has a stable user ID, any data it creates (rows tied to auth.uid()) carries over automatically once the user later links an email, password, or OAuth provider to that same account — there's no separate migration step needed, since it's the same underlying user row gaining a real identity rather than a new one being created.

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 authenticate as that same person.

This distinction matters for account consolidation: if a user originally signed up with email/password and later wants to also sign in with Google, linking keeps their existing orders, settings, and RLS-scoped data intact under one identity, whereas treating the Google sign-in as a brand-new account would silently fragment their data across two separate user IDs with no data in common.

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 system.

A typical policy restricts access based on the object's path, for example allowing a user to read or write only within a folder matching their own user ID: bucket_id = 'avatars' and (storage.foldername(name))[1] = auth.uid()::text. This means the same mental model, and the same debugging techniques, used for table RLS apply directly to file access — there's no separate storage-permissions language to learn.

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 upload can continue from where it left off instead of restarting.

TUS is the better choice for large files (videos, large datasets) or mobile clients on unreliable networks, where restarting a multi-gigabyte upload from scratch after a dropped connection would be costly.

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/render/image/public/avatars/user1.png?width=200&height=200

The transformation happens at the storage/CDN layer itself and the resulting resized image is cached, so repeated requests for the same dimensions are served quickly without re-processing. This avoids the common pattern of running a separate image-processing service or pre-computing thumbnail variants ahead of time for every size an app might need.

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 built-in authorization model.

Supabase Realtime's Postgres Changes channel is built on top of logical replication (not LISTEN/NOTIFY) and is designed for browser and mobile clients: it delivers change events over WebSockets, integrates with Row Level Security so a client only receives events for rows it's allowed to see, and doesn't require the client to hold a raw database connection at all. LISTEN/NOTIFY remains more suited to backend-to-backend signaling within trusted server processes, while Realtime is the client-facing, authorization-aware option.

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.

RLS-based multi-tenancy instead keeps every tenant's rows in the same shared tables, distinguished by a tenant_id column, and relies on a policy like tenant_id = current_tenant() to enforce isolation. It's the more common Supabase pattern because a single migration updates every tenant at once and it scales to far more tenants than provisioning a schema per customer would allow, at the cost of isolation being a policy you must get right rather than a structural guarantee.

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 adding an index based on intuition that doesn't match what the planner actually does; a query might skip an existing index entirely because a function wraps the filtered column, or because the planner estimates a full table scan is cheaper for a small table. Reading the plan first turns index or query changes into an evidence-based decision instead of a guess, and re-running EXPLAIN ANALYZE after a change confirms whether it actually helped rather than assuming it did.

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 enforces consistency through foreign keys and makes cross-entity queries (joins) efficient, but requires a schema change whenever the shape of the data changes. JSONB tolerates a flexible, evolving shape without a migration — useful for things like a form's arbitrary custom fields, third-party API payloads you don't fully control, or settings blobs — but it trades away foreign key enforcement and makes some queries (like aggregating across a specific nested field on a very large table) less efficient than an equivalent normalized column would be.

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 to add new fields.

It's a poor fit when the data has a stable, well-understood shape and you'll need to filter, join, or aggregate on individual fields frequently, since those operations are more efficient and more expressive against dedicated columns with proper types and indexes. A reasonable middle ground many teams use is normalizing the fields that are queried often and filterable, while keeping a JSONB column for genuinely miscellaneous or long-tail data that doesn't justify its own column.

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.

Wrapping it as (select auth.uid()) instead gives the planner a subquery it can evaluate once, cache the result as a constant, and then filter every row against that single cached value — the same technique used to make any expensive, row-invariant expression cheaper inside a large scan.

-- Slower: potentially re-evaluated per row
create policy "owner_select" on documents
  for select using (auth.uid() = user_id);

-- Faster: evaluated once, then reused as a constant
create policy "owner_select" on documents
  for select using ((select auth.uid()) = user_id);

On a small table the difference is negligible, but on a table with hundreds of thousands or millions of rows subject to a policy, this rewrite can turn a noticeably slow filtered query into one that performs close to an equivalent query with no RLS at all. It's one of the first things worth checking when a table's read performance mysteriously regresses after enabling RLS.

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 log entry tied to another user's action, or checking whether a username is already taken across all users (something a user-scoped SELECT policy would never allow).

A SECURITY DEFINER function lets you carve out exactly that one operation as a privileged, narrowly-scoped exception: the function runs with the owner's elevated privileges regardless of who calls it, so it can perform the cross-boundary action, while the RLS policies on the underlying tables remain untouched and still apply to every other query. The key discipline is keeping the function narrow — validating its inputs carefully and doing only the one privileged thing it's meant to do — rather than exposing a general-purpose bypass, since anyone who can call the function inherits whatever access it grants.

This pattern shows up often for things like a "check availability" RPC, a moderation action performed by a non-admin trigger, or aggregating counts across all users for a public leaderboard that individual RLS policies would otherwise hide.

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.

pg_net avoids this by queuing the HTTP request in an internal table and returning immediately, letting a background worker process actually perform the request outside of the calling transaction. A call like select net.http_post(url := 'https://example.com/webhook', body := jsonb_build_object('event', 'new_order')); returns a request ID right away; the transaction that issued it can commit without waiting on the network at all, and the result of the HTTP call can be checked later from the net._http_response table if needed.

This makes pg_net a natural fit for firing webhooks from a trigger — a new row insert can kick off an external notification without the insert itself being slowed down or rolled back by an unrelated third-party service being slow or down.

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 snapshot.

To restore to a specific moment, Supabase takes the most recent base backup before the target time and replays the archived WAL records forward, transaction by transaction, up to the exact second requested — effectively reconstructing the database as it existed at that instant, including any writes that happened seconds before an incident but excluding whatever came after it.

This has two practical consequences worth knowing. First, the restore doesn't happen in-place on the live database; it typically provisions the recovered state as a new environment you review and cut over to, so a bad restore target doesn't destroy the current production data. Second, the retention window is finite and tied to plan tier — WAL older than that window is pruned, so PITR can only reach as far back as the retention period allows, after which only daily backups (if retained longer) can help.

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 claims object that Auth then uses to actually sign the token.

create function custom_access_token_hook(event jsonb)
returns jsonb as $$
declare
  claims jsonb;
  org_id uuid;
begin
  select organization_id into org_id from profiles
    where id = (event->>'user_id')::uuid;
  claims := event->'claims';
  claims := jsonb_set(claims, '{organization_id}', to_jsonb(org_id));
  return jsonb_set(event, '{claims}', claims);
end;
$$ language plpgsql stable;

Because this runs once at token-issuance time rather than on every request, the resulting organization_id claim is then available inside every Row Level Security policy for the lifetime of that token without an extra database lookup per query — a meaningful performance win for policies that would otherwise need a join back to a profile table on every single row check.

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 once the second factor has also been verified.

Enforcement then happens at the Row Level Security layer rather than only in application UI: a policy can check the session's AAL claim directly and refuse access to sensitive rows (like payment methods or account-recovery settings) unless the session is at aal2, for example using ((select auth.jwt()->>'aal') = 'aal2'). This means MFA enforcement isn't just a login-screen gate that a client could theoretically skip — it's checked by the database itself on every query against the protected table, regardless of which client or code path issues the request.

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 access-control mechanism, not a visibility toggle for the schema itself.

Practically, this means a table with RLS disabled is still fully listed, documented, and queryable through the API exactly like any other table — the difference is that without RLS, every row is returned to every requester rather than a filtered subset. Disabling RLS doesn't hide the table; it removes the one thing that would have restricted what data flows through the API that already exposes it. This is precisely why leaving RLS off on a table containing sensitive data is a common and serious misconfiguration — the endpoint isn't hidden, it's just unfiltered.

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 dedicated function rather than a plain INSERT, for example select vault.create_secret('sk_live_...', 'stripe_api_key');, which stores the ciphertext in an internal table. Reading a secret back requires querying the vault.decrypted_secrets view, which itself can be restricted with Row Level Security or role grants so only specifically authorized database roles (typically a SECURITY DEFINER function used by an Edge Function, not arbitrary client roles) can ever see the decrypted value.

The practical benefit over a plain table column is that "store the value" and "read the plaintext back" are two distinctly permissioned operations rather than one — a role with SELECT on a regular table sees everything in plaintext, whereas a role without explicit access to the decrypted view sees only ciphertext, which is the difference between a genuine secrets-management layer and a table that merely holds sensitive data unprotected.

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 any other piece of application data, so it's easy to accidentally include in a query result, a log line, or an exported CSV without realizing it's sensitive.

Vault treats the secret as a distinct, encrypted object from the moment it's written: it's encrypted at rest, decryption is gated behind its own permission (the vault.decrypted_secrets view), and it's clearly marked as a secret in the schema rather than looking like an ordinary text column. This narrows the blast radius of common mistakes — a broad RLS policy or an over-permissioned service role still won't expose the plaintext unless it specifically has decrypt access — and makes it much harder to accidentally leak the key through a routine query, log export, or backup.

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 inserted, go process it") rather than a browser-facing data feed.

Because NOTIFY messages aren't persisted (a listener that isn't connected at the moment a notification fires simply misses it) and there's no authorization model, it's a poor fit for anything client-facing or anything that needs guaranteed delivery. Realtime is the better choice whenever the recipient is a browser or mobile client, when authorization per row matters, or when the message needs to survive a temporary disconnect — Realtime's channel subscriptions handle reconnection and can be scoped with RLS in ways a raw NOTIFY message was never designed to support.

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 policy's filter can use an index scan rather than degrading toward a sequential scan as row counts grow across many tenants sharing the same table.

create policy "tenant_isolation" on orders
  for select using (tenant_id = (select current_tenant_id()));

create index orders_tenant_id_idx on orders (tenant_id, created_at);

Wrapping the tenant-lookup function as (select current_tenant_id()), the same technique used for auth.uid(), avoids re-evaluating it per row. Beyond indexing, partitioning very large tables by tenant_id (or a hash of it) can further help once individual tenants grow large enough that even an indexed scan touches a meaningful fraction of the table, since partition pruning lets Postgres skip entire partitions that don't match the current tenant.

The overall goal is treating tenant_id as a first-class part of every index and query plan, not an afterthought bolted onto an existing single-tenant schema, since RLS makes it implicit in every query whether or not the developer remembers to filter by it explicitly.

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 policy on the table is folded into the query plan — a plan that can look completely different from the unfiltered version you tested manually.

The fix is to reproduce the real conditions: run EXPLAIN ANALYZE in the SQL editor as the actual role the API uses (set role authenticated; first), with the same RLS policies active, rather than testing as a superuser. This often reveals that a policy condition — especially one calling auth.uid() unwrapped, or joining out to another table to check a permission — is what's actually driving the slow plan, not the query's own filters.

Beyond RLS, also check whether PostgREST's default row limit or an unindexed order by is silently forcing a large sort, and confirm the API request isn't requesting a much larger page size or a deeper embedded resource (a joined related table) than the manual test did — both are easy to overlook when comparing a hand-written query to what the client SDK actually generated.

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 specific channel, rather than allowing any authenticated client onto any channel name.

1. The client establishes a WebSocket connection to Realtime, presenting its JWT.
2. On a subscribe attempt to a private channel, Realtime queries a Postgres function (or checks a realtime.channels-linked RLS policy) to determine whether this user is authorized for that channel name.
3. If the check passes, the subscription is accepted and the client starts receiving Broadcast or Presence events published to that channel; if it fails, the subscription is rejected before any data is sent.
4. Every subsequent message published to the channel is delivered only to connections that passed this authorization step, rather than to every WebSocket connected to the server.

This differs from the plain "Postgres Changes" channel type, where authorization is enforced per-row via table RLS on every change event; a private broadcast/presence channel instead authorizes at the channel-subscription level, once, up front — useful for scenarios like a chat room or collaborative document where the "row" being protected isn't a single table row but an entire logical room.

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 line that never touches a table at all.

Without a dedicated authorization step, any client holding a valid JWT could subscribe to any channel name and both read and publish messages on it, since a channel name alone (like room-42) carries no inherent permission information the server can check against ordinary table policies. Requiring an explicit authorization check — typically a function that looks up whether the calling user is actually a participant in that room — closes that gap, making channel access something the application deliberately grants per user rather than something implied just by knowing (or guessing) a channel's name.

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.

The direct connection is a real, dedicated Postgres backend process, so it supports every session-level Postgres feature but doesn't scale to thousands of concurrent short-lived clients. The transaction pooler trades some of those features away specifically to support very high connection concurrency cheaply, which is exactly the trade a serverless or Edge Function workload wants to make.

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 backend process's session state.

Transaction-mode pooling breaks that assumption by design: a client's connection is handed a real Postgres backend only for the duration of a single transaction, and as soon as that transaction commits, the underlying connection is returned to the pool and may be handed to a completely different client next. There's no guarantee the next statement from the same logical client lands on the same backend, so a prepared statement created moments earlier may simply not exist anymore from that connection's point of view.

The practical workaround is to avoid relying on named prepared statements when using the transaction pooler — most drivers and the Supabase client library already account for this and send plain, unprepared statements over pooled connections, reserving actual prepared-statement usage for direct or session-mode connections where the same backend is guaranteed to persist for the client's session.

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 application code in one atomic, simultaneous step.

Concretely, adding a new column should be done as nullable (or with a fast, non-blocking default) rather than NOT NULL immediately, since a NOT NULL constraint with a non-null default historically forced a full table rewrite; adding a column and backfilling it in small batches afterward avoids a long-held lock on a large table. Renaming a column is safer done as add-new-column, dual-write from the application, backfill, then drop-old-column, rather than a single ALTER TABLE ... RENAME that breaks any code still referencing the old name mid-deploy.

-- Step 1: additive, non-blocking
alter table orders add column status_v2 text;

-- Step 2: app dual-writes both columns, backfill in batches
update orders set status_v2 = status where status_v2 is null limit 1000;

-- Step 3: once fully migrated and app only reads status_v2
alter table orders drop column status;

Indexes deserve the same care: creating one with CREATE INDEX CONCURRENTLY avoids holding an exclusive lock for the index build's duration, at the cost of it taking longer and needing a retry if it's interrupted. The unifying principle is minimizing the time any single migration statement holds a lock that blocks concurrent reads or writes on a live table.

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 — has to wait in a queue behind it, not just other writes.

If the migration itself is slow (rewriting a huge table, or building an index the blocking way), that queue of waiting queries can grow for the entire duration, and application requests hitting that table will appear to hang until the migration finishes or times out. In the worst case, a migration that acquires its lock but then has to wait behind an existing long-running query already holding a weaker lock on the same table can itself stall indefinitely, while every subsequent query queues up behind it in turn — a cascading pile-up sometimes called a "lock queue" incident.

This is exactly why migrations against live, high-traffic tables favor non-blocking techniques (adding nullable columns, CREATE INDEX CONCURRENTLY, batched backfills) over a single heavy statement, and why setting a conservative lock_timeout before running a risky migration is a common safeguard — it fails the migration attempt quickly and lets you retry, rather than letting it silently queue behind production traffic for minutes.

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 migration workflow instead treats every schema change as a versioned file (supabase migration new ...) that gets committed to source control, reviewed like any other code change, and applied through supabase db push or a CI job in a defined, repeatable order across every environment — local, staging, and production all end up at the identical schema state because they all replay the same ordered file history rather than each having its own ad hoc set of manual changes.

The practical implication is that anything run only in Studio's SQL editor and never captured as a migration file effectively doesn't exist from the perspective of any other environment; the next `supabase db push` against a fresh environment won't recreate that change, since there's no file recording that it should happen. Studio is best treated as a tool for inspection and quick manual fixes, with anything structural or repeatable expected to also land as a committed migration.

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 environment — the same properties that make version-controlled application code safer than editing files directly on a production server.

Editing directly in Studio's SQL editor against production has a real place: diagnosing an incident with read queries, or a genuinely urgent one-line fix when going through a full PR-and-CI cycle would take too long relative to the cost of continued downtime. Even then, the discipline that keeps this safe is treating it as an exception rather than the norm — retroactively capturing whatever was changed as a proper migration file afterward, so the change isn't silently lost the next time someone runs supabase db push against a fresh environment or a teammate wonders why staging and production have quietly diverged.

The honest answer, then, is CLI-managed migrations as the default and Studio edits as a deliberate, logged exception — not a permanent alternative workflow running alongside it.