PostgreSQL Deep Dive

PostgreSQL and MySQL are both open-source relational databases, but they differ significantly in feature depth. PostgreSQL is strictly ACID-compliant, has more powerful window functions and CTEs, supports JSONB (binary JSON with indexing), has a richer type system (arrays, custom types, hstore), enforces foreign key constraints more rigorously, and has a more permissive license. MySQL has historically had better replication features and raw read performance for simple queries. In practice, PostgreSQL is the stronger choice for most application development in 2026 because of its feature completeness, the quality of its extensions ecosystem (pgvector, PostGIS, pg_cron), and its deep integration with modern platforms like Supabase and Neon.

EXPLAIN ANALYZE runs your query and returns the query execution plan — the sequence of operations Postgres actually performed to retrieve your data — along with real timing information. It shows you whether a query is using your indexes, where the most expensive operations are, and what the planner estimated vs what actually happened. Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...` to get the most detailed output. The key metrics to look for: Seq Scan (sequential scan — usually bad on large tables, should be an Index Scan or Index Only Scan for filtered queries), rows= discrepancy (if the planner estimates 10 rows but 10,000 are returned, your statistics are stale — run ANALYZE), and high Execution Time in individual nodes.

A partial index includes only the rows matching a WHERE condition, making it smaller, faster to scan, and cheaper to maintain than a full index. Use a partial index when your queries consistently filter on a condition that covers a small fraction of your table: `CREATE INDEX idx_active_users ON users(email) WHERE active = true` — if 5% of users are active and you always filter WHERE active = true, this index is 20x smaller than a full index on email. Other good candidates: unprocessed jobs (WHERE processed_at IS NULL), unpaid invoices (WHERE paid = false), non-deleted records with soft deletes. Partial indexes are one of the most underused Postgres performance tools.

Row-level security (RLS) is a PostgreSQL feature that restricts which rows a database user can access, enforced at the database layer rather than the application layer. When you enable RLS on a table and create policies, Postgres automatically appends WHERE clauses to every query based on the current user's context. This is foundational to Supabase's multi-tenancy model: users query tables directly via the PostgREST API, and RLS policies ensure they only see their own data. Use RLS when you are building a multi-tenant application, when you want to enforce data isolation without trusting your application layer, or when you use Supabase and want to leverage its auth JWT integration to control data access.

pgvector is a PostgreSQL extension that adds vector data types and similarity search operators, turning Postgres into a vector database without needing a separate service like Pinecone or Qdrant. You store embedding vectors (arrays of floats produced by models like text-embedding-3-small) in a vector column, create an HNSW or IVFFlat index on it, and query with `ORDER BY embedding <=> query_vector LIMIT 10` to find the 10 most semantically similar rows. This enables semantic search ("find documents similar in meaning to this query"), recommendation systems ("find products similar to what this user bought"), and RAG (retrieval-augmented generation) pipelines — all in the same Postgres database where you already store your application data.