Home/MySQL/MySQL vs PostgreSQL: ?

MySQL vs PostgreSQL: ?

MySQL, PostgreSQL,

Comparisons

MySQLPostgreSQL
,, New ,
() , ,
() InnoDB, OLTP MVCC, VACUUM
, JSON (5.7+) :, JSONB,,
SQL , MySQL Syntax SQL, CTE, and more
(InnoDB, MyISAM) ,,
Copy Copy, Group Replication, InnoDB Cluster Copy, Copy, Patroni/Citus and more
, and ,
GPL(), PostgreSQL License( MIT),
Web, CMS, OLTP , GIS, SQL

MySQL

  • Web and (PHP/Java/Node.js )
  • MySQL,
  • OLTP
  • Copy and
  • ,

PostgreSQL

  • SQL (, CTE, )
  • (JSON, )
  • and (, )
  • (, )
  • OLTP + OLAP

Examples

mergeExample

-- MySQL : SELECT o.order_no, o.total_amount, u.username
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1
ORDER BY o.created_at DESC
LIMIT 20;

rebaseExample

-- PostgreSQL : + CTE
WITH monthly_sales AS ( SELECT user_id, DATE_TRUNC('month', created_at) AS month, SUM(total_amount) AS total FROM orders GROUP BY user_id, DATE_TRUNC('month', created_at)
)
SELECT user_id, month, total, RANK() OVER (PARTITION BY month ORDER BY total DESC) AS rank
FROM monthly_sales;

Common Errors

"PostgreSQL " PostgreSQL, and
MySQL, PostgreSQL
( AWS RDS )
SQL Syntax( MySQL vs PostgreSQL )

Web MySQL,,., SQL, PostgreSQL., and.