Database / Supabase basics Interview Questions

Supabase is an open-source backend-as-a-service platform built directly on top of managed PostgreSQL. Rather than a proprietary database engine, it exposes standard Postgres with a layer of ready-made tooling on top: authentication, auto-generated REST and GraphQL APIs, file storage, realtime subscriptions, and serverless edge functions.

Teams reach for it when they want a production-grade relational database without wiring up each of those pieces themselves. Because the core is plain Postgres, a Supabase project can be exported, self-hosted, or migrated elsewhere without the vendor lock-in that a closed NoSQL backend typically imposes.

PostgreSQL is the actual database engine that stores every table, row, and relationship in a Supabase project — it is not a thin wrapper or a Postgres-compatible clone. Every feature Supabase adds, such as auth or storage, is implemented as ordinary Postgres tables, functions, or extensions rather than a separate system.

This matters because it means developers get full SQL: joins, foreign keys, constraints, transactions, and the wider Postgres extension ecosystem (like pg_vector for embeddings) instead of a limited proprietary query language.

A Supabase project bundles several services around one Postgres database. The main ones are: a managed Postgres Database, Auth for user sign-up and session management, auto-generated APIs (REST and GraphQL), Storage for files, Realtime for live data streaming, and Edge Functions for custom serverless logic.

Supabase Auth handles user identity: sign-up, login, session/token management, and password resets, without a developer needing to build that infrastructure from scratch. It supports email/password, magic links, phone OTP, and third-party OAuth providers such as Google or GitHub.

Every authenticated user is represented by a row in Supabase's internal auth.users table, and their JWT-encoded user ID is what Row Level Security policies check against to decide what data they can access.

Supabase Storage organizes files into buckets, and each bucket can be configured as one of two types. Public buckets serve files over a direct URL to anyone, which suits assets like public profile images or marketing media. Private buckets require an authenticated request and are governed by access policies, similar to Row Level Security but applied to storage objects.

Within a bucket, files can be nested in folders and tagged with metadata, and access can be scoped so a user only reaches their own folder.

Row Level Security (RLS) is a native PostgreSQL feature that restricts which rows a given database role can see or modify, enforced by the database engine itself rather than by application code. Supabase turns this into its primary authorization model.

Because RLS runs inside Postgres, it holds even if a client bypasses your app and calls the auto-generated API directly — a policy such as "users can only select rows where user_id = auth.uid()" is checked on every query, not just ones your frontend happens to write correctly.

The Supabase client library (available for JavaScript, Python, Dart, Swift, and others) is an SDK that wraps the project's auto-generated REST API behind a query-builder syntax, so a developer writes chained methods like .from('posts').select('*').eq('published', true) instead of hand-crafting HTTP requests.

The same client also exposes Auth methods (sign-up, sign-in), Storage methods (upload, download), and a Realtime subscription API, so most of a Supabase project can be driven from one imported object in the app code.

Edge Functions let you run custom TypeScript/JavaScript server-side code that isn't a natural fit for a database query — things like calling a third-party payment API, sending a webhook, or performing logic that needs a secret key the client should never see.

They're deployed close to users on a distributed edge network for low latency, and are typically invoked via HTTP from the client or triggered by database webhooks.

From a single Postgres schema, Supabase automatically exposes two API styles without extra configuration:

1. REST (via PostgREST) — every table and view becomes a resource you can query with filters, ordering, and pagination through URL parameters.
2. GraphQL (via pg_graphql) — the same schema is exposed as a GraphQL endpoint with generated types and relationships.

Both respect Row Level Security, so switching API styles doesn't change what data a given user can reach.

A new project is created either from the Supabase dashboard (choosing an organization, region, and database password) or from the CLI, which is common for teams that keep infrastructure in version control.

npx supabase init
npx supabase login
npx supabase link --project-ref your-project-ref
npx supabase db pull

Once created, the project exposes a connection string for direct Postgres access, a REST/GraphQL URL, and an anon public API key used to initialize the client library in your app.

Supabase runs PostgREST, a standalone server that introspects your Postgres schema at startup and turns every table, view, and stored procedure into a REST resource on the fly — there's no code generation step or API definition file to maintain.

A table named posts with columns id, title, and published automatically becomes queryable at /rest/v1/posts, and query string parameters map directly to SQL: ?published=eq.true&order=id.desc becomes a WHERE clause and ORDER BY. Because PostgREST talks to Postgres as the authenticated user's role, Row Level Security is applied automatically on every generated endpoint, so the API layer never becomes a way to bypass your data access rules.

In a typical Supabase app, the frontend talks to the database almost directly through the auto-generated API using a public anon key that ships inside the client bundle. Without RLS, that key alone would be enough for anyone to read or modify every row in every table.

RLS closes that gap by moving authorization into the database itself: a policy checks conditions like auth.uid() = user_id on every row before returning or writing it, regardless of which client or tool issued the request. This is different from a traditional backend, where a private server sits between the client and the database and enforces access control in application code; Supabase's model instead assumes the API is public-facing and pushes the trust boundary down to Postgres.

Skipping RLS on a table that should be restricted is one of the most common Supabase security mistakes, since the table remains fully open to any authenticated (or even anonymous) client by default once RLS is off.

Supabase Realtime listens to Postgres's built-in logical replication stream (the same mechanism used for replication to standby servers) and converts row-level INSERT, UPDATE, and DELETE events into messages pushed over WebSockets to subscribed clients.

A client subscribes to a channel scoped to a table or even a specific filter, for example rows where room_id = 42, and receives a payload the moment a matching change commits. Realtime also supports two other channel types beyond database changes: Presence, for tracking which users are currently online in a shared space, and Broadcast, for sending arbitrary low-latency messages between clients (like cursor positions) without touching the database at all.

Both are API keys generated per project, but they carry very different levels of trust and belong in very different places.

Because the service role key skips RLS, leaking it — for example by embedding it in a mobile app or a public Edge Function response — effectively grants full read/write access to the entire database.

Both let you run custom logic, but they suit different jobs. A Postgres function (written in SQL or PL/pgSQL and callable via RPC) is the right choice when the logic is fundamentally about data: aggregations, multi-table transactions, or anything that benefits from running inside the database's own transaction boundary.

An Edge Function is the better fit when the logic needs to reach outside the database: calling a third-party REST API, verifying a webhook signature, sending an email through an external provider, or using a secret credential that shouldn't live inside SQL. Edge Functions also make sense when you need a general-purpose runtime (npm packages, async HTTP calls) rather than SQL's more limited procedural surface.

A common pattern combines both: a database trigger calls a webhook that invokes an Edge Function, so a data change (like a new order row) triggers external side effects (like a payment charge) without the client needing to orchestrate the sequence itself.

The first step is confirming RLS is actually the cause: querying the same table with the service_role key (server-side only, for debugging) bypasses RLS entirely, so if that query succeeds while the client's fails, the policy is the culprit rather than the query itself.

From there, check three common causes in order. First, confirm RLS is enabled on the table but that a policy actually exists for the operation being run (SELECT, INSERT, UPDATE, DELETE each need their own policy). Second, inspect the policy's USING and WITH CHECK expressions for a mismatch, such as comparing auth.uid() against the wrong column, or a foreign key case-sensitivity issue. Third, verify the client is actually sending an authenticated request; an unauthenticated call runs as the anon role, which most policies deliberately restrict.

Supabase's dashboard SQL editor and Postgres logs can also show the exact policy expression being evaluated, which is usually faster than guessing from the client-side error alone.

Supabase Auth is a managed identity service: it stores users in auth.users, issues and refreshes JWTs, handles password hashing and reset flows, and wires several OAuth providers, all pre-integrated with Row Level Security so auth.uid() is available inside your policies for free.

Rolling your own JWT auth means owning every part of that: password storage and hashing, token issuance and rotation, OAuth provider integration, session revocation, and connecting the resulting identity into your database's authorization checks manually. It offers more control — useful if you need a non-standard identity model or must integrate with an existing enterprise identity provider Supabase doesn't support out of the box — but it also means owning the security surface area that Supabase Auth already covers, including edge cases like token refresh races and email verification flows.

Most teams start with Supabase Auth and only move to a custom or external solution when a specific requirement (like a particular SSO protocol) isn't supported.

Validation in Supabase typically happens in layers rather than one place. At the database level, Postgres constraints (NOT NULL, CHECK, UNIQUE, foreign keys) reject structurally invalid rows regardless of which client sends them, making them the most reliable layer since they can't be bypassed by calling the API directly.

Beyond structural constraints, a BEFORE INSERT trigger calling a PL/pgSQL function can enforce business rules that are harder to express as a plain constraint, such as normalizing an email to lowercase or rejecting a row based on a calculation across other columns. Application-level validation (in the client or an Edge Function) is still useful for fast user feedback, but should be treated as a UX convenience layered on top of database constraints, not a substitute for them, since a client-side check can always be skipped by calling the API directly.

Migrations are versioned, ordered SQL files that describe every schema change — new tables, altered columns, new RLS policies — so the database structure can be recreated deterministically instead of relying on manual dashboard edits that nobody tracked.

With the Supabase CLI, running supabase migration new add_orders_table creates a timestamped file that gets applied with supabase db push, and the same file can be committed to git, reviewed in a pull request, and applied identically to a teammate's local database, a staging project, and production. This also enables environment branching, where each git branch can spin up its own isolated Supabase project seeded from the same migration history, catching schema issues before they reach a shared environment.

A database webhook is really just a trigger wired to fire on INSERT, UPDATE, or DELETE for a specific table; when the triggering statement commits, Postgres sends an HTTP POST containing the changed row's data (and, for updates, the previous values) to a configured URL, commonly an Edge Function endpoint.

The Edge Function then runs independently of the original database transaction — it receives the payload, executes its own logic (like charging a card or sending a notification email), and its outcome doesn't roll back the original insert even if the function itself fails. Because of that, the pattern is best suited to side effects that are acceptable to retry or handle asynchronously, and teams often add their own retry or dead-letter handling in the function rather than assuming delivery is guaranteed.

When a client calls supabase.from('posts').select('*'), the request travels through several distinct layers before a row ever comes back.

1. The client SDK builds an HTTP GET request to /rest/v1/posts, attaching the API key and, if the user is signed in, an Authorization: Bearer <jwt> header.
2. The request hits Kong, Supabase's API gateway, which routes it to the PostgREST service and can apply rate limiting.
3. PostgREST verifies the JWT, extracts claims like the user's role and auth.uid(), and opens a Postgres connection as that role rather than as a superuser.
4. Postgres compiles the query, and because the connection is running as the authenticated (or anon) role, every Row Level Security policy on posts is evaluated as part of the query plan — not as a separate check afterward.
5. Only rows that pass the policy are returned, serialized to JSON by PostgREST, and sent back through the gateway to the client.

The key architectural point is that authorization isn't a middleware step bolted onto the API; it's inseparable from the SQL execution itself, which is what makes it consistent no matter which client or tool issues the request.

The service_role key is designed to connect to Postgres as a role that has RLS_bypass privileges built in — it doesn't just have broad permissions, it explicitly skips the Row Level Security checks that every other connection is subject to. That makes it functionally equivalent to a database superuser for the purposes of the API.

If that key ships inside a mobile app binary or a public web bundle, anyone who inspects the network traffic or decompiles the app can extract it and use it to read or write any row in any table, completely ignoring the ownership and access rules the rest of the app depends on. This is categorically different from leaking the anon key, which is meant to be public and is still constrained by RLS — a leaked anon key is a non-event by design, while a leaked service_role key is a full data breach.

In practice, the service_role key should only ever be used from environments a client can't inspect: Edge Functions, a backend server, or CI/CD scripts running migrations, and ideally stored as an encrypted secret rather than an environment variable checked into source control.

Serverless functions scale by spinning up many short-lived instances, and each one that opens a direct Postgres connection can quickly exhaust Postgres's fairly low default connection limit (often in the low hundreds), since Postgres allocates real memory per connection rather than using lightweight threads.

Supabase addresses this with Supavisor, a connection pooler that sits in front of Postgres and multiplexes many client connections onto a much smaller pool of actual database connections. It offers two modes: transaction mode, which hands out a connection only for the duration of a single transaction and is well suited to the bursty, short-lived connections that serverless functions create, and session mode, which holds a connection for the client's full session and is closer to a direct connection — needed for features like prepared statements or session-level settings that transaction mode can't support.

In practice, the optimization is straightforward: point Edge Functions and other serverless callers at the transaction-mode pooler port rather than the direct database port, keep each function's own connection lifecycle short (open, query, close, don't hold connections across invocations), and reserve the direct connection or session-mode pooler for long-lived clients like a traditional backend server or local development.

When RLS is enabled on a table, Postgres doesn't run policies as a separate pass after fetching rows — it rewrites the query plan so that each policy's USING expression is folded in as an additional filter condition, conceptually similar to an implicit AND clause appended to the query's WHERE clause, but applied per-command-type.

Each operation type has its own expression: USING controls which existing rows are visible for SELECT, UPDATE, and DELETE, while WITH CHECK validates rows being inserted or the new values of an update, preventing a user from writing a row that would immediately violate the policy that would otherwise hide it. If a table has multiple permissive policies for the same operation, Postgres combines them with OR — a row is visible if it satisfies any one of them — whereas restrictive policies (declared with AS RESTRICTIVE) are combined with AND and narrow access further on top of the permissive set.

Because this expression becomes part of the query plan, Postgres's optimizer can use indexes referenced inside the policy condition itself; a policy like user_id = auth.uid() on an indexed user_id column performs like any other indexed filter, which is why RLS-heavy tables should have their policy columns indexed just as deliberately as any other frequently filtered column.

pgvector is a Postgres extension that adds a vector column type and distance operators (cosine distance, L2, inner product) so you can store embeddings alongside your relational data and query them with SQL, for example: create extension if not exists vector; create table documents (id bigserial primary key, content text, embedding vector(1536));. Pure vector similarity search finds rows whose embeddings are numerically closest to a query embedding, which is powerful for semantic matches but can miss results that share exact keywords without being semantically close in the embedding space, and vice versa.

Hybrid search combines that vector similarity ranking with traditional keyword search (typically BM25-style full-text ranking via Postgres's tsvector) and merges the two rankings, often using a technique like Reciprocal Rank Fusion, into a single result order. This captures cases pure vector search misses — like an exact product code or acronym a user typed — while still surfacing semantically related results that don't share exact wording.

Because both the vector index and the full-text index live in the same Postgres database, a hybrid search can be expressed as a single SQL query joining both ranking signals, without needing to synchronize data between a separate vector database and a separate search engine.

Polling means the client repeatedly re-runs a query on an interval (say, every 3 seconds) to check for changes, while Supabase Realtime pushes a message over an already-open WebSocket the instant a matching row changes. For most collaborative use cases — shared cursors, live comments, presence indicators — Realtime is the better fit on nearly every axis: lower latency (changes appear in milliseconds rather than waiting for the next poll interval), lower database load (no repeated queries hitting Postgres from every connected client), and lower bandwidth (only actual changes are sent, not the full result set each time).

Polling still has a legitimate niche: it's simpler to reason about, doesn't require managing a persistent connection, and can be preferable for infrequent updates where sub-second latency doesn't matter, or in environments where WebSocket connections are unreliable (some restrictive corporate proxies, for instance). It also degrades more predictably under connection loss, since a client just tries again on the next interval rather than needing reconnection and resubscription logic.

In practice, most teams default to Realtime for anything genuinely collaborative and reserve polling for background, low-frequency checks like periodically refreshing a dashboard that doesn't need to feel live.

Supabase runs Supavisor, a Postgres-aware connection pooler, in front of every project's database. Instead of each application instance opening its own direct Postgres connection — which is expensive because Postgres spawns a full backend process per connection — clients connect to Supavisor, which maintains a smaller pool of real connections to Postgres and shares them across many incoming client connections.

Supavisor supports both transaction-mode pooling, where a connection is checked out only for the lifetime of a single transaction (ideal for serverless and Edge Functions with bursty, short-lived usage), and session-mode pooling, where a connection is held for a client's entire session (needed for features like prepared statements, LISTEN/NOTIFY, or advisory locks that assume a stable underlying connection). It also supports pooling across multiple Postgres instances via a single external endpoint, which matters for teams running read replicas or multi-tenant setups where connections need to be distributed rather than pointed at one fixed host.

The practical upshot is that connection scaling becomes a pooler configuration problem rather than something requiring a hand-rolled pooling layer (like PgBouncer manually configured) in application code, though teams with unusual connection patterns can still tune pool size and mode per use case.

When a user signs in, Supabase Auth issues two tokens: a short-lived access token (a JWT, typically valid for about an hour) that's sent with every API request to prove identity, and a longer-lived refresh token that's used only to obtain a new access token once the current one expires.

The access token's claims (including the user's ID as sub, which becomes auth.uid() inside RLS policies) are self-contained and verifiable without a database round trip, which is why PostgREST can check them cheaply on every request. When the access token nears expiry, the client SDK automatically calls the token refresh endpoint with the refresh token, and if that refresh token is still valid and hasn't been revoked, Auth issues a new access/refresh token pair — refresh tokens are typically single-use and rotated on each refresh, so a stolen but already-used refresh token becomes worthless.

Sign-out invalidates the current session's refresh token server-side, which prevents further refreshes, though any access token issued before sign-out remains technically valid (and usable) until its own short expiry passes, since it's a self-contained JWT rather than something checked against a live session table on every request.

Because Supabase's stack is fully open source, self-hosting is a real option, not just a fallback, and the deciding factor is usually a specific constraint the managed cloud can't satisfy rather than cost alone.

Data residency and compliance requirements are a common driver: an organization that must keep data inside a specific country or private network the managed cloud doesn't offer a region for may need to self-host on their own infrastructure. Similarly, teams already running everything inside a private VPC with strict network isolation policies, or needing custom Postgres extensions the managed platform doesn't enable, often self-host to get full control over the underlying environment.

The trade-off is real operational ownership: patching and upgrading each component (Postgres, PostgREST, GoTrue, Realtime, Storage) yourself, managing backups and high availability, and handling scaling that the managed cloud otherwise absorbs. For most startups and small teams, the managed cloud's Pro or Team tier is the more practical default, and self-hosting tends to make sense once an organization has both a concrete compliance or infrastructure requirement and the platform engineering capacity to operate it.

Postgres's full-text search works by converting text into a tsvector — a normalized, stemmed representation of the words in a column — and comparing it against a tsquery built from the search terms. On a small table this works fine unindexed, but on a large table, computing that tsvector on every query row-by-row becomes the dominant cost.

The standard optimization is to add a generated tsvector column and index it with GIN rather than computing the vector inline on every search:

alter table articles add column fts tsvector
  generated always as (to_tsvector('english', title || ' ' || body)) stored;
create index articles_fts_idx on articles using gin(fts);

With this in place, a query like select * from articles where fts @@ websearch_to_tsquery('english', 'connection pooling') can use the GIN index directly instead of scanning and re-vectorizing every row, which is the difference between an index scan and a full table scan on large datasets.

Beyond indexing, further gains come from ranking only a reasonably sized candidate set (filter first with other WHERE conditions, then rank with ts_rank), and, for workloads that need to blend keyword relevance with semantic meaning, combining this indexed full-text score with a pgvector similarity score in a hybrid ranking query rather than relying on full-text search alone.