SELECT * in Hot Path: Why Mesrai Always Flags This

A posts-list endpoint. `SELECT *`. Reads every column. The posts table has 24 columns including a `content_json` blob and a `metadata` JSONB. The endpoint shows a list — title, slug, date. Mesrai flagged the projection gap.

The vulnerable diff

// api/posts.ts
postsRouter.get("/by-author/:id", async (req, res) => {
  // BUG: SELECT * pulls every column including TOAST'd JSON
  const posts = await db.query(
    "SELECT * FROM posts WHERE author_id = $1 ORDER BY published_at DESC LIMIT 20",
    [req.params.id]
  );
  res.json(posts.map(p => ({ id: p.id, title: p.title, slug: p.slug, published_at: p.published_at })));
});

What is wrong

`SELECT *` reads every column from the row, including TOAST'd JSON columns that Postgres has to decompress on read. The caller uses 4 columns. Three problems: the database does extra I/O it does not need to, the wire protocol carries data nobody will use, and any added column in a future migration changes the response shape unexpectedly. The fix is to project to the columns you need — explicit column lists at the query layer.

The attack

Wire-size delta on a typical posts list:

-- SELECT * with 24 columns × 20 rows
-- avg row size: 8.2 KB (mostly content_json + metadata JSONB)
-- total: 164 KB
-- p99 read latency: 22 ms

-- SELECT id, title, slug, published_at × 20 rows
-- avg row size: 180 bytes
-- total: 3.6 KB
-- p99 read latency: 1.4 ms

Same data shape returned to the user, ~45× smaller payload, ~15× lower latency.

Mesrai's review comment

mesraipilot · Bot · reviewed 1 min ago

[mesrai] [code-review] [Performance] [Select-Star] [medium]

`SELECT *` on a hot read path. Most columns thrown away client-side
and one of them is TOAST'd (`content_json`) — decompression cost on
every read.

Project to the columns the caller actually uses:

  const posts = await db.query(
    `SELECT id, title, slug, published_at
       FROM posts WHERE author_id = $1
       ORDER BY published_at DESC LIMIT 20`,
    [req.params.id]
  );

Bonus: this query can now be served from a covering index
(id, author_id, published_at, title) without touching the heap.

The fix

// api/posts.ts — fixed
postsRouter.get("/by-author/:id", async (req, res) => {
  const posts = await db.query(
    `SELECT id, title, slug, published_at
       FROM posts WHERE author_id = $1
       ORDER BY published_at DESC LIMIT 20`,
    [req.params.id]
  );
  res.json(posts);
});

Explicit column projection. The query now reads only the columns the response needs. If Postgres has a covering index on `(author_id, published_at) INCLUDE (title, slug)`, the query never touches the table heap at all — index-only scan, p99 in microseconds.

Why human review missed it

`SELECT *` is the default in tutorials and the easiest thing to write. The performance cost is invisible until the table grows wide or one column gets TOAST'd. Mesrai catches every `SELECT *` against a table the rule pack knows is on a hot path (or against any table with TOAST-able column types).

Related rules + further reading

Mesrai rule pack: performance/no-select-star-hot-path — flags SELECT * on endpoints with high traffic.

Postgres docs: TOAST and the cost of large columns.

Common bottleneck in CMS-style schemas with JSONB content fields.

Takeaway

Project to what you use. The query is faster, the wire payload is smaller, and the schema can grow without breaking the response.