Posted on May 6, 2026May 6, 2026 | by Rajesh Kumar

Introduction

If you already know MySQL well, learning PostgreSQL becomes much easier when you stop trying to memorize PostgreSQL from zero and instead ask:

“What is the PostgreSQL equivalent of the thing I already know in MySQL?”

That is exactly how this guide is structured.

We will compare MySQL and PostgreSQL components, resources, architecture, commands, tools, users, roles, schemas, storage, logs, backup, replication, monitoring, performance, and SQL syntax.

The goal is not just to say:

MySQL thing = PostgreSQL thing

The goal is to understand:

What does it mean?
How does it behave?
How should I think about it as a MySQL user?

1. Big Picture Mental Model

MySQL Mental Model

In MySQL, you usually think like this:

MySQL Server
 ├── Database / Schema
 │    ├── Tables
 │    ├── Views
 │    ├── Procedures
 │    └── Triggers
 ├── Users
 ├── Privileges
 ├── Storage Engines
 │    ├── InnoDB
 │    ├── MyISAM
 │    └── MEMORY
 └── Logs
      ├── Error log
      ├── Binary log
      ├── Slow query log
      └── General log

PostgreSQL Mental Model

In PostgreSQL, think like this:

PostgreSQL Server / Cluster
 ├── Databases
 │    ├── Schemas
 │    │    ├── Tables
 │    │    ├── Views
 │    │    ├── Functions
 │    │    ├── Sequences
 │    │    └── Types
 ├── Roles
 │    ├── Login roles
 │    └── Group roles
 ├── Tablespaces
 ├── WAL
 ├── Background processes
 └── Extensions

The most important difference:

MySQL:
Database and schema usually mean the same thing.

PostgreSQL:
A database contains schemas.
A schema contains tables.

So:

MySQL:
server → database → table

PostgreSQL:
server/cluster → database → schema → table

2. Core Component Mapping

MySQL Component	PostgreSQL Equivalent	Meaning
MySQL server	PostgreSQL server / cluster	Running database service
Database	Database	Logical database container
Schema	Schema inside database	Namespace inside a database
Table	Table	Stores rows and columns
View	View	Saved query
Materialized view	Materialized view	Stored query result
User	Role with `LOGIN`	PostgreSQL users are login roles
Group	Role without `LOGIN`	Used to group permissions
Privilege	Privilege	Controls access
Storage engine	PostgreSQL storage system	PostgreSQL does not use engines like MySQL
InnoDB redo log	WAL	Write-Ahead Log
Binary log	WAL / logical replication	Used for recovery and replication
AUTO_INCREMENT	Identity / sequence	Auto-generated numbers
Performance Schema	`pg_stat_*` views	Monitoring and metrics
`my.cnf`	`postgresql.conf`	Main config
User host grants	`pg_hba.conf`	Client authentication rules
`mysqldump`	`pg_dump`	Backup tool
`mysql` CLI	`psql`	Command-line client

3. Server, Instance, and Cluster

MySQL

In MySQL, you usually say:

MySQL instance
MySQL server
mysqld process

A MySQL instance usually means one running MySQL server process using one data directory.

Example:

mysqld

PostgreSQL

PostgreSQL commonly uses the word cluster.

A PostgreSQL cluster means:

One PostgreSQL data directory initialized by initdb

It contains:

Databases
Roles
WAL
System catalogs
Configuration

Important: in PostgreSQL, a cluster does not mean multiple servers in this context.

That confuses many people.

In PostgreSQL:

Cluster = one initialized PostgreSQL data directory

Example data directory:

/var/lib/postgresql/16/main

or:

/var/lib/pgsql/data

depending on Linux distribution.

4. Database and Schema Mapping

MySQL Database

In MySQL:

CREATE DATABASE shop;
USE shop;
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

Here, shop is the database.

Many MySQL people also call this a schema.

So in MySQL:

Database ≈ Schema

PostgreSQL Database

In PostgreSQL:

CREATE DATABASE shop;

Then connect to it:

\c shop

Inside that database, you usually have a schema called:

public

Then you create tables inside the schema:

CREATE TABLE public.customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

So in PostgreSQL:

Database contains schemas.
Schema contains tables.

PostgreSQL Object Path

A full table name in PostgreSQL can look like this:

database.schema.table

But inside SQL queries, you usually use:

SELECT * FROM public.customers;

You do not usually write the database name in front of the table because you are already connected to one database.

Important Difference

In MySQL, you can commonly do:

SELECT *
FROM db1.customers c
JOIN db2.orders o ON c.id = o.customer_id;

In PostgreSQL, cross-database queries are not naturally done this way. You usually work inside one database and use multiple schemas:

shop database
 ├── sales.customers
 ├── sales.orders
 ├── accounting.invoices
 └── audit.events

PostgreSQL encourages this style:

SELECT *
FROM sales.customers c
JOIN accounting.invoices i ON c.id = i.customer_id;

5. System Databases and System Schemas

MySQL System Databases

Common MySQL system databases:

MySQL Database	Purpose
`mysql`	Users, privileges, system metadata
`information_schema`	SQL-standard metadata views
`performance_schema`	Performance and runtime metrics
`sys`	Friendly views over performance schema

You may see:

SHOW DATABASES;

Output:

information_schema
mysql
performance_schema
sys
myapp

PostgreSQL System Databases and Schemas

PostgreSQL has special databases and schemas.

Common PostgreSQL databases:

PostgreSQL Database	Purpose
`postgres`	Default administrative database
`template0`	Clean database template
`template1`	Default template for new databases

Common PostgreSQL schemas:

PostgreSQL Schema	Purpose
`pg_catalog`	PostgreSQL system catalog
`information_schema`	SQL-standard metadata views
`public`	Default user schema
`pg_toast`	Internal storage for large values

In PostgreSQL, system metadata mostly lives in:

pg_catalog

Example:

SELECT *
FROM pg_catalog.pg_tables;

But as a beginner, you often use psql commands:

\l
\dt
\d table_name
\du

6. Users vs Roles

This is one of the biggest differences.

MySQL User

In MySQL, a user is identified by username and host:

'root'@'localhost'
'appuser'@'%'
'appuser'@'192.168.1.%'

Example:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secret';
GRANT SELECT ON shop.* TO 'appuser'@'localhost';

In MySQL:

User identity = username + host

PostgreSQL Role

PostgreSQL has roles.

A role can be:

A user
A group
Both

A PostgreSQL “user” is simply:

A role with LOGIN permission

Example:

CREATE ROLE appuser WITH LOGIN PASSWORD 'secret';

This is equivalent to:

CREATE USER appuser WITH PASSWORD 'secret';

Because:

CREATE USER

is shorthand for:

CREATE ROLE ... LOGIN

PostgreSQL Group-Like Role

Create a role that cannot log in:

CREATE ROLE readonly;

Give permissions to that group role:

GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Assign it to a login role:

GRANT readonly TO appuser;

Now appuser inherits permissions from readonly.

MySQL vs PostgreSQL User Mapping

MySQL	PostgreSQL
User	Role with `LOGIN`
Group of permissions	Role without `LOGIN`
`'user'@'host'`	Role + `pg_hba.conf`
`CREATE USER`	`CREATE ROLE ... LOGIN`
`GRANT ON db.*`	`GRANT ON DATABASE`, `SCHEMA`, `TABLES`, `SEQUENCES`

Important PostgreSQL Access Layers

PostgreSQL access usually has four layers:

1. Can the client reach the server?
2. Does pg_hba.conf allow this connection?
3. Can the role connect to the database?
4. Does the role have privileges on schema/table/sequence?

This surprises MySQL users because MySQL grants often feel more direct.

7. Authentication Mapping

MySQL Authentication

MySQL controls user access through user accounts like:

'appuser'@'localhost'
'appuser'@'%'

Examples:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'secret';
GRANT SELECT ON shop.* TO 'appuser'@'%';

PostgreSQL Authentication

PostgreSQL separates:

Who you are       → role
Can you log in    → LOGIN attribute
Where from/how    → pg_hba.conf
What you can do   → GRANT privileges

The file:

pg_hba.conf

controls connection rules.

Example:

host    shop    appuser    192.168.1.0/24    scram-sha-256

Meaning:

Allow appuser to connect to database shop
from 192.168.1.x
using password authentication.

Common PostgreSQL Authentication Methods

Method	Meaning
`trust`	No password required
`peer`	OS user must match database role
`md5`	Password authentication
`scram-sha-256`	Stronger password authentication
`cert`	Client certificate authentication
`ldap`	LDAP authentication
`pam`	PAM authentication

For modern PostgreSQL, scram-sha-256 is commonly preferred over md5.

8. Privileges and Grants

MySQL Grant Style

GRANT SELECT, INSERT, UPDATE
ON shop.*
TO 'appuser'@'localhost';

This grants privileges on all tables in database shop.

PostgreSQL Grant Style

PostgreSQL is more layered.

You may need:

GRANT CONNECT ON DATABASE shop TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

Why sequences?

Because PostgreSQL auto-increment values are often backed by sequence objects.

PostgreSQL Default Privileges

If you want future tables to automatically get permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE ON TABLES TO appuser;

For future sequences:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO appuser;

Permission Mapping

MySQL Permission	PostgreSQL Equivalent
`SELECT`	`SELECT`
`INSERT`	`INSERT`
`UPDATE`	`UPDATE`
`DELETE`	`DELETE`
`CREATE`	`CREATE`
`DROP`	object ownership / `DROP` as owner
`ALTER`	object ownership / specific privileges
`INDEX`	usually table ownership
`EXECUTE`	`EXECUTE` on functions
`USAGE`	schema/sequence/type usage
`ALL PRIVILEGES`	`ALL PRIVILEGES`

9. Storage Engine vs PostgreSQL Storage

MySQL Storage Engines

MySQL supports multiple storage engines:

Engine	Meaning
InnoDB	Default transactional engine
MyISAM	Older non-transactional engine
MEMORY	Stores data in memory
CSV	Stores data in CSV files
ARCHIVE	Compressed archival storage
FEDERATED	Remote table access

Most production MySQL systems use:

InnoDB

PostgreSQL Storage

PostgreSQL does not work the same way.

You do not normally choose:

ENGINE=InnoDB

There is no typical equivalent of:

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

In PostgreSQL, table storage is native.

You choose table types instead:

PostgreSQL Table Type	Meaning
Regular table	Normal persistent table
Temporary table	Session-local temporary table
Unlogged table	Faster table, not WAL-logged
Partitioned table	Logical parent table with partitions
Foreign table	Table backed by external data source
Materialized view	Stored result of a query

Example regular table:

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

Example unlogged table:

CREATE UNLOGGED TABLE staging_imports (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB
);

Unlogged tables are faster for writes, but they are not crash-safe in the same way regular tables are.

10. Tablespaces

MySQL

In MySQL/InnoDB, tablespaces exist, but many users mostly deal with:

ibdata files
.ibd files
datadir

With innodb_file_per_table, each table can have its own .ibd file.

PostgreSQL

PostgreSQL has tablespaces too.

A tablespace lets you place database objects in a different filesystem location.

Example:

CREATE TABLESPACE fastspace
LOCATION '/mnt/fastdisk/postgres';

Create table in that tablespace:

CREATE TABLE big_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_data JSONB
) TABLESPACE fastspace;

Mental model:

Tablespace = storage location for database objects

11. Auto Increment vs Sequence / Identity

MySQL AUTO_INCREMENT

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

Insert:

INSERT INTO users (name) VALUES ('Alice');

MySQL automatically generates id.

PostgreSQL Identity

Modern PostgreSQL style:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

Another style:

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

Difference:

PostgreSQL Identity Type	Meaning
`GENERATED ALWAYS`	PostgreSQL always generates value unless explicitly overridden
`GENERATED BY DEFAULT`	PostgreSQL generates value if you do not provide one

Older PostgreSQL style:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

SERIAL still works, but identity columns are the modern SQL-standard style.

PostgreSQL Sequence

PostgreSQL identity and serial columns use sequences.

A sequence is an independent object that generates numbers.

Example:

CREATE SEQUENCE order_id_seq;

Get next value:

SELECT nextval('order_id_seq');

Important:

PostgreSQL sequences are not rolled back.

If a transaction gets a sequence number and then rolls back, that number may be skipped.

That is normal.

12. Data Type Mapping

MySQL Type	PostgreSQL Type	Notes
`TINYINT`	`SMALLINT`	PostgreSQL has no `TINYINT`
`TINYINT(1)`	`BOOLEAN`	Common MySQL boolean conversion
`SMALLINT`	`SMALLINT`	Same
`INT`	`INTEGER` / `INT`	Same
`BIGINT`	`BIGINT`	Same
`DECIMAL`	`NUMERIC` / `DECIMAL`	Same purpose
`FLOAT`	`REAL`	4-byte floating number
`DOUBLE`	`DOUBLE PRECISION`	8-byte floating number
`CHAR(n)`	`CHAR(n)`	Same
`VARCHAR(n)`	`VARCHAR(n)`	Same
`TEXT`	`TEXT`	PostgreSQL uses this heavily
`DATE`	`DATE`	Same
`DATETIME`	`TIMESTAMP`	PostgreSQL equivalent
`TIMESTAMP`	`TIMESTAMP` / `TIMESTAMPTZ`	Prefer `TIMESTAMPTZ` for real-world time
`TIME`	`TIME`	Same
`BLOB`	`BYTEA`	Binary data
`JSON`	`JSON` / `JSONB`	`JSONB` is usually preferred
`ENUM`	`ENUM` / lookup table	PostgreSQL supports custom enum types
`SET`	Array / join table	No direct same feature
`UUID`	`UUID`	PostgreSQL has strong native UUID support

Boolean Difference

MySQL often uses:

is_active TINYINT(1)

PostgreSQL uses:

is_active BOOLEAN

Values:

TRUE
FALSE

Example:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

Date and Time Difference

MySQL:

created_at DATETIME

PostgreSQL:

created_at TIMESTAMP

Better for timezone-aware data:

created_at TIMESTAMPTZ

Important PostgreSQL note:

TIMESTAMPTZ does not store the timezone name.
It stores an absolute point in time and displays it according to session timezone.

JSON Difference

MySQL:

data JSON

PostgreSQL:

data JSONB

Example:

CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB NOT NULL
);

Query:

SELECT *
FROM events
WHERE payload->>'type' = 'login';

JSONB indexing:

CREATE INDEX idx_events_payload
ON events USING GIN (payload);

13. SQL Syntax Mapping

Selecting Rows

Same:

SELECT *
FROM users;

Limit

Same:

SELECT *
FROM users
LIMIT 10;

Offset

Same:

SELECT *
FROM users
LIMIT 10 OFFSET 20;

String Concatenation

MySQL:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

PostgreSQL:

SELECT first_name || ' ' || last_name AS full_name
FROM users;

PostgreSQL also supports:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

Current Date and Time

MySQL	PostgreSQL
`NOW()`	`NOW()`
`CURRENT_TIMESTAMP`	`CURRENT_TIMESTAMP`
`CURDATE()`	`CURRENT_DATE`
`CURTIME()`	`CURRENT_TIME`

MySQL:

SELECT CURDATE();

PostgreSQL:

SELECT CURRENT_DATE;

Case-Insensitive Search

MySQL often depends on collation:

SELECT *
FROM users
WHERE name LIKE '%john%';

PostgreSQL has ILIKE:

SELECT *
FROM users
WHERE name ILIKE '%john%';

Upsert

MySQL:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE
    name = VALUES(name);

PostgreSQL:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name;

This is a major mapping:

MySQL ON DUPLICATE KEY UPDATE
=
PostgreSQL ON CONFLICT DO UPDATE

Replace

MySQL has:

REPLACE INTO users (id, name)
VALUES (1, 'Alice');

PostgreSQL does not have the same REPLACE INTO.

Use:

INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

Insert Ignore

MySQL:

INSERT IGNORE INTO users (email, name)
VALUES ('alice@example.com', 'Alice');

PostgreSQL:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT DO NOTHING;

Group By Strictness

MySQL may allow loose grouping depending on SQL mode:

SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

PostgreSQL rejects this because name is neither grouped nor aggregated.

Correct PostgreSQL:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This is important:

PostgreSQL is stricter and more SQL-standard in many areas.

14. Command-Line Tool Mapping

MySQL Client

mysql -u root -p

Connect to database:

mysql -u appuser -p shop

PostgreSQL Client

psql -U postgres

Connect to database:

psql -U appuser -d shop

Connect with host and port:

psql -h localhost -p 5432 -U appuser -d shop

Useful MySQL vs PostgreSQL Commands

Task	MySQL	PostgreSQL
Show databases	`SHOW DATABASES;`	`\l`
Use database	`USE shop;`	`\c shop`
Show tables	`SHOW TABLES;`	`\dt`
Describe table	`DESCRIBE users;`	`\d users`
Show users	Query `mysql.user`	`\du`
Show current database	`SELECT DATABASE();`	`SELECT current_database();`
Show current user	`SELECT USER();`	`SELECT current_user;`
Show variables	`SHOW VARIABLES;`	`SHOW ALL;`
Show process list	`SHOW PROCESSLIST;`	Query `pg_stat_activity`
Exit	`exit`	`\q`

PostgreSQL `psql` Commands

Command	Meaning
`\l`	List databases
`\c dbname`	Connect to database
`\dn`	List schemas
`\dt`	List tables
`\dv`	List views
`\dm`	List materialized views
`\df`	List functions
`\ds`	List sequences
`\du`	List roles
`\d table_name`	Describe table
`\x`	Expanded output
`\q`	Quit

Example:

\l
\c shop
\dn
\dt
\d users

15. Configuration Files

MySQL

Common MySQL config files:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

Main config sections:

[mysqld]

[client]

[mysql]

Example:

[mysqld]
bind-address = 0.0.0.0
max_connections = 200
innodb_buffer_pool_size = 2G
slow_query_log = 1
long_query_time = 2

PostgreSQL

Main PostgreSQL config files:

postgresql.conf
pg_hba.conf
pg_ident.conf

File	Meaning
`postgresql.conf`	Main server settings
`pg_hba.conf`	Host-based authentication
`pg_ident.conf`	Maps OS users to DB users

Example postgresql.conf:

listen_addresses = '*'
max_connections = 200
shared_buffers = 2GB
work_mem = 16MB
maintenance_work_mem = 512MB
log_min_duration_statement = 2000

Example pg_hba.conf:

host    shop    appuser    192.168.1.0/24    scram-sha-256

16. Port and Service Mapping

Component	MySQL	PostgreSQL
Default port	`3306`	`5432`
Server process	`mysqld`	`postgres`
CLI client	`mysql`	`psql`
Service name	`mysql` / `mysqld`	`postgresql`
Admin database	`mysql`	`postgres`

Check service:

systemctl status mysql

PostgreSQL:

systemctl status postgresql

17. Memory Resource Mapping

MySQL Important Memory Settings

MySQL Setting	Meaning
`innodb_buffer_pool_size`	Main InnoDB data/index cache
`sort_buffer_size`	Per-connection sort buffer
`join_buffer_size`	Per-connection join buffer
`tmp_table_size`	Max internal temporary table size
`max_heap_table_size`	Max MEMORY table size
`key_buffer_size`	MyISAM index cache
`max_connections`	Max client connections

Most important MySQL memory setting for InnoDB:

innodb_buffer_pool_size

PostgreSQL Important Memory Settings

PostgreSQL Setting	Meaning
`shared_buffers`	Main PostgreSQL buffer cache
`work_mem`	Per-operation memory for sort/hash
`maintenance_work_mem`	Memory for vacuum, create index, alter table
`effective_cache_size`	Planner estimate of OS cache
`wal_buffers`	Memory for WAL
`max_connections`	Max client connections

Most important PostgreSQL memory settings:

shared_buffers
work_mem
maintenance_work_mem
effective_cache_size

Key Difference

MySQL/InnoDB heavily relies on:

InnoDB buffer pool

PostgreSQL relies on:

shared_buffers + operating system page cache

So do not map this too literally:

innodb_buffer_pool_size = shared_buffers

They are related, but not identical.

18. Connection Resource Mapping

MySQL

MySQL commonly uses threads for connections.

Command:

SHOW PROCESSLIST;

PostgreSQL

PostgreSQL traditionally uses a process per connection.

View active sessions:

SELECT *
FROM pg_stat_activity;

Useful query:

SELECT
    pid,
    usename,
    datname,
    state,
    query
FROM pg_stat_activity;

Connection Pooling

PostgreSQL often benefits from connection pooling because too many direct connections can become expensive.

Common pooler:

PgBouncer

Mental mapping:

MySQL	PostgreSQL
Many app connections may be okay depending on setup	Use connection pooling earlier
`SHOW PROCESSLIST`	`pg_stat_activity`
Kill connection with `KILL id`	`SELECT pg_terminate_backend(pid);`

PostgreSQL terminate session:

SELECT pg_terminate_backend(12345);

Cancel running query:

SELECT pg_cancel_backend(12345);

19. Transaction Mapping

MySQL

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

PostgreSQL

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Same general concept.

Rollback:

ROLLBACK;

Important PostgreSQL Difference

In PostgreSQL, if an error happens inside a transaction, the transaction becomes failed.

Example:

BEGIN;

SELECT 1 / 0;

SELECT * FROM users;

PostgreSQL will say the transaction is aborted until you run:

ROLLBACK;

This surprises MySQL users.

Transactional DDL

PostgreSQL supports transactional DDL for many operations.

Example:

BEGIN;

CREATE TABLE test_table (
    id INT
);

ROLLBACK;

After rollback, the table is gone.

In MySQL, many DDL operations cause implicit commits.

20. MVCC Mapping

Both MySQL/InnoDB and PostgreSQL use MVCC.

MVCC means:

Multi-Version Concurrency Control

It allows readers and writers to avoid blocking each other in many cases.

MySQL/InnoDB MVCC

InnoDB stores undo information in undo logs.

Old row versions are reconstructed from undo data.

PostgreSQL MVCC

PostgreSQL stores multiple row versions directly in the table.

When you update a row, PostgreSQL creates a new row version.

Old versions are later cleaned up by:

VACUUM

Important PostgreSQL Concept: Dead Tuples

When PostgreSQL updates or deletes rows, old row versions remain for a while.

These old row versions are called:

dead tuples

They are cleaned by:

autovacuum

This is one of the biggest PostgreSQL operational topics.

21. Vacuum vs MySQL Purge

MySQL/InnoDB

InnoDB uses undo logs and purge threads to clean old row versions.

You usually do not manually run something called vacuum.

PostgreSQL

PostgreSQL needs vacuum.

Manual vacuum:

VACUUM users;

Vacuum and update statistics:

VACUUM ANALYZE users;

Full table rewrite:

VACUUM FULL users;

Important:

VACUUM FULL is heavier and takes stronger locks.
Do not treat it as a routine command.

Autovacuum

PostgreSQL normally runs autovacuum automatically.

Autovacuum handles:

Cleaning dead tuples
Preventing transaction ID wraparound
Updating table statistics

For MySQL users, think:

PostgreSQL autovacuum is not optional housekeeping.
It is essential database maintenance.

22. Index Mapping

Common Index Types

MySQL Index	PostgreSQL Equivalent
Primary key	Primary key
Unique index	Unique index
Normal index	B-tree index
Composite index	Composite index
Full-text index	Full-text search index
Spatial index	GiST/SP-GiST, often with PostGIS
Functional index	Expression index
Prefix index	Expression or specialized index
Invisible index	No exact same common equivalent

Basic Index

MySQL:

CREATE INDEX idx_users_email
ON users(email);

PostgreSQL:

CREATE INDEX idx_users_email
ON users(email);

Same.

Unique Index

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Composite Index

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Same concept as MySQL.

Expression Index

PostgreSQL is excellent here.

CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

Then query:

SELECT *
FROM users
WHERE LOWER(email) = LOWER('Alice@Example.com');

Partial Index

This is a powerful PostgreSQL feature.

CREATE INDEX idx_active_users_email
ON users(email)
WHERE is_active = TRUE;

This indexes only active users.

MySQL does not have the same general partial-index feature.

PostgreSQL Index Types

PostgreSQL Index Type	Use
B-tree	Default, equality/range
Hash	Equality
GIN	JSONB, arrays, full-text
GiST	Geometric, full-text, ranges
SP-GiST	Partitioned search structures
BRIN	Very large naturally ordered tables

Example JSONB GIN index:

CREATE INDEX idx_events_payload
ON events USING GIN (payload);

Example BRIN index:

CREATE INDEX idx_logs_created_at_brin
ON logs USING BRIN (created_at);

BRIN is useful for huge append-only tables where data is naturally ordered by time.

23. Explain Plan Mapping

MySQL

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

MySQL also supports:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

in newer versions.

PostgreSQL

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

Better:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

Even better for details:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = 'alice@example.com';

PostgreSQL Plan Terms

Term	Meaning
`Seq Scan`	Full table scan
`Index Scan`	Uses index and table
`Index Only Scan`	Uses only index if possible
`Bitmap Index Scan`	Builds bitmap from index
`Nested Loop`	Join by looping
`Hash Join`	Join using hash table
`Merge Join`	Join sorted inputs
`Sort`	Sort operation
`Aggregate`	Aggregation
`HashAggregate`	Aggregation using hash table

Important:

EXPLAIN ANALYZE actually runs the query.

So be careful with:

EXPLAIN ANALYZE
DELETE FROM large_table;

24. Logs Mapping

MySQL Logs

MySQL Log	Meaning
Error log	Server errors and startup messages
General query log	All client queries
Slow query log	Slow SQL statements
Binary log	Replication and point-in-time recovery
Relay log	Replica receives source binary log events
Redo log	InnoDB crash recovery
Undo log	MVCC rollback data

PostgreSQL Logs

PostgreSQL Log/Resource	Meaning
Server log	Errors, messages, slow queries
WAL	Write-Ahead Log for recovery and replication
Archive WAL	Saved WAL files for PITR
Autovacuum logs	Vacuum activity if enabled
Checkpoint logs	Checkpoint activity if enabled

Slow Query Logging

MySQL:

slow_query_log = 1
long_query_time = 2

PostgreSQL:

log_min_duration_statement = 2000

This logs queries taking longer than 2000 milliseconds.

Statement Logging

PostgreSQL:

log_statement = 'none'
log_statement = 'ddl'
log_statement = 'mod'
log_statement = 'all'

Common safer setting:

log_statement = 'ddl'

25. WAL vs Binary Log

This is a major concept.

MySQL Binary Log

MySQL binary log records changes for:

Replication
Point-in-time recovery
Auditing changes

Common formats:

STATEMENT
ROW
MIXED

PostgreSQL WAL

PostgreSQL uses:

WAL = Write-Ahead Log

WAL is used for:

Crash recovery
Streaming replication
Point-in-time recovery
Logical decoding

Mapping

MySQL	PostgreSQL
Binary log	WAL for replication/recovery concepts
Redo log	WAL
Relay log	Received WAL on standby
Binlog position	LSN
GTID	Replication slots / LSN concepts
Row-based replication	Logical replication / WAL decoding

PostgreSQL uses LSN:

Log Sequence Number

Example:

SELECT pg_current_wal_lsn();

26. Backup and Restore Mapping

MySQL Logical Backup

mysqldump -u root -p shop > shop.sql

Restore:

mysql -u root -p shop < shop.sql

PostgreSQL Logical Backup

pg_dump -U postgres shop > shop.sql

Restore:

psql -U postgres -d shop < shop.sql

PostgreSQL Custom Format Backup

pg_dump -U postgres -Fc shop > shop.dump

Restore:

pg_restore -U postgres -d shop shop.dump

Backup All Databases

MySQL:

mysqldump -u root -p --all-databases > all.sql

PostgreSQL:

pg_dumpall -U postgres > all.sql

Physical Backup

MySQL:

Filesystem snapshot
MySQL Enterprise Backup
Percona XtraBackup

PostgreSQL:

pg_basebackup
Filesystem snapshot
Storage snapshot

Example:

pg_basebackup -U replication_user -D /backup/pgbase -Fp -Xs -P

Point-In-Time Recovery

MySQL PITR uses:

Full backup + binary logs

PostgreSQL PITR uses:

Base backup + archived WAL

27. Replication Mapping

MySQL Replication

Common terms:

Source
Replica
Binary log
Relay log
GTID
Semi-sync replication

Older terms:

Master
Slave

PostgreSQL Replication

Common terms:

Primary
Standby
WAL streaming
Replication slot
Synchronous replication
Logical replication

Mapping Table

MySQL	PostgreSQL
Source/master	Primary
Replica/slave	Standby
Binary log	WAL
Relay log	WAL received by standby
Binlog position	LSN
GTID	Replication slots / LSN tracking
Row-based replication	Logical replication
Statement-based replication	No exact direct default equivalent
Semi-sync replication	Synchronous replication
Read replica	Hot standby

PostgreSQL Physical Streaming Replication

This replicates the whole PostgreSQL cluster.

Good for:

Read replicas
Failover
Disaster recovery

PostgreSQL Logical Replication

This replicates selected tables.

Good for:

Selective replication
Data migration
Partial sync
Version upgrades

Example publication:

CREATE PUBLICATION shop_pub
FOR TABLE customers, orders;

Example subscription:

CREATE SUBSCRIPTION shop_sub
CONNECTION 'host=primary dbname=shop user=repl password=secret'
PUBLICATION shop_pub;

28. High Availability Mapping

MySQL HA Tools

Common MySQL HA options:

Group Replication
InnoDB Cluster
MHA
Orchestrator
ProxySQL
Semi-sync replication

PostgreSQL HA Tools

Common PostgreSQL HA tools:

Patroni
repmgr
pg_auto_failover
Pacemaker/Corosync
HAProxy
PgBouncer

Conceptual Mapping

MySQL HA Concept	PostgreSQL Equivalent
Primary with replicas	Primary with standbys
Automatic failover	Patroni/repmgr/pg_auto_failover
ProxySQL routing	HAProxy/PgBouncer
Group Replication	No exact default equivalent
InnoDB Cluster	PostgreSQL HA stack using external tools

PostgreSQL core provides strong replication primitives, but automatic HA is commonly built with external tooling.

29. Partitioning Mapping

MySQL Partitioning

Example:

CREATE TABLE orders (
    id BIGINT,
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

PostgreSQL Partitioning

PostgreSQL uses declarative partitioning.

Example:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);

Create partitions:

CREATE TABLE orders_2025
PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE orders_2026
PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Partitioning Types

MySQL	PostgreSQL
RANGE	RANGE
LIST	LIST
HASH	HASH
KEY	No exact same style

PostgreSQL partitioning is commonly used for:

Large time-series tables
Log tables
Multi-tenant data
Data retention

30. Temporary Tables

MySQL

CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(100)
);

PostgreSQL

CREATE TEMP TABLE temp_users (
    id INT,
    name TEXT
);

PostgreSQL temporary tables are session-local.

You can also use:

CREATE TEMPORARY TABLE temp_users (
    id INT,
    name TEXT
);

Same meaning.

31. Views and Materialized Views

Views

MySQL:

CREATE VIEW active_users AS
SELECT *
FROM users
WHERE is_active = 1;

PostgreSQL:

CREATE VIEW active_users AS
SELECT *
FROM users
WHERE is_active = TRUE;

Materialized Views

PostgreSQL supports materialized views natively:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY date_trunc('month', order_date);

Refresh:

REFRESH MATERIALIZED VIEW monthly_sales;

MySQL does not have true built-in materialized views in the same way. You usually simulate them with tables, scheduled jobs, or triggers.

32. Functions, Procedures, and Triggers

MySQL

MySQL supports:

Stored procedures
Functions
Triggers
Events

Example procedure:

DELIMITER //

CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //

DELIMITER ;

PostgreSQL

PostgreSQL supports:

Functions
Procedures
Triggers
Rules
Extensions

PostgreSQL function example:

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
DECLARE
    total INTEGER;
BEGIN
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Call:

SELECT get_user_count();

Procedure example:

CREATE PROCEDURE deactivate_user(p_user_id BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users
    SET is_active = FALSE
    WHERE id = p_user_id;
END;
$$;

Call:

CALL deactivate_user(10);

Trigger Example in PostgreSQL

CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();

33. Events vs Scheduled Jobs

MySQL Event Scheduler

MySQL has an event scheduler:

CREATE EVENT cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL 30 DAY;

PostgreSQL

PostgreSQL core does not have exactly the same built-in event scheduler.

Common options:

cron
pg_cron extension
application scheduler
external job scheduler

With pg_cron, PostgreSQL can schedule jobs from inside the database.

Conceptual mapping:

MySQL	PostgreSQL
Event Scheduler	`pg_cron`, cron, external scheduler

34. Extensions vs Plugins

MySQL Plugins

MySQL supports plugins for:

Authentication
Storage engines
Full-text parsers
Audit
Replication

PostgreSQL Extensions

PostgreSQL has a powerful extension system.

List available extensions:

SELECT *
FROM pg_available_extensions;

Install extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Extension	Purpose
`pg_stat_statements`	Query performance tracking
`uuid-ossp`	UUID generation
`pgcrypto`	Cryptographic functions
`citext`	Case-insensitive text
`postgis`	Spatial/geographic data
`pg_trgm`	Trigram search
`btree_gin`	Extra GIN support
`pg_cron`	Scheduled jobs

35. Monitoring Mapping

MySQL Monitoring

Common commands:

SHOW STATUS;
SHOW GLOBAL STATUS;
SHOW VARIABLES;
SHOW PROCESSLIST;

Common schemas:

performance_schema
sys
information_schema

PostgreSQL Monitoring

PostgreSQL uses catalog and statistics views.

Common views:

PostgreSQL View	Meaning
`pg_stat_activity`	Active sessions and queries
`pg_stat_database`	Database-level stats
`pg_stat_user_tables`	Table-level stats
`pg_stat_user_indexes`	Index-level stats
`pg_stat_bgwriter`	Background writer stats
`pg_stat_wal`	WAL stats
`pg_locks`	Lock information
`pg_stat_replication`	Replication status
`pg_stat_statements`	Query stats extension

Active Queries

MySQL:

SHOW PROCESSLIST;

PostgreSQL:

SELECT
    pid,
    usename,
    datname,
    state,
    query,
    query_start
FROM pg_stat_activity
ORDER BY query_start;

Locks

PostgreSQL:

SELECT *
FROM pg_locks;

Useful lock query:

SELECT
    a.pid,
    a.usename,
    a.query,
    l.locktype,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;

36. Query Performance Mapping

MySQL

You may use:

EXPLAIN
slow query log
performance_schema
sys schema
SHOW STATUS

PostgreSQL

You commonly use:

EXPLAIN ANALYZE
pg_stat_statements
pg_stat_activity
pg_stat_user_tables
pg_stat_user_indexes
auto_explain

`pg_stat_statements`

Enable extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query slow/expensive SQL:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This is somewhat like using MySQL Performance Schema or slow query analysis.

37. Locking Mapping

MySQL/InnoDB

Common lock concepts:

Row locks
Gap locks
Next-key locks
Metadata locks
Table locks
Deadlocks

PostgreSQL

Common lock concepts:

Row locks
Table locks
Advisory locks
Predicate locks
Deadlocks

Row Lock

Same style:

SELECT *
FROM orders
WHERE id = 100
FOR UPDATE;

PostgreSQL Lock Modes

Common PostgreSQL table lock modes include:

ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

The strongest common lock is:

ACCESS EXCLUSIVE

It blocks almost everything.

Advisory Locks

PostgreSQL has advisory locks:

SELECT pg_advisory_lock(12345);

Release:

SELECT pg_advisory_unlock(12345);

These are application-controlled locks.

38. Isolation Levels

MySQL

Common isolation levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

MySQL/InnoDB default is commonly:

REPEATABLE READ

PostgreSQL

PostgreSQL supports:

READ COMMITTED
REPEATABLE READ
SERIALIZABLE

PostgreSQL default:

READ COMMITTED

Set isolation level:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

or:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Important mapping:

MySQL	PostgreSQL
Common default: `REPEATABLE READ`	Default: `READ COMMITTED`
Gap locks under InnoDB	Different MVCC/locking behavior
Serializable	Serializable Snapshot Isolation

39. Character Set and Collation

MySQL

MySQL often uses:

CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci

Example:

CREATE DATABASE shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

Many MySQL collations are case-insensitive by default.

That means:

WHERE name = 'alice'

may match:

Alice
ALICE
alice

depending on collation.

PostgreSQL

PostgreSQL supports collations too, but case-sensitive behavior is more common by default.

Case-insensitive search:

SELECT *
FROM users
WHERE name ILIKE 'alice';

Or use LOWER():

SELECT *
FROM users
WHERE LOWER(name) = LOWER('Alice');

Or use citext extension:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    email CITEXT UNIQUE
);

CITEXT means case-insensitive text.

40. Full-Text Search

MySQL

MySQL full-text index:

CREATE FULLTEXT INDEX idx_articles_body
ON articles(body);

Query:

SELECT *
FROM articles
WHERE MATCH(title, body) AGAINST ('database tutorial');

PostgreSQL

PostgreSQL full-text search uses tsvector and tsquery.

Simple query:

SELECT *
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ plainto_tsquery('english', 'database tutorial');

Better with generated column:

ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;

Index:

CREATE INDEX idx_articles_search
ON articles USING GIN (search_vector);

Query:

SELECT *
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database tutorial');

41. JSON Mapping

MySQL JSON

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    payload JSON
);

Query:

SELECT JSON_EXTRACT(payload, '$.type')
FROM events;

PostgreSQL JSONB

CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB
);

Get field as JSON:

SELECT payload->'type'
FROM events;

Get field as text:

SELECT payload->>'type'
FROM events;

Filter:

SELECT *
FROM events
WHERE payload->>'type' = 'login';

Containment:

SELECT *
FROM events
WHERE payload @> '{"type": "login"}';

Index:

CREATE INDEX idx_events_payload
ON events USING GIN (payload);

42. Constraints Mapping

Constraint	MySQL	PostgreSQL
Primary key	Yes	Yes
Foreign key	Yes with InnoDB	Yes
Unique	Yes	Yes
Not null	Yes	Yes
Check	Supported in modern MySQL	Strong support
Exclusion constraint	No common equivalent	Yes

PostgreSQL Check Constraint

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    CONSTRAINT chk_price_positive CHECK (price >= 0)
);

Foreign Key

Same idea:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

Deferrable Constraints

PostgreSQL supports deferrable constraints.

CREATE TABLE child (
    id BIGINT PRIMARY KEY,
    parent_id BIGINT,
    CONSTRAINT fk_parent
        FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        DEFERRABLE INITIALLY DEFERRED
);

This lets constraint checking happen at transaction commit.

Very powerful. Not something most MySQL users commonly use.

43. Generated Columns

MySQL

CREATE TABLE users (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(201)
        GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);

PostgreSQL

CREATE TABLE users (
    first_name TEXT,
    last_name TEXT,
    full_name TEXT
        GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

Both support generated columns, but syntax and function support differ.

44. Error Handling and Strictness

PostgreSQL is stricter than MySQL in many situations.

Example: Invalid Integer

MySQL may allow or coerce depending on SQL mode:

INSERT INTO users(id) VALUES ('abc');

PostgreSQL will reject invalid type conversion.

Example: Group By

PostgreSQL requires correct grouping.

Example: Transaction Error

PostgreSQL aborts the whole transaction after an error until rollback.

This strictness is actually one reason many developers like PostgreSQL: fewer silent surprises.

45. DDL Differences

Create Database

MySQL:

CREATE DATABASE shop;

PostgreSQL:

CREATE DATABASE shop;

Same.

Use Database

MySQL:

USE shop;

PostgreSQL:

\c shop

In PostgreSQL, \c is a psql command, not SQL.

Create Table

MySQL:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

PostgreSQL:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Drop Table

Same:

DROP TABLE users;

Truncate

Same:

TRUNCATE TABLE users;

PostgreSQL can restart identity:

TRUNCATE TABLE users RESTART IDENTITY;

46. Admin Commands Mapping

Task	MySQL	PostgreSQL
Start server	`systemctl start mysql`	`systemctl start postgresql`
Stop server	`systemctl stop mysql`	`systemctl stop postgresql`
Restart server	`systemctl restart mysql`	`systemctl restart postgresql`
Reload config	`systemctl reload mysql`	`SELECT pg_reload_conf();` or service reload
Check variables	`SHOW VARIABLES;`	`SHOW ALL;`
Check status	`SHOW STATUS;`	`pg_stat_*` views
List sessions	`SHOW PROCESSLIST;`	`pg_stat_activity`
Kill session	`KILL id;`	`pg_terminate_backend(pid)`

PostgreSQL reload config:

SELECT pg_reload_conf();

47. Information Schema and Catalogs

MySQL

SELECT *
FROM information_schema.tables
WHERE table_schema = 'shop';

PostgreSQL

SELECT *
FROM information_schema.tables
WHERE table_schema = 'public';

PostgreSQL also has richer internal catalogs:

SELECT *
FROM pg_catalog.pg_class;

List user tables:

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public';

List columns:

SELECT
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'users';

48. Security Model Mapping

MySQL Security Model

MySQL commonly combines:

User
Host
Password
Privileges

Example:

CREATE USER 'reporter'@'10.%' IDENTIFIED BY 'secret';
GRANT SELECT ON shop.* TO 'reporter'@'10.%';

PostgreSQL Security Model

PostgreSQL separates:

Role
Database permission
Schema permission
Object permission
Authentication rule

Example:

CREATE ROLE reporter WITH LOGIN PASSWORD 'secret';

GRANT CONNECT ON DATABASE shop TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

And in pg_hba.conf:

host    shop    reporter    10.0.0.0/8    scram-sha-256

PostgreSQL Row-Level Security

PostgreSQL has strong row-level security.

Enable:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Create policy:

CREATE POLICY user_orders_policy
ON orders
FOR SELECT
USING (customer_id = current_setting('app.customer_id')::BIGINT);

This is more advanced, but powerful for multi-tenant systems.

49. Schema Design Difference

MySQL Style

You may create multiple databases:

shop
shop_audit
shop_reporting

And query between them:

SELECT *
FROM shop.orders o
JOIN shop_reporting.order_summary s ON o.id = s.order_id;

PostgreSQL Style

You may create one database with multiple schemas:

shop database
 ├── app
 ├── audit
 ├── reporting
 └── staging

Query:

SELECT *
FROM app.orders o
JOIN reporting.order_summary s ON o.id = s.order_id;

This is one of the best PostgreSQL habits to learn early.

50. Practical Side-by-Side Example

Let us build the same small application schema in both databases.

MySQL Version

CREATE DATABASE shop;
USE shop;

CREATE TABLE customers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_total DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

PostgreSQL Version

CREATE DATABASE shop;

Connect:

\c shop

Create schema:

CREATE SCHEMA app;

Create tables:

CREATE TABLE app.customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE app.orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES app.customers(id)
);

CREATE INDEX idx_orders_customer_id
ON app.orders(customer_id);

Key Differences in This Example

MySQL	PostgreSQL
`AUTO_INCREMENT`	`GENERATED ALWAYS AS IDENTITY`
`TINYINT(1)`	`BOOLEAN`
`DATETIME`	`TIMESTAMPTZ`
`DECIMAL`	`NUMERIC`
Database used as namespace	Schema used as namespace
`USE shop`	`\c shop`

51. Common Migration Mapping

When converting MySQL schema to PostgreSQL:

MySQL	Convert To PostgreSQL
`AUTO_INCREMENT`	`GENERATED BY DEFAULT AS IDENTITY`
`TINYINT(1)`	`BOOLEAN`
`DATETIME`	`TIMESTAMP` or `TIMESTAMPTZ`
`LONGTEXT`	`TEXT`
`BLOB`	`BYTEA`
`JSON`	`JSONB`
`ENUM`	PostgreSQL enum or lookup table
`UNSIGNED INT`	Larger signed type or check constraint
`ON DUPLICATE KEY UPDATE`	`ON CONFLICT DO UPDATE`
`INSERT IGNORE`	`ON CONFLICT DO NOTHING`
Backticks	Double quotes or no quotes
`IFNULL()`	`COALESCE()`
`DATE_FORMAT()`	`to_char()`
`STR_TO_DATE()`	`to_date()` / `to_timestamp()`
`GROUP_CONCAT()`	`string_agg()`

Function Mapping

MySQL Function	PostgreSQL Function
`IFNULL(a,b)`	`COALESCE(a,b)`
`NOW()`	`NOW()`
`CURDATE()`	`CURRENT_DATE`
`CONCAT()`	`CONCAT()` or `
`GROUP_CONCAT()`	`string_agg()`
`DATE_FORMAT()`	`to_char()`
`JSON_EXTRACT()`	`->`, `->>`, `jsonb_path_query`
`RAND()`	`random()`
`UUID()`	`gen_random_uuid()` with `pgcrypto`

Example MySQL:

SELECT GROUP_CONCAT(name)
FROM users;

PostgreSQL:

SELECT string_agg(name, ',')
FROM users;

52. Quoting Difference

MySQL

MySQL often uses backticks:

SELECT `user`, `order`
FROM `my_table`;

PostgreSQL

PostgreSQL uses double quotes for identifiers:

SELECT "user", "order"
FROM "my_table";

But best practice:

Avoid reserved words and mixed-case identifiers.
Use lowercase snake_case.

Good PostgreSQL style:

CREATE TABLE customer_orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_status TEXT NOT NULL
);

Avoid:

CREATE TABLE "CustomerOrders" (
    "ID" BIGINT
);

Because then you must quote it forever.

53. Naming Conventions

54. PostgreSQL Features MySQL Users Should Appreciate

PostgreSQL has several powerful features that MySQL users may not expect.

Strong JSONB

payload JSONB

with GIN indexes.

Partial Indexes

CREATE INDEX idx_active_users
ON users(email)
WHERE is_active = TRUE;

Expression Indexes

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Rich Data Types

PostgreSQL supports:

UUID
JSONB
ARRAY
RANGE
INET
CIDR
MACADDR
TSVECTOR
GEOMETRY through PostGIS

Extensions

CREATE EXTENSION postgis;
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pgcrypto;

Transactional DDL

Rollback many schema changes safely.

Powerful CTEs

WITH active_customers AS (
    SELECT *
    FROM customers
    WHERE is_active = TRUE
)
SELECT *
FROM active_customers;

Returning Clause

PostgreSQL can return inserted/updated/deleted rows:

INSERT INTO users (name)
VALUES ('Alice')
RETURNING id, name;

This is extremely useful.

MySQL has some newer returning support in limited areas, but PostgreSQL’s RETURNING is widely used and very natural.

55. Common PostgreSQL Surprises for MySQL Users

Surprise 1: Users Are Roles

User = role with LOGIN

Surprise 2: Schema Is Not Database

database → schema → table

Surprise 3: Permissions Need Schema Grants

This is not enough:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

You also often need:

GRANT USAGE ON SCHEMA public TO appuser;

Surprise 4: Sequences Need Permissions

For identity/serial inserts, you may need:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO appuser;

Surprise 5: Failed Transaction Requires Rollback

After an error:

ROLLBACK;

Surprise 6: Case Sensitivity

PostgreSQL does not behave like case-insensitive MySQL collations by default.

Use:

ILIKE
LOWER()
citext

Surprise 7: Vacuum Matters

Autovacuum is essential.

Surprise 8: No `USE database`

Use:

\c database_name

Surprise 9: No Backticks

Use double quotes only when necessary.

Better: avoid quoting by using lowercase names.

Surprise 10: `EXPLAIN ANALYZE` Runs the Query

Do not use carelessly with destructive SQL.

56. Quick Reference Cheat Sheet

Object Hierarchy

MySQL:
server → database/schema → table

PostgreSQL:
cluster/server → database → schema → table

User System

MySQL:
'user'@'host'

PostgreSQL:
role + pg_hba.conf

Auto Increment

MySQL:
AUTO_INCREMENT

PostgreSQL:
GENERATED ALWAYS AS IDENTITY
sequence

Logs

MySQL:
binary log, redo log, slow query log

PostgreSQL:
WAL, server log, slow query via log_min_duration_statement

Monitoring

MySQL:
SHOW PROCESSLIST
SHOW STATUS
performance_schema

PostgreSQL:
pg_stat_activity
pg_stat_database
pg_stat_user_tables
pg_stat_statements

Backup

MySQL:
mysqldump

PostgreSQL:
pg_dump
pg_restore
pg_basebackup

Replication

MySQL:
binlog replication

PostgreSQL:
WAL streaming replication
logical replication

57. Recommended Learning Order for a MySQL Expert

Study PostgreSQL in this order:

Step 1: Basic Navigation

Learn:

\l
\c
\dn
\dt
\d
\du

Step 2: Database vs Schema

Understand:

database → schema → table

Step 3: Roles and Permissions

Understand:

role with LOGIN = user
role without LOGIN = group-like role

Step 4: Data Type Differences

Focus on:

BOOLEAN
TEXT
TIMESTAMPTZ
NUMERIC
JSONB
UUID
ARRAY

Step 5: Auto Increment

Learn:

GENERATED ALWAYS AS IDENTITY

and sequences.

Step 6: Upsert

Learn:

ON CONFLICT DO UPDATE
ON CONFLICT DO NOTHING

Step 7: Monitoring

Learn:

pg_stat_activity
pg_stat_user_tables
pg_stat_statements

Step 8: Performance

Learn:

EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)

Step 9: Vacuum

Understand:

MVCC
dead tuples
autovacuum
VACUUM ANALYZE

Step 10: Backup and Replication

Learn:

pg_dump
pg_restore
pg_basebackup
WAL
streaming replication
logical replication

58. Final Mental Translation

When you see this in MySQL:

Create user appuser@localhost and grant access to database shop.

Think PostgreSQL:

Create a role with LOGIN.
Allow connection through pg_hba.conf.
Grant CONNECT on database.
Grant USAGE on schema.
Grant privileges on tables.
Grant privileges on sequences if needed.

When you see this in MySQL:

Create database shop and tables inside it.

Think PostgreSQL:

Create database shop.
Create schema app or use public.
Create tables inside that schema.

When you see this in MySQL:

AUTO_INCREMENT

Think PostgreSQL:

IDENTITY column backed by sequence.

When you see this in MySQL:

Binary log replication

Think PostgreSQL:

WAL streaming or logical replication.

When you see this in MySQL:

Performance Schema

Think PostgreSQL:

pg_stat_* views and pg_stat_statements.

When you see this in MySQL:

InnoDB purge cleans old versions.

Think PostgreSQL:

Autovacuum cleans dead tuples.

Conclusion

PostgreSQL is not “MySQL with different syntax.” It has a different internal model.

But once you map the major ideas, it becomes much easier:

MySQL database/schema        → PostgreSQL database + schema
MySQL user@host              → PostgreSQL role + pg_hba.conf
AUTO_INCREMENT               → identity/sequence
Binary log                   → WAL
SHOW PROCESSLIST             → pg_stat_activity
Performance Schema           → pg_stat_* views
mysqldump                    → pg_dump
ON DUPLICATE KEY UPDATE      → ON CONFLICT DO UPDATE
InnoDB purge                 → VACUUM/autovacuum
Storage engines              → PostgreSQL native storage + table types

The most important PostgreSQL concepts for a MySQL user are:

Roles
Schemas
Sequences
WAL
MVCC
Autovacuum
pg_hba.conf
JSONB
Extensions
EXPLAIN ANALYZE
pg_stat_* views

Once these are clear, PostgreSQL starts to feel very logical — just stricter, more layered, and more explicit than MySQL.

MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL

Introduction

1. Big Picture Mental Model

MySQL Mental Model

PostgreSQL Mental Model

2. Core Component Mapping

3. Server, Instance, and Cluster

MySQL

PostgreSQL

4. Database and Schema Mapping

MySQL Database

PostgreSQL Database

PostgreSQL Object Path

Important Difference

5. System Databases and System Schemas

MySQL System Databases

PostgreSQL System Databases and Schemas

6. Users vs Roles

MySQL User

PostgreSQL Role

PostgreSQL Group-Like Role

MySQL vs PostgreSQL User Mapping

Important PostgreSQL Access Layers

7. Authentication Mapping

MySQL Authentication

PostgreSQL Authentication

Common PostgreSQL Authentication Methods

8. Privileges and Grants

MySQL Grant Style

PostgreSQL Grant Style

PostgreSQL Default Privileges

Permission Mapping

9. Storage Engine vs PostgreSQL Storage

MySQL Storage Engines

PostgreSQL Storage

10. Tablespaces

MySQL

PostgreSQL

11. Auto Increment vs Sequence / Identity

MySQL AUTO_INCREMENT

PostgreSQL Identity

PostgreSQL Sequence

12. Data Type Mapping

Boolean Difference

Date and Time Difference

JSON Difference

13. SQL Syntax Mapping

Selecting Rows

Limit

Offset

String Concatenation

Current Date and Time

Case-Insensitive Search

Upsert

Replace

Insert Ignore

Group By Strictness

14. Command-Line Tool Mapping

MySQL Client

PostgreSQL Client

Useful MySQL vs PostgreSQL Commands

PostgreSQL psql Commands

15. Configuration Files

MySQL

PostgreSQL

16. Port and Service Mapping

17. Memory Resource Mapping

MySQL Important Memory Settings

PostgreSQL Important Memory Settings

Key Difference

18. Connection Resource Mapping

MySQL

PostgreSQL

Connection Pooling

19. Transaction Mapping

MySQL

PostgreSQL

Important PostgreSQL Difference

Transactional DDL

20. MVCC Mapping

PostgreSQL `psql` Commands