MySQL → PostgreSQL

-- validated text, binary internally
CREATE TABLE events (
  id      BIGINT AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL
);

-- index a path via a generated column
ALTER TABLE events
  ADD kind VARCHAR(32)
    AS (payload->>'$.type') STORED,
  ADD INDEX (kind);

SELECT payload->>'$.user'
FROM   events
WHERE  kind = 'click';

-- parsed, binary, richly indexable
CREATE TABLE events (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

-- one index covers every key
CREATE INDEX ON events
  USING gin (payload);

SELECT payload->>'user'
FROM   events
WHERE  payload @> '{"type":"click"}';

CREATE TABLE posts (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  tags JSON
);

INSERT INTO posts (tags)
VALUES (JSON_ARRAY('sql','mysql'));

SELECT * FROM posts
WHERE  JSON_CONTAINS(tags, '"sql"');

CREATE TABLE posts (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[]
);

INSERT INTO posts (tags)
VALUES (ARRAY['sql','postgres']);

SELECT * FROM posts
WHERE  'sql' = ANY(tags);

BEGIN;
  CREATE TABLE staging LIKE orders;  -- commits!
  ALTER TABLE orders
    ADD shipped_at TIMESTAMP;         -- commits!
ROLLBACK;  -- too late, schema changed

BEGIN;
  CREATE TABLE staging
    (LIKE orders INCLUDING ALL);
  ALTER TABLE orders
    ADD COLUMN shipped_at timestamptz;
ROLLBACK;  -- schema is untouched

With a primary key, rows live inside that clustered index; secondary indexes store the PK and may require a second hop. Without one, InnoDB chooses a suitable unique index or creates a hidden clustered index.

Rows live in an unordered heap; every index (including the PK) points into it. This enables several index methods, but every added index still costs storage and write maintenance.

-- functional index (8.0.13+)
CREATE INDEX idx_email_lc
  ON users ((lower(email)));

-- no partial index: emulate it
ALTER TABLE users
  ADD active_email VARCHAR(255)
    AS (IF(status='active',email,NULL)) STORED,
  ADD INDEX (active_email);

-- keep an index but hide it from planner
ALTER TABLE orders
  ALTER INDEX idx_cust INVISIBLE;

-- partial index: only the rows you query
CREATE INDEX idx_active_email
  ON users (email)
  WHERE status = 'active';

-- expression index
CREATE INDEX ON users (lower(email));

-- covering, built without locking writes
CREATE INDEX CONCURRENTLY idx_cust
  ON orders (customer_id) INCLUDE (total);

INSERT INTO stock (sku, qty)
VALUES ('A1', 5) AS incoming
ON DUPLICATE KEY UPDATE
  qty = stock.qty + incoming.qty;

INSERT INTO stock (sku, qty)
VALUES ('A1', 5)
ON CONFLICT (sku)
DO UPDATE SET
  qty = stock.qty + EXCLUDED.qty;

-- choose an engine per table
CREATE TABLE session_cache (
  k VARCHAR(64) PRIMARY KEY, v BLOB
) ENGINE = MEMORY;

CREATE TABLE orders (
  ...
) ENGINE = InnoDB;

CREATE EXTENSION IF NOT EXISTS postgis;   -- geo
CREATE EXTENSION IF NOT EXISTS vector;    -- ai
CREATE EXTENSION pg_stat_statements;

-- define your own type
CREATE TYPE money_range AS
  RANGE (subtype = numeric);

• ·InnoDB's clustered primary key rewards compact keys and access patterns built around them.
• ·Secondary lookups may make a second hop through the primary key.
• ·Thread-per-connection by default; reuse connections to avoid churn at scale.
• ·Replication and high availability are mature, but topology and failover choices remain workload-specific.

• ·Several index methods, richer SQL semantics, and parallel plans give the optimizer more options.
• ·Heap access and table statistics make plan quality sensitive to vacuum and analyze health.
• ·Process-per-connection: pool with PgBouncer for many clients.
• ·Watch for table bloat; tune autovacuum on write-heavy tables.

• ·Your existing schema, tooling, and operating model already fit it well.
• ·Your team and tooling already know it well.
• ·Your provider and application stack offer a well-understood MySQL path.

• ·Your data model wants JSON, arrays, ranges, or custom types.
• ·You write complex analytical queries alongside transactions.
• ·You need extensions such as PostGIS or pgvector, or richer constraint and index semantics.