10 PostgreSQL Queries for Pune Full Stack Devs (2026)

The list

1. 1

   UPSERT with INSERT ... ON CONFLICT

   Insert a row; if conflict on a unique constraint, update existing row instead. Syntax: `INSERT INTO users (email, name) VALUES (...) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name`. Use EXCLUDED to reference the values that would have been inserted.

   Why it matters: Asked at ~60% of Pune full-stack rounds. Replaces brittle SELECT-then-INSERT-or-UPDATE patterns; atomic + faster.

   Best for: Idempotent writes; webhook handlers; deduplication.

2. 2

   Window functions for ranking + running totals

   PARTITION BY groups rows; ORDER BY orders within partition. Examples: `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)` (latest per user), `SUM(amount) OVER (PARTITION BY user_id ORDER BY date)` (running totals). LAG / LEAD access previous / next row values.

   Why it matters: Asked at ~55% of Pune product company rounds. Strongly differentiates senior-fresher candidates from basic-SQL-only ones.

   Best for: Analytics queries; dashboards; per-row calculations across groups.

3. 3

   JSONB column queries + indexing

   PostgreSQL's native JSON type with binary storage + indexing. Query: `WHERE data->>'status' = 'active'` (text), `WHERE data->'tags' @> '["premium"]'` (contains). Index: `CREATE INDEX ... USING gin (data jsonb_path_ops)` for containment queries.

   Why it matters: Asked at ~50% of Pune product company rounds. JSONB is the most-used PostgreSQL-specific feature beyond basic SQL.

   Best for: Flexible schemas; event logs; user preferences; semi-structured data.

4. 4

   CTEs (Common Table Expressions) for readability + recursion

   Named temporary result sets referenced within a single query: `WITH user_stats AS (SELECT user_id, COUNT(*) FROM orders GROUP BY user_id) SELECT * FROM user_stats WHERE ...`. Recursive variant for hierarchies: `WITH RECURSIVE tree AS (base case UNION ALL recursive case)`.

   Why it matters: Asked at ~45% of Pune rounds. Modern PostgreSQL CTE optimisation (since 12) inlines them — no performance penalty vs subqueries.

   Best for: Complex query readability; recursive queries (org charts, dependency graphs).

5. 5

   Generated columns + computed values

   Columns whose values are computed from other columns automatically. Syntax: `total NUMERIC GENERATED ALWAYS AS (qty * price) STORED`. Saves application-side calculations; indexable like regular columns. Modern PostgreSQL (12+) feature.

   Why it matters: Asked at ~25% of Pune product company rounds; rare at services-major fresher tier. Senior-fresher signal.

   Best for: Derived values; full-text search precomputation; tax + total calculations.

6. 6

   Array columns + operations

   Native array support: `tags TEXT[]` column. Query: `WHERE 'premium' = ANY(tags)`, `WHERE tags @> ARRAY['vip']` (contains all). Index with GIN: `CREATE INDEX ... USING gin (tags)`. Unnest into rows: `SELECT unnest(tags) FROM users`.

   Why it matters: Asked at ~35% of Pune rounds. PostgreSQL-specific feature that distinguishes you from MySQL-only candidates.

   Best for: Multi-value attributes; tags; permissions arrays without join tables.

7. 7

   Full-text search with tsvector + tsquery

   Built-in search: `SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & query')`. Faster + saner than LIKE '%term%' for any non-trivial search. Index with GIN. For more advanced needs, layer on PostgreSQL extensions or move to Elasticsearch.

   Why it matters: Asked at ~30% of Pune rounds, especially product company + search-feature roles. Mention pg_trgm extension for fuzzy matching.

   Best for: In-product search; documentation search; small-to-medium search workloads.

8. 8

   Transactions + isolation levels

   BEGIN; ... COMMIT; or ROLLBACK; on error. Default isolation level READ COMMITTED prevents dirty reads. SERIALIZABLE for strict ordering at cost of performance. Use SELECT ... FOR UPDATE to lock rows being modified. Critical for any financial or inventory data.

   Why it matters: Asked at ~50% of Pune backend rounds. Walking through a banking transfer + isolation level choice signals real production thinking.

   Best for: ACID-critical operations; financial systems; inventory management.

9. 9

   Index types: B-tree + GIN + BRIN

   B-tree (default): equality + range queries. GIN (Generalised Inverted Index): JSONB, arrays, full-text. BRIN (Block Range Index): very large tables with naturally-sorted data (timestamps). Pick the right index for your query pattern; over-indexing slows writes.

   Why it matters: Asked at ~40% of Pune rounds. EXPLAIN ANALYZE walks through which index PostgreSQL chose + why.

   Best for: Query performance + production database health.

10. 10

    EXPLAIN ANALYZE for query optimisation

    Show the query plan + actual execution timing: `EXPLAIN (ANALYZE, BUFFERS) SELECT ...`. Look for: Seq Scans on large tables (need index), Nested Loop joins on large datasets (consider hash/merge join), high Buffers usage (memory pressure). The fundamental tool for production query optimisation.

    Why it matters: Asked at ~45% of Pune product company rounds. Walking through an EXPLAIN output signals real query-optimisation experience.

    Best for: Performance debugging; production-grade query tuning.