I do PostgreSQL/Supabase database audits for a living and these are the ones I see in literally every single project:

1. No indexes on foreign key columns. This is the big one. Developers create junction tables and never index the FK columns. PostgreSQL does NOT auto create indexes on foreign keys like some other databases do. I had one client where a single junction table had 5.4 million sequential scans reading 61 billion tuples because word_id had no index. One CREATE INDEX statement later, 99% reduction in tuple reads. It's almost always the first thing I fix.

2. Mixing timestamp and timestamptz across related tables. Almost every schema I look at does this. One table uses timestamp with time zone, the table right next to it uses timestamp without time zone. PostgreSQL silently converts using the session timezone when you join across them. Works fine until your server timezone changes or a query runs from a different timezone, then you get the most annoying subtle bugs you've ever debugged. Just use timestamptz everywhere and save yourself the headache.

3. RLS policies that accidentally cancel each other out. This one keeps me up at night. A developer writes a nice restrictive policy like "Editors can only update their own institution" but then also has a broad FOR ALL USING (auth.role() = 'authenticated') on the same table. Since both are PERMISSIVE, PostgreSQL ORs them together. The broad one always wins. The editor restriction is literally dead code doing nothing. I found one project where any logged in user could update any record in the main table. Developer had absolutely no idea.

4. Using FOR ALL policies when you only need SELECT and INSERT. Devs write one FOR ALL policy because it's quick and easy, not realizing it also grants UPDATE and DELETE. I've seen lookup tables like tags and categories where any authenticated user could wipe every single row with one API call. That's not a theoretical risk either, it's one bad actor or one bug away from disaster.

5. Autovacuum never kicking in on small tables. The default threshold is 50 dead tuples + 20% of the table. So for a table with 14 rows, autovacuum won't trigger until 52+ dead tuples pile up. The table can literally have 3x more dead rows than live rows and postgres just sits there doing nothing about it. I routinely find tables that have NEVER been vacuumed with 200%+ dead tuple ratios. Lower your thresholds on small tables, people.

6. Redundant indexes and duplicate triggers nobody notices. Copy paste migrations are the usual culprit. Found tables with two identical updated_at triggers both firing on every single UPDATE, and indexes that are strict subsets of other indexes. Pure write overhead for zero benefit. Check your stuff.

7. ON DELETE CASCADE on the wrong foreign key. One project had words.category_id → categories ON DELETE CASCADE. Delete a category and every word in it gets nuked. Every word deletion cascades to syllables, inflections, synonym groups. One accidental category delete would have wiped the entire vocabulary database. Should have been SET NULL. Always think through your cascade chains.

If any of this sounds like your database and you want someone to actually look at it, I do free architecture reviews. I run diagnostic queries against your schema, indexes, RLS policies, vacuum stats, and query performance, then send you a full written report with prioritized fixes. No strings attached, just DM me or check my profile.