Why I Prefer Postgres Over MySQL

For most of my projects, I reach for PostgreSQL. Here's why it's become my default database.

A Matter of Preference

Both Postgres and MySQL are excellent databases. This isn't about one being objectively better. It's about what works for me.

Why Postgres

Better JSON Support

Native JSONB type with actual indexing:

-- Store JSON
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

-- Index a JSON field
CREATE INDEX idx_events_user ON events ((data->>'userId'));

-- Query JSON
SELECT * FROM events 
WHERE data->>'type' = 'purchase'
AND (data->'amount')::int > 100;

MySQL has JSON support too, but Postgres feels more mature.

Advanced Types

Arrays, ranges, network addresses:

-- Arrays
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  tags TEXT[] NOT NULL
);

-- Query arrays
SELECT * FROM posts WHERE 'javascript' = ANY(tags);

-- Ranges
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  duration TSTZRANGE
);

Full-Text Search

Built-in, no extensions needed:

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content) 
@@ to_tsquery('react & hooks');

Not as powerful as Elasticsearch, but often good enough.

Better Standards Compliance

Postgres follows SQL standards more closely. Less surprise behavior when you're used to standard SQL.

Extensions

• **PostGIS** - Geospatial queries
• **pg_trgm** - Fuzzy matching
• **pgcrypto** - Encryption functions

When I'd Use MySQL

• **Existing ecosystem** - If the project already uses it
• **WordPress** - It's what WordPress expects
• **Simple needs** - For basic CRUD, both work fine

Getting Started

# Mac
brew install postgresql
brew services start postgresql

# Create database
createdb myapp
psql myapp

Or just use Docker:

services:
  db:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_DB: myapp

If you haven't tried Postgres, give it a shot. It might become your default too.

Back to Journal