Postgres isn't just a database. It's a belief system.
I've built projects on Firebase, MongoDB, PlanetScale, and SQLite. Each one taught me something. But every time I reached for a quick win with a flexible schema or a hosted NoSQL store, I eventually hit a wall — some subtle inconsistency, a missing constraint, a query that couldn't express what I needed without pulling half the data into memory first.
These days I default to Postgres for everything. Not because it's trendy, but because I've stopped hitting those walls.
The schema is the contract
The thing that shifted my thinking was realizing that a well-designed schema isn't overhead — it's documentation that the database enforces for you. When I built TradeDash, I made it structurally impossible for a user's balance to go negative. Not with application-level checks. Not with a validation function that could be bypassed by a race condition. With a CHECK (balance >= 0) constraint at the column level.
That constraint can never be out of sync with the application. It can't be forgotten in a new API route. It can't be disabled by a junior developer who didn't read the code comment. It just exists, at the layer that matters.
This is the mindset shift: stop thinking of your schema as a layout for your data, and start thinking of it as a set of invariants your entire system inherits for free.
RLS is underrated
Row-level security gets dismissed as a Supabase gimmick, but it's a core Postgres feature that's been around since version 9.5. The idea is simple: attach a policy to a table, and the database enforces who can see or modify which rows — before the query even reaches your application code.
In practice this means your API routes stop being the last line of defense. You can have a bug in your server code that accidentally skips an authorization check, and the row-level policy catches it anyway. Multi-tenant applications especially benefit here: instead of scattering WHERE user_id = $1 across every single query, you set the policy once and the database handles it everywhere.
Triggers are logic that can't drift
In TradeDash, every trade, transfer, and security event fires a notification automatically via a Postgres trigger. The trigger is defined once, in a migration file that lives in version control, and it runs regardless of which surface created the transaction — the web app, the API, a one-off script, a future mobile client.
Compare that to application-level event hooks. Every new entry point into your system needs to remember to call the notification function. Triggers don't have that problem. They're attached to the data, not to a particular code path.
The stuff that still trips people up
None of this is free. Postgres demands upfront thinking that document databases don't. Schema migrations require care. Adding a column with a default to a large table used to lock the whole table (though Postgres 11+ handles this safely with ADD COLUMN ... DEFAULT). And explaining query plans via EXPLAIN ANALYZE is a skill that takes time to build.
But these are the right problems to have. They're problems that surface early, when they're cheap to fix, rather than late — when your production data is a mess of inconsistent states that no migration can cleanly resolve.
Start with the schema
My advice for any new project: before you write a single API route, sit down and design the schema. Ask what the invariants are. What should never be possible? Model those as constraints. What relationships exist between entities? Model those as foreign keys. What queries will you run most often? Think about indexes now, not after you're slow in production.
The schema is the most durable part of your codebase. Your frontend will be rewritten. Your API layer will be refactored. But three years from now, the shape of your data will still be recognizably what you designed on day one. Get it right early.