PostgreSQL Interview Questions 2026

Design a REST API for an e-commerce cart system. Define the endpoints (add item, remove item, get cart, checkout), explain authentication with JWT, handle race conditions on inventory, and discuss idempotency for the checkout endpoint.

Write a SQL query to find the second highest salary from an Employee table. Show at least two approaches: using LIMIT/OFFSET, using a subquery with MAX, and using DENSE_RANK(). Discuss which is most efficient and why.

Design Razorpay's payment retry system. When a payment fails due to a transient error (bank timeout, network blip), how do you retry safely without double-charging the customer? Cover: idempotency keys, exponential backoff with jitter, dead-letter queues, and reconciliation with bank statements.

Design Razorpay's payout system (RazorpayX). Businesses send bulk payouts to thousands of vendor accounts simultaneously. Cover: batching, bank API rate limits, real-time status tracking, and failure handling.

How do you implement a distributed ledger for financial transactions? Explain double-entry bookkeeping in code, why you never update a balance directly, and how you use event sourcing to reconstruct the current balance from a transaction log.

Design Razorpay's dashboard for merchants. A merchant wants to see real-time revenue, transaction success rates, and refund trends. How do you aggregate millions of transactions into live charts with under 5-second latency?

In PostgreSQL, what is the difference between SERIALIZABLE and READ COMMITTED isolation levels for a transaction that reads then updates an account balance?

Design PhonePe's UPI transaction system to handle 1 billion transactions per day. Cover: request routing to the correct bank, NPCI integration, handling duplicate transactions, consistency vs availability trade-offs, and how you build a reliable audit trail for regulatory compliance.

Explain how PhonePe handles transaction deduplication. A user taps "Pay" twice due to network lag. The same debit must not happen twice. Walk through idempotency at the API level, database level, and bank integration level.

Design PhonePe's merchant settlement system. Thousands of merchants receive daily settlements of their collected payments minus fees. How do you compute net payables, batch into bank transfers, and provide a reconciliation report?

Design Stripe's webhook delivery system. Stripe sends millions of webhooks per day to customer endpoints that can be slow, down, or buggy. Cover: at-least-once delivery guarantees, retry with exponential backoff, ordering guarantees (or lack thereof), how to handle a customer endpoint that is down for 24h, and the dead-letter strategy.

Explain how Stripe handles exactly-once payment processing. A customer clicks "Pay" twice in quick succession. How does Stripe prevent double-charging? Walk through idempotency keys in the API, database-level constraints, and how the client should handle the case where the first request times out.

Design Stripe's fraud detection system. For every payment, you must decide accept/decline in under 100ms using hundreds of signals. Cover: feature engineering, real-time ML scoring, rule engine, and how you minimise false positives (declined legitimate payments).

Explain the difference between synchronous and asynchronous API design using Stripe as an example. When does a charge complete immediately vs require a webhook? How do you handle the intermediate "pending" state in your database?

Design Stripe's subscription billing system (Stripe Billing). Customers are charged on recurring intervals (monthly/annual) in different currencies, with proration for plan upgrades. Cover: invoice generation, dunning for failed payments, and SCA (Strong Customer Authentication) compliance.

Design a multi-tenant SaaS database architecture like Salesforce's. Thousands of enterprise customers share the same infrastructure but must never see each other's data. Compare silo (one DB per tenant), pool (shared DB, tenant_id column), and bridge models. Discuss query performance, schema customisation, and compliance isolation.

Explain Salesforce's metadata-driven architecture. Why does storing field definitions as data (instead of schema changes) enable the multi-tenant model? What are the trade-offs in query performance?

Explain Oracle's MVCC (Multi-Version Concurrency Control) and how it enables consistent reads without blocking writers. How does it differ from SQL Server's locking approach? What are the downsides of MVCC (undo log growth, long-running transactions)?

Design a distributed SQL database that supports ACID transactions across shards. Explain two-phase commit (2PC), its failure modes, and how Google Spanner uses TrueTime to replace 2PC with a more scalable approach.

Explain Oracle's redo log and undo log. How do they work together to provide crash recovery and read consistency? Walk through what happens when a transaction commits, then the server crashes, then restarts.

Write a SQL query to find the Nth highest salary from an Employee table without using LIMIT/TOP. Then solve it with a window function (DENSE_RANK) and explain which is more portable across Oracle, PostgreSQL, and MySQL.

What is the difference between a PostgreSQL VIEW and a MATERIALIZED VIEW? When would a materialized view actually hurt you?

Design Airbnb's search and availability system. A guest searches for "Paris, 3 nights, 2 guests" and sees ranked listings. Cover: availability calendar storage, real-time inventory updates (when host blocks dates), geospatial search, ranking signals (price, reviews, Superhost status), and how you handle the thundering herd on popular dates.

Design Airbnb's review system. Guests and hosts review each other after a stay. Reviews are revealed simultaneously to prevent bias. Cover: the blind reveal mechanism, preventing fake reviews, and abuse detection.

Design Airbnb's host payout system. Hosts in 220+ countries receive payouts in local currency via bank transfer, PayPal, or Payoneer. Cover: currency conversion, scheduled payouts, failed payout handling, and tax compliance.

Design a coupon/discount system for Flipkart Big Billion Days. The system must validate, apply, and prevent double-use of millions of coupons with high concurrency. Cover idempotency and distributed locking.

Design the data model for Swiggy's menu system. A restaurant has categories, items, add-ons, variants, and availability windows (breakfast/lunch/dinner). How do you model this in PostgreSQL and handle real-time menu updates?

Design Uber's payment reconciliation system. Uber collects payments in 70+ countries with different currencies, payment methods, and regulations. How do you reconcile driver payouts against rider charges and detect discrepancies?

Design Paytm's cashback system. When a user completes a transaction, they may receive cashback based on complex rules (merchant, time, amount, user segment). The cashback is credited to their wallet. Cover: rule engine, deduplication, and fraud prevention.

Design Paytm's KYC (Know Your Customer) pipeline. Users must submit Aadhaar/PAN documents for verification. How do you store documents securely (encrypted at rest), verify them (ML + manual review), and handle status callbacks?

Design Zomato Pro (subscription). Members get free delivery, discounts, and priority support. Cover: subscription lifecycle, prorated upgrades/downgrades, cohort-based analytics to measure retention, and how you handle payment failures.

Explain Zomato's restaurant onboarding data pipeline. A new restaurant submits menus, photos, and operating hours. How do you validate, normalise, and make this data searchable within minutes of submission?

Design Adobe Sign (e-signature platform). A contract is sent to 5 signatories who must sign in a specific order. Cover: document integrity (hash verification), audit trail, legal compliance (e-SIGN Act), and what happens if a signatory refuses.

Design Confluence's page version history. Every edit creates a new version. Users can view any historical version and restore it. Cover: delta vs full-snapshot storage, efficient diff computation, and how you handle concurrent edits creating a branch.

Explain how Jira's JQL (Jira Query Language) parser works. Walk through lexing, parsing, and AST generation. How do you translate a JQL query like "assignee = currentUser() AND status = Done" into an efficient SQL query?

Design Bitbucket's code review system (Pull Requests). Multiple reviewers comment on specific lines of code. The PR is mergeable when all reviewers approve. Cover: diff computation, inline comments anchored to code lines, and what happens when new commits invalidate existing comments.