PostgreSQL vs MySQL in 2026: A Complete Guide to Choosing the Right Database

-- PostgreSQL: complex analytical query (PostgreSQL outperforms significantly)
SELECT
  DATE_TRUNC('month', created_at) AS month,
  customer_segment,
  COUNT(*) AS orders,
  SUM(total) AS revenue,
  AVG(total) OVER (
    PARTITION BY customer_segment
    ORDER BY DATE_TRUNC('month', created_at)
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_3m_avg
FROM orders
JOIN customers USING (customer_id)
GROUP BY 1, 2
ORDER BY 1 DESC;

-- PostgreSQL: GIN-indexed JSONB containment query (fast at any scale)
CREATE INDEX idx_products_attrs
ON products USING GIN(attributes jsonb_path_ops);

SELECT * FROM products
WHERE attributes @> '{"color": "blue", "in_stock": true}';

-- Extract and aggregate on a nested JSON value
SELECT attributes->>'brand' AS brand,
       COUNT(*) AS product_count
FROM products
WHERE attributes @> '{"category": "electronics"}'
GROUP BY 1
ORDER BY 2 DESC;

-- PostgreSQL: full-text search with weighted multi-column ranking
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- PostgreSQL: monitor replication lag across all replicas
SELECT
  client_addr,
  state,
  sent_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes,
  extract(epoch FROM (now() - reply_time)) AS lag_seconds
FROM pg_stat_replication;

-- Logical replication: publish specific tables to a subscriber
CREATE PUBLICATION orders_pub
FOR TABLE orders, order_items;

-- PostgreSQL extensions unlock specialised workloads
CREATE EXTENSION IF NOT EXISTS timescaledb;   -- Time-series data
CREATE EXTENSION IF NOT EXISTS postgis;        -- Geospatial queries
CREATE EXTENSION IF NOT EXISTS vector;         -- AI/ML embeddings
CREATE EXTENSION IF NOT EXISTS pg_cron;        -- Job scheduling

-- pgvector: store embeddings and find nearest neighbours
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops);

-- Find the 5 most semantically similar documents
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- pgLoader: migrate a MySQL database to PostgreSQL in one command
-- pgloader mysql://user:pass@host/dbname postgresql://user:pass@host/dbname

-- Common MySQL → PostgreSQL syntax adjustments required:
-- AUTO_INCREMENT  → GENERATED ALWAYS AS IDENTITY
-- TINYINT(1)      → BOOLEAN
-- DATETIME        → TIMESTAMP WITH TIME ZONE
-- IFNULL()        → COALESCE()
-- LIMIT x, y      → LIMIT y OFFSET x

-- PostgreSQL: modern identity column (SQL standard)
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);