{"id":2836,"date":"2026-05-06T01:18:23","date_gmt":"2026-05-06T01:18:23","guid":{"rendered":"https:\/\/sreschool.com\/blog\/?p=2836"},"modified":"2026-05-06T01:18:24","modified_gmt":"2026-05-06T01:18:24","slug":"mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql","status":"publish","type":"post","link":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/","title":{"rendered":"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

If you already know MySQL well, learning PostgreSQL becomes much easier when you stop trying to memorize PostgreSQL from zero and instead ask:<\/p>\n\n\n\n

\n

\u201cWhat is the PostgreSQL equivalent of the thing I already know in MySQL?\u201d<\/p>\n<\/blockquote>\n\n\n\n

That is exactly how this guide is structured.<\/p>\n\n\n\n

We will compare MySQL and PostgreSQL components, resources, architecture, commands, tools, users, roles, schemas, storage, logs, backup, replication, monitoring, performance, and SQL syntax<\/strong>.<\/p>\n\n\n\n

The goal is not just to say:<\/p>\n\n\n\n

MySQL thing = PostgreSQL thing\n<\/code><\/pre>\n\n\n\n
The goal is to understand:<\/p>\n\n\n\n
What does it mean?\nHow does it behave?\nHow should I think about it as a MySQL user?\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
1. Big Picture Mental Model<\/h1>\n\n\n\n
MySQL Mental Model<\/h2>\n\n\n\n
In MySQL, you usually think like this:<\/p>\n\n\n\n
MySQL Server\n \u251c\u2500\u2500 Database \/ Schema\n \u2502    \u251c\u2500\u2500 Tables\n \u2502    \u251c\u2500\u2500 Views\n \u2502    \u251c\u2500\u2500 Procedures\n \u2502    \u2514\u2500\u2500 Triggers\n \u251c\u2500\u2500 Users\n \u251c\u2500\u2500 Privileges\n \u251c\u2500\u2500 Storage Engines\n \u2502    \u251c\u2500\u2500 InnoDB\n \u2502    \u251c\u2500\u2500 MyISAM\n \u2502    \u2514\u2500\u2500 MEMORY\n \u2514\u2500\u2500 Logs\n      \u251c\u2500\u2500 Error log\n      \u251c\u2500\u2500 Binary log\n      \u251c\u2500\u2500 Slow query log\n      \u2514\u2500\u2500 General log\n<\/code><\/pre>\n\n\n\n
PostgreSQL Mental Model<\/h2>\n\n\n\n
In PostgreSQL, think like this:<\/p>\n\n\n\n
PostgreSQL Server \/ Cluster\n \u251c\u2500\u2500 Databases\n \u2502    \u251c\u2500\u2500 Schemas\n \u2502    \u2502    \u251c\u2500\u2500 Tables\n \u2502    \u2502    \u251c\u2500\u2500 Views\n \u2502    \u2502    \u251c\u2500\u2500 Functions\n \u2502    \u2502    \u251c\u2500\u2500 Sequences\n \u2502    \u2502    \u2514\u2500\u2500 Types\n \u251c\u2500\u2500 Roles\n \u2502    \u251c\u2500\u2500 Login roles\n \u2502    \u2514\u2500\u2500 Group roles\n \u251c\u2500\u2500 Tablespaces\n \u251c\u2500\u2500 WAL\n \u251c\u2500\u2500 Background processes\n \u2514\u2500\u2500 Extensions\n<\/code><\/pre>\n\n\n\n
The most important difference:<\/p>\n\n\n\n
MySQL:\nDatabase and schema usually mean the same thing.\n\nPostgreSQL:\nA database contains schemas.\nA schema contains tables.\n<\/code><\/pre>\n\n\n\n
So:<\/p>\n\n\n\n
MySQL:\nserver \u2192 database \u2192 table\n\nPostgreSQL:\nserver\/cluster \u2192 database \u2192 schema \u2192 table\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
2. Core Component Mapping<\/h1>\n\n\n\n
MySQL Component<\/th>PostgreSQL Equivalent<\/th>Meaning<\/th><\/tr><\/thead>
MySQL server<\/td>PostgreSQL server \/ cluster<\/td>Running database service<\/td><\/tr>
Database<\/td>Database<\/td>Logical database container<\/td><\/tr>
Schema<\/td>Schema inside database<\/td>Namespace inside a database<\/td><\/tr>
Table<\/td>Table<\/td>Stores rows and columns<\/td><\/tr>
View<\/td>View<\/td>Saved query<\/td><\/tr>
Materialized view<\/td>Materialized view<\/td>Stored query result<\/td><\/tr>
User<\/td>Role with LOGIN<\/code><\/td>PostgreSQL users are login roles<\/td><\/tr>
Group<\/td>Role without LOGIN<\/code><\/td>Used to group permissions<\/td><\/tr>
Privilege<\/td>Privilege<\/td>Controls access<\/td><\/tr>
Storage engine<\/td>PostgreSQL storage system<\/td>PostgreSQL does not use engines like MySQL<\/td><\/tr>
InnoDB redo log<\/td>WAL<\/td>Write-Ahead Log<\/td><\/tr>
Binary log<\/td>WAL \/ logical replication<\/td>Used for recovery and replication<\/td><\/tr>
AUTO_INCREMENT<\/td>Identity \/ sequence<\/td>Auto-generated numbers<\/td><\/tr>
Performance Schema<\/td>pg_stat_*<\/code> views<\/td>Monitoring and metrics<\/td><\/tr>
my.cnf<\/code><\/td>postgresql.conf<\/code><\/td>Main config<\/td><\/tr>
User host grants<\/td>pg_hba.conf<\/code><\/td>Client authentication rules<\/td><\/tr>
mysqldump<\/code><\/td>pg_dump<\/code><\/td>Backup tool<\/td><\/tr>
mysql<\/code> CLI<\/td>psql<\/code><\/td>Command-line client<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
3. Server, Instance, and Cluster<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
In MySQL, you usually say:<\/p>\n\n\n\n
MySQL instance\nMySQL server\nmysqld process\n<\/code><\/pre>\n\n\n\n
A MySQL instance usually means one running MySQL server process using one data directory.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
mysqld\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL commonly uses the word cluster<\/strong>.<\/p>\n\n\n\n
A PostgreSQL cluster means:<\/p>\n\n\n\n
One PostgreSQL data directory initialized by initdb\n<\/code><\/pre>\n\n\n\n
It contains:<\/p>\n\n\n\n
Databases\nRoles\nWAL\nSystem catalogs\nConfiguration\n<\/code><\/pre>\n\n\n\n
Important: in PostgreSQL, a cluster does not mean multiple servers<\/strong> in this context.<\/p>\n\n\n\n
That confuses many people.<\/p>\n\n\n\n
In PostgreSQL:<\/p>\n\n\n\n
Cluster = one initialized PostgreSQL data directory\n<\/code><\/pre>\n\n\n\n
Example data directory:<\/p>\n\n\n\n
\/var\/lib\/postgresql\/16\/main\n<\/code><\/pre>\n\n\n\n
or:<\/p>\n\n\n\n
\/var\/lib\/pgsql\/data\n<\/code><\/pre>\n\n\n\n
depending on Linux distribution.<\/p>\n\n\n\n
\n\n\n\n
4. Database and Schema Mapping<\/h1>\n\n\n\n
MySQL Database<\/h2>\n\n\n\n
In MySQL:<\/p>\n\n\n\n
CREATE DATABASE shop;\nUSE shop;\nCREATE TABLE customers (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
Here, shop<\/code> is the database.<\/p>\n\n\n\n
Many MySQL people also call this a schema.<\/p>\n\n\n\n
So in MySQL:<\/p>\n\n\n\n
Database \u2248 Schema\n<\/code><\/pre>\n\n\n\n
PostgreSQL Database<\/h2>\n\n\n\n
In PostgreSQL:<\/p>\n\n\n\n
CREATE DATABASE shop;\n<\/code><\/pre>\n\n\n\n
Then connect to it:<\/p>\n\n\n\n
\\c shop\n<\/code><\/pre>\n\n\n\n
Inside that database, you usually have a schema called:<\/p>\n\n\n\n
public\n<\/code><\/pre>\n\n\n\n
Then you create tables inside the schema:<\/p>\n\n\n\n
CREATE TABLE public.customers (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
So in PostgreSQL:<\/p>\n\n\n\n
Database contains schemas.\nSchema contains tables.\n<\/code><\/pre>\n\n\n\n
PostgreSQL Object Path<\/h2>\n\n\n\n
A full table name in PostgreSQL can look like this:<\/p>\n\n\n\n
database.schema.table\n<\/code><\/pre>\n\n\n\n
But inside SQL queries, you usually use:<\/p>\n\n\n\n
SELECT * FROM public.customers;\n<\/code><\/pre>\n\n\n\n
You do not usually write the database name in front of the table because you are already connected to one database.<\/p>\n\n\n\n
Important Difference<\/h2>\n\n\n\n
In MySQL, you can commonly do:<\/p>\n\n\n\n
SELECT *\nFROM db1.customers c\nJOIN db2.orders o ON c.id = o.customer_id;\n<\/code><\/pre>\n\n\n\n
In PostgreSQL, cross-database queries are not naturally done this way. You usually work inside one database and use multiple schemas:<\/p>\n\n\n\n
shop database\n \u251c\u2500\u2500 sales.customers\n \u251c\u2500\u2500 sales.orders\n \u251c\u2500\u2500 accounting.invoices\n \u2514\u2500\u2500 audit.events\n<\/code><\/pre>\n\n\n\n
PostgreSQL encourages this style:<\/p>\n\n\n\n
SELECT *\nFROM sales.customers c\nJOIN accounting.invoices i ON c.id = i.customer_id;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
5. System Databases and System Schemas<\/h1>\n\n\n\n
MySQL System Databases<\/h2>\n\n\n\n
Common MySQL system databases:<\/p>\n\n\n\n
MySQL Database<\/th>Purpose<\/th><\/tr><\/thead>
mysql<\/code><\/td>Users, privileges, system metadata<\/td><\/tr>
information_schema<\/code><\/td>SQL-standard metadata views<\/td><\/tr>
performance_schema<\/code><\/td>Performance and runtime metrics<\/td><\/tr>
sys<\/code><\/td>Friendly views over performance schema<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
You may see:<\/p>\n\n\n\n
SHOW DATABASES;\n<\/code><\/pre>\n\n\n\n
Output:<\/p>\n\n\n\n
information_schema\nmysql\nperformance_schema\nsys\nmyapp\n<\/code><\/pre>\n\n\n\n
PostgreSQL System Databases and Schemas<\/h2>\n\n\n\n
PostgreSQL has special databases and schemas.<\/p>\n\n\n\n
Common PostgreSQL databases:<\/p>\n\n\n\n
PostgreSQL Database<\/th>Purpose<\/th><\/tr><\/thead>
postgres<\/code><\/td>Default administrative database<\/td><\/tr>
template0<\/code><\/td>Clean database template<\/td><\/tr>
template1<\/code><\/td>Default template for new databases<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Common PostgreSQL schemas:<\/p>\n\n\n\n
PostgreSQL Schema<\/th>Purpose<\/th><\/tr><\/thead>
pg_catalog<\/code><\/td>PostgreSQL system catalog<\/td><\/tr>
information_schema<\/code><\/td>SQL-standard metadata views<\/td><\/tr>
public<\/code><\/td>Default user schema<\/td><\/tr>
pg_toast<\/code><\/td>Internal storage for large values<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
In PostgreSQL, system metadata mostly lives in:<\/p>\n\n\n\n
pg_catalog\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT *\nFROM pg_catalog.pg_tables;\n<\/code><\/pre>\n\n\n\n
But as a beginner, you often use psql<\/code> commands:<\/p>\n\n\n\n
\\l\n\\dt\n\\d table_name\n\\du\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
6. Users vs Roles<\/h1>\n\n\n\n
This is one of the biggest differences.<\/p>\n\n\n\n
MySQL User<\/h2>\n\n\n\n
In MySQL, a user is identified by username and host:<\/p>\n\n\n\n
'root'@'localhost'\n'appuser'@'%'\n'appuser'@'192.168.1.%'\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secret';\nGRANT SELECT ON shop.* TO 'appuser'@'localhost';\n<\/code><\/pre>\n\n\n\n
In MySQL:<\/p>\n\n\n\n
User identity = username + host\n<\/code><\/pre>\n\n\n\n
PostgreSQL Role<\/h2>\n\n\n\n
PostgreSQL has roles<\/strong>.<\/p>\n\n\n\n
A role can be:<\/p>\n\n\n\n
A user\nA group\nBoth\n<\/code><\/pre>\n\n\n\n
A PostgreSQL \u201cuser\u201d is simply:<\/p>\n\n\n\n
A role with LOGIN permission\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE ROLE appuser WITH LOGIN PASSWORD 'secret';\n<\/code><\/pre>\n\n\n\n
This is equivalent to:<\/p>\n\n\n\n
CREATE USER appuser WITH PASSWORD 'secret';\n<\/code><\/pre>\n\n\n\n
Because:<\/p>\n\n\n\n
CREATE USER\n<\/code><\/pre>\n\n\n\n
is shorthand for:<\/p>\n\n\n\n
CREATE ROLE ... LOGIN\n<\/code><\/pre>\n\n\n\n
PostgreSQL Group-Like Role<\/h2>\n\n\n\n
Create a role that cannot log in:<\/p>\n\n\n\n
CREATE ROLE readonly;\n<\/code><\/pre>\n\n\n\n
Give permissions to that group role:<\/p>\n\n\n\n
GRANT CONNECT ON DATABASE shop TO readonly;\nGRANT USAGE ON SCHEMA public TO readonly;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;\n<\/code><\/pre>\n\n\n\n
Assign it to a login role:<\/p>\n\n\n\n
GRANT readonly TO appuser;\n<\/code><\/pre>\n\n\n\n
Now appuser<\/code> inherits permissions from readonly<\/code>.<\/p>\n\n\n\n
MySQL vs PostgreSQL User Mapping<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
User<\/td>Role with LOGIN<\/code><\/td><\/tr>
Group of permissions<\/td>Role without LOGIN<\/code><\/td><\/tr>
'user'@'host'<\/code><\/td>Role + pg_hba.conf<\/code><\/td><\/tr>
CREATE USER<\/code><\/td>CREATE ROLE ... LOGIN<\/code><\/td><\/tr>
GRANT ON db.*<\/code><\/td>GRANT ON DATABASE<\/code>, SCHEMA<\/code>, TABLES<\/code>, SEQUENCES<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Important PostgreSQL Access Layers<\/h2>\n\n\n\n
PostgreSQL access usually has four layers:<\/p>\n\n\n\n
1. Can the client reach the server?\n2. Does pg_hba.conf allow this connection?\n3. Can the role connect to the database?\n4. Does the role have privileges on schema\/table\/sequence?\n<\/code><\/pre>\n\n\n\n
This surprises MySQL users because MySQL grants often feel more direct.<\/p>\n\n\n\n
\n\n\n\n
7. Authentication Mapping<\/h1>\n\n\n\n
MySQL Authentication<\/h2>\n\n\n\n
MySQL controls user access through user accounts like:<\/p>\n\n\n\n
'appuser'@'localhost'\n'appuser'@'%'\n<\/code><\/pre>\n\n\n\n
Examples:<\/p>\n\n\n\n
CREATE USER 'appuser'@'%' IDENTIFIED BY 'secret';\nGRANT SELECT ON shop.* TO 'appuser'@'%';\n<\/code><\/pre>\n\n\n\n
PostgreSQL Authentication<\/h2>\n\n\n\n
PostgreSQL separates:<\/p>\n\n\n\n
Who you are       \u2192 role\nCan you log in    \u2192 LOGIN attribute\nWhere from\/how    \u2192 pg_hba.conf\nWhat you can do   \u2192 GRANT privileges\n<\/code><\/pre>\n\n\n\n
The file:<\/p>\n\n\n\n
pg_hba.conf\n<\/code><\/pre>\n\n\n\n
controls connection rules.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
host    shop    appuser    192.168.1.0\/24    scram-sha-256\n<\/code><\/pre>\n\n\n\n
Meaning:<\/p>\n\n\n\n
Allow appuser to connect to database shop\nfrom 192.168.1.x\nusing password authentication.\n<\/code><\/pre>\n\n\n\n
Common PostgreSQL Authentication Methods<\/h2>\n\n\n\n
Method<\/th>Meaning<\/th><\/tr><\/thead>
trust<\/code><\/td>No password required<\/td><\/tr>
peer<\/code><\/td>OS user must match database role<\/td><\/tr>
md5<\/code><\/td>Password authentication<\/td><\/tr>
scram-sha-256<\/code><\/td>Stronger password authentication<\/td><\/tr>
cert<\/code><\/td>Client certificate authentication<\/td><\/tr>
ldap<\/code><\/td>LDAP authentication<\/td><\/tr>
pam<\/code><\/td>PAM authentication<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
For modern PostgreSQL, scram-sha-256<\/code> is commonly preferred over md5<\/code>.<\/p>\n\n\n\n
\n\n\n\n
8. Privileges and Grants<\/h1>\n\n\n\n
MySQL Grant Style<\/h2>\n\n\n\n
GRANT SELECT, INSERT, UPDATE\nON shop.*\nTO 'appuser'@'localhost';\n<\/code><\/pre>\n\n\n\n
This grants privileges on all tables in database shop<\/code>.<\/p>\n\n\n\n
PostgreSQL Grant Style<\/h2>\n\n\n\n
PostgreSQL is more layered.<\/p>\n\n\n\n
You may need:<\/p>\n\n\n\n
GRANT CONNECT ON DATABASE shop TO appuser;\nGRANT USAGE ON SCHEMA public TO appuser;\nGRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO appuser;\nGRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;\n<\/code><\/pre>\n\n\n\n
Why sequences?<\/p>\n\n\n\n
Because PostgreSQL auto-increment values are often backed by sequence objects.<\/p>\n\n\n\n
PostgreSQL Default Privileges<\/h2>\n\n\n\n
If you want future tables to automatically get permissions:<\/p>\n\n\n\n
ALTER DEFAULT PRIVILEGES IN SCHEMA public\nGRANT SELECT, INSERT, UPDATE ON TABLES TO appuser;\n<\/code><\/pre>\n\n\n\n
For future sequences:<\/p>\n\n\n\n
ALTER DEFAULT PRIVILEGES IN SCHEMA public\nGRANT USAGE, SELECT ON SEQUENCES TO appuser;\n<\/code><\/pre>\n\n\n\n
Permission Mapping<\/h2>\n\n\n\n
MySQL Permission<\/th>PostgreSQL Equivalent<\/th><\/tr><\/thead>
SELECT<\/code><\/td>SELECT<\/code><\/td><\/tr>
INSERT<\/code><\/td>INSERT<\/code><\/td><\/tr>
UPDATE<\/code><\/td>UPDATE<\/code><\/td><\/tr>
DELETE<\/code><\/td>DELETE<\/code><\/td><\/tr>
CREATE<\/code><\/td>CREATE<\/code><\/td><\/tr>
DROP<\/code><\/td>object ownership \/ DROP<\/code> as owner<\/td><\/tr>
ALTER<\/code><\/td>object ownership \/ specific privileges<\/td><\/tr>
INDEX<\/code><\/td>usually table ownership<\/td><\/tr>
EXECUTE<\/code><\/td>EXECUTE<\/code> on functions<\/td><\/tr>
USAGE<\/code><\/td>schema\/sequence\/type usage<\/td><\/tr>
ALL PRIVILEGES<\/code><\/td>ALL PRIVILEGES<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
9. Storage Engine vs PostgreSQL Storage<\/h1>\n\n\n\n
MySQL Storage Engines<\/h2>\n\n\n\n
MySQL supports multiple storage engines:<\/p>\n\n\n\n
Engine<\/th>Meaning<\/th><\/tr><\/thead>
InnoDB<\/td>Default transactional engine<\/td><\/tr>
MyISAM<\/td>Older non-transactional engine<\/td><\/tr>
MEMORY<\/td>Stores data in memory<\/td><\/tr>
CSV<\/td>Stores data in CSV files<\/td><\/tr>
ARCHIVE<\/td>Compressed archival storage<\/td><\/tr>
FEDERATED<\/td>Remote table access<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Most production MySQL systems use:<\/p>\n\n\n\n
InnoDB\n<\/code><\/pre>\n\n\n\n
PostgreSQL Storage<\/h2>\n\n\n\n
PostgreSQL does not work the same way.<\/p>\n\n\n\n
You do not normally choose:<\/p>\n\n\n\n
ENGINE=InnoDB\n<\/code><\/pre>\n\n\n\n
There is no typical equivalent of:<\/p>\n\n\n\n
CREATE TABLE t (...) ENGINE=InnoDB;\n<\/code><\/pre>\n\n\n\n
In PostgreSQL, table storage is native.<\/p>\n\n\n\n
You choose table types instead:<\/p>\n\n\n\n
PostgreSQL Table Type<\/th>Meaning<\/th><\/tr><\/thead>
Regular table<\/td>Normal persistent table<\/td><\/tr>
Temporary table<\/td>Session-local temporary table<\/td><\/tr>
Unlogged table<\/td>Faster table, not WAL-logged<\/td><\/tr>
Partitioned table<\/td>Logical parent table with partitions<\/td><\/tr>
Foreign table<\/td>Table backed by external data source<\/td><\/tr>
Materialized view<\/td>Stored result of a query<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example regular table:<\/p>\n\n\n\n
CREATE TABLE customers (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name TEXT NOT NULL\n);\n<\/code><\/pre>\n\n\n\n
Example unlogged table:<\/p>\n\n\n\n
CREATE UNLOGGED TABLE staging_imports (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    payload JSONB\n);\n<\/code><\/pre>\n\n\n\n
Unlogged tables are faster for writes, but they are not crash-safe in the same way regular tables are.<\/p>\n\n\n\n
\n\n\n\n
10. Tablespaces<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
In MySQL\/InnoDB, tablespaces exist, but many users mostly deal with:<\/p>\n\n\n\n
ibdata files\n.ibd files\ndatadir\n<\/code><\/pre>\n\n\n\n
With innodb_file_per_table<\/code>, each table can have its own .ibd<\/code> file.<\/p>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL has tablespaces<\/strong> too.<\/p>\n\n\n\n
A tablespace lets you place database objects in a different filesystem location.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE TABLESPACE fastspace\nLOCATION '\/mnt\/fastdisk\/postgres';\n<\/code><\/pre>\n\n\n\n
Create table in that tablespace:<\/p>\n\n\n\n
CREATE TABLE big_events (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    event_data JSONB\n) TABLESPACE fastspace;\n<\/code><\/pre>\n\n\n\n
Mental model:<\/p>\n\n\n\n
Tablespace = storage location for database objects\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
11. Auto Increment vs Sequence \/ Identity<\/h1>\n\n\n\n
MySQL AUTO_INCREMENT<\/h2>\n\n\n\n
CREATE TABLE users (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
Insert:<\/p>\n\n\n\n
INSERT INTO users (name) VALUES ('Alice');\n<\/code><\/pre>\n\n\n\n
MySQL automatically generates id<\/code>.<\/p>\n\n\n\n
PostgreSQL Identity<\/h2>\n\n\n\n
Modern PostgreSQL style:<\/p>\n\n\n\n
CREATE TABLE users (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
Another style:<\/p>\n\n\n\n
CREATE TABLE users (\n    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
Difference:<\/p>\n\n\n\n
PostgreSQL Identity Type<\/th>Meaning<\/th><\/tr><\/thead>
GENERATED ALWAYS<\/code><\/td>PostgreSQL always generates value unless explicitly overridden<\/td><\/tr>
GENERATED BY DEFAULT<\/code><\/td>PostgreSQL generates value if you do not provide one<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Older PostgreSQL style:<\/p>\n\n\n\n
CREATE TABLE users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
SERIAL<\/code> still works, but identity columns are the modern SQL-standard style.<\/p>\n\n\n\n
PostgreSQL Sequence<\/h2>\n\n\n\n
PostgreSQL identity and serial columns use sequences.<\/p>\n\n\n\n
A sequence is an independent object that generates numbers.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE SEQUENCE order_id_seq;\n<\/code><\/pre>\n\n\n\n
Get next value:<\/p>\n\n\n\n
SELECT nextval('order_id_seq');\n<\/code><\/pre>\n\n\n\n
Important:<\/p>\n\n\n\n
PostgreSQL sequences are not rolled back.\n<\/code><\/pre>\n\n\n\n
If a transaction gets a sequence number and then rolls back, that number may be skipped.<\/p>\n\n\n\n
That is normal.<\/p>\n\n\n\n
\n\n\n\n
12. Data Type Mapping<\/h1>\n\n\n\n
MySQL Type<\/th>PostgreSQL Type<\/th>Notes<\/th><\/tr><\/thead>
TINYINT<\/code><\/td>SMALLINT<\/code><\/td>PostgreSQL has no TINYINT<\/code><\/td><\/tr>
TINYINT(1)<\/code><\/td>BOOLEAN<\/code><\/td>Common MySQL boolean conversion<\/td><\/tr>
SMALLINT<\/code><\/td>SMALLINT<\/code><\/td>Same<\/td><\/tr>
INT<\/code><\/td>INTEGER<\/code> \/ INT<\/code><\/td>Same<\/td><\/tr>
BIGINT<\/code><\/td>BIGINT<\/code><\/td>Same<\/td><\/tr>
DECIMAL<\/code><\/td>NUMERIC<\/code> \/ DECIMAL<\/code><\/td>Same purpose<\/td><\/tr>
FLOAT<\/code><\/td>REAL<\/code><\/td>4-byte floating number<\/td><\/tr>
DOUBLE<\/code><\/td>DOUBLE PRECISION<\/code><\/td>8-byte floating number<\/td><\/tr>
CHAR(n)<\/code><\/td>CHAR(n)<\/code><\/td>Same<\/td><\/tr>
VARCHAR(n)<\/code><\/td>VARCHAR(n)<\/code><\/td>Same<\/td><\/tr>
TEXT<\/code><\/td>TEXT<\/code><\/td>PostgreSQL uses this heavily<\/td><\/tr>
DATE<\/code><\/td>DATE<\/code><\/td>Same<\/td><\/tr>
DATETIME<\/code><\/td>TIMESTAMP<\/code><\/td>PostgreSQL equivalent<\/td><\/tr>
TIMESTAMP<\/code><\/td>TIMESTAMP<\/code> \/ TIMESTAMPTZ<\/code><\/td>Prefer TIMESTAMPTZ<\/code> for real-world time<\/td><\/tr>
TIME<\/code><\/td>TIME<\/code><\/td>Same<\/td><\/tr>
BLOB<\/code><\/td>BYTEA<\/code><\/td>Binary data<\/td><\/tr>
JSON<\/code><\/td>JSON<\/code> \/ JSONB<\/code><\/td>JSONB<\/code> is usually preferred<\/td><\/tr>
ENUM<\/code><\/td>ENUM<\/code> \/ lookup table<\/td>PostgreSQL supports custom enum types<\/td><\/tr>
SET<\/code><\/td>Array \/ join table<\/td>No direct same feature<\/td><\/tr>
UUID<\/code><\/td>UUID<\/code><\/td>PostgreSQL has strong native UUID support<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Boolean Difference<\/h2>\n\n\n\n
MySQL often uses:<\/p>\n\n\n\n
is_active TINYINT(1)\n<\/code><\/pre>\n\n\n\n
PostgreSQL uses:<\/p>\n\n\n\n
is_active BOOLEAN\n<\/code><\/pre>\n\n\n\n
Values:<\/p>\n\n\n\n
TRUE\nFALSE\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE TABLE users (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name TEXT NOT NULL,\n    is_active BOOLEAN NOT NULL DEFAULT TRUE\n);\n<\/code><\/pre>\n\n\n\n
Date and Time Difference<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
created_at DATETIME\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
created_at TIMESTAMP\n<\/code><\/pre>\n\n\n\n
Better for timezone-aware data:<\/p>\n\n\n\n
created_at TIMESTAMPTZ\n<\/code><\/pre>\n\n\n\n
Important PostgreSQL note:<\/p>\n\n\n\n
TIMESTAMPTZ does not store the timezone name.\nIt stores an absolute point in time and displays it according to session timezone.\n<\/code><\/pre>\n\n\n\n
JSON Difference<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
data JSON\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
data JSONB\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE TABLE events (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    payload JSONB NOT NULL\n);\n<\/code><\/pre>\n\n\n\n
Query:<\/p>\n\n\n\n
SELECT *\nFROM events\nWHERE payload->>'type' = 'login';\n<\/code><\/pre>\n\n\n\n
JSONB indexing:<\/p>\n\n\n\n
CREATE INDEX idx_events_payload\nON events USING GIN (payload);\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
13. SQL Syntax Mapping<\/h1>\n\n\n\n
Selecting Rows<\/h2>\n\n\n\n
Same:<\/p>\n\n\n\n
SELECT *\nFROM users;\n<\/code><\/pre>\n\n\n\n
Limit<\/h2>\n\n\n\n
Same:<\/p>\n\n\n\n
SELECT *\nFROM users\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
Offset<\/h2>\n\n\n\n
Same:<\/p>\n\n\n\n
SELECT *\nFROM users\nLIMIT 10 OFFSET 20;\n<\/code><\/pre>\n\n\n\n
String Concatenation<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
SELECT CONCAT(first_name, ' ', last_name) AS full_name\nFROM users;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
SELECT first_name || ' ' || last_name AS full_name\nFROM users;\n<\/code><\/pre>\n\n\n\n
PostgreSQL also supports:<\/p>\n\n\n\n
SELECT CONCAT(first_name, ' ', last_name) AS full_name\nFROM users;\n<\/code><\/pre>\n\n\n\n
Current Date and Time<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
NOW()<\/code><\/td>NOW()<\/code><\/td><\/tr>
CURRENT_TIMESTAMP<\/code><\/td>CURRENT_TIMESTAMP<\/code><\/td><\/tr>
CURDATE()<\/code><\/td>CURRENT_DATE<\/code><\/td><\/tr>
CURTIME()<\/code><\/td>CURRENT_TIME<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
MySQL:<\/p>\n\n\n\n
SELECT CURDATE();\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
SELECT CURRENT_DATE;\n<\/code><\/pre>\n\n\n\n
Case-Insensitive Search<\/h2>\n\n\n\n
MySQL often depends on collation:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE name LIKE '%john%';\n<\/code><\/pre>\n\n\n\n
PostgreSQL has ILIKE<\/code>:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE name ILIKE '%john%';\n<\/code><\/pre>\n\n\n\n
Upsert<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
INSERT INTO users (email, name)\nVALUES ('alice@example.com', 'Alice')\nON DUPLICATE KEY UPDATE\n    name = VALUES(name);\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
INSERT INTO users (email, name)\nVALUES ('alice@example.com', 'Alice')\nON CONFLICT (email)\nDO UPDATE SET\n    name = EXCLUDED.name;\n<\/code><\/pre>\n\n\n\n
This is a major mapping:<\/p>\n\n\n\n
MySQL ON DUPLICATE KEY UPDATE\n=\nPostgreSQL ON CONFLICT DO UPDATE\n<\/code><\/pre>\n\n\n\n
Replace<\/h2>\n\n\n\n
MySQL has:<\/p>\n\n\n\n
REPLACE INTO users (id, name)\nVALUES (1, 'Alice');\n<\/code><\/pre>\n\n\n\n
PostgreSQL does not have the same REPLACE INTO<\/code>.<\/p>\n\n\n\n
Use:<\/p>\n\n\n\n
INSERT INTO users (id, name)\nVALUES (1, 'Alice')\nON CONFLICT (id)\nDO UPDATE SET name = EXCLUDED.name;\n<\/code><\/pre>\n\n\n\n
Insert Ignore<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
INSERT IGNORE INTO users (email, name)\nVALUES ('alice@example.com', 'Alice');\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
INSERT INTO users (email, name)\nVALUES ('alice@example.com', 'Alice')\nON CONFLICT DO NOTHING;\n<\/code><\/pre>\n\n\n\n
Group By Strictness<\/h2>\n\n\n\n
MySQL may allow loose grouping depending on SQL mode:<\/p>\n\n\n\n
SELECT department, name, COUNT(*)\nFROM employees\nGROUP BY department;\n<\/code><\/pre>\n\n\n\n
PostgreSQL rejects this because name<\/code> is neither grouped nor aggregated.<\/p>\n\n\n\n
Correct PostgreSQL:<\/p>\n\n\n\n
SELECT department, COUNT(*)\nFROM employees\nGROUP BY department;\n<\/code><\/pre>\n\n\n\n
This is important:<\/p>\n\n\n\n
PostgreSQL is stricter and more SQL-standard in many areas.\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
14. Command-Line Tool Mapping<\/h1>\n\n\n\n
MySQL Client<\/h2>\n\n\n\n
mysql -u root -p\n<\/code><\/pre>\n\n\n\n
Connect to database:<\/p>\n\n\n\n
mysql -u appuser -p shop\n<\/code><\/pre>\n\n\n\n
PostgreSQL Client<\/h2>\n\n\n\n
psql -U postgres\n<\/code><\/pre>\n\n\n\n
Connect to database:<\/p>\n\n\n\n
psql -U appuser -d shop\n<\/code><\/pre>\n\n\n\n
Connect with host and port:<\/p>\n\n\n\n
psql -h localhost -p 5432 -U appuser -d shop\n<\/code><\/pre>\n\n\n\n
Useful MySQL vs PostgreSQL Commands<\/h2>\n\n\n\n
Task<\/th>MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Show databases<\/td>SHOW DATABASES;<\/code><\/td>\\l<\/code><\/td><\/tr>
Use database<\/td>USE shop;<\/code><\/td>\\c shop<\/code><\/td><\/tr>
Show tables<\/td>SHOW TABLES;<\/code><\/td>\\dt<\/code><\/td><\/tr>
Describe table<\/td>DESCRIBE users;<\/code><\/td>\\d users<\/code><\/td><\/tr>
Show users<\/td>Query mysql.user<\/code><\/td>\\du<\/code><\/td><\/tr>
Show current database<\/td>SELECT DATABASE();<\/code><\/td>SELECT current_database();<\/code><\/td><\/tr>
Show current user<\/td>SELECT USER();<\/code><\/td>SELECT current_user;<\/code><\/td><\/tr>
Show variables<\/td>SHOW VARIABLES;<\/code><\/td>SHOW ALL;<\/code><\/td><\/tr>
Show process list<\/td>SHOW PROCESSLIST;<\/code><\/td>Query pg_stat_activity<\/code><\/td><\/tr>
Exit<\/td>exit<\/code><\/td>\\q<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL psql<\/code> Commands<\/h2>\n\n\n\n
Command<\/th>Meaning<\/th><\/tr><\/thead>
\\l<\/code><\/td>List databases<\/td><\/tr>
\\c dbname<\/code><\/td>Connect to database<\/td><\/tr>
\\dn<\/code><\/td>List schemas<\/td><\/tr>
\\dt<\/code><\/td>List tables<\/td><\/tr>
\\dv<\/code><\/td>List views<\/td><\/tr>
\\dm<\/code><\/td>List materialized views<\/td><\/tr>
\\df<\/code><\/td>List functions<\/td><\/tr>
\\ds<\/code><\/td>List sequences<\/td><\/tr>
\\du<\/code><\/td>List roles<\/td><\/tr>
\\d table_name<\/code><\/td>Describe table<\/td><\/tr>
\\x<\/code><\/td>Expanded output<\/td><\/tr>
\\q<\/code><\/td>Quit<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example:<\/p>\n\n\n\n
\\l\n\\c shop\n\\dn\n\\dt\n\\d users\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
15. Configuration Files<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
Common MySQL config files:<\/p>\n\n\n\n
\/etc\/my.cnf\n\/etc\/mysql\/my.cnf\n\/etc\/mysql\/mysql.conf.d\/mysqld.cnf\n<\/code><\/pre>\n\n\n\n
Main config sections:<\/p>\n\n\n\n
[mysqld]<\/code><\/pre>\n\n\n
[client]<\/p>\n\n\n\n
[mysql]<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
[mysqld]\nbind-address = 0.0.0.0\nmax_connections = 200\ninnodb_buffer_pool_size = 2G\nslow_query_log = 1\nlong_query_time = 2\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
Main PostgreSQL config files:<\/p>\n\n\n\n
postgresql.conf\npg_hba.conf\npg_ident.conf\n<\/code><\/pre>\n\n\n\n
File<\/th>Meaning<\/th><\/tr><\/thead>
postgresql.conf<\/code><\/td>Main server settings<\/td><\/tr>
pg_hba.conf<\/code><\/td>Host-based authentication<\/td><\/tr>
pg_ident.conf<\/code><\/td>Maps OS users to DB users<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example postgresql.conf<\/code>:<\/p>\n\n\n\n
listen_addresses = '*'\nmax_connections = 200\nshared_buffers = 2GB\nwork_mem = 16MB\nmaintenance_work_mem = 512MB\nlog_min_duration_statement = 2000\n<\/code><\/pre>\n\n\n\n
Example pg_hba.conf<\/code>:<\/p>\n\n\n\n
host    shop    appuser    192.168.1.0\/24    scram-sha-256\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
16. Port and Service Mapping<\/h1>\n\n\n\n
Component<\/th>MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Default port<\/td>3306<\/code><\/td>5432<\/code><\/td><\/tr>
Server process<\/td>mysqld<\/code><\/td>postgres<\/code><\/td><\/tr>
CLI client<\/td>mysql<\/code><\/td>psql<\/code><\/td><\/tr>
Service name<\/td>mysql<\/code> \/ mysqld<\/code><\/td>postgresql<\/code><\/td><\/tr>
Admin database<\/td>mysql<\/code><\/td>postgres<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Check service:<\/p>\n\n\n\n
systemctl status mysql\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
systemctl status postgresql\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
17. Memory Resource Mapping<\/h1>\n\n\n\n
MySQL Important Memory Settings<\/h2>\n\n\n\n
MySQL Setting<\/th>Meaning<\/th><\/tr><\/thead>
innodb_buffer_pool_size<\/code><\/td>Main InnoDB data\/index cache<\/td><\/tr>
sort_buffer_size<\/code><\/td>Per-connection sort buffer<\/td><\/tr>
join_buffer_size<\/code><\/td>Per-connection join buffer<\/td><\/tr>
tmp_table_size<\/code><\/td>Max internal temporary table size<\/td><\/tr>
max_heap_table_size<\/code><\/td>Max MEMORY table size<\/td><\/tr>
key_buffer_size<\/code><\/td>MyISAM index cache<\/td><\/tr>
max_connections<\/code><\/td>Max client connections<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Most important MySQL memory setting for InnoDB:<\/p>\n\n\n\n
innodb_buffer_pool_size\n<\/code><\/pre>\n\n\n\n
PostgreSQL Important Memory Settings<\/h2>\n\n\n\n
PostgreSQL Setting<\/th>Meaning<\/th><\/tr><\/thead>
shared_buffers<\/code><\/td>Main PostgreSQL buffer cache<\/td><\/tr>
work_mem<\/code><\/td>Per-operation memory for sort\/hash<\/td><\/tr>
maintenance_work_mem<\/code><\/td>Memory for vacuum, create index, alter table<\/td><\/tr>
effective_cache_size<\/code><\/td>Planner estimate of OS cache<\/td><\/tr>
wal_buffers<\/code><\/td>Memory for WAL<\/td><\/tr>
max_connections<\/code><\/td>Max client connections<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Most important PostgreSQL memory settings:<\/p>\n\n\n\n
shared_buffers\nwork_mem\nmaintenance_work_mem\neffective_cache_size\n<\/code><\/pre>\n\n\n\n
Key Difference<\/h2>\n\n\n\n
MySQL\/InnoDB heavily relies on:<\/p>\n\n\n\n
InnoDB buffer pool\n<\/code><\/pre>\n\n\n\n
PostgreSQL relies on:<\/p>\n\n\n\n
shared_buffers + operating system page cache\n<\/code><\/pre>\n\n\n\n
So do not map this too literally:<\/p>\n\n\n\n
innodb_buffer_pool_size = shared_buffers\n<\/code><\/pre>\n\n\n\n
They are related, but not identical.<\/p>\n\n\n\n
\n\n\n\n
18. Connection Resource Mapping<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
MySQL commonly uses threads for connections.<\/p>\n\n\n\n
Command:<\/p>\n\n\n\n
SHOW PROCESSLIST;\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL traditionally uses a process per connection.<\/p>\n\n\n\n
View active sessions:<\/p>\n\n\n\n
SELECT *\nFROM pg_stat_activity;\n<\/code><\/pre>\n\n\n\n
Useful query:<\/p>\n\n\n\n
SELECT\n    pid,\n    usename,\n    datname,\n    state,\n    query\nFROM pg_stat_activity;\n<\/code><\/pre>\n\n\n\n
Connection Pooling<\/h2>\n\n\n\n
PostgreSQL often benefits from connection pooling because too many direct connections can become expensive.<\/p>\n\n\n\n
Common pooler:<\/p>\n\n\n\n
PgBouncer\n<\/code><\/pre>\n\n\n\n
Mental mapping:<\/p>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Many app connections may be okay depending on setup<\/td>Use connection pooling earlier<\/td><\/tr>
SHOW PROCESSLIST<\/code><\/td>pg_stat_activity<\/code><\/td><\/tr>
Kill connection with KILL id<\/code><\/td>SELECT pg_terminate_backend(pid);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL terminate session:<\/p>\n\n\n\n
SELECT pg_terminate_backend(12345);\n<\/code><\/pre>\n\n\n\n
Cancel running query:<\/p>\n\n\n\n
SELECT pg_cancel_backend(12345);\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
19. Transaction Mapping<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
START TRANSACTION;\n\nUPDATE accounts\nSET balance = balance - 100\nWHERE id = 1;\n\nUPDATE accounts\nSET balance = balance + 100\nWHERE id = 2;\n\nCOMMIT;\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
BEGIN;\n\nUPDATE accounts\nSET balance = balance - 100\nWHERE id = 1;\n\nUPDATE accounts\nSET balance = balance + 100\nWHERE id = 2;\n\nCOMMIT;\n<\/code><\/pre>\n\n\n\n
Same general concept.<\/p>\n\n\n\n
Rollback:<\/p>\n\n\n\n
ROLLBACK;\n<\/code><\/pre>\n\n\n\n
Important PostgreSQL Difference<\/h2>\n\n\n\n
In PostgreSQL, if an error happens inside a transaction, the transaction becomes failed.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
BEGIN;\n\nSELECT 1 \/ 0;\n\nSELECT * FROM users;\n<\/code><\/pre>\n\n\n\n
PostgreSQL will say the transaction is aborted until you run:<\/p>\n\n\n\n
ROLLBACK;\n<\/code><\/pre>\n\n\n\n
This surprises MySQL users.<\/p>\n\n\n\n
Transactional DDL<\/h2>\n\n\n\n
PostgreSQL supports transactional DDL for many operations.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
BEGIN;\n\nCREATE TABLE test_table (\n    id INT\n);\n\nROLLBACK;\n<\/code><\/pre>\n\n\n\n
After rollback, the table is gone.<\/p>\n\n\n\n
In MySQL, many DDL operations cause implicit commits.<\/p>\n\n\n\n
\n\n\n\n
20. MVCC Mapping<\/h1>\n\n\n\n
Both MySQL\/InnoDB and PostgreSQL use MVCC.<\/p>\n\n\n\n
MVCC means:<\/p>\n\n\n\n
Multi-Version Concurrency Control\n<\/code><\/pre>\n\n\n\n
It allows readers and writers to avoid blocking each other in many cases.<\/p>\n\n\n\n
MySQL\/InnoDB MVCC<\/h2>\n\n\n\n
InnoDB stores undo information in undo logs.<\/p>\n\n\n\n
Old row versions are reconstructed from undo data.<\/p>\n\n\n\n
PostgreSQL MVCC<\/h2>\n\n\n\n
PostgreSQL stores multiple row versions directly in the table.<\/p>\n\n\n\n
When you update a row, PostgreSQL creates a new row version.<\/p>\n\n\n\n
Old versions are later cleaned up by:<\/p>\n\n\n\n
VACUUM\n<\/code><\/pre>\n\n\n\n
Important PostgreSQL Concept: Dead Tuples<\/h2>\n\n\n\n
When PostgreSQL updates or deletes rows, old row versions remain for a while.<\/p>\n\n\n\n
These old row versions are called:<\/p>\n\n\n\n
dead tuples\n<\/code><\/pre>\n\n\n\n
They are cleaned by:<\/p>\n\n\n\n
autovacuum\n<\/code><\/pre>\n\n\n\n
This is one of the biggest PostgreSQL operational topics.<\/p>\n\n\n\n
\n\n\n\n
21. Vacuum vs MySQL Purge<\/h1>\n\n\n\n
MySQL\/InnoDB<\/h2>\n\n\n\n
InnoDB uses undo logs and purge threads to clean old row versions.<\/p>\n\n\n\n
You usually do not manually run something called vacuum.<\/p>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL needs vacuum.<\/p>\n\n\n\n
Manual vacuum:<\/p>\n\n\n\n
VACUUM users;\n<\/code><\/pre>\n\n\n\n
Vacuum and update statistics:<\/p>\n\n\n\n
VACUUM ANALYZE users;\n<\/code><\/pre>\n\n\n\n
Full table rewrite:<\/p>\n\n\n\n
VACUUM FULL users;\n<\/code><\/pre>\n\n\n\n
Important:<\/p>\n\n\n\n
VACUUM FULL is heavier and takes stronger locks.\nDo not treat it as a routine command.\n<\/code><\/pre>\n\n\n\n
Autovacuum<\/h2>\n\n\n\n
PostgreSQL normally runs autovacuum automatically.<\/p>\n\n\n\n
Autovacuum handles:<\/p>\n\n\n\n
Cleaning dead tuples\nPreventing transaction ID wraparound\nUpdating table statistics\n<\/code><\/pre>\n\n\n\n
For MySQL users, think:<\/p>\n\n\n\n
PostgreSQL autovacuum is not optional housekeeping.\nIt is essential database maintenance.\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
22. Index Mapping<\/h1>\n\n\n\n
Common Index Types<\/h2>\n\n\n\n
MySQL Index<\/th>PostgreSQL Equivalent<\/th><\/tr><\/thead>
Primary key<\/td>Primary key<\/td><\/tr>
Unique index<\/td>Unique index<\/td><\/tr>
Normal index<\/td>B-tree index<\/td><\/tr>
Composite index<\/td>Composite index<\/td><\/tr>
Full-text index<\/td>Full-text search index<\/td><\/tr>
Spatial index<\/td>GiST\/SP-GiST, often with PostGIS<\/td><\/tr>
Functional index<\/td>Expression index<\/td><\/tr>
Prefix index<\/td>Expression or specialized index<\/td><\/tr>
Invisible index<\/td>No exact same common equivalent<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Basic Index<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
CREATE INDEX idx_users_email\nON users(email);\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
CREATE INDEX idx_users_email\nON users(email);\n<\/code><\/pre>\n\n\n\n
Same.<\/p>\n\n\n\n
Unique Index<\/h2>\n\n\n\n
CREATE UNIQUE INDEX idx_users_email_unique\nON users(email);\n<\/code><\/pre>\n\n\n\n
Composite Index<\/h2>\n\n\n\n
CREATE INDEX idx_orders_customer_status\nON orders(customer_id, status);\n<\/code><\/pre>\n\n\n\n
Same concept as MySQL.<\/p>\n\n\n\n
Expression Index<\/h2>\n\n\n\n
PostgreSQL is excellent here.<\/p>\n\n\n\n
CREATE INDEX idx_users_lower_email\nON users (LOWER(email));\n<\/code><\/pre>\n\n\n\n
Then query:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE LOWER(email) = LOWER('Alice@Example.com');\n<\/code><\/pre>\n\n\n\n
Partial Index<\/h2>\n\n\n\n
This is a powerful PostgreSQL feature.<\/p>\n\n\n\n
CREATE INDEX idx_active_users_email\nON users(email)\nWHERE is_active = TRUE;\n<\/code><\/pre>\n\n\n\n
This indexes only active users.<\/p>\n\n\n\n
MySQL does not have the same general partial-index feature.<\/p>\n\n\n\n
PostgreSQL Index Types<\/h2>\n\n\n\n
PostgreSQL Index Type<\/th>Use<\/th><\/tr><\/thead>
B-tree<\/td>Default, equality\/range<\/td><\/tr>
Hash<\/td>Equality<\/td><\/tr>
GIN<\/td>JSONB, arrays, full-text<\/td><\/tr>
GiST<\/td>Geometric, full-text, ranges<\/td><\/tr>
SP-GiST<\/td>Partitioned search structures<\/td><\/tr>
BRIN<\/td>Very large naturally ordered tables<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example JSONB GIN index:<\/p>\n\n\n\n
CREATE INDEX idx_events_payload\nON events USING GIN (payload);\n<\/code><\/pre>\n\n\n\n
Example BRIN index:<\/p>\n\n\n\n
CREATE INDEX idx_logs_created_at_brin\nON logs USING BRIN (created_at);\n<\/code><\/pre>\n\n\n\n
BRIN is useful for huge append-only tables where data is naturally ordered by time.<\/p>\n\n\n\n
\n\n\n\n
23. Explain Plan Mapping<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
EXPLAIN\nSELECT *\nFROM users\nWHERE email = 'alice@example.com';\n<\/code><\/pre>\n\n\n\n
MySQL also supports:<\/p>\n\n\n\n
EXPLAIN ANALYZE\nSELECT *\nFROM users\nWHERE email = 'alice@example.com';\n<\/code><\/pre>\n\n\n\n
in newer versions.<\/p>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
EXPLAIN\nSELECT *\nFROM users\nWHERE email = 'alice@example.com';\n<\/code><\/pre>\n\n\n\n
Better:<\/p>\n\n\n\n
EXPLAIN ANALYZE\nSELECT *\nFROM users\nWHERE email = 'alice@example.com';\n<\/code><\/pre>\n\n\n\n
Even better for details:<\/p>\n\n\n\n
EXPLAIN (ANALYZE, BUFFERS)\nSELECT *\nFROM users\nWHERE email = 'alice@example.com';\n<\/code><\/pre>\n\n\n\n
PostgreSQL Plan Terms<\/h2>\n\n\n\n
Term<\/th>Meaning<\/th><\/tr><\/thead>
Seq Scan<\/code><\/td>Full table scan<\/td><\/tr>
Index Scan<\/code><\/td>Uses index and table<\/td><\/tr>
Index Only Scan<\/code><\/td>Uses only index if possible<\/td><\/tr>
Bitmap Index Scan<\/code><\/td>Builds bitmap from index<\/td><\/tr>
Nested Loop<\/code><\/td>Join by looping<\/td><\/tr>
Hash Join<\/code><\/td>Join using hash table<\/td><\/tr>
Merge Join<\/code><\/td>Join sorted inputs<\/td><\/tr>
Sort<\/code><\/td>Sort operation<\/td><\/tr>
Aggregate<\/code><\/td>Aggregation<\/td><\/tr>
HashAggregate<\/code><\/td>Aggregation using hash table<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Important:<\/p>\n\n\n\n
EXPLAIN ANALYZE actually runs the query.\n<\/code><\/pre>\n\n\n\n
So be careful with:<\/p>\n\n\n\n
EXPLAIN ANALYZE\nDELETE FROM large_table;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
24. Logs Mapping<\/h1>\n\n\n\n
MySQL Logs<\/h2>\n\n\n\n
MySQL Log<\/th>Meaning<\/th><\/tr><\/thead>
Error log<\/td>Server errors and startup messages<\/td><\/tr>
General query log<\/td>All client queries<\/td><\/tr>
Slow query log<\/td>Slow SQL statements<\/td><\/tr>
Binary log<\/td>Replication and point-in-time recovery<\/td><\/tr>
Relay log<\/td>Replica receives source binary log events<\/td><\/tr>
Redo log<\/td>InnoDB crash recovery<\/td><\/tr>
Undo log<\/td>MVCC rollback data<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL Logs<\/h2>\n\n\n\n
PostgreSQL Log\/Resource<\/th>Meaning<\/th><\/tr><\/thead>
Server log<\/td>Errors, messages, slow queries<\/td><\/tr>
WAL<\/td>Write-Ahead Log for recovery and replication<\/td><\/tr>
Archive WAL<\/td>Saved WAL files for PITR<\/td><\/tr>
Autovacuum logs<\/td>Vacuum activity if enabled<\/td><\/tr>
Checkpoint logs<\/td>Checkpoint activity if enabled<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Slow Query Logging<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
slow_query_log = 1\nlong_query_time = 2\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
log_min_duration_statement = 2000\n<\/code><\/pre>\n\n\n\n
This logs queries taking longer than 2000 milliseconds.<\/p>\n\n\n\n
Statement Logging<\/h2>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
log_statement = 'none'\nlog_statement = 'ddl'\nlog_statement = 'mod'\nlog_statement = 'all'\n<\/code><\/pre>\n\n\n\n
Common safer setting:<\/p>\n\n\n\n
log_statement = 'ddl'\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
25. WAL vs Binary Log<\/h1>\n\n\n\n
This is a major concept.<\/p>\n\n\n\n
MySQL Binary Log<\/h2>\n\n\n\n
MySQL binary log records changes for:<\/p>\n\n\n\n
Replication\nPoint-in-time recovery\nAuditing changes\n<\/code><\/pre>\n\n\n\n
Common formats:<\/p>\n\n\n\n
STATEMENT\nROW\nMIXED\n<\/code><\/pre>\n\n\n\n
PostgreSQL WAL<\/h2>\n\n\n\n
PostgreSQL uses:<\/p>\n\n\n\n
WAL = Write-Ahead Log\n<\/code><\/pre>\n\n\n\n
WAL is used for:<\/p>\n\n\n\n
Crash recovery\nStreaming replication\nPoint-in-time recovery\nLogical decoding\n<\/code><\/pre>\n\n\n\n
Mapping<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Binary log<\/td>WAL for replication\/recovery concepts<\/td><\/tr>
Redo log<\/td>WAL<\/td><\/tr>
Relay log<\/td>Received WAL on standby<\/td><\/tr>
Binlog position<\/td>LSN<\/td><\/tr>
GTID<\/td>Replication slots \/ LSN concepts<\/td><\/tr>
Row-based replication<\/td>Logical replication \/ WAL decoding<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL uses LSN:<\/p>\n\n\n\n
Log Sequence Number\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT pg_current_wal_lsn();\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
26. Backup and Restore Mapping<\/h1>\n\n\n\n
MySQL Logical Backup<\/h2>\n\n\n\n
mysqldump -u root -p shop > shop.sql\n<\/code><\/pre>\n\n\n\n
Restore:<\/p>\n\n\n\n
mysql -u root -p shop < shop.sql\n<\/code><\/pre>\n\n\n\n
PostgreSQL Logical Backup<\/h2>\n\n\n\n
pg_dump -U postgres shop > shop.sql\n<\/code><\/pre>\n\n\n\n
Restore:<\/p>\n\n\n\n
psql -U postgres -d shop < shop.sql\n<\/code><\/pre>\n\n\n\n
PostgreSQL Custom Format Backup<\/h2>\n\n\n\n
pg_dump -U postgres -Fc shop > shop.dump\n<\/code><\/pre>\n\n\n\n
Restore:<\/p>\n\n\n\n
pg_restore -U postgres -d shop shop.dump\n<\/code><\/pre>\n\n\n\n
Backup All Databases<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
mysqldump -u root -p --all-databases > all.sql\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
pg_dumpall -U postgres > all.sql\n<\/code><\/pre>\n\n\n\n
Physical Backup<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
Filesystem snapshot\nMySQL Enterprise Backup\nPercona XtraBackup\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
pg_basebackup\nFilesystem snapshot\nStorage snapshot\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
pg_basebackup -U replication_user -D \/backup\/pgbase -Fp -Xs -P\n<\/code><\/pre>\n\n\n\n
Point-In-Time Recovery<\/h2>\n\n\n\n
MySQL PITR uses:<\/p>\n\n\n\n
Full backup + binary logs\n<\/code><\/pre>\n\n\n\n
PostgreSQL PITR uses:<\/p>\n\n\n\n
Base backup + archived WAL\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
27. Replication Mapping<\/h1>\n\n\n\n
MySQL Replication<\/h2>\n\n\n\n
Common terms:<\/p>\n\n\n\n
Source\nReplica\nBinary log\nRelay log\nGTID\nSemi-sync replication\n<\/code><\/pre>\n\n\n\n
Older terms:<\/p>\n\n\n\n
Master\nSlave\n<\/code><\/pre>\n\n\n\n
PostgreSQL Replication<\/h2>\n\n\n\n
Common terms:<\/p>\n\n\n\n
Primary\nStandby\nWAL streaming\nReplication slot\nSynchronous replication\nLogical replication\n<\/code><\/pre>\n\n\n\n
Mapping Table<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Source\/master<\/td>Primary<\/td><\/tr>
Replica\/slave<\/td>Standby<\/td><\/tr>
Binary log<\/td>WAL<\/td><\/tr>
Relay log<\/td>WAL received by standby<\/td><\/tr>
Binlog position<\/td>LSN<\/td><\/tr>
GTID<\/td>Replication slots \/ LSN tracking<\/td><\/tr>
Row-based replication<\/td>Logical replication<\/td><\/tr>
Statement-based replication<\/td>No exact direct default equivalent<\/td><\/tr>
Semi-sync replication<\/td>Synchronous replication<\/td><\/tr>
Read replica<\/td>Hot standby<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL Physical Streaming Replication<\/h2>\n\n\n\n
This replicates the whole PostgreSQL cluster.<\/p>\n\n\n\n
Good for:<\/p>\n\n\n\n
Read replicas\nFailover\nDisaster recovery\n<\/code><\/pre>\n\n\n\n
PostgreSQL Logical Replication<\/h2>\n\n\n\n
This replicates selected tables.<\/p>\n\n\n\n
Good for:<\/p>\n\n\n\n
Selective replication\nData migration\nPartial sync\nVersion upgrades\n<\/code><\/pre>\n\n\n\n
Example publication:<\/p>\n\n\n\n
CREATE PUBLICATION shop_pub\nFOR TABLE customers, orders;\n<\/code><\/pre>\n\n\n\n
Example subscription:<\/p>\n\n\n\n
CREATE SUBSCRIPTION shop_sub\nCONNECTION 'host=primary dbname=shop user=repl password=secret'\nPUBLICATION shop_pub;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
28. High Availability Mapping<\/h1>\n\n\n\n
MySQL HA Tools<\/h2>\n\n\n\n
Common MySQL HA options:<\/p>\n\n\n\n
Group Replication\nInnoDB Cluster\nMHA\nOrchestrator\nProxySQL\nSemi-sync replication\n<\/code><\/pre>\n\n\n\n
PostgreSQL HA Tools<\/h2>\n\n\n\n
Common PostgreSQL HA tools:<\/p>\n\n\n\n
Patroni\nrepmgr\npg_auto_failover\nPacemaker\/Corosync\nHAProxy\nPgBouncer\n<\/code><\/pre>\n\n\n\n
Conceptual Mapping<\/h2>\n\n\n\n
MySQL HA Concept<\/th>PostgreSQL Equivalent<\/th><\/tr><\/thead>
Primary with replicas<\/td>Primary with standbys<\/td><\/tr>
Automatic failover<\/td>Patroni\/repmgr\/pg_auto_failover<\/td><\/tr>
ProxySQL routing<\/td>HAProxy\/PgBouncer<\/td><\/tr>
Group Replication<\/td>No exact default equivalent<\/td><\/tr>
InnoDB Cluster<\/td>PostgreSQL HA stack using external tools<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL core provides strong replication primitives, but automatic HA is commonly built with external tooling.<\/p>\n\n\n\n
\n\n\n\n
29. Partitioning Mapping<\/h1>\n\n\n\n
MySQL Partitioning<\/h2>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE TABLE orders (\n    id BIGINT,\n    order_date DATE\n)\nPARTITION BY RANGE (YEAR(order_date)) (\n    PARTITION p2024 VALUES LESS THAN (2025),\n    PARTITION p2025 VALUES LESS THAN (2026)\n);\n<\/code><\/pre>\n\n\n\n
PostgreSQL Partitioning<\/h2>\n\n\n\n
PostgreSQL uses declarative partitioning.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE TABLE orders (\n    id BIGINT GENERATED ALWAYS AS IDENTITY,\n    order_date DATE NOT NULL,\n    amount NUMERIC(10,2)\n) PARTITION BY RANGE (order_date);\n<\/code><\/pre>\n\n\n\n
Create partitions:<\/p>\n\n\n\n
CREATE TABLE orders_2025\nPARTITION OF orders\nFOR VALUES FROM ('2025-01-01') TO ('2026-01-01');\n\nCREATE TABLE orders_2026\nPARTITION OF orders\nFOR VALUES FROM ('2026-01-01') TO ('2027-01-01');\n<\/code><\/pre>\n\n\n\n
Partitioning Types<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
RANGE<\/td>RANGE<\/td><\/tr>
LIST<\/td>LIST<\/td><\/tr>
HASH<\/td>HASH<\/td><\/tr>
KEY<\/td>No exact same style<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL partitioning is commonly used for:<\/p>\n\n\n\n
Large time-series tables\nLog tables\nMulti-tenant data\nData retention\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
30. Temporary Tables<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
CREATE TEMPORARY TABLE temp_users (\n    id INT,\n    name VARCHAR(100)\n);\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
CREATE TEMP TABLE temp_users (\n    id INT,\n    name TEXT\n);\n<\/code><\/pre>\n\n\n\n
PostgreSQL temporary tables are session-local.<\/p>\n\n\n\n
You can also use:<\/p>\n\n\n\n
CREATE TEMPORARY TABLE temp_users (\n    id INT,\n    name TEXT\n);\n<\/code><\/pre>\n\n\n\n
Same meaning.<\/p>\n\n\n\n
\n\n\n\n
31. Views and Materialized Views<\/h1>\n\n\n\n
Views<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
CREATE VIEW active_users AS\nSELECT *\nFROM users\nWHERE is_active = 1;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
CREATE VIEW active_users AS\nSELECT *\nFROM users\nWHERE is_active = TRUE;\n<\/code><\/pre>\n\n\n\n
Materialized Views<\/h2>\n\n\n\n
PostgreSQL supports materialized views natively:<\/p>\n\n\n\n
CREATE MATERIALIZED VIEW monthly_sales AS\nSELECT\n    date_trunc('month', order_date) AS month,\n    SUM(amount) AS total_sales\nFROM orders\nGROUP BY date_trunc('month', order_date);\n<\/code><\/pre>\n\n\n\n
Refresh:<\/p>\n\n\n\n
REFRESH MATERIALIZED VIEW monthly_sales;\n<\/code><\/pre>\n\n\n\n
MySQL does not have true built-in materialized views in the same way. You usually simulate them with tables, scheduled jobs, or triggers.<\/p>\n\n\n\n
\n\n\n\n
32. Functions, Procedures, and Triggers<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
MySQL supports:<\/p>\n\n\n\n
Stored procedures\nFunctions\nTriggers\nEvents\n<\/code><\/pre>\n\n\n\n
Example procedure:<\/p>\n\n\n\n
DELIMITER \/\/\n\nCREATE PROCEDURE GetUsers()\nBEGIN\n    SELECT * FROM users;\nEND \/\/\n\nDELIMITER ;\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL supports:<\/p>\n\n\n\n
Functions\nProcedures\nTriggers\nRules\nExtensions\n<\/code><\/pre>\n\n\n\n
PostgreSQL function example:<\/p>\n\n\n\n
CREATE OR REPLACE FUNCTION get_user_count()\nRETURNS INTEGER AS $$\nDECLARE\n    total INTEGER;\nBEGIN\n    SELECT COUNT(*) INTO total FROM users;\n    RETURN total;\nEND;\n$$ LANGUAGE plpgsql;\n<\/code><\/pre>\n\n\n\n
Call:<\/p>\n\n\n\n
SELECT get_user_count();\n<\/code><\/pre>\n\n\n\n
Procedure example:<\/p>\n\n\n\n
CREATE PROCEDURE deactivate_user(p_user_id BIGINT)\nLANGUAGE plpgsql\nAS $$\nBEGIN\n    UPDATE users\n    SET is_active = FALSE\n    WHERE id = p_user_id;\nEND;\n$$;\n<\/code><\/pre>\n\n\n\n
Call:<\/p>\n\n\n\n
CALL deactivate_user(10);\n<\/code><\/pre>\n\n\n\n
Trigger Example in PostgreSQL<\/h2>\n\n\n\n
CREATE OR REPLACE FUNCTION update_modified_time()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n<\/code><\/pre>\n\n\n\n
CREATE TRIGGER trg_users_updated_at\nBEFORE UPDATE ON users\nFOR EACH ROW\nEXECUTE FUNCTION update_modified_time();\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
33. Events vs Scheduled Jobs<\/h1>\n\n\n\n
MySQL Event Scheduler<\/h2>\n\n\n\n
MySQL has an event scheduler:<\/p>\n\n\n\n
CREATE EVENT cleanup_old_logs\nON SCHEDULE EVERY 1 DAY\nDO\nDELETE FROM logs\nWHERE created_at < NOW() - INTERVAL 30 DAY;\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL core does not have exactly the same built-in event scheduler.<\/p>\n\n\n\n
Common options:<\/p>\n\n\n\n
cron\npg_cron extension\napplication scheduler\nexternal job scheduler\n<\/code><\/pre>\n\n\n\n
With pg_cron<\/code>, PostgreSQL can schedule jobs from inside the database.<\/p>\n\n\n\n
Conceptual mapping:<\/p>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Event Scheduler<\/td>pg_cron<\/code>, cron, external scheduler<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
34. Extensions vs Plugins<\/h1>\n\n\n\n
MySQL Plugins<\/h2>\n\n\n\n
MySQL supports plugins for:<\/p>\n\n\n\n
Authentication\nStorage engines\nFull-text parsers\nAudit\nReplication\n<\/code><\/pre>\n\n\n\n
PostgreSQL Extensions<\/h2>\n\n\n\n
PostgreSQL has a powerful extension system.<\/p>\n\n\n\n
List available extensions:<\/p>\n\n\n\n
SELECT *\nFROM pg_available_extensions;\n<\/code><\/pre>\n\n\n\n
Install extension:<\/p>\n\n\n\n
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n<\/code><\/pre>\n\n\n\n
Popular PostgreSQL extensions:<\/p>\n\n\n\n
Extension<\/th>Purpose<\/th><\/tr><\/thead>
pg_stat_statements<\/code><\/td>Query performance tracking<\/td><\/tr>
uuid-ossp<\/code><\/td>UUID generation<\/td><\/tr>
pgcrypto<\/code><\/td>Cryptographic functions<\/td><\/tr>
citext<\/code><\/td>Case-insensitive text<\/td><\/tr>
postgis<\/code><\/td>Spatial\/geographic data<\/td><\/tr>
pg_trgm<\/code><\/td>Trigram search<\/td><\/tr>
btree_gin<\/code><\/td>Extra GIN support<\/td><\/tr>
pg_cron<\/code><\/td>Scheduled jobs<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE EXTENSION IF NOT EXISTS pg_trgm;\n<\/code><\/pre>\n\n\n\n
Then create trigram index:<\/p>\n\n\n\n
CREATE INDEX idx_users_name_trgm\nON users USING GIN (name gin_trgm_ops);\n<\/code><\/pre>\n\n\n\n
Useful for fuzzy search.<\/p>\n\n\n\n
\n\n\n\n
35. Monitoring Mapping<\/h1>\n\n\n\n
MySQL Monitoring<\/h2>\n\n\n\n
Common commands:<\/p>\n\n\n\n
SHOW STATUS;\nSHOW GLOBAL STATUS;\nSHOW VARIABLES;\nSHOW PROCESSLIST;\n<\/code><\/pre>\n\n\n\n
Common schemas:<\/p>\n\n\n\n
performance_schema\nsys\ninformation_schema\n<\/code><\/pre>\n\n\n\n
PostgreSQL Monitoring<\/h2>\n\n\n\n
PostgreSQL uses catalog and statistics views.<\/p>\n\n\n\n
Common views:<\/p>\n\n\n\n
PostgreSQL View<\/th>Meaning<\/th><\/tr><\/thead>
pg_stat_activity<\/code><\/td>Active sessions and queries<\/td><\/tr>
pg_stat_database<\/code><\/td>Database-level stats<\/td><\/tr>
pg_stat_user_tables<\/code><\/td>Table-level stats<\/td><\/tr>
pg_stat_user_indexes<\/code><\/td>Index-level stats<\/td><\/tr>
pg_stat_bgwriter<\/code><\/td>Background writer stats<\/td><\/tr>
pg_stat_wal<\/code><\/td>WAL stats<\/td><\/tr>
pg_locks<\/code><\/td>Lock information<\/td><\/tr>
pg_stat_replication<\/code><\/td>Replication status<\/td><\/tr>
pg_stat_statements<\/code><\/td>Query stats extension<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Active Queries<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
SHOW PROCESSLIST;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
SELECT\n    pid,\n    usename,\n    datname,\n    state,\n    query,\n    query_start\nFROM pg_stat_activity\nORDER BY query_start;\n<\/code><\/pre>\n\n\n\n
Locks<\/h2>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
SELECT *\nFROM pg_locks;\n<\/code><\/pre>\n\n\n\n
Useful lock query:<\/p>\n\n\n\n
SELECT\n    a.pid,\n    a.usename,\n    a.query,\n    l.locktype,\n    l.mode,\n    l.granted\nFROM pg_locks l\nJOIN pg_stat_activity a ON l.pid = a.pid;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
36. Query Performance Mapping<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
You may use:<\/p>\n\n\n\n
EXPLAIN\nslow query log\nperformance_schema\nsys schema\nSHOW STATUS\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
You commonly use:<\/p>\n\n\n\n
EXPLAIN ANALYZE\npg_stat_statements\npg_stat_activity\npg_stat_user_tables\npg_stat_user_indexes\nauto_explain\n<\/code><\/pre>\n\n\n\n
pg_stat_statements<\/code><\/h2>\n\n\n\n
Enable extension:<\/p>\n\n\n\n
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n<\/code><\/pre>\n\n\n\n
Query slow\/expensive SQL:<\/p>\n\n\n\n
SELECT\n    query,\n    calls,\n    total_exec_time,\n    mean_exec_time,\n    rows\nFROM pg_stat_statements\nORDER BY total_exec_time DESC\nLIMIT 10;\n<\/code><\/pre>\n\n\n\n
This is somewhat like using MySQL Performance Schema or slow query analysis.<\/p>\n\n\n\n
\n\n\n\n
37. Locking Mapping<\/h1>\n\n\n\n
MySQL\/InnoDB<\/h2>\n\n\n\n
Common lock concepts:<\/p>\n\n\n\n
Row locks\nGap locks\nNext-key locks\nMetadata locks\nTable locks\nDeadlocks\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
Common lock concepts:<\/p>\n\n\n\n
Row locks\nTable locks\nAdvisory locks\nPredicate locks\nDeadlocks\n<\/code><\/pre>\n\n\n\n
Row Lock<\/h2>\n\n\n\n
Same style:<\/p>\n\n\n\n
SELECT *\nFROM orders\nWHERE id = 100\nFOR UPDATE;\n<\/code><\/pre>\n\n\n\n
PostgreSQL Lock Modes<\/h2>\n\n\n\n
Common PostgreSQL table lock modes include:<\/p>\n\n\n\n
ACCESS SHARE\nROW SHARE\nROW EXCLUSIVE\nSHARE UPDATE EXCLUSIVE\nSHARE\nSHARE ROW EXCLUSIVE\nEXCLUSIVE\nACCESS EXCLUSIVE\n<\/code><\/pre>\n\n\n\n
The strongest common lock is:<\/p>\n\n\n\n
ACCESS EXCLUSIVE\n<\/code><\/pre>\n\n\n\n
It blocks almost everything.<\/p>\n\n\n\n
Advisory Locks<\/h2>\n\n\n\n
PostgreSQL has advisory locks:<\/p>\n\n\n\n
SELECT pg_advisory_lock(12345);\n<\/code><\/pre>\n\n\n\n
Release:<\/p>\n\n\n\n
SELECT pg_advisory_unlock(12345);\n<\/code><\/pre>\n\n\n\n
These are application-controlled locks.<\/p>\n\n\n\n
\n\n\n\n
38. Isolation Levels<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
Common isolation levels:<\/p>\n\n\n\n
READ UNCOMMITTED\nREAD COMMITTED\nREPEATABLE READ\nSERIALIZABLE\n<\/code><\/pre>\n\n\n\n
MySQL\/InnoDB default is commonly:<\/p>\n\n\n\n
REPEATABLE READ\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL supports:<\/p>\n\n\n\n
READ COMMITTED\nREPEATABLE READ\nSERIALIZABLE\n<\/code><\/pre>\n\n\n\n
PostgreSQL default:<\/p>\n\n\n\n
READ COMMITTED\n<\/code><\/pre>\n\n\n\n
Set isolation level:<\/p>\n\n\n\n
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;\n<\/code><\/pre>\n\n\n\n
or:<\/p>\n\n\n\n
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n<\/code><\/pre>\n\n\n\n
Important mapping:<\/p>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Common default: REPEATABLE READ<\/code><\/td>Default: READ COMMITTED<\/code><\/td><\/tr>
Gap locks under InnoDB<\/td>Different MVCC\/locking behavior<\/td><\/tr>
Serializable<\/td>Serializable Snapshot Isolation<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
39. Character Set and Collation<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
MySQL often uses:<\/p>\n\n\n\n
CHARACTER SET utf8mb4\nCOLLATE utf8mb4_0900_ai_ci\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE DATABASE shop\nCHARACTER SET utf8mb4\nCOLLATE utf8mb4_0900_ai_ci;\n<\/code><\/pre>\n\n\n\n
Many MySQL collations are case-insensitive by default.<\/p>\n\n\n\n
That means:<\/p>\n\n\n\n
WHERE name = 'alice'\n<\/code><\/pre>\n\n\n\n
may match:<\/p>\n\n\n\n
Alice\nALICE\nalice\n<\/code><\/pre>\n\n\n\n
depending on collation.<\/p>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL supports collations too, but case-sensitive behavior is more common by default.<\/p>\n\n\n\n
Case-insensitive search:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE name ILIKE 'alice';\n<\/code><\/pre>\n\n\n\n
Or use LOWER()<\/code>:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE LOWER(name) = LOWER('Alice');\n<\/code><\/pre>\n\n\n\n
Or use citext<\/code> extension:<\/p>\n\n\n\n
CREATE EXTENSION IF NOT EXISTS citext;\n<\/code><\/pre>\n\n\n\n
CREATE TABLE users (\n    email CITEXT UNIQUE\n);\n<\/code><\/pre>\n\n\n\n
CITEXT<\/code> means case-insensitive text.<\/p>\n\n\n\n
\n\n\n\n
40. Full-Text Search<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
MySQL full-text index:<\/p>\n\n\n\n
CREATE FULLTEXT INDEX idx_articles_body\nON articles(body);\n<\/code><\/pre>\n\n\n\n
Query:<\/p>\n\n\n\n
SELECT *\nFROM articles\nWHERE MATCH(title, body) AGAINST ('database tutorial');\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL full-text search uses tsvector<\/code> and tsquery<\/code>.<\/p>\n\n\n\n
Simple query:<\/p>\n\n\n\n
SELECT *\nFROM articles\nWHERE to_tsvector('english', title || ' ' || body)\n      @@ plainto_tsquery('english', 'database tutorial');\n<\/code><\/pre>\n\n\n\n
Better with generated column:<\/p>\n\n\n\n
ALTER TABLE articles\nADD COLUMN search_vector tsvector\nGENERATED ALWAYS AS (\n    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))\n) STORED;\n<\/code><\/pre>\n\n\n\n
Index:<\/p>\n\n\n\n
CREATE INDEX idx_articles_search\nON articles USING GIN (search_vector);\n<\/code><\/pre>\n\n\n\n
Query:<\/p>\n\n\n\n
SELECT *\nFROM articles\nWHERE search_vector @@ plainto_tsquery('english', 'database tutorial');\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
41. JSON Mapping<\/h1>\n\n\n\n
MySQL JSON<\/h2>\n\n\n\n
CREATE TABLE events (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    payload JSON\n);\n<\/code><\/pre>\n\n\n\n
Query:<\/p>\n\n\n\n
SELECT JSON_EXTRACT(payload, '$.type')\nFROM events;\n<\/code><\/pre>\n\n\n\n
PostgreSQL JSONB<\/h2>\n\n\n\n
CREATE TABLE events (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    payload JSONB\n);\n<\/code><\/pre>\n\n\n\n
Get field as JSON:<\/p>\n\n\n\n
SELECT payload->'type'\nFROM events;\n<\/code><\/pre>\n\n\n\n
Get field as text:<\/p>\n\n\n\n
SELECT payload->>'type'\nFROM events;\n<\/code><\/pre>\n\n\n\n
Filter:<\/p>\n\n\n\n
SELECT *\nFROM events\nWHERE payload->>'type' = 'login';\n<\/code><\/pre>\n\n\n\n
Containment:<\/p>\n\n\n\n
SELECT *\nFROM events\nWHERE payload @> '{\"type\": \"login\"}';\n<\/code><\/pre>\n\n\n\n
Index:<\/p>\n\n\n\n
CREATE INDEX idx_events_payload\nON events USING GIN (payload);\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
42. Constraints Mapping<\/h1>\n\n\n\n
Constraint<\/th>MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Primary key<\/td>Yes<\/td>Yes<\/td><\/tr>
Foreign key<\/td>Yes with InnoDB<\/td>Yes<\/td><\/tr>
Unique<\/td>Yes<\/td>Yes<\/td><\/tr>
Not null<\/td>Yes<\/td>Yes<\/td><\/tr>
Check<\/td>Supported in modern MySQL<\/td>Strong support<\/td><\/tr>
Exclusion constraint<\/td>No common equivalent<\/td>Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL Check Constraint<\/h2>\n\n\n\n
CREATE TABLE products (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    price NUMERIC(10,2) NOT NULL,\n    CONSTRAINT chk_price_positive CHECK (price >= 0)\n);\n<\/code><\/pre>\n\n\n\n
Foreign Key<\/h2>\n\n\n\n
Same idea:<\/p>\n\n\n\n
CREATE TABLE orders (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    customer_id BIGINT NOT NULL,\n    CONSTRAINT fk_orders_customer\n        FOREIGN KEY (customer_id)\n        REFERENCES customers(id)\n);\n<\/code><\/pre>\n\n\n\n
Deferrable Constraints<\/h2>\n\n\n\n
PostgreSQL supports deferrable constraints.<\/p>\n\n\n\n
CREATE TABLE child (\n    id BIGINT PRIMARY KEY,\n    parent_id BIGINT,\n    CONSTRAINT fk_parent\n        FOREIGN KEY (parent_id)\n        REFERENCES parent(id)\n        DEFERRABLE INITIALLY DEFERRED\n);\n<\/code><\/pre>\n\n\n\n
This lets constraint checking happen at transaction commit.<\/p>\n\n\n\n
Very powerful. Not something most MySQL users commonly use.<\/p>\n\n\n\n
\n\n\n\n
43. Generated Columns<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
CREATE TABLE users (\n    first_name VARCHAR(100),\n    last_name VARCHAR(100),\n    full_name VARCHAR(201)\n        GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED\n);\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
CREATE TABLE users (\n    first_name TEXT,\n    last_name TEXT,\n    full_name TEXT\n        GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED\n);\n<\/code><\/pre>\n\n\n\n
Both support generated columns, but syntax and function support differ.<\/p>\n\n\n\n
\n\n\n\n
44. Error Handling and Strictness<\/h1>\n\n\n\n
PostgreSQL is stricter than MySQL in many situations.<\/p>\n\n\n\n
Example: Invalid Integer<\/h2>\n\n\n\n
MySQL may allow or coerce depending on SQL mode:<\/p>\n\n\n\n
INSERT INTO users(id) VALUES ('abc');\n<\/code><\/pre>\n\n\n\n
PostgreSQL will reject invalid type conversion.<\/p>\n\n\n\n
Example: Group By<\/h2>\n\n\n\n
PostgreSQL requires correct grouping.<\/p>\n\n\n\n
Example: Transaction Error<\/h2>\n\n\n\n
PostgreSQL aborts the whole transaction after an error until rollback.<\/p>\n\n\n\n
This strictness is actually one reason many developers like PostgreSQL: fewer silent surprises.<\/p>\n\n\n\n
\n\n\n\n
45. DDL Differences<\/h1>\n\n\n\n
Create Database<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
CREATE DATABASE shop;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
CREATE DATABASE shop;\n<\/code><\/pre>\n\n\n\n
Same.<\/p>\n\n\n\n
Use Database<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
USE shop;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
\\c shop\n<\/code><\/pre>\n\n\n\n
In PostgreSQL, \\c<\/code> is a psql<\/code> command, not SQL.<\/p>\n\n\n\n
Create Table<\/h2>\n\n\n\n
MySQL:<\/p>\n\n\n\n
CREATE TABLE users (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(100) NOT NULL\n);\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
CREATE TABLE users (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name VARCHAR(100) NOT NULL\n);\n<\/code><\/pre>\n\n\n\n
Drop Table<\/h2>\n\n\n\n
Same:<\/p>\n\n\n\n
DROP TABLE users;\n<\/code><\/pre>\n\n\n\n
Truncate<\/h2>\n\n\n\n
Same:<\/p>\n\n\n\n
TRUNCATE TABLE users;\n<\/code><\/pre>\n\n\n\n
PostgreSQL can restart identity:<\/p>\n\n\n\n
TRUNCATE TABLE users RESTART IDENTITY;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
46. Admin Commands Mapping<\/h1>\n\n\n\n
Task<\/th>MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
Start server<\/td>systemctl start mysql<\/code><\/td>systemctl start postgresql<\/code><\/td><\/tr>
Stop server<\/td>systemctl stop mysql<\/code><\/td>systemctl stop postgresql<\/code><\/td><\/tr>
Restart server<\/td>systemctl restart mysql<\/code><\/td>systemctl restart postgresql<\/code><\/td><\/tr>
Reload config<\/td>systemctl reload mysql<\/code><\/td>SELECT pg_reload_conf();<\/code> or service reload<\/td><\/tr>
Check variables<\/td>SHOW VARIABLES;<\/code><\/td>SHOW ALL;<\/code><\/td><\/tr>
Check status<\/td>SHOW STATUS;<\/code><\/td>pg_stat_*<\/code> views<\/td><\/tr>
List sessions<\/td>SHOW PROCESSLIST;<\/code><\/td>pg_stat_activity<\/code><\/td><\/tr>
Kill session<\/td>KILL id;<\/code><\/td>pg_terminate_backend(pid)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
PostgreSQL reload config:<\/p>\n\n\n\n
SELECT pg_reload_conf();\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
47. Information Schema and Catalogs<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
SELECT *\nFROM information_schema.tables\nWHERE table_schema = 'shop';\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
SELECT *\nFROM information_schema.tables\nWHERE table_schema = 'public';\n<\/code><\/pre>\n\n\n\n
PostgreSQL also has richer internal catalogs:<\/p>\n\n\n\n
SELECT *\nFROM pg_catalog.pg_class;\n<\/code><\/pre>\n\n\n\n
List user tables:<\/p>\n\n\n\n
SELECT schemaname, tablename\nFROM pg_tables\nWHERE schemaname = 'public';\n<\/code><\/pre>\n\n\n\n
List columns:<\/p>\n\n\n\n
SELECT\n    column_name,\n    data_type,\n    is_nullable\nFROM information_schema.columns\nWHERE table_schema = 'public'\n  AND table_name = 'users';\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
48. Security Model Mapping<\/h1>\n\n\n\n
MySQL Security Model<\/h2>\n\n\n\n
MySQL commonly combines:<\/p>\n\n\n\n
User\nHost\nPassword\nPrivileges\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE USER 'reporter'@'10.%' IDENTIFIED BY 'secret';\nGRANT SELECT ON shop.* TO 'reporter'@'10.%';\n<\/code><\/pre>\n\n\n\n
PostgreSQL Security Model<\/h2>\n\n\n\n
PostgreSQL separates:<\/p>\n\n\n\n
Role\nDatabase permission\nSchema permission\nObject permission\nAuthentication rule\n<\/code><\/pre>\n\n\n\n
Example:<\/p>\n\n\n\n
CREATE ROLE reporter WITH LOGIN PASSWORD 'secret';\n\nGRANT CONNECT ON DATABASE shop TO reporter;\nGRANT USAGE ON SCHEMA public TO reporter;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;\n<\/code><\/pre>\n\n\n\n
And in pg_hba.conf<\/code>:<\/p>\n\n\n\n
host    shop    reporter    10.0.0.0\/8    scram-sha-256\n<\/code><\/pre>\n\n\n\n
PostgreSQL Row-Level Security<\/h2>\n\n\n\n
PostgreSQL has strong row-level security.<\/p>\n\n\n\n
Enable:<\/p>\n\n\n\n
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;\n<\/code><\/pre>\n\n\n\n
Create policy:<\/p>\n\n\n\n
CREATE POLICY user_orders_policy\nON orders\nFOR SELECT\nUSING (customer_id = current_setting('app.customer_id')::BIGINT);\n<\/code><\/pre>\n\n\n\n
This is more advanced, but powerful for multi-tenant systems.<\/p>\n\n\n\n
\n\n\n\n
49. Schema Design Difference<\/h1>\n\n\n\n
MySQL Style<\/h2>\n\n\n\n
You may create multiple databases:<\/p>\n\n\n\n
shop\nshop_audit\nshop_reporting\n<\/code><\/pre>\n\n\n\n
And query between them:<\/p>\n\n\n\n
SELECT *\nFROM shop.orders o\nJOIN shop_reporting.order_summary s ON o.id = s.order_id;\n<\/code><\/pre>\n\n\n\n
PostgreSQL Style<\/h2>\n\n\n\n
You may create one database with multiple schemas:<\/p>\n\n\n\n
shop database\n \u251c\u2500\u2500 app\n \u251c\u2500\u2500 audit\n \u251c\u2500\u2500 reporting\n \u2514\u2500\u2500 staging\n<\/code><\/pre>\n\n\n\n
Query:<\/p>\n\n\n\n
SELECT *\nFROM app.orders o\nJOIN reporting.order_summary s ON o.id = s.order_id;\n<\/code><\/pre>\n\n\n\n
This is one of the best PostgreSQL habits to learn early.<\/p>\n\n\n\n
\n\n\n\n
50. Practical Side-by-Side Example<\/h1>\n\n\n\n
Let us build the same small application schema in both databases.<\/p>\n\n\n\n
MySQL Version<\/h2>\n\n\n\n
CREATE DATABASE shop;\nUSE shop;\n\nCREATE TABLE customers (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    email VARCHAR(255) NOT NULL UNIQUE,\n    name VARCHAR(100) NOT NULL,\n    is_active TINYINT(1) NOT NULL DEFAULT 1,\n    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE orders (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    customer_id BIGINT NOT NULL,\n    order_total DECIMAL(10,2) NOT NULL,\n    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    CONSTRAINT fk_orders_customer\n        FOREIGN KEY (customer_id)\n        REFERENCES customers(id)\n);\n\nCREATE INDEX idx_orders_customer_id\nON orders(customer_id);\n<\/code><\/pre>\n\n\n\n
PostgreSQL Version<\/h2>\n\n\n\n
CREATE DATABASE shop;\n<\/code><\/pre>\n\n\n\n
Connect:<\/p>\n\n\n\n
\\c shop\n<\/code><\/pre>\n\n\n\n
Create schema:<\/p>\n\n\n\n
CREATE SCHEMA app;\n<\/code><\/pre>\n\n\n\n
Create tables:<\/p>\n\n\n\n
CREATE TABLE app.customers (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    email VARCHAR(255) NOT NULL UNIQUE,\n    name VARCHAR(100) NOT NULL,\n    is_active BOOLEAN NOT NULL DEFAULT TRUE,\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n);\n\nCREATE TABLE app.orders (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    customer_id BIGINT NOT NULL,\n    order_total NUMERIC(10,2) NOT NULL,\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    CONSTRAINT fk_orders_customer\n        FOREIGN KEY (customer_id)\n        REFERENCES app.customers(id)\n);\n\nCREATE INDEX idx_orders_customer_id\nON app.orders(customer_id);\n<\/code><\/pre>\n\n\n\n
Key Differences in This Example<\/h2>\n\n\n\n
MySQL<\/th>PostgreSQL<\/th><\/tr><\/thead>
AUTO_INCREMENT<\/code><\/td>GENERATED ALWAYS AS IDENTITY<\/code><\/td><\/tr>
TINYINT(1)<\/code><\/td>BOOLEAN<\/code><\/td><\/tr>
DATETIME<\/code><\/td>TIMESTAMPTZ<\/code><\/td><\/tr>
DECIMAL<\/code><\/td>NUMERIC<\/code><\/td><\/tr>
Database used as namespace<\/td>Schema used as namespace<\/td><\/tr>
USE shop<\/code><\/td>\\c shop<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
51. Common Migration Mapping<\/h1>\n\n\n\n
When converting MySQL schema to PostgreSQL:<\/p>\n\n\n\n
MySQL<\/th>Convert To PostgreSQL<\/th><\/tr><\/thead>
AUTO_INCREMENT<\/code><\/td>GENERATED BY DEFAULT AS IDENTITY<\/code><\/td><\/tr>
TINYINT(1)<\/code><\/td>BOOLEAN<\/code><\/td><\/tr>
DATETIME<\/code><\/td>TIMESTAMP<\/code> or TIMESTAMPTZ<\/code><\/td><\/tr>
LONGTEXT<\/code><\/td>TEXT<\/code><\/td><\/tr>
BLOB<\/code><\/td>BYTEA<\/code><\/td><\/tr>
JSON<\/code><\/td>JSONB<\/code><\/td><\/tr>
ENUM<\/code><\/td>PostgreSQL enum or lookup table<\/td><\/tr>
UNSIGNED INT<\/code><\/td>Larger signed type or check constraint<\/td><\/tr>
ON DUPLICATE KEY UPDATE<\/code><\/td>ON CONFLICT DO UPDATE<\/code><\/td><\/tr>
INSERT IGNORE<\/code><\/td>ON CONFLICT DO NOTHING<\/code><\/td><\/tr>
Backticks<\/td>Double quotes or no quotes<\/td><\/tr>
IFNULL()<\/code><\/td>COALESCE()<\/code><\/td><\/tr>
DATE_FORMAT()<\/code><\/td>to_char()<\/code><\/td><\/tr>
STR_TO_DATE()<\/code><\/td>to_date()<\/code> \/ to_timestamp()<\/code><\/td><\/tr>
GROUP_CONCAT()<\/code><\/td>string_agg()<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Function Mapping<\/h2>\n\n\n\n
MySQL Function<\/th>PostgreSQL Function<\/th><\/tr><\/thead>
IFNULL(a,b)<\/code><\/td>COALESCE(a,b)<\/code><\/td><\/tr>
NOW()<\/code><\/td>NOW()<\/code><\/td><\/tr>
CURDATE()<\/code><\/td>CURRENT_DATE<\/code><\/td><\/tr>
CONCAT()<\/code><\/td>CONCAT()<\/code> or `<\/td><\/tr>
GROUP_CONCAT()<\/code><\/td>string_agg()<\/code><\/td><\/tr>
DATE_FORMAT()<\/code><\/td>to_char()<\/code><\/td><\/tr>
JSON_EXTRACT()<\/code><\/td>-><\/code>, ->><\/code>, jsonb_path_query<\/code><\/td><\/tr>
RAND()<\/code><\/td>random()<\/code><\/td><\/tr>
UUID()<\/code><\/td>gen_random_uuid()<\/code> with pgcrypto<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example MySQL:<\/p>\n\n\n\n
SELECT GROUP_CONCAT(name)\nFROM users;\n<\/code><\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
SELECT string_agg(name, ',')\nFROM users;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
52. Quoting Difference<\/h1>\n\n\n\n
MySQL<\/h2>\n\n\n\n
MySQL often uses backticks:<\/p>\n\n\n\n
SELECT `user`, `order`\nFROM `my_table`;\n<\/code><\/pre>\n\n\n\n
PostgreSQL<\/h2>\n\n\n\n
PostgreSQL uses double quotes for identifiers:<\/p>\n\n\n\n
SELECT \"user\", \"order\"\nFROM \"my_table\";\n<\/code><\/pre>\n\n\n\n
But best practice:<\/p>\n\n\n\n
Avoid reserved words and mixed-case identifiers.\nUse lowercase snake_case.\n<\/code><\/pre>\n\n\n\n
Good PostgreSQL style:<\/p>\n\n\n\n
CREATE TABLE customer_orders (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    order_status TEXT NOT NULL\n);\n<\/code><\/pre>\n\n\n\n
Avoid:<\/p>\n\n\n\n
CREATE TABLE \"CustomerOrders\" (\n    \"ID\" BIGINT\n);\n<\/code><\/pre>\n\n\n\n
Because then you must quote it forever.<\/p>\n\n\n\n
\n\n\n\n
53. Naming Conventions<\/h1>\n\n\n\n
Recommended PostgreSQL style:<\/p>\n\n\n\n
lowercase\nsnake_case\nplural or singular consistently\navoid reserved words\navoid quoted identifiers\n<\/code><\/pre>\n\n\n\n
Good:<\/p>\n\n\n\n
customer_orders\ncreated_at\nis_active\norder_total\n<\/code><\/pre>\n\n\n\n
Avoid:<\/p>\n\n\n\n
CustomerOrders\nOrder\nUser\nCreatedAt\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
54. PostgreSQL Features MySQL Users Should Appreciate<\/h1>\n\n\n\n
PostgreSQL has several powerful features that MySQL users may not expect.<\/p>\n\n\n\n
Strong JSONB<\/h2>\n\n\n\n
payload JSONB\n<\/code><\/pre>\n\n\n\n
with GIN indexes.<\/p>\n\n\n\n
Partial Indexes<\/h2>\n\n\n\n
CREATE INDEX idx_active_users\nON users(email)\nWHERE is_active = TRUE;\n<\/code><\/pre>\n\n\n\n
Expression Indexes<\/h2>\n\n\n\n
CREATE INDEX idx_lower_email\nON users(LOWER(email));\n<\/code><\/pre>\n\n\n\n
Rich Data Types<\/h2>\n\n\n\n
PostgreSQL supports:<\/p>\n\n\n\n
UUID\nJSONB\nARRAY\nRANGE\nINET\nCIDR\nMACADDR\nTSVECTOR\nGEOMETRY through PostGIS\n<\/code><\/pre>\n\n\n\n
Extensions<\/h2>\n\n\n\n
CREATE EXTENSION postgis;\nCREATE EXTENSION pg_stat_statements;\nCREATE EXTENSION pgcrypto;\n<\/code><\/pre>\n\n\n\n
Transactional DDL<\/h2>\n\n\n\n
Rollback many schema changes safely.<\/p>\n\n\n\n
Powerful CTEs<\/h2>\n\n\n\n
WITH active_customers AS (\n    SELECT *\n    FROM customers\n    WHERE is_active = TRUE\n)\nSELECT *\nFROM active_customers;\n<\/code><\/pre>\n\n\n\n
Returning Clause<\/h2>\n\n\n\n
PostgreSQL can return inserted\/updated\/deleted rows:<\/p>\n\n\n\n
INSERT INTO users (name)\nVALUES ('Alice')\nRETURNING id, name;\n<\/code><\/pre>\n\n\n\n
This is extremely useful.<\/p>\n\n\n\n
MySQL has some newer returning support in limited areas, but PostgreSQL\u2019s RETURNING<\/code> is widely used and very natural.<\/p>\n\n\n\n
\n\n\n\n
55. Common PostgreSQL Surprises for MySQL Users<\/h1>\n\n\n\n
Surprise 1: Users Are Roles<\/h2>\n\n\n\n
User = role with LOGIN\n<\/code><\/pre>\n\n\n\n
Surprise 2: Schema Is Not Database<\/h2>\n\n\n\n
database \u2192 schema \u2192 table\n<\/code><\/pre>\n\n\n\n
Surprise 3: Permissions Need Schema Grants<\/h2>\n\n\n\n
This is not enough:<\/p>\n\n\n\n
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;\n<\/code><\/pre>\n\n\n\n
You also often need:<\/p>\n\n\n\n
GRANT USAGE ON SCHEMA public TO appuser;\n<\/code><\/pre>\n\n\n\n
Surprise 4: Sequences Need Permissions<\/h2>\n\n\n\n
For identity\/serial inserts, you may need:<\/p>\n\n\n\n
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO appuser;\n<\/code><\/pre>\n\n\n\n
Surprise 5: Failed Transaction Requires Rollback<\/h2>\n\n\n\n
After an error:<\/p>\n\n\n\n
ROLLBACK;\n<\/code><\/pre>\n\n\n\n
Surprise 6: Case Sensitivity<\/h2>\n\n\n\n
PostgreSQL does not behave like case-insensitive MySQL collations by default.<\/p>\n\n\n\n
Use:<\/p>\n\n\n\n
ILIKE\nLOWER()\ncitext\n<\/code><\/pre>\n\n\n\n
Surprise 7: Vacuum Matters<\/h2>\n\n\n\n
Autovacuum is essential.<\/p>\n\n\n\n
Surprise 8: No USE database<\/code><\/h2>\n\n\n\n
Use:<\/p>\n\n\n\n
\\c database_name\n<\/code><\/pre>\n\n\n\n
Surprise 9: No Backticks<\/h2>\n\n\n\n
Use double quotes only when necessary.<\/p>\n\n\n\n
Better: avoid quoting by using lowercase names.<\/p>\n\n\n\n
Surprise 10: EXPLAIN ANALYZE<\/code> Runs the Query<\/h2>\n\n\n\n
Do not use carelessly with destructive SQL.<\/p>\n\n\n\n
\n\n\n\n
56. Quick Reference Cheat Sheet<\/h1>\n\n\n\n
Object Hierarchy<\/h2>\n\n\n\n
MySQL:\nserver \u2192 database\/schema \u2192 table\n\nPostgreSQL:\ncluster\/server \u2192 database \u2192 schema \u2192 table\n<\/code><\/pre>\n\n\n\n
User System<\/h2>\n\n\n\n
MySQL:\n'user'@'host'\n\nPostgreSQL:\nrole + pg_hba.conf\n<\/code><\/pre>\n\n\n\n
Auto Increment<\/h2>\n\n\n\n
MySQL:\nAUTO_INCREMENT\n\nPostgreSQL:\nGENERATED ALWAYS AS IDENTITY\nsequence\n<\/code><\/pre>\n\n\n\n
Logs<\/h2>\n\n\n\n
MySQL:\nbinary log, redo log, slow query log\n\nPostgreSQL:\nWAL, server log, slow query via log_min_duration_statement\n<\/code><\/pre>\n\n\n\n
Monitoring<\/h2>\n\n\n\n
MySQL:\nSHOW PROCESSLIST\nSHOW STATUS\nperformance_schema\n\nPostgreSQL:\npg_stat_activity\npg_stat_database\npg_stat_user_tables\npg_stat_statements\n<\/code><\/pre>\n\n\n\n
Backup<\/h2>\n\n\n\n
MySQL:\nmysqldump\n\nPostgreSQL:\npg_dump\npg_restore\npg_basebackup\n<\/code><\/pre>\n\n\n\n
Replication<\/h2>\n\n\n\n
MySQL:\nbinlog replication\n\nPostgreSQL:\nWAL streaming replication\nlogical replication\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
57. Recommended Learning Order for a MySQL Expert<\/h1>\n\n\n\n
Study PostgreSQL in this order:<\/p>\n\n\n\n
Step 1: Basic Navigation<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
\\l\n\\c\n\\dn\n\\dt\n\\d\n\\du\n<\/code><\/pre>\n\n\n\n
Step 2: Database vs Schema<\/h2>\n\n\n\n
Understand:<\/p>\n\n\n\n
database \u2192 schema \u2192 table\n<\/code><\/pre>\n\n\n\n
Step 3: Roles and Permissions<\/h2>\n\n\n\n
Understand:<\/p>\n\n\n\n
role with LOGIN = user\nrole without LOGIN = group-like role\n<\/code><\/pre>\n\n\n\n
Step 4: Data Type Differences<\/h2>\n\n\n\n
Focus on:<\/p>\n\n\n\n
BOOLEAN\nTEXT\nTIMESTAMPTZ\nNUMERIC\nJSONB\nUUID\nARRAY\n<\/code><\/pre>\n\n\n\n
Step 5: Auto Increment<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
GENERATED ALWAYS AS IDENTITY\n<\/code><\/pre>\n\n\n\n
and sequences.<\/p>\n\n\n\n
Step 6: Upsert<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
ON CONFLICT DO UPDATE\nON CONFLICT DO NOTHING\n<\/code><\/pre>\n\n\n\n
Step 7: Monitoring<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
pg_stat_activity\npg_stat_user_tables\npg_stat_statements\n<\/code><\/pre>\n\n\n\n
Step 8: Performance<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
EXPLAIN ANALYZE\nEXPLAIN (ANALYZE, BUFFERS)\n<\/code><\/pre>\n\n\n\n
Step 9: Vacuum<\/h2>\n\n\n\n
Understand:<\/p>\n\n\n\n
MVCC\ndead tuples\nautovacuum\nVACUUM ANALYZE\n<\/code><\/pre>\n\n\n\n
Step 10: Backup and Replication<\/h2>\n\n\n\n
Learn:<\/p>\n\n\n\n
pg_dump\npg_restore\npg_basebackup\nWAL\nstreaming replication\nlogical replication\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
58. Final Mental Translation<\/h1>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
Create user appuser@localhost and grant access to database shop.\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
Create a role with LOGIN.\nAllow connection through pg_hba.conf.\nGrant CONNECT on database.\nGrant USAGE on schema.\nGrant privileges on tables.\nGrant privileges on sequences if needed.\n<\/code><\/pre>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
Create database shop and tables inside it.\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
Create database shop.\nCreate schema app or use public.\nCreate tables inside that schema.\n<\/code><\/pre>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
AUTO_INCREMENT\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
IDENTITY column backed by sequence.\n<\/code><\/pre>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
Binary log replication\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
WAL streaming or logical replication.\n<\/code><\/pre>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
Performance Schema\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
pg_stat_* views and pg_stat_statements.\n<\/code><\/pre>\n\n\n\n
When you see this in MySQL:<\/p>\n\n\n\n
InnoDB purge cleans old versions.\n<\/code><\/pre>\n\n\n\n
Think PostgreSQL:<\/p>\n\n\n\n
Autovacuum cleans dead tuples.\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
Conclusion<\/h1>\n\n\n\n
PostgreSQL is not \u201cMySQL with different syntax.\u201d It has a different internal model.<\/p>\n\n\n\n
But once you map the major ideas, it becomes much easier:<\/p>\n\n\n\n
MySQL database\/schema        \u2192 PostgreSQL database + schema\nMySQL user@host              \u2192 PostgreSQL role + pg_hba.conf\nAUTO_INCREMENT               \u2192 identity\/sequence\nBinary log                   \u2192 WAL\nSHOW PROCESSLIST             \u2192 pg_stat_activity\nPerformance Schema           \u2192 pg_stat_* views\nmysqldump                    \u2192 pg_dump\nON DUPLICATE KEY UPDATE      \u2192 ON CONFLICT DO UPDATE\nInnoDB purge                 \u2192 VACUUM\/autovacuum\nStorage engines              \u2192 PostgreSQL native storage + table types\n<\/code><\/pre>\n\n\n\n
The most important PostgreSQL concepts for a MySQL user are:<\/p>\n\n\n\n
Roles\nSchemas\nSequences\nWAL\nMVCC\nAutovacuum\npg_hba.conf\nJSONB\nExtensions\nEXPLAIN ANALYZE\npg_stat_* views\n<\/code><\/pre>\n\n\n\n
Once these are clear, PostgreSQL starts to feel very logical \u2014 just stricter, more layered, and more explicit than MySQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction If you already know MySQL well, learning PostgreSQL becomes much easier when you stop trying to memorize PostgreSQL from […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2836","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nMySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School\" \/>\n<meta property=\"og:description\" content=\"Introduction If you already know MySQL well, learning PostgreSQL becomes much easier when you stop trying to memorize PostgreSQL from […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"SRE School\" \/>\n<meta property=\"article:published_time\" content=\"2026-05-06T01:18:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-05-06T01:18:24+00:00\" \/>\n<meta name=\"author\" content=\"Rajesh Kumar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Rajesh Kumar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/\",\"url\":\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/\",\"name\":\"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School\",\"isPartOf\":{\"@id\":\"https:\/\/sreschool.com\/blog\/#website\"},\"datePublished\":\"2026-05-06T01:18:23+00:00\",\"dateModified\":\"2026-05-06T01:18:24+00:00\",\"author\":{\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201\"},\"breadcrumb\":{\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/sreschool.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/sreschool.com\/blog\/#website\",\"url\":\"https:\/\/sreschool.com\/blog\/\",\"name\":\"SRESchool\",\"description\":\"Master SRE. Build Resilient Systems. Lead the Future of Reliability\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/sreschool.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201\",\"name\":\"Rajesh Kumar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g\",\"caption\":\"Rajesh Kumar\"},\"sameAs\":[\"http:\/\/sreschool.com\/blog\"],\"url\":\"https:\/\/sreschool.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School","og_description":"Introduction If you already know MySQL well, learning PostgreSQL becomes much easier when you stop trying to memorize PostgreSQL from […]","og_url":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/","og_site_name":"SRE School","article_published_time":"2026-05-06T01:18:23+00:00","article_modified_time":"2026-05-06T01:18:24+00:00","author":"Rajesh Kumar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Rajesh Kumar","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/","url":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/","name":"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL - SRE School","isPartOf":{"@id":"https:\/\/sreschool.com\/blog\/#website"},"datePublished":"2026-05-06T01:18:23+00:00","dateModified":"2026-05-06T01:18:24+00:00","author":{"@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201"},"breadcrumb":{"@id":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sreschool.com\/blog\/mysql-vs-postgresql-comprehensive-guide-for-mysql-users-learning-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sreschool.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL vs PostgreSQL: Comprehensive Guide for MySQL Users Learning PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/sreschool.com\/blog\/#website","url":"https:\/\/sreschool.com\/blog\/","name":"SRESchool","description":"Master SRE. Build Resilient Systems. Lead the Future of Reliability","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sreschool.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":"Person","@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201","name":"Rajesh Kumar","image":{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g","caption":"Rajesh Kumar"},"sameAs":["http:\/\/sreschool.com\/blog"],"url":"https:\/\/sreschool.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/2836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/comments?post=2836"}],"version-history":[{"count":1,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/2836\/revisions"}],"predecessor-version":[{"id":2837,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/2836\/revisions\/2837"}],"wp:attachment":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/media?parent=2836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/categories?post=2836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/tags?post=2836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}