Practical PostgreSQL — debugging sessions, gotchas with real costs, and the functions that actually matter. Written from experience building on PostgreSQL, not from reading the docs.
After building pgref.dev and cataloguing over 400 PostgreSQL functions, most days I use about 12 of them. The Pareto principle applies brutally to SQL. Here are the ones I reach for without thinking — with war stories attached.
The first team I saw move from MySQL to PostgreSQL expected the infrastructure to be the hard part. It wasn't. The string functions were the problem — not the syntax, the behaviour.
Every one of these has cost me at least an hour. Most cost more. PostgreSQL is doing exactly what the documentation says — I just hadn't read it carefully enough.
The query was taking 14 seconds. It should have taken under 100 milliseconds. Here's exactly how I found the problem — with real EXPLAIN ANALYZE output and the dead ends I hit along the way.
JSONB is PostgreSQL's most seductive feature. It's also the one most likely to ruin your schema if you let it. I've done both — used it well and used it badly. The badly version took three months to untangle.
Window functions clicked for me the day I stopped reading tutorials and started with a problem: I needed the second-highest salary in each department. That's where we'll start.
PostgreSQL error messages are surprisingly good — if you know where to look. Most developers read the message and move on. The SQLSTATE code underneath tells you the category of problem, which matters when you're writing error handling code.
Moving from MySQL to PostgreSQL is easier than moving the other way. "Easier" still means a week of fixing things that worked fine yesterday — and a few that will catch you completely off-guard.
Every reporting database needs a calendar table. Here's one you can create in PostgreSQL with a single query, no external tools, no Python scripts generating CSVs.
You don't need a separate vector database. PostgreSQL can do semantic search alongside your existing relational data — and the hybrid query capability is where it beats dedicated vector databases for most applications.