Quick PostgreSQL Guide for MySQL Users: Beyond Syntax Differences

Introduction

If you are already comfortable with MySQL, most PostgreSQL concepts will feel familiar because both are relational databases that support transactions, indexes, constraints, and SQL. The real challenges lie in the subtle differences that appear "similar" at first glance.

1. Overall Architecture – The Extra Schema Layer

MySQL uses a three‑level hierarchy (instance → database → table). PostgreSQL inserts an additional schema level between database and table, creating a four‑level hierarchy: instance → database → schema → table. Objects such as indexes, sequences, types, and functions belong to a schema, and a single database can contain many schemas, allowing names like account.users and audit.users to coexist.

Because a PostgreSQL connection can only access one database, cross‑database joins are impossible. If you need to query across logical modules within the same transaction, place them in different schemas of the same database; otherwise use postgres_fdw, dblink, or multiple connections.

Use multiple schemas when you need cross‑module transactions and joins.

Use separate databases for strong isolation or independent operations.

Avoid creating many databases just because you used databases for business separation in MySQL.

1.2 The Default public Schema

Every new database contains a public schema, which is merely a default namespace, not a separate database. To avoid ambiguity, reference objects explicitly, e.g., SELECT * FROM app.users instead of relying on search_path.

1.3 Connecting with psql

Run a PostgreSQL instance quickly with Docker:

docker run --name pg-dev \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=shop \
  -p 5432:5432 \
  -d postgres:18

Connect using a PostgreSQL URI similar to MySQL’s connection string:

psql "postgresql://postgres:postgres@localhost:5432/shop"

2. Connection Model – One Process per Connection

In PostgreSQL each client connection spawns a separate backend process. This incurs higher per‑connection memory and scheduling cost than MySQL’s thread‑based model, so connection‑pool sizing must consider the total budget of max_connections and leave headroom for admin tasks.

When using external poolers such as PgBouncer, prefer the transaction‑pooling mode only for short‑lived transactions; otherwise session state (e.g., search_path) may be lost.

3. Authentication & Authorization – Roles Only

PostgreSQL distinguishes only role objects; a role with the LOGIN attribute acts as a user. Create a non‑login owner role and a login application role, then grant privileges on a schema:

CREATE ROLE shop_owner NOLOGIN;
CREATE ROLE shop_app LOGIN PASSWORD 'replace-me';
CREATE SCHEMA app AUTHORIZATION shop_owner;
GRANT USAGE ON SCHEMA app TO shop_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO shop_app;

Remember that GRANT ALL ON TABLES only affects tables existing at grant time; use ALTER DEFAULT PRIVILEGES to apply to future tables.

4. Data Types – Not a Simple One‑to‑One Mapping

Most MySQL types have direct PostgreSQL equivalents (e.g., TINYINT → smallint, INT → integer, FLOAT → real, TEXT → text, DATE → date, BLOB → bytea). However, several conversions require attention: BIGINT AUTO_INCREMENT becomes bigint GENERATED BY DEFAULT AS IDENTITY (or bigserial for legacy code). INT UNSIGNED has no unsigned counterpart; use bigint or numeric(20,0) with a CHECK constraint. TINYINT(1) maps to boolean, which only accepts true, false, or NULL. DATETIME maps to timestamp (wall‑clock) while TIMESTAMP maps to timestamptz (absolute UTC). Choose timestamptz for order timestamps, date / time for local‑only values. JSON should be stored as jsonb to enable indexing and containment queries.

When using jsonb, a typical query with a GIN index looks like:

SELECT * FROM app.orders
WHERE attributes @> '{"channel": "ios"}'::jsonb;
CREATE INDEX orders_attributes_gin_idx ON app.orders USING gin (attributes);

5. Constraints – Let the Database Enforce Correctness

Both systems support PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and NOT NULL. PostgreSQL adds useful variations: UNIQUE NULLS NOT DISTINCT treats NULL as equal.

Partial unique indexes allow conditions, e.g., enforce a single active email per user:

CREATE UNIQUE INDEX users_active_email_uq ON app.users (lower(email))
WHERE deleted_at IS NULL;

Exclusion constraints ( EXCLUDE USING gist) can prevent overlapping reservation periods:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE app.reservations (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  room_id bigint NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Remember that foreign‑key columns are not indexed automatically; add an index manually or as part of a composite index.

6. SQL – Dialect Differences

6.1 RETURNING

Instead of MySQL’s LAST_INSERT_ID(), PostgreSQL can return inserted values directly:

INSERT INTO app.users (email, display_name)
VALUES ($1, $2)
RETURNING id, email, created_at;

6.2 ON CONFLICT vs. INSERT IGNORE / REPLACE

Use INSERT ... ON CONFLICT with an explicit conflict target. For a case‑insensitive unique email:

INSERT INTO app.users (email, display_name)
VALUES ($1, $2)
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL
DO UPDATE SET display_name = EXCLUDED.display_name
RETURNING id, email, display_name;

ON CONFLICT DO NOTHING

only suppresses unique‑key violations, unlike MySQL’s broader INSERT IGNORE.

6.3 UPDATE ... FROM and DELETE ... USING

MySQL’s UPDATE ... JOIN becomes:

UPDATE app.orders AS o
SET status = 'canceled', updated_at = now()
FROM app.users AS u
WHERE u.id = o.user_id AND u.deleted_at IS NOT NULL;

Note that the target table must appear only once; the join may produce multiple matches, in which case PostgreSQL picks an arbitrary row.

6.4 Function Differences

IFNULL(a,b)

→

COALESCE(a,b)

GROUP_CONCAT(...)

→

string_agg(..., ',')

DATE_FORMAT(...)

→

to_char(..., 'YYYY-MM-DD')

LIMIT offset,count

→ LIMIT count OFFSET offset String comparison is case‑sensitive in PostgreSQL; use lower(col) or the citext extension for case‑insensitive behavior.

7. Transactions & Concurrency

7.1 Isolation Levels

MySQL InnoDB defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED. In READ COMMITTED, each statement sees a fresh snapshot, so two reads in the same transaction can return different results if another transaction commits in between.

When using REPEATABLE READ or SERIALIZABLE in PostgreSQL, be prepared to catch SQLSTATE 40001 (serialization failure) and retry the whole transaction.

7.2 No Gap Locks

PostgreSQL does not lock gaps like InnoDB’s next‑key locks. Rely on unique/exclusion constraints, INSERT ... ON CONFLICT, or SERIALIZABLE isolation instead of “check‑then‑insert” patterns.

7.3 NOWAIT and SKIP LOCKED

Use FOR UPDATE NOWAIT to fail immediately if a row is locked, or FOR UPDATE SKIP LOCKED to build task‑queue workers that skip busy rows.

7.4 Sequences Do Not Roll Back

IDs generated by a sequence are not reclaimed on transaction rollback; they remain consumed.

7.5 DDL in Transactions

PostgreSQL allows DDL inside a transaction, but operations like CREATE INDEX CONCURRENTLY cannot be run inside a transaction block.

7.6 Timeout Parameters

lock_timeout

– max wait for a lock. statement_timeout – max execution time for a statement. transaction_timeout – max total time for a transaction. idle_in_transaction_session_timeout – kills sessions that stay idle in a transaction.

7.7 Error‑Driven Retries

After an error, the transaction enters the ABORTED state; you must ROLLBACK before retrying. Use the SQLSTATE codes to decide whether to retry (e.g., 40001, 40P01) or handle the error directly.

8. Vacuum – The Missing Piece for MySQL Developers

PostgreSQL stores old row versions (dead tuples) in the table itself. VACUUM reclaims space, updates the visibility map, cleans index references, and prevents transaction‑ID wraparound. Autovacuum runs automatically based on thresholds.

Regular VACUUM does not shrink the physical file; use VACUUM FULL only when necessary because it requires an ACCESS EXCLUSIVE lock.

Long‑running transactions or sessions idle in a transaction prevent vacuum from cleaning old tuples, so keep transactions short.

9. Indexes & Query Optimization

9.1 B‑Tree Basics

Composite B‑Tree indexes must start with the most selective columns. For paginated user orders, a covering index looks like:

CREATE INDEX orders_user_created_idx ON app.orders (user_id, created_at DESC, id DESC) INCLUDE (status, amount);
SELECT id, created_at, status, amount
FROM app.orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

9.2 Partial, Expression, and Include Indexes

Expression index for case‑insensitive lookup:

CREATE INDEX users_display_name_lower_idx ON app.users (lower(display_name));

Partial index for pending orders:

CREATE INDEX orders_pending_created_idx ON app.orders (created_at, id) WHERE status = 'pending';

Include columns let an Index Only Scan return extra fields without visiting the heap.

9.3 Specialized Index Types

GIN for jsonb, arrays, full‑text search.

GiST for ranges, geometric data, and exclusion constraints.

BRIN for very large, append‑only tables where column order matches physical storage.

9.4 Real Execution Plans

Run EXPLAIN (ANALYZE, BUFFERS) to see actual timings, row estimates vs. actual rows, and whether data came from cache or disk. Use the information to adjust statistics, indexes, or query structure.

10. Extensions – PostgreSQL’s Extensible Ecosystem

Extensions are installed per‑database with CREATE EXTENSION. Trusted extensions (e.g., pg_trgm, btree_gist, citext) can be installed by non‑superusers; others require superuser rights.

Useful extensions include: pg_stat_statements – aggregates query execution statistics. pg_trgm – enables indexed LIKE '%text%' and similarity searches. citext – case‑insensitive text type. pgcrypto – cryptographic functions. postgres_fdw – foreign‑data wrapper for remote PostgreSQL servers. pgvector – vector type and nearest‑neighbor index for embeddings. PostGIS – full GIS capabilities.

Be aware that hosted databases may restrict which extensions are available, and some extensions require version‑specific updates.

11. Conclusion – Turn MySQL Experience into a Coordinate System

Most relational concepts transfer directly, but you must adjust assumptions about schemas, connection processes, isolation levels, gap locking, vacuuming, and the richer index toolbox. Treat the MySQL knowledge as a coordinate system rather than a rigid lock, and verify each “obvious” mapping in PostgreSQL before relying on it.