Data APIs Over the Warehouse: PostgREST, Hasura & GraphQL for Analytics Serving

Data APIs are the layer that finally lets a modelled warehouse table become something an application can ask a question of — a governed request/response endpoint returning JSON in tens of milliseconds — instead of a dataset you can only reach through a batch query, a BI tool, or a hand-built backend nobody has time to maintain. The hard problem was never "run the query"; it was the consumption gap. A warehouse or lakehouse is optimised for scanning billions of rows in a scheduled job, not for answering ten thousand tiny concurrent lookups a second with a sub-100-millisecond budget, and every product team that wanted to put a curated metric behind a login ended up writing yet another bespoke REST service, wiring yet another connection pool, and re-implementing authorization from scratch.

This guide is the senior-data-engineering walkthrough for closing that gap — for building the analytics serving layer as a set of reusable data products rather than one-off backends — framed the way interviewers actually probe it: why the warehouse is not itself a serving database, how PostgREST turns a Postgres schema into a REST API with row-level security doing the authorization, how Hasura serves instant GraphQL over Postgres, Snowflake, and BigQuery with per-role permissions and live subscriptions, how a GraphQL schema for analytics avoids the N+1 trap and stays cheap through DataLoader batching and persisted queries, and how the surrounding data serving layer — gateway auth, rate limits, connection pooling, caching tiers, and the precompute-versus-query-live decision — keeps the warehouse from falling over. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the API integration practice library →, rehearse serving patterns on the real-time analytics practice library →, and sharpen the architecture axis with the system design practice library →.

---

On this page

• Why serve data APIs over the warehouse
• PostgREST — auto-generated REST over Postgres
• Hasura — instant GraphQL over the warehouse
• GraphQL for analytics serving — schema, N+1, caching
• The serving architecture — caching, rate limits, auth, pooling
• Cheat sheet — data API serving
• Frequently asked questions
• Practice on PipeCode

---

1. Why serve data APIs over the warehouse

The consumption gap — a warehouse answers batch questions; a data API answers request/response ones

The one-sentence invariant: a data API is a governed request/response contract in front of curated data, and the reason it exists is that a warehouse or lakehouse is built for high-throughput batch scans over huge scans, not for high-concurrency low-latency point lookups, so the serving layer's whole job is to bridge that gap — deciding what to precompute versus query live, where authorization lives, and what contract shape (REST or GraphQL) the consumer sees — while turning datasets into reusable data products instead of a new bespoke backend per team. Point a thousand dashboard widgets straight at the warehouse and you get slow queries, a runaway bill, and a coupling nightmare; put a data API in between and the warehouse serves the API, and the API serves the world.

The four axes interviewers actually probe.

• Latency budget. What is the response-time target, and does it force precomputation? A warehouse cold query is seconds; a serving endpoint is often budgeted at tens of milliseconds. The senior answer names the budget first and lets it dictate whether the data is precomputed into a mart/serving store or queried live. Reaching for "just query the warehouse per request" without stating the budget is the tell of someone who has not served real traffic.
• Governance. Who is allowed to see which rows and columns, and where is that enforced? A data product is only a product if it is governed. The senior answer names row-level security, per-role column masking, and the difference between authenticating at the edge and authorizing at the data layer.
• Contract shape. REST or GraphQL — and why? REST is cache-friendly, simple, and resource-oriented; GraphQL lets a client fetch exactly the fields it needs across relationships in one round-trip and is the natural federation contract. The senior answer picks by consumer, not by fashion.
• Cost and concurrency. How many concurrent connections and requests, and what stops them from crushing the backend? Warehouses and Postgres both fall over under connection storms. The senior answer talks about connection pooling, caching tiers, and rate limiting as first-class parts of the serving layer.

The 2026 reality — the serving layer is a small stack of well-worn tools.

• PostgREST turns any Postgres database into a REST API automatically: tables, views, and functions become endpoints, and row-level security is the authorization model. It is a single stateless binary, trivial to scale horizontally.
• Hasura serves instant GraphQL over Postgres and, through connectors, over Snowflake, BigQuery, and other sources — with declarative per-role permissions, auto-derived relationships, and live subscriptions.
• GraphQL has become the contract of choice when many consumers each want different slices of related data; the ecosystem (DataLoader, persisted queries, @cacheControl, federation) exists precisely to make it cheap at serving scale.
• The serving store is usually not the warehouse itself: a Postgres read replica, a materialized mart, or a fast key-value/OLAP store fronts the warehouse, refreshed on a schedule, so the API never issues an ad-hoc scan against the analytical engine on the hot path.

What interviewers listen for.

• Do you say the warehouse is not a serving database and explain the consumption gap unprompted? — senior signal.
• Do you name row-level security (or an equivalent authz model) as where governance lives, not "the app checks a flag"? — required answer.
• Do you frame the choice as precompute vs query-live driven by a stated latency budget? — senior signal.
• Do you treat connection pooling and caching as part of the serving layer, not an afterthought? — required answer.
• Do you describe a dataset as a data product with a versioned contract, owner, and SLO? — senior signal.

Worked example — the precompute-vs-query-live decision table

Detailed explanation. The single most useful artifact for a serving-layer interview is a memorised mapping of access pattern → serving strategy. Every senior discussion converges on it: given a consumer and a latency budget, do you query the warehouse live, precompute a mart, or cache a response? Walk through building the table for a product that exposes a "daily sales by region" metric to an in-app dashboard.

• The consumers. An in-app tile (10k req/min, p95 < 100 ms), an analyst ad-hoc explorer (low volume, freshness-tolerant), a partner bulk export (nightly, large).
• The tension. Live warehouse queries are fresh but slow and expensive; precomputed marts are fast and cheap but stale between refreshes.
• The rule. Match the strategy to the access pattern's tolerance for latency, freshness, and cost.

Question. For each consumer, name the serving strategy and where the data physically lives when the request arrives.

Input.

Consumer	Latency budget	Freshness need	Strategy
In-app dashboard tile	< 100 ms	minutes OK	precomputed mart + response cache
Analyst ad-hoc explorer	seconds OK	live	query the warehouse/replica live
Partner nightly export	minutes OK	daily	precompute + paginate
Real-time ops alert	< 1 s	seconds	streaming/serving store, not warehouse

Code.

-- Precompute a serving mart the data API reads from, refreshed on a schedule.
-- The API NEVER scans the raw fact table on the hot path.
CREATE MATERIALIZED VIEW serving.daily_sales_by_region AS
SELECT
    order_date,
    region,
    count(*)              AS orders,
    sum(total_cents)::bigint AS revenue_cents
FROM analytics.fct_orders
WHERE order_date >= current_date - INTERVAL '400 days'
GROUP BY order_date, region;

-- A covering index so the API's point lookups are index scans, not seq scans.
CREATE UNIQUE INDEX ON serving.daily_sales_by_region (region, order_date);

-- Scheduled refresh (e.g. after the nightly dbt/warehouse load completes).
-- CONCURRENTLY keeps the mart readable by the API during the refresh.
REFRESH MATERIALIZED VIEW CONCURRENTLY serving.daily_sales_by_region;

Step-by-step explanation.

1. The materialized view serving.daily_sales_by_region is the serving store: the expensive aggregation over fct_orders runs once per refresh, not once per request. The in-app tile reads a tiny pre-aggregated table, so its p95 is an index lookup, not a warehouse scan.
2. The UNIQUE INDEX on (region, order_date) turns the API's filtered reads (WHERE region = 'EU') into index scans. Without it, every request seq-scans the mart — fast at small size, fatal as it grows.
3. REFRESH ... CONCURRENTLY rebuilds the mart without taking an exclusive lock, so the API keeps serving the previous snapshot while the new one builds — the freshness cost is bounded by the refresh cadence, not by request time.
4. The analyst explorer, by contrast, is freshness-sensitive and low-volume, so it queries the warehouse (or a read replica) live — precomputing every ad-hoc slice they might want is impossible and wasteful.
5. The mistake is a single strategy for all consumers: querying live for the high-QPS tile melts the warehouse, and precomputing for the ad-hoc explorer builds marts nobody reads. The table is the antidote — strategy follows the access pattern.

Output.

Access pattern	Right strategy	Wrong strategy (common mistake)
High-QPS, freshness-tolerant	precomputed mart + cache	live warehouse query per request
Low-volume, freshness-critical	live query on replica	precompute every possible slice
Bulk nightly	precompute + keyset paginate	one giant unpaginated response
Sub-second ops	streaming/serving store	ad-hoc warehouse scan

Rule of thumb. State the latency budget first, then let it choose the strategy: precompute a mart for hot, freshness-tolerant reads; query live for cold, freshness-critical ones. The warehouse serves the serving store; the serving store serves the request.

Worked example — what interviewers actually probe

Detailed explanation. The senior serving-layer interview has a predictable escalation: an ambiguous opener ("expose this dataset to the app"), then progressive narrowing to test whether you understand the consumption gap, governance, and cost. The candidates who name precompute-vs-live, row-level security, and pooling score highest.

• Ambiguous opener. "The app team wants the daily_sales data. Point them at the warehouse?"
• Follow-up 1. "Ten thousand requests a minute hit it. Now what?" — probes precompute + caching.
• Follow-up 2. "Tenant A must not see tenant B's rows. Where's that enforced?" — probes authz / RLS.
• Follow-up 3. "Postgres runs out of connections. Why?" — probes pooling.
• Follow-up 4. "REST or GraphQL?" — probes contract fit.

Question. Draft a 5-minute senior serving answer that pre-empts all four follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Access	"give them a warehouse login"	"put a data API in front; the warehouse isn't a serving DB"
High QPS	"add more warehouse compute"	"precompute a mart; cache responses"
Tenant isolation	"the app filters by tenant"	"row-level security in the data layer"
Connections	"raise max_connections"	"a transaction pooler (PgBouncer) in front"
Contract	"whatever's easiest"	"REST for simple resources, GraphQL for related slices"

Code.

Senior data-API answer template (5 minutes)
============================================

Minute 1 — name the gap up front
  "The warehouse is for batch scans, not request/response. I'd put a
   data API in front and serve from a store built for point reads —
   a materialized mart or a read replica, not the raw fact tables."

Minute 2 — precompute vs live
  "For the high-QPS app tile I precompute an aggregate mart and cache
   the responses; for the analyst's ad-hoc explorer I query live on a
   replica. The latency budget picks the strategy per consumer."

Minute 3 — governance
  "Authorization lives in the data layer, not the app: row-level
   security policies scope every read to the caller's tenant/role, so
   there is no code path that can leak another tenant's rows."

Minute 4 — connections + cost
  "Postgres dies on connection storms, so a transaction pooler
   (PgBouncer) sits in front and multiplexes thousands of clients onto
   a small pool. A gateway rate-limits and a cache absorbs the hot keys."

Minute 5 — contract shape
  "REST via PostgREST for simple resource access, GraphQL via Hasura
   when consumers each want different related slices in one round-trip.
   Either way it's a versioned data product with an owner and an SLO."

Step-by-step explanation.

1. Minute 1 frames the whole answer around the consumption gap. Weak candidates hand out a warehouse login; naming "the warehouse isn't a serving database" signals you understand the architecture, not just the tools.
2. Minute 2 shows you serve high-QPS traffic from a precomputed store and reserve live queries for cold, freshness-critical paths — the single most senior thing you can say about serving.
3. Minute 3 pre-empts the isolation follow-up. Naming row-level security and "no code path can leak another tenant" is the difference between "the app checks a flag" and "the data layer enforces it."
4. Minute 4 pre-empts the cost follow-up. Volunteering the pooler and rate limit before the interviewer raises max_connections shows you have run a data API under real concurrency.
5. Minute 5 closes on contract fit and the data product framing — versioned, owned, SLO-backed — which is the sentence that separates a platform engineer from someone bolting on an endpoint.

Output.

Grading criterion	Weak score	Senior score
Names the consumption gap	rare	mandatory
Precompute vs live by budget	occasional	mandatory
Authz via row-level security	rare	senior signal
Pooling for connection storms	rare	senior signal
Frames it as a data product	rare	senior signal

Rule of thumb. The senior serving answer is a 5-minute monologue covering the consumption gap, precompute-vs-live, governance, pooling, and contract fit without waiting for the follow-ups. Rehearse it once; deploy it every interview.

Worked example — REST vs GraphQL for a serving contract

Detailed explanation. A common interview trap is "REST or GraphQL for the data API?" The weak answer picks by fashion. The senior answer picks by consumer: how many related resources they fetch together, how cache-friendly the access is, and who owns the query shape. Walk the comparison for two consumers of the same warehouse data.

• The REST-shaped consumer. A dashboard hitting one resource at a time (/daily_sales?region=eq.EU) — simple, cacheable at the URL, no relationship traversal.
• The GraphQL-shaped consumer. A mobile screen needing an order plus its customer plus its line items plus a rolled-up metric — three or four joined resources it wants in one round-trip with only the fields it uses.
• The decision. REST when access is resource-at-a-time and cache-friendly; GraphQL when consumers each want different related slices and round-trips matter.

Question. Contrast a REST endpoint and a GraphQL query serving the same warehouse data on round-trips, caching, and who controls the shape.

Input.

Dimension	REST (PostgREST)	GraphQL (Hasura)
Fetch related data	multiple requests or embed	one query, nested
Over/under-fetch	fixed response shape	client picks exact fields
HTTP caching	easy (URL is the key)	harder (POST body) — needs persisted queries
Who owns query shape	server (resources)	client (query)
Best fit	simple resource access	many consumers, varied slices

Code.

### REST (PostgREST): one resource, cache-friendly URL, server-defined shape
GET /daily_sales?region=eq.EU&order=order_date.desc&limit=30
Accept: application/json
Authorization: Bearer <jwt>

### GraphQL (Hasura): one round-trip, client picks fields across relationships
POST /v1/graphql
Authorization: Bearer <jwt>
Content-Type: application/json

{"query":"query TopRegion {
  daily_sales(where: {region: {_eq: \"EU\"}},
              order_by: {order_date: desc}, limit: 30) {
    order_date
    revenue_cents
    region_ref { manager }      # related resource, same round-trip
  }
}"}

Step-by-step explanation.

1. The REST call is a single GET whose URL fully describes the request, so a CDN or reverse proxy can cache it by key with no cooperation from the server — the cache-friendliness that makes REST the default for simple, high-read resources.
2. The GraphQL call fetches daily_sales and its related region_ref.manager in one round-trip, and the client lists exactly the fields it needs — no over-fetching a fat resource, no under-fetching that forces a second call.
3. The trade-off is caching: GraphQL requests are POSTs with the query in the body, so URL-based HTTP caching does not apply out of the box — you recover it with persisted queries (a stable hash becomes the cache key), covered in section 4.
4. Ownership flips: with REST the server defines resource shapes and the client composes calls; with GraphQL the client defines the query and the server resolves it — powerful for many diverse consumers, but it puts complexity/cost control (depth limits, batching) on the server.
5. The senior move is not "GraphQL is better" but "match the contract to the consumer": a single high-read tile is happiest on a cacheable REST URL; a screen assembling several related resources is happiest with one GraphQL round-trip.

Output.

Question	REST	GraphQL
"Fetch order + customer + items"	3 requests (or embed)	1 query
"Cache at the CDN"	trivial (URL key)	needs persisted queries
"Client wants only 2 fields"	gets the whole resource	asks for exactly 2
"Add a new consumer with different needs"	new endpoints	same schema, new query

Rule of thumb. Pick REST for simple, cache-friendly, resource-at-a-time access and GraphQL when many consumers each want different related slices in one round-trip. It is a per-consumer decision, not a religious one — and a serving layer can offer both over the same data.

Senior interview question on data-API serving strategy

A senior interviewer often opens with: "A product team wants your curated daily_sales data behind a low-latency API for an in-app dashboard, an analyst explorer, and a partner export. You currently have only a warehouse. Design the serving layer: what you precompute versus query live, where authorization lives, how you keep the backend from being crushed by concurrency, and whether you expose REST, GraphQL, or both — and why it's a data product, not a one-off endpoint."

Solution Using a serving store, row-level security, a pooler, and a per-consumer contract

-- Step 1 — a serving store fronts the warehouse; the API reads THIS, not fct_orders.
CREATE MATERIALIZED VIEW serving.daily_sales_by_region AS
SELECT order_date, region, tenant_id,
       count(*) AS orders, sum(total_cents)::bigint AS revenue_cents
FROM analytics.fct_orders
GROUP BY order_date, region, tenant_id;
CREATE UNIQUE INDEX ON serving.daily_sales_by_region (tenant_id, region, order_date);

-- Step 2 — governance in the data layer: row-level security scopes every read.
ALTER TABLE serving.daily_sales_by_region ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON serving.daily_sales_by_region
  FOR SELECT
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

# Step 3 — PgBouncer multiplexes thousands of API clients onto a small DB pool,
# so a connection storm never exhausts Postgres.
[databases]
serving = host=pg-replica port=5432 dbname=serving

[pgbouncer]
pool_mode = transaction        ; return the connection after each txn
max_client_conn = 5000         ; clients the pooler accepts
default_pool_size = 20         ; actual server connections per db/user

# Step 4 — the contract, per consumer, as a governed data product.
data_product: daily_sales
owner: analytics-platform
slo: { availability: 99.9%, p95_latency_ms: 100, freshness: "<= 15m" }
contracts:
  - consumer: in-app-tile     # high QPS, cache-friendly
    style: REST               # PostgREST: GET /daily_sales_by_region?region=eq.EU
    serving: materialized-mart + response-cache
  - consumer: analyst-explorer
    style: GraphQL            # Hasura: ad-hoc field selection over a replica
    serving: live-replica
  - consumer: partner-export
    style: REST               # keyset-paginated bulk pull
    serving: materialized-mart

Step-by-step trace.

Decision	Before (warehouse only)	After (serving layer)
Hot tile reads	ad-hoc scan of fct_orders	index lookup on a mart + cache
Tenant isolation	app-side WHERE tenant_id	RLS policy, enforced in DB
Concurrency	connection storm → errors	PgBouncer multiplexes onto 20 conns
Contract	one warehouse login	REST + GraphQL per consumer
Ownership	"some table"	versioned data product with an SLO
Freshness	always "live" (and slow)	mart refreshed on schedule

After the rollout, the in-app tile reads the pre-aggregated serving.daily_sales_by_region through PostgREST with row-level security scoping every row to the caller's tenant_id; PgBouncer in transaction mode lets 5,000 clients share 20 real Postgres connections; the analyst explorer hits Hasura live on a read replica; and the whole thing is described by one data-product contract with an owner and an SLO. The warehouse is touched only by the scheduled refresh — never on the request hot path.

Output:

Metric	Before	After
Hot-tile p95 latency	1.5–4 s (warehouse scan)	< 100 ms (mart + cache)
Cross-tenant leak risk	app-code dependent	zero (RLS in the DB)
Max concurrent clients	~ Postgres max_connections	5,000 via pooler
Warehouse load from serving	every request	scheduled refresh only
Contract clarity	tribal knowledge	versioned, owned, SLO-backed

Why this works — concept by concept:

• Serving store, not the warehouse — a materialized mart (or replica) built for point reads absorbs the request traffic, so the analytical engine never handles the hot path. Precompute the hot, freshness-tolerant slices; query the warehouse only on cold paths.
• Row-level security — authorization lives in the data layer as a USING policy keyed on a JWT claim, so no application bug can return another tenant's rows. Governance is enforced where the data is, not where the code is.
• PgBouncer transaction pooling — the pooler multiplexes thousands of short-lived API clients onto a tiny pool of real connections, converting a connection storm into a manageable queue and keeping Postgres alive under concurrency.
• Per-consumer contract — REST for cache-friendly resource access, GraphQL for varied related slices, each with its own serving strategy, all under one versioned data-product contract with an owner and an SLO.
• Cost — one mart refresh per cadence, a handful of pooled DB connections, and a cache absorbing hot keys, versus a per-request warehouse scan. The eliminated cost is the warehouse bill and the outage risk of pointing production traffic at an analytical engine — O(1) cached/index reads versus O(scan) per request.

Design
Topic — design
Design problems on data serving and API layers

Practice →

API integration Topic — api-integration API integration problems on data products and contracts

Practice →

---

2. PostgREST — auto-generated REST over Postgres

A Postgres schema becomes a REST API; row-level security decides which rows a role may read

The mental model in one line: PostgREST is a stateless binary that introspects a Postgres schema and serves it as a full REST API — every table and view becomes a queryable resource with filtering, ordering, and pagination via URL and headers, every function becomes an /rpc/ endpoint, and authorization is delegated entirely to Postgres row-level security: the API translates a request's JWT into a database role, and the database's RLS policies decide which rows and columns that role may touch — so you write policies in SQL once and every endpoint enforces them, instead of scattering if user.can(...) checks across application code. Get the schema and policies right and the API is essentially free; get RLS wrong and you have published your whole warehouse.

How the schema maps to the API.

• Tables and views → resources. orders becomes GET/POST/PATCH/DELETE /orders; a curated view vw_daily_sales becomes a read-only /vw_daily_sales. Exposing views (not raw tables) is the standard way to serve a clean, stable contract over messy internals.
• Functions → RPC. A stored function becomes POST /rpc/<name>, taking JSON arguments — the escape hatch for computed results, multi-step logic, or parameterised aggregations that do not fit a plain resource.
• Filtering and ordering via URL. ?status=eq.paid&order=created_at.desc maps to WHERE status = 'paid' ORDER BY created_at DESC. Operators (eq, gt, like, in, is) are spelled in the query string.
• The dedicated schema. You expose a separate api schema of views and functions, keeping raw tables private. The exposed surface is a deliberate contract, not your internal model.

Row-level security — the authorization model.

• The role swap. PostgREST authenticates with a low-privilege anonymous/authenticator role, reads the JWT, and SET ROLEs to the role named in the token. All subsequent SQL runs as that role.
• Policies do the filtering. ENABLE ROW LEVEL SECURITY plus a CREATE POLICY ... USING (...) clause means every SELECT is silently filtered to the rows the policy admits — tenant isolation, ownership, and visibility all live in SQL.
• JWT claims in policies. current_setting('request.jwt.claims', true)::json->>'tenant_id' pulls a claim into the policy, so the same policy scopes every caller to their own data without any per-request app code.
• Column privileges. Standard GRANT SELECT (col_a, col_b) controls which columns a role sees; combined with RLS you get row and column governance from the database.

Reads: embedding, filtering, pagination.

• Resource embedding. GET /orders?select=*,customers(name) follows the foreign key and nests the related customers row — one request, joined server-side, no client-side stitching.
• Keyset pagination. For deep result sets, filter on the last-seen key (?id=gt.<last>&order=id&limit=50) rather than offset, so page 10,000 costs the same as page 1.
• Range / Content-Range. PostgREST also supports Range headers and returns Content-Range with the total count, useful for UI paginators — but keyset is what keeps deep pages fast.

The failure modes senior engineers pre-empt.

• RLS forgotten = open data. A table exposed without ENABLE ROW LEVEL SECURITY (or with a permissive default) returns everything to everyone who can reach the endpoint. Mitigation: default-deny; enable RLS on every exposed relation and test with a low-privilege token.
• Offset pagination at depth. ?offset=500000 makes Postgres count and discard half a million rows per request. Mitigation: keyset pagination on an indexed key.
• N+1 via naive embedding. Deeply nested embeds or client loops that fetch each related resource separately re-introduce N+1. Mitigation: embed in one request; expose a purpose-built view for the exact shape.

Common interview probes on PostgREST.

• "How does PostgREST do authorization?" — it maps the JWT to a DB role and lets row-level security policies filter every query.
• "Would you expose raw tables?" — no; expose an api schema of views/functions as a deliberate contract.
• "How do you paginate deep result sets?" — keyset pagination on an indexed key, not offset.
• "How do you return related data in one call?" — resource embedding via foreign keys (select=*,related(...)).

Worked example — expose a curated view with a row-level-security policy

Detailed explanation. The canonical PostgREST setup: a dedicated api schema, a view over the curated data, RLS scoping rows to the caller's tenant, and a role the JWT selects. Build a multi-tenant daily_sales endpoint that only ever returns the caller's own rows.

• Exposed surface. api.daily_sales view (not the raw mart).
• Policy. tenant_id must equal the JWT's tenant_id claim.
• Role. web_user, granted SELECT on the view.

Question. Expose daily_sales as a REST resource that enforces tenant isolation in the database, so no request can read another tenant's rows.

Input.

Piece	Value
Exposed view	api.daily_sales
RLS predicate	tenant_id = jwt.tenant_id
Role	web_user (from JWT role claim)
Endpoint	GET /daily_sales?region=eq.EU

Code.

-- 1. A dedicated api schema and a curated view (the contract, not raw tables).
CREATE SCHEMA api;
CREATE VIEW api.daily_sales
  WITH (security_invoker = true) AS      -- run RLS as the CALLER, not the view owner
SELECT order_date, region, tenant_id, orders, revenue_cents
FROM serving.daily_sales_by_region;

-- 2. Enable RLS on the underlying table and scope reads to the caller's tenant.
ALTER TABLE serving.daily_sales_by_region ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_read ON serving.daily_sales_by_region
  FOR SELECT
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

-- 3. A web role the JWT selects; grant it only what the contract needs.
CREATE ROLE web_user NOLOGIN;
GRANT USAGE ON SCHEMA api TO web_user;
GRANT SELECT ON api.daily_sales TO web_user;
GRANT SELECT ON serving.daily_sales_by_region TO web_user;

### The request: PostgREST maps the JWT role to web_user and applies the policy.
GET /daily_sales?region=eq.EU&order=order_date.desc&limit=30
Authorization: Bearer <jwt with {"role":"web_user","tenant_id":"acme"}>

Step-by-step explanation.

1. The api.daily_sales view is the only thing clients see; the raw serving.daily_sales_by_region stays private. security_invoker = true makes the view run with the caller's privileges, so the caller's RLS policy applies rather than the view owner's.
2. ENABLE ROW LEVEL SECURITY plus the tenant_read policy means every SELECT is filtered to rows where tenant_id matches the JWT's tenant_id claim — enforced by Postgres, unbypassable by any request shape.
3. PostgREST reads the JWT, SET ROLE web_user, and runs the query; because web_user has only SELECT on the view, it cannot write, cannot see other schemas, and cannot escape the policy.
4. The URL ?region=eq.EU&order=order_date.desc&limit=30 becomes WHERE region='EU' ORDER BY order_date DESC LIMIT 30 — and then the RLS predicate is AND-ed on, so even a caller who omits a tenant filter only ever gets their own rows.
5. Swap the token's tenant_id to globex and the identical URL returns globex's rows and nothing of acme's — one policy, enforced in SQL, replaces every per-endpoint authorization check the app would otherwise carry.

Output.

Request (JWT tenant)	URL	Rows returned
acme	/daily_sales?region=eq.EU	only acme's EU rows
globex	/daily_sales?region=eq.EU	only globex's EU rows
acme (no region filter)	/daily_sales	only acme's rows (all regions)
anonymous (no role)	/daily_sales	denied (403)

Rule of thumb. Expose views from a dedicated api schema, enable row-level security on the underlying relation, and let a JWT-selected role plus a USING policy do authorization in SQL. Never expose raw tables, and always test with a low-privilege token to prove the policy actually filters.

Worked example — resource embedding across a foreign key

Detailed explanation. The feature that makes PostgREST feel like a real API rather than a table dump is resource embedding: declare a foreign key and PostgREST will nest related rows in a single request, doing the join server-side. Serve an order with its customer and its line items in one call.

• The keys. orders.customer_id → customers.id; order_items.order_id → orders.id.
• The request. select=*,customers(name),order_items(sku,qty).
• The result. One JSON document, joined in Postgres, no client stitching.

Question. Return each order with its customer name and its line items in a single request, avoiding a client-side N+1.

Input.

Relationship	Direction	Embed syntax
order → customer	many-to-one (FK on orders)	customers(name)
order → items	one-to-many (FK on items)	order_items(sku,qty)
filter	on the parent	?status=eq.paid

Code.

### One request embeds a to-one (customer) and a to-many (items) relationship.
GET /orders?status=eq.paid&select=id,total_cents,customers(name),order_items(sku,qty)&limit=2
Authorization: Bearer <jwt>

// Response — joined server-side; the client makes ONE round-trip, not N+1.
[
  {
    "id": 1001,
    "total_cents": 4200,
    "customers": { "name": "Acme Retail" },
    "order_items": [
      { "sku": "WIDGET-1", "qty": 3 },
      { "sku": "GADGET-9", "qty": 1 }
    ]
  },
  {
    "id": 1002,
    "total_cents": 990,
    "customers": { "name": "Globex" },
    "order_items": [ { "sku": "WIDGET-1", "qty": 1 } ]
  }
]

Step-by-step explanation.

1. PostgREST reads the declared foreign keys to know how to join: orders.customer_id → customers.id is many-to-one, so customers(name) embeds a single object; order_items.order_id → orders.id is one-to-many, so order_items(...) embeds an array.
2. The select= list controls the exact shape and the exact columns — customers(name) returns only the name, not the whole customer row, so the response carries only what the client asked for.
3. The ?status=eq.paid filter is applied to the parent orders, and the embeds are resolved for exactly the matching parents — the entire nested document is assembled in one SQL statement inside Postgres.
4. This is the antidote to the client-side N+1: a naive client would fetch the orders, then loop issuing one /customers?id=eq.X per order. Embedding collapses that into a single round-trip with a single server-side join.
5. The senior caution: embedding is powerful but bounded — deeply nested or fan-out-heavy embeds can generate expensive joins, so for hot paths you often expose a purpose-built view shaped exactly for the response instead of embedding at request time.

Output.

Approach	Round-trips	Server work
Client loop (N+1)	1 + N	N separate queries
Resource embedding	1	1 joined query
Purpose-built view	1	1 pre-shaped query
(hot path)	1	pre-joined + cached

Rule of thumb. Use resource embedding to return related data in one round-trip instead of a client-side N+1 loop, but keep embeds shallow and, for hot paths, serve a purpose-built view shaped for the response. Declared foreign keys are what make embedding possible — model them.

Worked example — keyset pagination for deep result sets

Detailed explanation. The pagination bug that surfaces in production is offset at depth: ?offset=500000&limit=50 forces Postgres to scan and throw away half a million rows on every request. Keyset (a.k.a. seek) pagination filters on the last-seen key instead, so any page costs the same. Convert an offset paginator to keyset.

• The anti-pattern. ?limit=50&offset=N — cost grows linearly with N.
• The fix. Order by an indexed key and filter ?<key>=gt.<last_seen>.
• The index. A B-tree on the ordering key makes each page an index range scan.

Question. Replace offset pagination on a large orders feed with keyset pagination that stays O(page size) at any depth.

Input.

Aspect	Offset	Keyset
Request	?limit=50&offset=500000	?id=gt.<last>&order=id&limit=50
Cost at page P	O(P × size)	O(size)
Needs	nothing	an index on the key
Stable under inserts	no (rows shift)	yes

Code.

-- Precondition: an index on the ordering key so each page is a range scan.
CREATE INDEX ON api.orders (id);

### Page 1 — no cursor yet, just order + limit.
GET /orders?order=id.asc&limit=50
Authorization: Bearer <jwt>
# ... client reads the last row's id, say 1050, and uses it as the cursor:

### Page 2 — seek past the last id instead of counting an offset.
GET /orders?id=gt.1050&order=id.asc&limit=50

### Page 10,000 — SAME cost: it seeks to the key, no rows are scanned-and-discarded.
GET /orders?id=gt.799950&order=id.asc&limit=50

Step-by-step explanation.

1. The index on id is the precondition: it lets Postgres seek directly to a key and read the next 50 rows as a range scan, instead of walking the table.
2. Page 1 is just order=id.asc&limit=50. The client records the id of the last row it received (the cursor) — no page numbers, no offsets.
3. Page 2 requests id=gt.1050 — "give me the 50 rows after id 1050." Postgres uses the index to jump straight there; it never touches the first 1,050 rows.
4. Page 10,000 with id=gt.799950 costs exactly the same as page 2, because seeking to a key is O(log n) index navigation plus reading 50 rows — independent of how deep the page is. Offset pagination would have scanned and discarded ~800k rows here.
5. Keyset is also stable under concurrent inserts: a new row does not shift the meaning of "after id 1050," so a paginating client never skips or double-reads rows the way offset pagination does when the underlying data changes mid-scroll.

Output.

Page	Offset cost	Keyset cost
1	50 rows	50 rows
100	~5,000 scanned	50 rows
10,000	~500,000 scanned	50 rows
under inserts	rows skip/repeat	stable

Rule of thumb. Paginate deep feeds by seeking on an indexed key (?key=gt.<last>&order=key&limit=N), never by offset. Keyset keeps every page O(page size) and stays correct while the underlying data changes — the two properties offset pagination structurally cannot provide.

Senior interview question on PostgREST and row-level security

A senior interviewer might ask: "You need a multi-tenant REST API over a curated Postgres serving store with zero bespoke backend code. Design it with PostgREST: which schema you expose, how authorization enforces tenant isolation so no request can leak another tenant's rows, how a client fetches an order with its related data in one call, and how you paginate a large feed without the offset performance cliff."

Solution Using a dedicated api schema, RLS policies, embedding, and keyset pagination

-- 1. Dedicated api schema of views (contract) + private serving tables.
CREATE SCHEMA api;
CREATE VIEW api.orders WITH (security_invoker = true) AS
  SELECT id, tenant_id, customer_id, status, total_cents, created_at
  FROM serving.orders;

-- 2. Tenant isolation via RLS on the underlying table.
ALTER TABLE serving.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON serving.orders FOR SELECT
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

-- 3. Roles: anon can do nothing; web_user reads the contract only.
CREATE ROLE anon NOLOGIN;
CREATE ROLE web_user NOLOGIN;
GRANT USAGE ON SCHEMA api TO web_user;
GRANT SELECT ON api.orders TO web_user;
GRANT SELECT ON serving.orders TO web_user;

-- 4. Index for keyset pagination.
CREATE INDEX ON serving.orders (tenant_id, id);

### 5. One-round-trip read: filter, embed related data, keyset-paginate.
GET /orders?status=eq.paid&select=id,total_cents,customers(name),order_items(sku,qty)&order=id.asc&limit=50
Authorization: Bearer <jwt {"role":"web_user","tenant_id":"acme"}>

### Next page — seek past the last id (no offset):
GET /orders?status=eq.paid&id=gt.1050&order=id.asc&limit=50
Authorization: Bearer <jwt {"role":"web_user","tenant_id":"acme"}>

# 6. PostgREST config — anon is the pre-auth role; JWT selects the real one.
db-uri = "postgres://authenticator@pg-replica/serving"
db-schemas = "api"
db-anon-role = "anon"
jwt-secret = "${PGRST_JWT_SECRET}"
db-max-rows = 1000            # hard cap so no request can pull unbounded data

Step-by-step trace.

Layer	Component	Purpose
Contract	api schema of views	stable surface, raw tables hidden
Authorization	RLS USING on tenant_id	tenant isolation in the DB
Identity	JWT role → SET ROLE	least-privilege per request
Related data	select=*,customers(...)	one round-trip, server-side join
Deep pages	id=gt.<last> + index	O(page size) at any depth
Safety net	db-max-rows cap	no unbounded pulls

After deployment, PostgREST exposes only the api schema; every request authenticates as authenticator, reads the JWT, and SET ROLEs to web_user; the tenant_isolation policy ANDs tenant_id = 'acme' onto every query so acme can never see globex; embedding returns orders with customers and items in one call; and keyset pagination on (tenant_id, id) keeps page 10,000 as cheap as page 1. db-max-rows guarantees no single request can drain the table.

Output:

Metric	Naive backend	PostgREST + RLS
Backend code to maintain	a bespoke service	zero (schema + policies)
Cross-tenant leak risk	per-endpoint checks	zero (one RLS policy)
Related-data fetch	N+1 client loop	1 embedded request
Deep pagination	offset cliff	flat keyset cost
Unbounded pull risk	app-dependent	capped by db-max-rows

Why this works — concept by concept:

• Dedicated api schema — exposing views, not raw tables, makes the API surface a deliberate, stable contract you can evolve without leaking internal columns or breaking clients.
• Row-level security — the USING (tenant_id = jwt.tenant_id) policy is AND-ed onto every query by Postgres, so tenant isolation is enforced in the data layer and no request shape, filter, or bug can bypass it.
• JWT-to-role mapping — PostgREST authenticates as a powerless role, then SET ROLEs to the JWT's role, so every request runs at least privilege and column/table grants are honoured automatically.
• Embedding + keyset pagination — declared foreign keys let one request return joined related data, and seeking on an indexed key keeps deep pages O(page size) and stable under inserts — the two performance properties a serving API needs.
• Cost — no bespoke backend to run, index-scan reads on a serving store, and a hard row cap, versus a hand-written service plus scattered authorization checks. The eliminated cost is an entire microservice per dataset — O(schema) to publish a governed API instead of O(engineers) to build one.

API integration
Topic — api-integration
API integration problems on REST endpoints and pagination

Practice →

Data validation Topic — data-validation Data validation problems on access control and row filtering

Practice →

---

3. Hasura — instant GraphQL over the warehouse

Track a table, set a permission, and a governed GraphQL API plus live subscriptions appear

The mental model in one line: Hasura is a metadata-driven engine that sits over Postgres — and, through connectors, over Snowflake, BigQuery, and other sources — and compiles a declarative GraphQL schema plus permissions into a single efficient SQL query per request, so you track a table to expose it, define per-role row/column permissions as boolean rules, wire relationships from foreign keys, and get queries, aggregations, and live subscriptions for free — the analytics-serving counterpart to PostgREST, trading REST's URL simplicity for GraphQL's one-round-trip, client-shaped, relationship-aware access. You describe what is exposed and who may see it in metadata; Hasura generates the resolver and the SQL.

What Hasura generates from metadata.

• Tracking a table → query fields. Tracking daily_sales exposes daily_sales (rows), daily_sales_by_pk (single row), and daily_sales_aggregate (count/sum/avg) with a rich where, order_by, limit, and offset argument set — all compiled to one SQL query.
• Relationships → nested fields. An object relationship (many-to-one) or array relationship (one-to-many), derived from a foreign key or defined manually, lets a query traverse related data in the same request — Hasura joins server-side.
• Aggregations. _aggregate fields give count, sum, avg, max, min grouped by the query's filters — the analytics-serving workhorse, computed in the database.
• Multiple data sources. Hasura can expose several sources under one GraphQL endpoint, so a query can stitch a Postgres serving store and a Snowflake source behind a single contract.

Permissions — governance as declarative rules.

• Per-role, per-operation. For each role (user, analyst, partner) and each operation (select, insert, update, delete) you declare a rule; absent a rule, the operation is denied — default-deny.
• Row rule (filter). A boolean expression, e.g. {"tenant_id": {"_eq": "X-Hasura-Tenant-Id"}}, is AND-ed onto every query for that role — the GraphQL analogue of an RLS USING clause, keyed on a session variable from the JWT.
• Column rule. A role sees only the columns you list, so PII or internal fields never leave the engine for that role.
• Limits. Per-role limit caps and aggregation permissions bound how much a single query can pull, closing the "unbounded query" hole.

Subscriptions — live analytics without polling.

• What they are. A GraphQL subscription opens a websocket; Hasura re-runs the underlying query efficiently and pushes the new result whenever the data changes — a live KPI tile with no client polling loop.
• The cost model. Subscriptions are multiplexed: many identical subscriptions share one underlying query, but distinct subscriptions each cost a live query, so fan-out is the thing to bound.
• Where they fit. Live ops dashboards, "orders in the last minute," a freshness indicator — anything where seconds of latency matter and the query is cheap enough to re-run.

Extending beyond the database.

• Actions. Wrap a custom HTTP handler as a GraphQL field for business logic (a computed forecast, a write with side effects) the database cannot express.
• Remote schemas. Stitch another GraphQL API into the same endpoint, so consumers see one unified graph.
• Event triggers. Fire a webhook on insert/update/delete for downstream reactions — the write-side complement to subscriptions.

The failure modes senior engineers pre-empt.

• Permission holes. A role with no row filter (or an overly broad one) exposes every row. Mitigation: default-deny, a mandatory tenant filter on every role, and reviewing generated permissions as code in metadata.
• Unbounded queries. A client asks for a million rows or a deeply nested explosion. Mitigation: per-role limit, disabling offset at depth, query depth/complexity limits, and aggregation caps.
• Subscription fan-out cost. Thousands of distinct live subscriptions each run a query. Mitigation: multiplex by designing shared subscription shapes, cap concurrent subscriptions, and reserve subscriptions for genuinely live tiles.

Common interview probes on Hasura.

• "How does Hasura authorize?" — declarative per-role row/column permissions, keyed on JWT session variables, compiled into the SQL WHERE.
• "How is it different from PostgREST?" — GraphQL contract, multi-source, relationships and aggregations as first-class fields, live subscriptions.
• "How do you serve live data?" — GraphQL subscriptions over websockets, bounded by fan-out.
• "How do you add logic the DB can't express?" — Actions (custom handlers) or Remote Schemas.

Worked example — track a table, define a permission, query with a relationship

Detailed explanation. The core Hasura loop: track a table, add a role permission with a tenant row filter and a column allow-list, then run a GraphQL query that traverses a relationship and aggregates — all compiled to one SQL statement. Expose daily_sales with a region relationship for a user role.

• Track. daily_sales and regions.
• Permission. user role: row filter on tenant_id, columns limited, limit: 100.
• Query. rows + related region + an aggregate, in one request.

Question. Configure a tenant-scoped select permission on daily_sales and write a GraphQL query returning filtered rows, a related field, and a sum aggregate.

Input.

Piece	Value
Tracked tables	daily_sales, regions
Role	user
Row filter	tenant_id = X-Hasura-Tenant-Id
Columns	order_date, region, revenue_cents
Query	rows + region_ref + _aggregate

Code.

# Hasura metadata — a select permission for the `user` role (default-deny otherwise).
- table: { schema: serving, name: daily_sales }
  select_permissions:
    - role: user
      permission:
        columns: [order_date, region, revenue_cents]     # column allow-list
        filter:                                           # row rule (AND-ed onto every query)
          tenant_id: { _eq: X-Hasura-Tenant-Id }          # session var from the JWT
        limit: 100                                        # cap rows per query
        allow_aggregations: true
  object_relationships:
    - name: region_ref
      using: { foreign_key_constraint_on: region }        # many-to-one to `regions`

# One GraphQL query: filtered rows + related region + an aggregate, one round-trip.
query RegionSales {
  serving_daily_sales(
    where: { region: { _eq: "EU" } }
    order_by: { order_date: desc }
    limit: 30
  ) {
    order_date
    revenue_cents
    region_ref { manager }              # traverses the object relationship
  }
  serving_daily_sales_aggregate(where: { region: { _eq: "EU" } }) {
    aggregate { sum { revenue_cents } }  # computed in SQL, not the client
  }
}

Step-by-step explanation.

1. The select_permissions block is the governance: it lists the columns the user role may read and a filter that Hasura ANDs onto every query. The X-Hasura-Tenant-Id session variable comes from the JWT, so each caller is scoped to their tenant — the GraphQL analogue of RLS.
2. Because no permission is declared for other operations, insert/update/delete are denied by default; a role sees only what you explicitly grant — default-deny, the safe posture.
3. The object_relationship region_ref is derived from the region foreign key, so the query can nest region_ref { manager } and Hasura resolves it as a join in the same SQL query, not a second round-trip.
4. serving_daily_sales_aggregate computes sum(revenue_cents) in the database, honouring the same permission filter — the client never pulls raw rows to sum them, which is the whole point of analytics serving.
5. Critically, even though the query only asks where: {region: {_eq: "EU"}}, the compiled SQL is WHERE region='EU' AND tenant_id='<caller>' LIMIT 30 — the permission filter and the limit are non-negotiable, so a caller cannot widen their own scope.

Output.

Query part	Compiles to	Governed by
daily_sales(where: EU)	WHERE region='EU' AND tenant_id=? LIMIT 30	row filter + limit
region_ref { manager }	join to regions	column allow-list
_aggregate { sum }	sum(revenue_cents) in SQL	allow_aggregations
other operations	denied	default-deny

Rule of thumb. In Hasura, governance is metadata: a per-role filter keyed on a JWT session variable, a column allow-list, and a limit, all AND-ed into the generated SQL. Track the minimum, grant the minimum, and let relationships and aggregates keep the work in the database.

Worked example — a live KPI subscription

Detailed explanation. The feature that separates Hasura from a plain query API is live subscriptions: swap query for subscription and Hasura pushes updates over a websocket whenever the result changes — a live KPI tile with no polling. Build a "revenue in the last 5 minutes" subscription and reason about its cost.

• The tile. Rolling 5-minute revenue for the caller's tenant.
• The mechanism. A subscription re-runs an efficient query and pushes deltas.
• The cost. Identical subscriptions multiplex; distinct ones each cost a live query.

Question. Write a subscription for a live rolling-revenue KPI and explain how to keep its fan-out cost bounded.

Input.

Aspect	Choice
Operation	subscription (websocket)
Shape	rolling 5-min revenue aggregate
Scope	tenant (permission filter)
Cost control	multiplex identical shapes; cap concurrency

Code.

# A live KPI: Hasura pushes a new value whenever the aggregate changes.
subscription LiveRevenue {
  serving_orders_aggregate(
    where: { created_at: { _gte: "now() - interval '5 minutes'" } }
  ) {
    aggregate {
      sum { total_cents }
      count
    }
  }
}

# Cost model — why the SHAPE matters more than the count of subscribers.

100 dashboards open the EXACT same LiveRevenue subscription
  -> Hasura multiplexes: ONE underlying query, one result fanned to 100 sockets.  cheap.

100 dashboards each open a DISTINCT subscription (per-user filter, per-region)
  -> ~100 distinct underlying live queries.  expensive — this is the fan-out trap.

Mitigation:
  - design shared subscription shapes (tenant-level, not user-level) so they multiplex
  - cap max concurrent subscriptions per connection/role
  - reserve subscriptions for genuinely live tiles; poll/cache the rest

Step-by-step explanation.

1. Changing the operation keyword from query to subscription is the entire API change: the same field, the same permission filter, now delivered over a websocket that pushes a fresh value whenever the underlying data changes — no client polling loop.
2. The permission filter from the role still applies, so LiveRevenue is automatically scoped to the caller's tenant; you do not re-specify authorization for the live path.
3. Hasura multiplexes identical subscriptions: if 100 dashboards open the byte-identical LiveRevenue, one underlying query feeds all 100 sockets. This is why shared shapes are cheap and the subscriber count alone is not the cost driver.
4. The fan-out trap is distinct subscriptions: a per-user or per-arbitrary-filter subscription cannot multiplex, so 100 distinct shapes mean ~100 live queries. Designing subscriptions at the tenant level (shared) instead of per-user keeps them multiplexable.
5. The senior discipline is to reserve subscriptions for tiles that genuinely need sub-second liveness and to serve everything else with cached queries — a live socket is far more expensive than a cached REST/GraphQL read, so you spend it deliberately.

Output.

Scenario	Underlying live queries	Cost
100 identical subscriptions	1 (multiplexed)	cheap
100 distinct subscriptions	~100	expensive
tenant-level shared shape	1 per tenant	bounded
cached query instead	0 live	cheapest

Rule of thumb. Reach for a subscription only when a tile must be live to the second, and design its shape to be shared (tenant-level) so Hasura multiplexes many subscribers onto one query. Distinct per-user subscriptions are the fan-out trap; cap concurrency and cache everything that does not need a live socket.

Worked example — extending with an Action for logic the database can't express

Detailed explanation. Not everything is a query. When a consumer needs a computed result the database cannot produce — a forecast, a call to an ML service, a write with external side effects — you expose it as a Hasura Action: a custom GraphQL field backed by an HTTP handler, with the same permission model. Add a revenueForecast field.

• The gap. A next-7-day forecast requires a model call, not SQL.
• The Action. A GraphQL field revenueForecast(region) → an HTTP handler.
• The governance. Same role permissions and session variables as the rest of the graph.

Question. Expose a revenueForecast query that calls an external forecasting service while keeping it inside the governed GraphQL graph.

Input.

Piece	Value
Field	revenueForecast(region): ForecastResult
Backing	HTTP handler (POST /forecast)
Auth	forwards session vars (tenant/role)
Permission	analyst role only

Code.

# Action definition — a custom GraphQL field backed by an HTTP handler.
actions:
  - name: revenueForecast
    definition:
      kind: synchronous
      handler: http://forecast-svc:8080/forecast
      forward_client_headers: true          # pass the JWT/session vars to the handler
    permissions:
      - role: analyst                        # only analysts may call it
type Query {
  revenueForecast(region: String!, horizon_days: Int!): ForecastResult
}
type ForecastResult {
  region: String!
  predicted_cents: [Int!]!
}

# The Action is just another field in the same graph — one endpoint, one contract.
query Forecast {
  revenueForecast(region: "EU", horizon_days: 7) {
    region
    predicted_cents
  }
}

Step-by-step explanation.

1. The Action declares a GraphQL field (revenueForecast) whose resolver is an external HTTP handler, so business logic the database cannot express joins the graph as a first-class field — consumers see one contract, not "GraphQL plus a separate REST call."
2. forward_client_headers: true passes the caller's JWT/session variables to the handler, so the forecasting service can enforce the same tenant scoping and the Action stays inside the governance model.
3. permissions: [{role: analyst}] restricts the field to the analyst role — Actions honour the same per-role permission system as tracked tables, so there is no privilege escape hatch.
4. From the client's perspective, revenueForecast is queried exactly like any other field, in the same round-trip and endpoint as the tracked-table queries — the custom logic is invisible plumbing.
5. The senior framing: use Actions for the thin layer of logic and side effects the database genuinely cannot do, and keep everything expressible as data in tracked tables/views — Actions are an extension point, not a place to rebuild a backend.

Output.

Concern	Tracked table	Action
Backing	SQL query	external HTTP handler
Use for	data reads/aggregates	computed logic, side effects
Governance	permissions in metadata	permissions + forwarded headers
Client view	a field	the same kind of field

Rule of thumb. Keep everything you can as governed tracked tables/views, and reach for Actions (or Remote Schemas) only for the thin slice of computed logic or side effects the database cannot express — forwarding session variables so the extension stays inside the same permission model.

Senior interview question on Hasura permissions and live serving

A senior interviewer might ask: "Stand up a GraphQL API over a multi-tenant Postgres serving store with Hasura. Cover how you track and expose only what's needed, how per-role permissions enforce tenant isolation and column masking, how a client fetches related data and aggregates in one query, how you serve a live KPI without polling, and how you stop a client from pulling unbounded data or melting the engine with subscription fan-out."

Solution Using tracked views, per-role permissions, relationships, aggregates, and bounded subscriptions

# 1. Expose a curated VIEW (not raw tables) and grant a tenant-scoped, capped permission.
- table: { schema: serving, name: vw_orders }
  select_permissions:
    - role: user
      permission:
        columns: [id, region, status, total_cents, created_at]   # no PII columns
        filter:  { tenant_id: { _eq: X-Hasura-Tenant-Id } }      # tenant isolation
        limit: 100                                                # per-query cap
        allow_aggregations: true
  object_relationships:
    - name: customer
      using: { foreign_key_constraint_on: customer_id }

# 2. One query: filtered rows + related customer + aggregate — all governed, one SQL.
query TenantOrders {
  serving_vw_orders(where: { status: { _eq: "paid" } },
                    order_by: { created_at: desc }, limit: 30) {
    id total_cents
    customer { name }                      # relationship join
  }
  serving_vw_orders_aggregate(where: { status: { _eq: "paid" } }) {
    aggregate { sum { total_cents } count }
  }
}

# 3. A live KPI tile — SHARED (tenant-level) shape so Hasura multiplexes subscribers.
subscription LivePaidRevenue {
  serving_vw_orders_aggregate(
    where: { created_at: { _gte: "now() - interval '5 minutes'" } }
  ) { aggregate { sum { total_cents } } }
}

# 4. Guardrails: cap query cost and subscription fan-out at the engine.
config:
  query_depth_limit: 6                # reject deeply nested explosions
  node_limit: 1000                    # max nodes a query may request
  max_concurrent_subscriptions: 50    # per connection — bound fan-out
  # role `user` cannot use `offset` beyond a page; keyset via cursor args instead

Step-by-step trace.

Layer	Component	Purpose
Surface	tracked vw_orders	contract, raw tables hidden
Authorization	per-role filter on tenant_id	tenant isolation, in SQL
Column masking	columns allow-list	PII never leaves the engine
Related + aggregate	relationship + _aggregate	one round-trip, DB-side compute
Live tile	shared subscription	multiplexed, bounded
Guardrails	depth/node/subscription limits	no unbounded or explosive queries

After deployment, Hasura exposes only vw_orders; the user role's filter scopes every query and subscription to the caller's tenant and the columns list masks PII; one query returns paid orders, their customers, and a revenue sum compiled to a single SQL statement; the LivePaidRevenue subscription is a shared tenant-level shape so many dashboards multiplex onto one underlying query; and depth/node/subscription limits close the unbounded-query and fan-out holes.

Output:

Metric	Ungoverned GraphQL	Hasura (governed)
Tenant isolation	app-dependent	per-role filter in SQL
PII exposure	whole row	column allow-list
Related-data fetch	N+1 resolvers	one SQL join
Live KPI	client polling	multiplexed subscription
Unbounded/explosive query	possible	capped (depth/node/limit)

Why this works — concept by concept:

• Tracked views — exposing curated views rather than raw tables makes the GraphQL surface a deliberate contract, and column allow-lists keep PII and internal fields out of the graph entirely for a given role.
• Per-role permission filter — a boolean filter keyed on a JWT session variable is AND-ed into every generated query and subscription, so tenant isolation is enforced in the compiled SQL, not in fragile resolver code.
• Relationships and aggregates — foreign-key relationships and _aggregate fields compile to server-side joins and SQL aggregation, so a client gets related data and computed metrics in one round-trip without pulling raw rows.
• Shared subscriptions + guardrails — tenant-level subscription shapes multiplex many subscribers onto one live query, and depth/node/concurrency limits bound both query complexity and subscription fan-out, the two ways a GraphQL engine gets overloaded.
• Cost — one compiled SQL query per request, multiplexed live queries, and hard complexity caps, versus hand-written resolvers plus N+1 fetches. The eliminated cost is an entire GraphQL backend and its authorization code — declared once in metadata, enforced by the engine.

Real-time analytics
Topic — real-time-analytics
Real-time analytics problems on live queries and subscriptions

Practice →

API integration Topic — api-integration API integration problems on GraphQL schemas and permissions

Practice →

---

4. GraphQL for analytics serving — schema, N+1, caching

One batched load beats N round-trips; persisted queries and cache hints keep the warehouse quiet

The mental model in one line: serving analytics over GraphQL well is three disciplines — designing a schema where metrics and dimensions are fields and filters are arguments, defeating the N+1 problem with the DataLoader batch-and-cache pattern so a nested query issues one query per level instead of one per row, and controlling cost with persisted queries plus response caching (@cacheControl) so a repeated dashboard query hits a cache instead of the backend — because a GraphQL query's flexibility is exactly what makes it dangerous at serving scale if you let it fan out or run uncached. Whether you hand-write resolvers or use Hasura's compiler, these three are what separate a demo from a data serving layer that survives production traffic.

Schema design for analytics serving.

• Metrics and dimensions as fields. Model a SalesMetric type with dimension fields (region, date) and measure fields (revenue, orders); the client selects exactly the dimensions and measures it needs.
• Filters and grouping as arguments. sales(region: "EU", from: "...", groupBy: DAY) puts the filter and grain in arguments, so one field serves many slices instead of a field per report.
• Connections for pagination. Use the cursor-connection pattern (edges/pageInfo/endCursor) so pagination is keyset-based and consistent across the schema.
• Deliberate depth. Analytics schemas should be shallow and wide — many fields, limited nesting — so no query can request an expensive deep traversal.

The N+1 problem and DataLoader.

• The problem. A query for 50 orders each resolving order.customer naively fires 1 query for the orders + 50 queries for customers — the N+1 explosion that makes GraphQL infamous.
• DataLoader. A per-request utility that batches all the keys requested within a tick into one WHERE id IN (...) query and caches results by key for the rest of the request — turning 1+N into 1+1.
• Batch function. You write a function keys -> rows that fetches all keys at once and returns them in key order; DataLoader handles the batching and per-request cache.
• Per-request scope. A DataLoader lives for one request so its cache never leaks across users — critical for correctness and authorization.

Caching and cost control.

• Persisted queries. The client sends a query hash instead of the full text; the server maps it to a pre-registered query. This shrinks payloads, allows GET-based HTTP/CDN caching of GraphQL, and lets you allow-list exactly which queries production may run.
• Response caching + @cacheControl. Field-level @cacheControl(maxAge: N, scope: PUBLIC|PRIVATE) hints let a caching layer store and reuse responses; the most restrictive hint in the query wins.
• Cache scope and keys. PRIVATE (per-viewer) vs PUBLIC (shared) is a governance decision: caching a tenant-scoped response as PUBLIC would leak it. The cache key must include the identity that changes the result.
• Complexity limits. Assign a cost to fields and reject queries over a budget, so an adversarial or accidental expensive query is refused before it runs.

The failure modes senior engineers pre-empt.

• Unbounded nesting/complexity. A deeply nested or wide query multiplies cost. Mitigation: depth limits, per-field complexity scoring, and a rejected-over-budget rule.
• Cache key on the wrong scope. Caching a per-tenant result as PUBLIC, or omitting the viewer from the key, leaks one tenant's data to another. Mitigation: PRIVATE scope for viewer-specific data; include tenant/role in the key.
• Over-fetching wide rows. Selecting every measure/dimension when the tile needs two wastes warehouse work. Mitigation: encourage narrow selections; charge complexity for wide ones.

Common interview probes on GraphQL serving.

• "What's the N+1 problem and how do you fix it?" — naive per-row resolves; fix with DataLoader batch + per-request cache.
• "How do you cache GraphQL when requests are POSTs?" — persisted queries (hash → GET/CDN) plus @cacheControl response caching.
• "How do you stop an expensive query?" — depth + complexity limits, reject over budget.
• "What scope do you cache a tenant response at?" — PRIVATE / include identity in the key; never PUBLIC for scoped data.

Worked example — a resolver with a DataLoader that batches dimension lookups

Detailed explanation. The canonical N+1 fix: a list field whose child resolver would fire one query per row, rewired through a DataLoader so all the child keys are batched into a single IN (...) query. Resolve a list of orders each needing its customer, going from 1+N to 1+1.

• The N+1. orders (1 query) then order.customer per order (N queries).
• The loader. customerLoader batches all customer_ids into one query.
• The scope. A fresh loader per request so its cache never crosses users.

Question. Implement a per-request DataLoader so resolving customer for a list of orders issues one batched query instead of N.

Input.

Aspect	Naive	DataLoader
orders query	1	1
customer queries	N (one per order)	1 (id IN (...))
total round-trips	1 + N	1 + 1
cache scope	none	per-request

Code.

import DataLoader from "dataloader";

// Batch function: given many customer ids, fetch them ALL in one query,
// then return rows in the SAME order as the requested keys (DataLoader contract).
function makeCustomerLoader(db) {
  return new DataLoader(async (ids) => {
    const rows = await db.query(
      "SELECT id, name FROM serving.customers WHERE id = ANY($1)", [ids]);
    const byId = new Map(rows.map((r) => [r.id, r]));
    return ids.map((id) => byId.get(id) || null);   // align to key order
  });
}

// A FRESH loader per request → its cache never leaks across users/tenants.
function context(req) {
  return { loaders: { customer: makeCustomerLoader(db) }, tenant: req.tenant };
}

const resolvers = {
  Query: {
    orders: (_p, args, ctx) =>
      db.query("SELECT id,total_cents,customer_id FROM serving.orders " +
               "WHERE tenant_id=$1 ORDER BY id LIMIT $2", [ctx.tenant, args.limit]),
  },
  Order: {
    // Instead of a query PER order, .load() enqueues the key; DataLoader
    // batches all keys in the tick into ONE `id = ANY(...)` query.
    customer: (order, _a, ctx) => ctx.loaders.customer.load(order.customer_id),
  },
};

Step-by-step explanation.

1. The orders resolver issues one query for the list. Then GraphQL resolves the customer field once per order — the exact spot where the N+1 explosion happens if each call queries the database directly.
2. ctx.loaders.customer.load(order.customer_id) does not query immediately; it enqueues the key. DataLoader collects every key requested within the same event-loop tick and calls the batch function once with all of them.
3. The batch function runs a single WHERE id = ANY($1) query for all customer ids at once, then returns the rows in the same order as the keys — DataLoader's contract — so each .load() promise resolves to the right customer.
4. DataLoader also caches by key within the request: if two orders share a customer, that customer is fetched once and served from the loader cache — deduplication on top of batching.
5. The loader is created fresh in context(req) per request, so its cache is scoped to one request and one tenant; a shared/global loader would leak one user's cached rows into another's response — a correctness and authorization bug.

Output.

Orders in list	Naive queries	DataLoader queries
1	1 + 1	1 + 1
50	1 + 50	1 + 1
500	1 + 500	1 + 1
shared customers	still N	deduped by cache

Rule of thumb. Wrap every relationship/dimension lookup in a per-request DataLoader so N per-row queries collapse into one batched IN (...) query with per-request caching. Always create the loader fresh per request — a shared loader leaks cached data across users and breaks authorization.

Worked example — persisted queries and a cache hint for a dashboard tile

Detailed explanation. A dashboard tile runs the same query thousands of times a minute. Persisted queries turn it into a cacheable GET keyed on a hash, and @cacheControl lets a caching layer reuse the response for a bounded window — so the backend and warehouse see a trickle, not a flood. Wire both for a "revenue by region" tile.

• Persisted query. Register the query once; clients send its hash.
• HTTP/CDN cache. The hash makes the request a cacheable GET.
• @cacheControl. maxAge bounds staleness; scope decides shared vs per-viewer.

Question. Make a hot dashboard query cacheable with a persisted query and a field-level cache hint, choosing the correct cache scope.

Input.

Piece	Value
Query	revenueByRegion (hot tile)
Transport	persisted-query hash → GET
Freshness	maxAge: 60 (1 min)
Scope	PUBLIC if not viewer-specific; else PRIVATE

Code.

# Schema: field-level cache hints. The MOST RESTRICTIVE hint in a query wins.
type Query {
  revenueByRegion(region: String!): RegionRevenue
    @cacheControl(maxAge: 60, scope: PUBLIC)     # shareable ONLY if not viewer-scoped
}
type RegionRevenue @cacheControl(maxAge: 60) {
  region: String!
  revenue_cents: Int!
}

### Persisted query: client sends a HASH, not the text → cacheable GET at the CDN.
GET /graphql?extensions={"persistedQuery":{"version":1,"sha256Hash":"9f2c...a1"}}&variables={"region":"EU"}

### First hit  -> MISS -> resolves -> stored for 60s (Cache-Control: public, max-age=60)
### Next 60s   -> HIT  -> served from CDN/edge; backend & warehouse untouched

# Scope is a GOVERNANCE decision, not a performance one:
#   PUBLIC  -> one cached entry shared by everyone. ONLY for non-viewer-specific data.
#   PRIVATE -> cached per viewer; key MUST include tenant/role.
#
# Caching a tenant-scoped response as PUBLIC leaks tenant A's data to tenant B.
# Rule: if the RLS/permission filter changes the result, the cache key must too.

Step-by-step explanation.

1. The persisted query replaces the full query text with a registered sha256Hash, so the request becomes a short GET — which a CDN or reverse proxy can cache by URL, recovering the HTTP caching that POST-body GraphQL normally forfeits.
2. Registering queries also allow-lists them: production only runs pre-registered queries, so an attacker cannot submit an arbitrary expensive or probing query — a security benefit on top of caching.
3. @cacheControl(maxAge: 60) tells the caching layer the response is reusable for 60 seconds; the first request is a MISS that resolves and stores, and every request in the next minute is a HIT served without touching the backend or the warehouse.
4. The scope is the dangerous knob: PUBLIC stores one shared entry for everyone, which is only safe if the result does not depend on the viewer. revenueByRegion is safe as PUBLIC only if region revenue is not tenant-scoped; if it is, it must be PRIVATE with the tenant in the cache key.
5. The rule that prevents leaks: if the authorization filter (RLS/Hasura permission) changes the result per caller, the cache key must include that identity — otherwise the first tenant's cached response is served to the next tenant. Most restrictive hint wins, so one PRIVATE/maxAge: 0 field forces the whole response uncached.

Output.

Request in the 60s window	Cache	Backend hit
1st	MISS	yes (resolves)
2nd–Nth	HIT	no
after 60s	MISS (revalidate)	yes
tenant-scoped as PUBLIC	HIT	leaks — bug

Rule of thumb. Turn hot dashboard queries into persisted queries (hash → cacheable GET, allow-listed) and add @cacheControl with a maxAge that matches your freshness budget — but choose scope as a governance decision: PUBLIC only for non-viewer-specific data, PRIVATE with identity in the key for anything the permission filter scopes.

Worked example — bounding cost with depth and complexity limits

Detailed explanation. GraphQL's flexibility lets a client write a query far more expensive than any REST endpoint. The defence is a complexity budget: score each field, sum the query's cost, and reject anything over budget before it executes. Add depth and complexity limits to a serving schema.

• The threat. A deeply nested or wide query that fans out into a huge join/scan.
• Depth limit. Reject queries nested beyond N levels.
• Complexity limit. Assign per-field cost (× list size) and reject over a budget.

Question. Configure depth and complexity limits so an over-expensive analytics query is rejected before execution.

Input.

Guard	Rule	Rejects
Depth	max 6 levels	deep nested traversals
Complexity	sum of field costs ≤ 1000	wide/large-list queries
List multiplier	cost × limit	unbounded limit
Result	400 before execution	never runs

Code.

import depthLimit from "graphql-depth-limit";
import { createComplexityLimitRule } from "graphql-validation-complexity";

const server = new ApolloServer({
  schema,
  validationRules: [
    depthLimit(6),                              // reject nesting deeper than 6
    createComplexityLimitRule(1000, {           // reject total cost > 1000
      scalarCost: 1,
      objectCost: 5,
      listFactor: 10,                           // lists multiply cost by their size
    }),
  ],
});

// Example: this query is REJECTED before execution (cost > budget) —
// a 1000-row list of orders, each expanding items and customer, blows the budget.
// query { orders(limit: 1000) { items { sku } customer { name } } }   // 400, not run

Step-by-step explanation.

1. depthLimit(6) runs at validation time, before any resolver executes, and rejects a query nested beyond six levels — the cheap defence against pathological deep traversals that would otherwise trigger expensive recursive joins.
2. createComplexityLimitRule(1000, ...) assigns a cost to each field: scalars cost 1, objects 5, and lists multiply their subtree cost by a factor, so a large limit on a list of objects with children explodes the score.
3. The query is scored and compared to the budget before execution; if it exceeds 1000 the server returns a 400 and never runs a single resolver — the warehouse is never touched by an over-budget query.
4. The list multiplier is the key insight: an unbounded limit is the most common way a GraphQL query gets expensive, so tying cost to list size forces clients to paginate rather than pull everything.
5. Combined with per-request DataLoaders (batching) and persisted queries (allow-listing), complexity limits give three independent brakes: what queries may run (allow-list), how expensive a query may be (complexity), and how efficiently it runs (batching) — the defence-in-depth a serving layer needs.

Output.

Query	Depth	Cost	Verdict
orders(limit:30){ id }	2	~30	allowed
orders(limit:30){ customer{name} }	3	~180	allowed
orders(limit:1000){ items{sku} customer{name} }	3	>1000	rejected (400)
8-level nested traversal	8	—	rejected (depth)

Rule of thumb. Give a serving GraphQL schema a complexity budget: reject over a depth limit and a per-field cost budget (with lists multiplying cost) before execution, so no adversarial or accidental query can fan out into an expensive scan. Pair it with persisted-query allow-listing and DataLoader batching for defence in depth.

Senior interview question on GraphQL serving performance and cost

A senior interviewer might ask: "Your GraphQL analytics API is hammering the warehouse — nested queries fire hundreds of per-row lookups, the same dashboard query runs uncached thousands of times a minute, and one client submitted a query that scanned everything. Design the fixes: the N+1 solution, a caching strategy that's safe for multi-tenant data, and cost limits — and explain how each keeps the backend quiet without breaking the contract."

Solution Using DataLoader batching, persisted queries with scoped caching, and complexity limits

// 1. N+1 → 1+1: a per-request DataLoader batches every relationship lookup.
function context(req) {
  return {
    tenant: req.tenant,
    loaders: {
      customer: new DataLoader(async (ids) => {
        const rows = await db.query(
          "SELECT id,name FROM serving.customers WHERE id = ANY($1)", [ids]);
        const m = new Map(rows.map((r) => [r.id, r]));
        return ids.map((id) => m.get(id) || null);
      }),
    },
  };
}
const resolvers = {
  Order: { customer: (o, _a, c) => c.loaders.customer.load(o.customer_id) },
};

# 2. Cache safely for multi-tenant: PRIVATE scope, identity in the key.
type Query {
  ordersByRegion(region: String!): [Order!]!
    @cacheControl(maxAge: 30, scope: PRIVATE)   # per-viewer: never shared across tenants
}

// 3. Cost limits + persisted-query allow-list: what may run, and how expensive.
const server = new ApolloServer({
  schema,
  persistedQueries: { ttl: null },              // allow-listed registered queries only
  validationRules: [
    depthLimit(6),
    createComplexityLimitRule(1000, { scalarCost: 1, objectCost: 5, listFactor: 10 }),
  ],
  plugins: [responseCachePlugin({               // cache keyed on identity + query hash
    sessionId: (ctx) => ctx.contextValue.tenant, // tenant in the key → no cross-tenant leak
  })],
});

Step-by-step trace.

Problem	Fix	Effect
N+1 per-row lookups	per-request DataLoader	1+N → 1+1, deduped
Uncached hot query	persisted query + @cacheControl	HIT served from cache
Cross-tenant cache leak	PRIVATE + tenant in key	each tenant cached separately
Runaway expensive query	depth + complexity limit	rejected before execution
Arbitrary client queries	persisted-query allow-list	only registered queries run
Backend load	all of the above	warehouse sees a trickle

After the fixes, every relationship resolves through a per-request DataLoader (one batched query per level, cache scoped to the request); hot queries are persisted and response-cached with PRIVATE scope and the tenant in the key, so a HIT never crosses tenants; depth and complexity limits reject over-budget queries at validation time; and the persisted-query allow-list means production only ever runs registered queries. The warehouse now sees cache misses and batched loads, not a per-row flood.

Output:

Metric	Before	After
Queries per nested request	1 + N	1 + 1
Hot-query backend hits/min	thousands	a handful (cache misses)
Cross-tenant cache leak	possible	impossible (identity in key)
Expensive query	scans everything	rejected (400)
Runnable queries	arbitrary	allow-listed only

Why this works — concept by concept:

• DataLoader batch + per-request cache — enqueuing keys and fetching them in one IN (...) query turns N per-row round-trips into one, and per-request scoping dedupes without ever leaking cached rows across users.
• Persisted queries — a hash-for-text swap makes GraphQL cacheable over GET/CDN and allow-lists exactly which queries production may run, closing both the cache-miss flood and the arbitrary-query attack surface.
• Scoped response caching — @cacheControl with PRIVATE scope and the tenant baked into the cache key means a cached response is reused only for the identity it belongs to, so caching a multi-tenant API cannot leak data.
• Depth + complexity limits — scoring queries and rejecting over budget at validation time stops fan-out before a resolver runs, so no accidental or adversarial query can turn into an expensive warehouse scan.
• Cost — batched loads, cache HITs, and rejected-over-budget queries mean the warehouse handles a trickle of cache misses instead of a per-row flood. The eliminated cost is the runaway backend load a flexible query language invites — O(1) cache/batch versus O(N) per-row round-trips.

Optimization
Topic — optimization
Optimization problems on batching, caching, and N+1

Practice →

Pagination Topic — pagination Pagination problems on cursor connections and page limits

Practice →

---

5. The serving architecture — caching, rate limits, auth, pooling

Front the warehouse with a serving store; the gateway guards it and the pooler keeps it alive under load

The mental model in one line: a production data serving layer is a short pipeline — an API gateway that authenticates and rate-limits, a data API (PostgREST or Hasura) that turns requests into SQL, a connection pooler (PgBouncer) that multiplexes thousands of clients onto a small database pool, and a serving store (read replica or materialized mart) that fronts the warehouse — wrapped in caching tiers (CDN edge, response cache, result cache), where the single most important design decision is precompute-versus-query-live driven by the freshness/latency/cost triangle. Every component exists to keep the analytical engine off the request hot path and to survive concurrency the warehouse never sees in batch.

The serving-layer anatomy.

• API gateway. The front door: terminates TLS, authenticates the JWT, enforces rate limits and quotas per API key/tenant, and can cache responses. It is where cross-cutting concerns live so the data API stays thin.
• Data API. PostgREST or Hasura — compiles the request to one SQL statement against the serving store, applying row-level security/permissions.
• Connection pooler. PgBouncer (or the engine's built-in pool) multiplexes short-lived client connections onto a small, fixed pool of real database connections — the difference between surviving and dying under a connection storm.
• Serving store. A Postgres read replica, a materialized mart, or a fast OLAP/KV store — built for point reads and refreshed from the warehouse on a schedule, never scanned ad-hoc on the hot path.

Precompute vs query-live — the freshness/latency/cost triangle.

• Precompute (materialized mart). Fast and cheap per request, but stale between refreshes and costly to store/refresh. For hot, freshness-tolerant reads (dashboards, tiles).
• Query-live (replica). Fresh and flexible, but slower and more expensive per request. For cold, freshness-critical, or ad-hoc reads.
• The triangle. You cannot maximise freshness, latency, and cost at once — pick two. A stated SLO (e.g. p95 < 100 ms, freshness ≤ 15 min) resolves the tension per data product.
• Invalidation. Precomputed data must be invalidated when the mart refreshes — cache TTLs aligned to the refresh cadence, or explicit purge after a refresh completes.

Caching tiers.

• CDN/edge cache. For public, cacheable GETs (persisted GraphQL queries, REST URLs) — absorbs the hottest keys before they reach your infrastructure.
• Response cache. At the gateway/API — caches full responses keyed on request + identity, honouring @cacheControl/Cache-Control.
• Result cache. In front of the database — caches query results (e.g. a Redis-backed layer) so identical SQL is not re-executed within a TTL.

Concurrency, rate limits, and reliability.

• Pooling mode. PgBouncer transaction mode returns a connection after each transaction, so a small pool serves thousands of clients — the right default for stateless serving APIs.
• Rate limits and quotas. Per-tenant/per-key limits at the gateway protect the backend from a single noisy consumer and make cost attributable.
• Backpressure and timeouts. Statement timeouts, queue limits, and circuit breakers ensure a slow warehouse refresh or a spike degrades gracefully instead of cascading.

The failure modes senior engineers pre-empt.

• Pointing serving traffic at the warehouse. Ad-hoc scans on the analytical engine per request → slow, expensive, and a batch-job contention nightmare. Mitigation: a serving store fronts it.
• Connection exhaustion. Every client opening a direct Postgres connection exhausts max_connections. Mitigation: a transaction-mode pooler.
• Stale-forever caches. A cache with no invalidation serves data long after the mart refreshed. Mitigation: TTLs aligned to refresh cadence, or purge-on-refresh.

Common interview probes on serving architecture.

• "Where does authentication vs authorization live?" — authn at the gateway, authorization in the data layer (RLS/permissions).
• "How do you survive a connection storm?" — a transaction-mode pooler multiplexing onto a small pool.
• "Precompute or query live?" — by the SLO: precompute hot/freshness-tolerant, query live cold/freshness-critical.
• "How do you keep caches correct?" — align TTL to refresh cadence or purge on refresh; scope keys by identity.

Worked example — PgBouncer transaction pooling in front of a data API

Detailed explanation. The classic production incident is connection exhaustion: 3,000 concurrent API requests each want a Postgres connection, but Postgres is configured for 200 — and it falls over. PgBouncer in transaction mode multiplexes them onto a tiny pool. Configure it in front of PostgREST/Hasura.

• The problem. Clients ≫ max_connections; direct connections exhaust the DB.
• The fix. PgBouncer transaction mode: a connection is borrowed per transaction, returned immediately.
• The math. 5,000 clients share 20 server connections.

Question. Configure PgBouncer so thousands of serving-API clients survive on a small pool of real database connections.

Input.

Aspect	Direct	PgBouncer (transaction)
Client connections	= server connections	multiplexed
Server connections	thousands (impossible)	20 (fixed pool)
Mode	session	transaction
Failure at scale	connection exhaustion	queued, served

Code.

# pgbouncer.ini — multiplex thousands of clients onto a small server pool.
[databases]
serving = host=pg-replica port=5432 dbname=serving

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction          ; connection returned after EACH transaction
max_client_conn = 5000           ; clients PgBouncer will accept
default_pool_size = 20           ; real Postgres connections per (db,user)
reserve_pool_size = 5            ; a few spares for spikes
server_idle_timeout = 60

# The data API connects to PgBouncer (6432), NOT to Postgres (5432), directly.
# PostgREST:
db-uri = "postgres://authenticator@pgbouncer:6432/serving"
# NOTE (transaction mode): avoid session-level features (prepared statements
# without protocol support, SET SESSION, LISTEN/NOTIFY) — they break pooling.

Step-by-step explanation.

1. The data API connects to PgBouncer on port 6432, not to Postgres on 5432 — so from Postgres's perspective there are only ever default_pool_size (20) connections, no matter how many clients the API serves.
2. pool_mode = transaction is the multiplexing lever: a client borrows a server connection only for the duration of a transaction, then returns it to the pool. Since serving queries are short, 20 connections cycle through thousands of clients.
3. max_client_conn = 5000 lets PgBouncer accept up to 5,000 client connections; when all 20 server connections are busy, additional transactions queue briefly rather than erroring — backpressure instead of collapse.
4. The trade-off with transaction mode: session-level state (session-scoped SET, some prepared-statement patterns, LISTEN/NOTIFY) does not survive because you get a different physical connection each transaction — fine for stateless serving APIs, which is why it is the correct default here.
5. The result is that a connection storm becomes a queue: 3,000 simultaneous requests are served by 20 real connections cycling rapidly, and Postgres never approaches max_connections — the difference between a degraded-but-up API and an outage.

Output.

Concurrent clients	Direct (max_conn=200)	PgBouncer (pool=20)
100	ok	ok
500	near limit	ok (queued)
3,000	connection errors	ok (queued)
5,000	outage	ok (at accept limit)

Rule of thumb. Never let serving clients connect to Postgres directly — put PgBouncer in transaction mode in front so thousands of short-lived clients multiplex onto a small fixed pool. Accept the loss of session-level features; a stateless serving API does not need them, and the pooler is what keeps the database alive under concurrency.

Worked example — a rate-limit and cache-key policy at the gateway

Detailed explanation. The gateway is where you protect the backend from a single noisy consumer and where you cache safely. Configure a per-tenant rate limit plus a response cache whose key includes the tenant, so caching a multi-tenant API cannot leak. Sketch a gateway policy.

• Rate limit. Per API key/tenant (e.g. 600 req/min), 429 over the limit.
• Cache. Response cache for cacheable GETs, TTL aligned to mart refresh.
• Cache key. Includes tenant/identity so responses never cross tenants.

Question. Write a gateway policy that rate-limits per tenant and caches responses with an identity-scoped key and a refresh-aligned TTL.

Input.

Concern	Policy
Rate limit	600 req/min per tenant → 429
Cacheable	GET with Cache-Control: public
Cache key	method + path + query + tenant_id
TTL	60 s (≤ mart refresh cadence)

Code.

# API gateway policy (illustrative): authn, per-tenant rate limit, scoped cache.
routes:
  - match: { path: /daily_sales, methods: [GET] }
    filters:
      - jwt_auth: { issuer: "https://auth.example.com", required: true }
      - rate_limit:
          key: "${jwt.tenant_id}"        # per-TENANT bucket, not global
          limit: 600
          window: 60s
          on_exceed: { status: 429, retry_after: 30 }
      - response_cache:
          # identity IN the key → tenant A's cache never served to tenant B
          key: "${method}:${path}:${query}:${jwt.tenant_id}"
          ttl: 60s                        # aligned to mart refresh cadence
          cache_when: { response_header: { "Cache-Control": "public*" } }
    upstream: postgrest:3000

# Why the key MUST include identity:
#   key = GET:/daily_sales?region=eq.EU           -> WRONG: shared across tenants (leak)
#   key = GET:/daily_sales?region=eq.EU:tenant=acme -> RIGHT: acme's entry is acme's only
#
# Why TTL <= refresh cadence:
#   mart refreshes every 15 min; a 60s TTL means at most 60s of staleness,
#   and the cache self-heals well within a refresh window.

Step-by-step explanation.

1. jwt_auth at the gateway is authentication — it verifies the token and extracts claims (tenant_id) — while authorization still happens in the data layer via RLS/permissions. The two concerns live in the two right places.
2. The rate_limit is keyed on jwt.tenant_id, so each tenant gets its own 600-req/min budget; a single noisy tenant hits its own 429 wall without starving others, and cost becomes attributable per tenant.
3. The response_cache key includes jwt.tenant_id, so acme's cached /daily_sales response is stored under a key no other tenant can hit — the identity-in-the-key rule that makes caching a multi-tenant API safe.
4. ttl: 60s is deliberately ≤ the mart's 15-minute refresh cadence, bounding staleness to at most a minute and guaranteeing the cache self-heals long before the next refresh — the alignment between cache TTL and precompute cadence that keeps served data correct.
5. Together these give the gateway three jobs — authenticate, throttle per tenant, and cache safely — so the data API and the database behind it see only authenticated, rate-limited, cache-missed traffic, which is exactly the load they were sized for.

Output.

Request	Gateway action	Backend hit
within limit, cache MISS	resolve + store	yes
within limit, cache HIT	serve cached	no
over 600/min (same tenant)	429 retry-after	no
different tenant, same URL	separate cache entry	yes (own MISS)

Rule of thumb. Put authentication, per-tenant rate limits, and response caching at the gateway, and always bake identity into the cache key and align the TTL to your precompute refresh cadence. The gateway absorbs the noise so the data API and database only ever see safe, throttled, cache-missed traffic.

Worked example — the precompute-vs-live decision under an SLO

Detailed explanation. The architecture's central choice is per-data-product: precompute into a mart or query live. An SLO — a latency target and a freshness target — resolves it. Walk three data products through the freshness/latency/cost triangle and place each.

• Product A. Exec dashboard tile: p95 < 100 ms, freshness ≤ 15 min.
• Product B. Fraud-ops view: p95 < 1 s, freshness ≤ 10 s.
• Product C. Analyst explorer: p95 < 5 s, freshness = live.

Question. For each product, choose precompute or query-live and the serving store, justified by its SLO.

Input.

Product	Latency SLO	Freshness SLO	Choice
A: exec tile	< 100 ms	≤ 15 min	precompute (mart) + cache
B: fraud ops	< 1 s	≤ 10 s	serving store fed by stream
C: analyst	< 5 s	live	query live on replica

Code.

Freshness / Latency / Cost — pick two; the SLO decides which.

Product A (exec tile)  p95<100ms, freshness<=15m
  -> PRECOMPUTE: materialized mart refreshed every 15m + response cache (TTL 60s)
     fast + cheap; trades freshness (bounded 15m). Warehouse touched only on refresh.

Product B (fraud ops)  p95<1s, freshness<=10s
  -> HOT SERVING STORE: stream (CDC/Kafka) maintains a serving table in ~seconds
     fresh + fast; trades cost (a streaming pipeline). NOT a warehouse query.

Product C (analyst)    p95<5s, freshness=live
  -> QUERY LIVE: read replica, no precompute
     fresh + flexible; trades latency (seconds) & per-query cost. Low volume makes it fine.

Anti-pattern for all three: ad-hoc queries against the warehouse fact tables on the hot path.

-- Product A serving store: a mart + a refresh aligned to the 15-min SLO.
CREATE MATERIALIZED VIEW serving.exec_tile AS
SELECT tenant_id, metric, value_cents, as_of FROM analytics.fct_metrics;
-- Scheduled every 15 min (freshness SLO); cache TTL 60s < 15 min (self-heals).
REFRESH MATERIALIZED VIEW CONCURRENTLY serving.exec_tile;

Step-by-step explanation.

1. Product A's tight latency SLO (< 100 ms) rules out a live warehouse query, and its loose freshness SLO (≤ 15 min) permits precomputation — so a materialized mart refreshed every 15 minutes plus a short response cache is the cheapest way to hit the target.
2. Product B needs both low latency and near-real-time freshness (≤ 10 s), which precompute-on-a-schedule cannot give and a warehouse query cannot serve fast — so a streaming-fed serving store (CDC/Kafka maintaining a serving table) is the only point of the triangle that satisfies it, at the cost of a streaming pipeline.
3. Product C is freshness-critical but latency-tolerant (< 5 s) and low-volume, so querying a read replica live is correct — precomputing every ad-hoc slice an analyst might want is impossible and pointless.
4. The triangle is the reasoning tool: you cannot maximise freshness, latency, and cost simultaneously, so each SLO names which two to optimise and which one to trade — and the trade is explicit, not accidental.
5. The invariant across all three is that none of them queries the warehouse fact tables on the hot path: A reads a mart, B reads a stream-fed store, C reads a replica — the warehouse is only ever the source that feeds the serving stores, never the thing serving requests.

Output.

Product	Serving store	Optimises	Trades
A: exec tile	materialized mart + cache	latency, cost	freshness (≤15m)
B: fraud ops	stream-fed serving table	freshness, latency	cost (pipeline)
C: analyst	read replica (live)	freshness, flexibility	latency, per-query cost
all	never the warehouse hot path	—	—

Rule of thumb. Resolve precompute-vs-live per data product with its SLO: precompute a mart when latency is tight and freshness is loose, stream-feed a serving store when you need both, and query a replica live only for freshness-critical low-volume access. Whatever you choose, the warehouse feeds the serving store — it never serves the request.

Senior interview question on end-to-end serving architecture

A senior interviewer might ask: "Design the full serving architecture for a multi-tenant analytics API on top of a warehouse. Cover the request path from gateway to database, where authentication and authorization each live, how you survive a connection storm, your caching tiers and how you keep them correct for multi-tenant data, and how you decide precompute versus query-live — all tied to an SLO."

Solution Using a gateway, pooler, serving store, tiered caches, and SLO-driven precompute

# 1. Request path + where each concern lives.
#    client
#      -> API gateway   : authn (JWT), per-tenant rate limit, response cache (identity-keyed)
#      -> data API      : PostgREST/Hasura -> ONE SQL stmt, authorization via RLS/permissions
#      -> PgBouncer     : transaction pooling, thousands of clients -> small pool
#      -> serving store : materialized mart / read replica (NOT the warehouse)
#      <- warehouse feeds the serving store on a SCHEDULE (never on the hot path)

gateway:
  auth: { jwt: { required: true } }              # authenticate here
  rate_limit: { key: "${jwt.tenant_id}", limit: 600, window: 60s }
  cache: { key_includes: [path, query, "jwt.tenant_id"], ttl: 60s }   # identity-scoped

# 2. Pooler: survive connection storms.
[pgbouncer]
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 20

-- 3. Authorization in the data layer (defence in depth with the gateway authn).
ALTER TABLE serving.metrics ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_read ON serving.metrics FOR SELECT
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

-- 4. SLO-driven precompute: mart refreshed to the freshness SLO; cache TTL below it.
CREATE MATERIALIZED VIEW serving.metrics AS
SELECT tenant_id, metric, value_cents, as_of FROM analytics.fct_metrics;
REFRESH MATERIALIZED VIEW CONCURRENTLY serving.metrics;   -- every 15 min (freshness SLO)

Step-by-step trace.

Layer	Component	Responsibility
Edge	CDN / gateway cache	absorb hot public GETs
Gateway	JWT authn + per-tenant rate limit	authenticate, throttle, cache (identity-keyed)
Data API	PostgREST/Hasura	compile to one SQL stmt, apply authz
Authorization	RLS / permissions	tenant isolation in the DB
Pooler	PgBouncer (transaction)	multiplex clients onto a small pool
Serving store	mart / replica	point reads; fed by warehouse on schedule

After deployment, a request is authenticated and rate-limited at the gateway (per tenant), possibly served from an identity-scoped cache, otherwise compiled by the data API into one SQL statement whose row-level security scopes it to the caller's tenant, executed through PgBouncer against a materialized mart refreshed every 15 minutes. Authentication lives at the edge, authorization in the data layer, concurrency is absorbed by the pooler, and the warehouse is touched only by the scheduled refresh — so the whole path honours a p95 < 100 ms, freshness ≤ 15 min SLO.

Output:

Metric	Naive (warehouse-direct)	Serving architecture
Hot-path latency p95	seconds (scan)	< 100 ms (mart + cache)
Connection storm	exhausts DB	multiplexed (pooler)
Cross-tenant leak	app-dependent	zero (RLS + scoped cache)
Noisy-tenant blast radius	global	own 429 bucket
Warehouse load	every request	scheduled refresh only
Staleness	none (but slow)	bounded (≤ 15 min, by SLO)

Why this works — concept by concept:

• Authn at the edge, authz in the data layer — the gateway verifies identity and throttles, while row-level security enforces which rows the identity may read, so a defence-in-depth split puts each concern where it is unbypassable.
• Transaction-mode pooling — PgBouncer multiplexes thousands of short-lived clients onto a small fixed pool, converting a connection storm into a brief queue and keeping the database alive at concurrency the warehouse never sees.
• Serving store fronting the warehouse — a mart or replica built for point reads handles the request path, and the warehouse only feeds it on a schedule, so production traffic never contends with batch jobs on the analytical engine.
• Identity-scoped, refresh-aligned caching — response caches keyed on tenant with a TTL below the mart refresh cadence make caching a multi-tenant API both safe (no cross-tenant leak) and correct (bounded staleness).
• Cost — one scheduled refresh, a handful of pooled connections, and tiered caches absorbing hot keys, versus a per-request warehouse scan and a connection per client. The eliminated cost is the warehouse bill and outage risk of serving from an analytical engine — O(1) cached/pooled reads versus O(scan) direct queries.

Design
Topic — design
Design problems on serving-layer and gateway architecture

Practice →

Optimization
Topic — optimization
Optimization problems on connection pooling and caching tiers

Practice →

---

Cheat sheet — data API serving

• The consumption gap. A warehouse/lakehouse is for high-throughput batch scans; a data API is request/response, high-concurrency, low-latency. Never serve production traffic from the analytical engine — front it with a serving store (materialized mart, read replica, or streaming-fed table). The warehouse feeds the serving store; the serving store serves the request.
• REST vs GraphQL decision. REST (PostgREST) for simple, cache-friendly, resource-at-a-time access — the URL is the cache key. GraphQL (Hasura) when many consumers each want different related slices in one round-trip, or you need live subscriptions. It is a per-consumer choice; a serving layer can offer both over the same data.
• PostgREST template. Expose a dedicated api schema of views (never raw tables); ENABLE ROW LEVEL SECURITY + CREATE POLICY ... USING (tenant_id = jwt.tenant_id) for authz; map the JWT to a DB role; resource-embed related data via foreign keys (select=*,customers(name)); keyset-paginate deep feeds (?id=gt.<last>&order=id&limit=N), never offset; cap with db-max-rows.
• Hasura template. Track curated views; per-role select permission = columns allow-list + row filter on X-Hasura-Tenant-Id + limit + allow_aggregations; relationships from FKs give nested fields; _aggregate computes in SQL; subscriptions for live tiles (shared/tenant-level shapes multiplex — distinct per-user shapes are the fan-out trap); Actions/Remote Schemas for logic the DB can't express; set query_depth_limit, node_limit, max_concurrent_subscriptions.
• N+1 / DataLoader. A naive nested resolver fires 1+N queries; wrap every relationship lookup in a per-request DataLoader that batches keys into one WHERE id = ANY(...) and caches by key for the request → 1+1. Create the loader fresh per request — a shared loader leaks cached rows across users and breaks authorization.
• Persisted queries + caching. Send a query hash (not text) → cacheable GET/CDN + an allow-list of runnable queries. @cacheControl(maxAge, scope): most-restrictive hint wins. Scope is governance: PUBLIC only for non-viewer-specific data; PRIVATE with the tenant baked into the cache key for anything a permission/RLS filter scopes. Align TTL to the mart refresh cadence.
• Complexity limits. Give a GraphQL schema a budget: depthLimit(N) + per-field complexity cost (lists multiply by size), reject over budget before execution. Three independent brakes: allow-list (what may run) + complexity (how expensive) + DataLoader (how efficient).
• Connection pooling. Never let serving clients connect to Postgres directly. PgBouncer pool_mode = transaction, max_client_conn in the thousands, default_pool_size ~10–25 — multiplex thousands of short-lived clients onto a small fixed pool. Accept the loss of session-level features; stateless serving APIs don't need them.
• Gateway responsibilities. Authentication (JWT) at the edge; per-tenant rate limits (key = jwt.tenant_id → 429) so one noisy consumer can't starve others; response cache keyed on identity + request. Authorization stays in the data layer (RLS/permissions) — authn at the edge, authz at the data, defence in depth.
• Precompute vs query-live. Freshness/latency/cost — pick two, and let the SLO decide. Precompute a materialized mart for hot, freshness-tolerant reads (REFRESH ... CONCURRENTLY on a schedule); stream-feed a serving store when you need both fresh and fast; query a replica live for freshness-critical, low-volume, ad-hoc access.
• Caching tiers. CDN/edge (public cacheable GETs) → response cache (gateway/API, honours @cacheControl) → result cache (in front of the DB, TTL'd SQL results). Keep them correct: identity in the key, TTL ≤ refresh cadence, purge-on-refresh for exactness.
• Data product framing. A served dataset is a product: a versioned contract, an owner, an SLO (availability, p95 latency, freshness), and governed access — not a one-off endpoint bolted onto the warehouse.

Frequently asked questions

What is a data API over the warehouse?

A data API is a governed request/response contract — usually REST or GraphQL returning JSON — that sits in front of curated warehouse data so applications, dashboards, and partners can ask questions of a dataset without a bespoke backend and without touching the analytical engine directly. It exists because a warehouse or lakehouse is optimised for high-throughput batch scans over huge tables, not for the high-concurrency, low-latency point lookups a product surface needs; the API bridges that consumption gap by reading from a serving store (a materialized mart or read replica) that the warehouse refreshes on a schedule. Done well, each dataset becomes a reusable data product with a versioned contract, an owner, and an SLO — rather than yet another one-off service every team rebuilds.

PostgREST vs Hasura — which do I pick?

Pick PostgREST when your data lives in Postgres and you want a simple, cache-friendly REST API with the least moving parts: it is a single stateless binary that turns a schema into REST endpoints and delegates authorization entirely to Postgres row-level security. Pick Hasura when you want a GraphQL contract — clients fetching exactly the related fields they need in one round-trip — or when you need to serve over multiple sources (Postgres plus Snowflake or BigQuery via connectors), declarative per-role permissions, relationships and aggregations as first-class fields, or live subscriptions. Both push authorization into the data layer and compile requests to efficient SQL; the deciding factors are contract shape (REST vs GraphQL), number and diversity of consumers, whether you need multi-source or live data, and operational simplicity. Many platforms run both over the same serving store and let each consumer use the contract that fits.

REST or GraphQL for analytics serving?

Choose by the consumer, not by fashion. REST is the better fit for simple, resource-at-a-time access that you want to cache trivially at a CDN — the URL is the cache key, and a single high-read dashboard tile is happiest here. GraphQL wins when many consumers each want different slices of related data in a single round-trip, letting the client select exactly the fields it needs and traverse relationships without over- or under-fetching; it is also the natural contract when you want live subscriptions or schema federation. The GraphQL trade-off is that requests are POSTs, so you lose free HTTP caching until you add persisted queries (a hash makes them cacheable GETs), and its flexibility demands guardrails — DataLoader batching for N+1, plus depth and complexity limits. A mature serving layer often exposes both over the same data and lets each consumer pick.

Where does authorization live — row-level security or the gateway?

Split them: authentication at the gateway, authorization in the data layer. The gateway verifies the JWT and extracts claims (tenant, role), rate-limits, and caches — but it should not be the thing deciding which rows a caller may read, because that logic then has to be duplicated and kept in sync everywhere data is accessed. Authorization belongs in the data layer as row-level security policies (PostgREST) or per-role permission filters (Hasura), keyed on a JWT claim/session variable and AND-ed onto every generated query, so no request shape, filter, or application bug can return another tenant's rows. This is defence in depth: the edge proves who you are, the database enforces what you may see. Enforcing authorization in SQL, once, is both safer and less code than scattering checks across services.

Do I query the warehouse live or precompute a serving store?

Let the SLO decide via the freshness/latency/cost triangle — you cannot maximise all three, so pick two. Precompute a materialized mart (refreshed on a schedule with REFRESH ... CONCURRENTLY) for hot, high-QPS reads whose freshness tolerance is minutes — dashboard tiles, aggregates — because it is fast and cheap per request at the cost of bounded staleness. Query live on a read replica for freshness-critical, low-volume, or ad-hoc access where seconds of latency are acceptable, since precomputing every possible slice is impossible. When you need both fresh and fast (fraud, ops), maintain a serving store from a stream (CDC/Kafka) rather than querying the warehouse. The one constant: the warehouse feeds the serving store on a schedule and never handles the request hot path — production traffic against analytical fact tables is slow, expensive, and contends with batch jobs.

How do I stop a data API from overloading the warehouse?

Layered defences, none of which involve the warehouse serving requests directly. First, front it with a serving store (mart or replica) so the analytical engine is only ever refreshed on a schedule. Second, put a transaction-mode connection pooler (PgBouncer) in front of the database so thousands of short-lived clients multiplex onto a small fixed pool instead of exhausting connections. Third, cache aggressively at tiered layers — CDN/edge for public GETs, a response cache keyed on identity, a result cache for repeated SQL — with TTLs aligned to your refresh cadence so hot keys never reach the backend. Fourth, rate-limit per tenant at the gateway so one noisy consumer hits its own 429 wall without affecting others. Fifth, for GraphQL, add DataLoader batching, persisted-query allow-lists, and depth/complexity limits so no single query fans out into an expensive scan. Together these keep the database seeing only authenticated, throttled, cache-missed, bounded traffic.

Practice on PipeCode

• Drill the API integration practice library → for the REST/GraphQL contract, pagination, and data-product problems that PostgREST and Hasura make concrete.
• Rehearse serving patterns on the real-time analytics practice library → for the live-query, subscription, and freshness scenarios where the precompute-vs-live decision earns its keep.
• Sharpen the architecture axis with the system design practice library → for the gateway, pooling, caching-tier, and authorization-placement trade-offs a serving layer must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the serving-store, row-level-security, and N+1/DataLoader patterns against real graded inputs — REST, GraphQL, permissions, and caching.

Lock in data-API serving muscle memory

Docs explain PostgREST and Hasura. PipeCode drills explain the decision — when the warehouse must not serve the request, when `row-level security` beats an app-side check, when a per-request DataLoader turns 1+N into 1+1, and when precompute has to win over a live query. Pipecode.ai is Leetcode for Data Engineering — serving-layer practice tuned for the production trade-offs senior data engineers actually face.

Practice API integration problems →
Practice real-time analytics problems →