PostgreSQL Fundamentals

Overview

Day-to-day PostgreSQL work is mostly a small set of composable building blocks: defining tables, querying and joining them, aggregating results, wrapping related changes in transactions, and controlling who can do what through roles and grants. This topic covers that everyday layer; the actual internals of how a single B-tree index is structured and when it does or doesn't get used live in the separate, deeper PostgreSQL Indexes topic; this one's indexing section stays at the "here's the command you'd reach for" level, not the storage-engine internals.

Quick Reference

The commands below are grouped by task, not an exhaustive reference (see the official PostgreSQL SQL command reference for every clause), but the set that covers nearly all everyday PostgreSQL work.

Syntax

SELECT
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email
HAVING COUNT(o.id) > 0
ORDER BY lifetime_value DESC
LIMIT 10;

Examples

BEGIN;

DO $$
DECLARE
  affected int;
BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  GET DIAGNOSTICS affected = ROW_COUNT;
  -- a no-op match isn't a SQL error, so PostgreSQL won't catch a
  -- mistyped id on its own; raising here aborts the transaction
  IF affected <> 1 THEN
    RAISE EXCEPTION 'expected 1 row updated for id=1, got %', affected;
  END IF;

  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  GET DIAGNOSTICS affected = ROW_COUNT;
  IF affected <> 1 THEN
    RAISE EXCEPTION 'expected 1 row updated for id=2, got %', affected;
  END IF;
END $$;

-- reached only if both row-count checks above passed; an EXCEPTION
-- above leaves the transaction aborted, making COMMIT a no-op rollback
COMMIT;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Visual Diagram

Common Mistakes

• Using INNER JOIN when the intent was "every row from the left table, matched or not," silently dropping rows with no match instead of surfacing them with NULLs via LEFT JOIN.
• Filtering on an aggregate with WHERE instead of HAVING; WHERE runs before grouping happens, so it can't reference the aggregated value at all.
• Running several related UPDATE/INSERT statements outside an explicit transaction, leaving the database in a partially-applied state if one of them fails.
• Granting broad privileges (GRANT ALL) to an application role instead of exactly what it needs, widening the blast radius of a compromised credential or a buggy query.
• Assuming a WHERE clause automatically uses an index, a function applied to the indexed column (WHERE lower(email) = ...), a leading wildcard (LIKE '%term'), or a type mismatch can all silently force a full table scan.

Performance

• EXPLAIN ANALYZE shows the real plan and real timing, not just the estimated one EXPLAIN alone shows; always confirm a slow query's actual bottleneck before adding an index speculatively.
• Aggregating over a large table without a supporting index on the filter/join columns forces a sequential scan; GROUP BY/JOIN performance is usually an indexing problem before it's a query-rewrite problem.
• Long-running transactions hold locks and prevent VACUUM from reclaiming dead row versions, keeping transactions as short as the logical unit of work actually requires matters at scale, not just for correctness.

Best Practices

• Wrap any multi-statement operation that needs to succeed or fail as a unit in an explicit BEGIN/COMMIT transaction.
• Grant the narrowest privilege a role actually needs (SELECT only, a specific table, not GRANT ALL ON ALL TABLES), and create separate roles per application rather than sharing one superuser credential.
• Reach for a window function instead of a self-join or a correlated subquery when you need a per-row calculation that references other rows in the same result set.
• Run EXPLAIN ANALYZE before adding an index speculatively; confirm the query is actually scanning more rows than it needs to.
• Keep transactions as short as the logical unit of work requires; a transaction left open while waiting on unrelated application logic holds locks longer than necessary.