9 Postgres Mistakes I Made Shipping Side Projects (And the One-Line Fixes)
· Tutorials
Missing indexes, N+1 queries, UUID primary keys, timestamptz vs timestamp — the Postgres mistakes that cost me real downtime, with the exact SQL that fixed each.
Last updated: August 3, 2026 · 9-minute read
Every one of these cost me either downtime, a confusing bug, or an afternoon. None of them are exotic. They're the same nine mistakes almost every side project makes with Postgres, ordered by how much pain they caused me.
1. No index on the column every query filters by
The classic. A posts table filtered by userid on every single request, with no index. Fine at 500 rows, a sequential scan at 500,000.
CONCURRENTLY is the important word — without it the table locks for writes while the index builds. Find the missing ones:
Any table where seqscan dwarfs idxscan is asking for an index.
2. Indexing one column when queries filter by two
An index on userid doesn't help WHERE userid = $1 AND status = 'published' ORDER BY createdat DESC. Composite indexes are ordered — equality columns first, then the sort column:
Confirm with EXPLAIN (ANALYZE, BUFFERS) and look for Index Scan instead of Seq Scan — and for the absence of a separate Sort node.
3. timestamp instead of timestamptz
timestamp without time zone stores a wall clock with no meaning. The moment one user is in IST and your server is in UTC, your ordering is wrong and your "today" filter is off by 5.5 hours.
Use timestamptz always. Store UTC, convert at the edge.
4. Random UUIDs as primary keys on a write-heavy table
genrandomuuid() scatters inserts across the B-tree, which fragments the index and hurts cache locality. On small projects this is invisible; past a few million rows it isn't. If you want UUIDs, use a time-ordered variant (UUIDv7) so inserts stay sequential — or use bigint identity and expose a separate public slug.
5. The N+1 query hiding behind a nice ORM
One query for the list, then one per row for the author. Twenty rows, twenty-one round trips. In SQL:
Turn on statement logging for a minute in development and count the queries behind one page load. The number is always higher than you expect.
6. OFFSET pagination on a growing table
LIMIT 20 OFFSET 10000 makes Postgres walk 10,020 rows to return 20. Keyset pagination is both faster and stable when rows are inserted mid-scroll:
7. SELECT on a table with a big text column
Pulling a 40KB markdown body into a list view that renders 200 characters of it. Select the columns you render — and if the big column is rarely read, Postgres will already be TOASTing it out of line, so leaving it out is nearly free.
8. Enabling RLS but forgetting the grants
This one is Supabase-specific and it bites everyone. Row-level security policies are not permissions. Without a GRANT, the API can't reach the table at all — you get a permission error that looks nothing like an RLS problem.
Order matters: create, grant, enable, then policies. More on the platform trade-offs in Supabase vs Firebase.
9. Policies that re-query the same table
A policy on posts that does SELECT ... FROM posts recurses. The fix is a SECURITY DEFINER function that bypasses RLS for the lookup:
Then policies call public.hasrole(auth.uid(), 'admin'). Roles live in their own table — never a boolean on the profile row, which is a privilege-escalation waiting to happen.
The 10-minute health check
Run these three queries on any project you haven't looked at in a while: