SQL and PostgreSQL Review

Written on August 4, 2026.

The point of this document is not to learn SQL from scratch again. It is to keep the important ideas in one place, revisit them, and remember how to reconstruct a query when I am staring at a blank page.

The other articles cover almost everything, but spread across different pages:

SQL by hand: filters, joins, aggregations, HAVING, NOT EXISTS, CTEs, and window functions.
PostgreSQL indexes: choosing indexes, EXPLAIN, EXPLAIN ANALYZE, ANALYZE, VACUUM, and VACUUM FULL.
Web security: SQL injection and parameterized queries.

Here is a summary of all three topics together, plus transactions, constraints, and N+1, which were not covered in the SQL by hand article.

My map for writing a query

First I decide what each result row represents. Then I think about where the data comes from, and only then do I write the query.

FROM / JOIN
    ↓
WHERE
    ↓
GROUP BY
    ↓
HAVING
    ↓
SELECT
    ↓
ORDER BY
    ↓
LIMIT

The query is written starting with SELECT, but when I reason about it, this logical order works better for me.

ON relates rows and can decide which matches a join accepts.
WHERE filters rows before grouping.
GROUP BY builds groups.
HAVING filters groups after calculating aggregates.
ORDER BY sorts the final result.

1. Filters and ordering

SELECT
  m.id,
  m.name,
  m.status
FROM monitors AS m
WHERE m.workspace_id = 7
  AND m.enabled = true
  AND m.status = 'down'
ORDER BY m.id ASC;

There is not much mystery: WHERE decides which rows remain, and ORDER BY is applied to the result.

2. Chaining joins

The ten workspaces with the most open incidents from enabled monitors:

SELECT
  w.id AS workspace_id,
  w.name AS workspace_name,
  COUNT(i.id) AS open_incidents_count
FROM workspaces AS w
JOIN monitors AS m
  ON m.workspace_id = w.id
JOIN incidents AS i
  ON i.monitor_id = m.id
WHERE m.enabled = true
  AND i.status = 'open'
GROUP BY w.id, w.name
ORDER BY open_incidents_count DESC, workspace_name ASC
LIMIT 10;

The path is:

workspace → sus monitores → los incidentes de esos monitores

Each JOIN needs its own relationship. I do not mix IDs arbitrarily: monitors.workspace_id points to workspaces.id and incidents.monitor_id points to monitors.id.

3. `LEFT JOIN` and the famous zero

All workspaces and their number of enabled monitors, including those with none:

SELECT
  w.id AS workspace_id,
  w.name AS workspace_name,
  COUNT(m.id) AS monitors_count
FROM workspaces AS w
LEFT JOIN monitors AS m
  ON m.workspace_id = w.id
 AND m.enabled = true
GROUP BY w.id, w.name
ORDER BY monitors_count DESC, workspace_name ASC;

The condition m.enabled = true is in ON because I want to decide which monitors to join without removing workspaces. If I put it in WHERE, a workspace with no match gets m.enabled = NULL, does not satisfy the condition, and disappears.

I use COUNT(m.id) because it ignores the NULL from the missing monitor and returns zero. COUNT(*) would count the row preserved by the LEFT JOIN and return one.

4. `GROUP BY` and `HAVING`

Workspaces with at least three enabled monitors:

SELECT
  w.id AS workspace_id,
  w.name AS workspace_name,
  COUNT(m.id) AS monitors_count
FROM workspaces AS w
JOIN monitors AS m
  ON m.workspace_id = w.id
WHERE m.enabled = true
GROUP BY w.id, w.name
HAVING COUNT(m.id) >= 3
ORDER BY monitors_count DESC, workspace_name ASC;

WHERE m.enabled = true filters monitors before grouping. Then HAVING COUNT(m.id) >= 3 filters groups. I cannot put the COUNT in ON or WHERE because the group does not exist at that point yet.

5. `NOT EXISTS`

Workspaces with no enabled monitor that is down:

SELECT
  w.id AS workspace_id,
  w.name AS workspace_name
FROM workspaces AS w
WHERE NOT EXISTS (
  SELECT 1
  FROM monitors AS m
  WHERE m.workspace_id = w.id
    AND m.enabled = true
    AND m.status = 'down'
)
ORDER BY workspace_name ASC;

The subquery is correlated because it uses w.id from the outer query. SELECT 1 does not mean the number one: for EXISTS, only whether any row appears matters. NOT EXISTS keeps the workspace when none appears.

When the question says “there is no related row that matches this,” this is the structure I want to remember.

6. CTE and window function

The most recent incident for each monitor:

WITH ranked_incidents AS (
  SELECT
    i.id,
    i.monitor_id,
    i.status,
    i.created_at,
    ROW_NUMBER() OVER (
      PARTITION BY i.monitor_id
      ORDER BY i.created_at DESC, i.id DESC
    ) AS position
  FROM incidents AS i
  WHERE i.monitor_id IN (4, 5, 6)
)
SELECT
  id,
  monitor_id,
  status,
  created_at
FROM ranked_incidents
WHERE position = 1
ORDER BY monitor_id ASC;

PARTITION BY creates an independent window for each monitor. ORDER BY puts the most recent incident first and ROW_NUMBER() numbers them. The outer query keeps number one.

I cannot calculate position and filter it in the WHERE at the same level because the window function is calculated after that WHERE. That is why I use the CTE.

A CTE is not a permanent table. It is a named result that exists only for that statement. It does not magically make a query faster either: it mainly helps separate ideas.

7. Choosing an index

I do not invent indexes by looking only at the table. I start with a real query:

SELECT id, name, status
FROM monitors
WHERE workspace_id = 7
  AND enabled = true
  AND status = 'down';

A reasonable option could be a partial composite index:

CREATE INDEX index_monitors_on_workspace_id_status_when_enabled
ON monitors (workspace_id, status)
WHERE enabled = true;

The idea is:

workspace_id comes first because it usually narrows the main set.
status helps within each workspace.
The partial predicate avoids indexing disabled monitors when that query always looks for enabled ones.
The index speeds up reads, but makes writes more expensive and takes space. I do not add one “just in case.”

An index (workspace_id, status) is usually useful for workspace_id alone and for both columns together. It is generally not efficient for searching only by status, because it is not the first column.

PostgreSQL may ignore an index if the query returns a large part of the table. In that case a Seq Scan may be cheaper than jumping between the index and the heap.

Access methods I need to recognize

B-tree: equality, ranges, and ordering. It is the default.
Hash: equality.
GIN: arrays, jsonb, and full-text search.
GiST: geometry, ranges, overlap, and nearest neighbors.
SP-GiST: data that can be partitioned, such as points or IP prefixes.
BRIN: huge tables whose physical order correlates with the value, such as chronological events.

8. `EXPLAIN`, `EXPLAIN ANALYZE`, and `ANALYZE`

EXPLAIN
SELECT * FROM monitors WHERE workspace_id = 7;

EXPLAIN shows the estimated plan and normally does not execute the query.

EXPLAIN ANALYZE
SELECT * FROM monitors WHERE workspace_id = 7;

EXPLAIN ANALYZE executes the query and adds timings, actual rows, and loops. If it is an UPDATE, DELETE, or INSERT, it executes that too. Be careful.

What I look at:

Seq Scan: sequential scan.
Index Scan, Index Only Scan, or Bitmap Index Scan: index usage.
cost and rows: planner estimates.
actual time, rows, and loops: what actually happened.
A large difference between estimated and actual rows can indicate stale statistics or a data distribution that is difficult to estimate.

ANALYZE monitors; updates statistics. It is not the same as EXPLAIN ANALYZE.

9. `VACUUM` and `VACUUM FULL`

PostgreSQL uses MVCC. An UPDATE or DELETE can leave old row versions because another transaction may still need them. When no transaction can see them anymore, they become dead tuples.

VACUUM lets the table reuse that space, updates the visibility map, and helps prevent transaction ID wraparound. It normally does not return the space to the operating system.
VACUUM FULL rewrites the table, can return space to the operating system, and needs temporary space. It also takes an ACCESS EXCLUSIVE lock, so it is much more disruptive.

10. SQL injection

The problem appears when untrusted data is concatenated as part of the syntax:

SELECT *
FROM users
WHERE username = '${username}'
  AND password = '${password}';

A value like this can modify the Boolean expression:

' OR 1=1 OR 'a'='b

With an empty password, the resulting query would be:

SELECT *
FROM users
WHERE username = '' OR 1=1 OR 'a'='b'
  AND password = '';

AND has higher precedence than OR, and the 1=1 branch makes the condition true for every row.

The main defense is not escaping by hand or blindly trusting the ORM. Use parameters so structure and values travel separately:

User.find_by(username: params[:username], password_digest: digest)

Or, when writing SQL:

User.where("username = ?", params[:username])

Parameters are for values. If a user can choose a column or sort direction, I need an allowlist; those identifiers cannot be handled with an ordinary placeholder.

11. What else was worth mentioning

Transactions

A transaction groups operations that must all succeed together:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

If something fails, I run ROLLBACK. A transaction provides atomicity, but I still need to think about concurrency, isolation, and locks.

Constraints

Rails validations help the user experience. Database constraints protect integrity even when another application or process writes the data.

Examples: NOT NULL, UNIQUE, FOREIGN KEY, and CHECK.

Locks and deadlocks

A lock coordinates concurrent access. A deadlock appears when two transactions wait for resources held by each other. PostgreSQL detects the cycle, aborts one transaction, and the application must be able to retry when it is safe. Keeping transactions short and acquiring locks in a consistent order reduces the risk.

N+1

N+1 appears when I load a collection with one query and then run another query for each element. In Rails I first confirm it by looking at logs or measurements, then consider includes, preload, or eager_load, depending on the query. I do not load huge associations blindly because I can also trade an N+1 for a memory problem.

My mistakes in this exercise

I related workspaces.id to monitors.id instead of monitors.workspace_id.
I put a condition on the right-hand table in WHERE and removed the rows that the LEFT JOIN was supposed to preserve.
I forgot the FROM when reconstructing a query.
I tried to put COUNT(m.id) >= 3 in ON; that condition belongs in HAVING.
I mixed up aliases such as workspace_count, workspace_name, and workspaces_name.
In some queries I forgot the m.enabled = true filter or the tie-breaker in ORDER BY.
With three tables I got stuck until I drew the relationship path again.

It is not a big deal. The rule I take away is not to rush during the first ten seconds. I read the problem, decide what a row represents, and check the keys for each join.

Twenty-second answers

`WHERE` versus `HAVING`

WHERE filters rows before grouping. HAVING filters groups after calculating aggregates such as COUNT or SUM.

`INNER JOIN` versus `LEFT JOIN`

INNER JOIN returns matches only. LEFT JOIN keeps every row from the left side even when there is no match on the right side.

`EXPLAIN` versus `EXPLAIN ANALYZE`

EXPLAIN shows the estimated plan. EXPLAIN ANALYZE executes the query and adds timings and actual rows, so be careful with statements that modify data.

`VACUUM` versus `VACUUM FULL`

VACUUM makes dead-tuple space reusable inside the table and usually does not return it to the operating system. VACUUM FULL rewrites the table, can return space to the system, and needs an exclusive lock.

How do I choose an index?

I start with a real query, look at filters, joins, and ordering, think about selectivity and frequency, design the smallest useful index, and verify it with EXPLAIN ANALYZE. Then I consider the cost for writes and storage.

The last rule

I do not need to recite all of this. I need to answer what is being asked first, explain the mechanism in one sentence, and give an example. If I do not know something, I say so and explain how I would verify it. That is it.

Until next time,
Gastón Ramos

 ::: If you would like to comment, email me: ramos.gaston AT gmail.com :::

Gastón Ramos

Personal blog