왜 PostgreSQL 을 선택 했나? ( PostgreSQL vs MySQL ) [번역]

PostgreSQL vs MySQL

[요약/번역] https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/

* 참고로 우버는 PostgreSQL에서 MySQL로 갈아탔습니다. -> 참고  )https://mysqlko.wordpress.com/2016/08/05/mysql-vs-postgresql-uber/    이런것은 해당 버전에 따라서 결과는 달라지니 최근 정보로 잘 살펴봐야겠습니다.

PostgreSQL 와 MySQL에는 근본적인 차이점이 분명히 있습니다.  두 시스템 간의 장,단점을 잘 파악해서 자신의 시스템에 사용 했으면 하는데 도움을 드리고자 유사성과 차이점에 대한 요약을 준비했습니다.

• Open Source
• Acid Compliance
• SQL Compliance
• Replication
• Performance
• Security
• Hosting
• Community Support
• Concurrency Support
• NoSQL/JSON Support
• Materialized Views & Temporary Tables
• GeoSpatial Data Support
• Programming Languages Support
• Extensible Type System
• Comparison Summary

Open Source 

PostgreSQL	MySQL
PostgreSQL 은 PostgreSQL 글로벌 개발그룹에 의해 개발되고 있으며, 여러 회사와 개인적 기여자들로 이루어진 다양한 그룹이 존재합니다. 자유 오픈소스 소프트웨어이며 PostgreSQL은 PostgreSQL 라이선스로 릴리즈 되었습니다. BSD나 MIT 와 비슷한 매우 자유로운 라이선스입니다.	The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as a variety of proprietary agreements.   지금은 오라클 소유이며, 여러 사용료를 지불해야하는 특정 에디션도 제공합니다.

ACID Compliance 

PostgreSQL	MySQL
PostgreSQL은 처음부터 ACID 를 지향했으며 모든 요구사항을 만족합니다.	MySQL는 InnoDB 와 NDB Cluster Storage engines 를 사용 했을 때만 준수하고 있습니다.

SQL Compliance

PostgreSQL

MySQL

PostgreSQL은 SQL 을 폭넓게 준수하고 있습니다.The level of conformance for each feature is clearly laid out in Appendix D of the manual, and any deviations are clearly documented in the “Reference” section of the PostgreSQL manual. Extracted from documentation: PostgreSQL supports most of the major features of SQL:2011. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2011.

MySQL 는 일부만 준수합니다. (e.g does not support CHECK constraints). Extracted from documentation: One of our main goals with the product is to continue to work toward compliance with the SQL standard, but without sacrificing speed or reliability. We are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL Server for a large segment of our user base.

Replication

PostgreSQL

MySQL

PostgreSQL supports Master-Standby replication and introduced significant enhancements producing extremely fast WAL processing that results in almost real-time replication and hot standby capabilities for standby servers.   Replications offered by PostgreSQL: Single master to one standby Single master to multiple standbys Hot Standby/Streaming Replication Bi-Directional replication Logical log streaming replication Cascading replication

MySQL supports Master-Standby replication.   Replications offered by MySQL: Single master to one standby Single master to multiple standbys Single master to one standby to one or more standbys Circular replication (A to B to C and back to A) Master to master

Performance

PostgreSQL

MySQL

PostgreSQL is widely used in large systems where read and write speeds are crucial and data needs to validated. In addition, it supports a variety of performance optimizations that are available only in commercial solutions such as Geospatial data support, concurrency without read locks, and so on (e.g. Oracle, SQL Server). Overall, PostgreSQL performance is utilized best in systems requiring execution of complex queries. PostgreSQL performs well in OLTP/OLAP systems when read/write speeds are required and extensive data analysis is needed. PostgreSQL also works well with Business Intelligence applications but is better suited for Data Warehousing and data analysis applications that require fast read/write speeds.

MySQL is a widely chosen for web based projects that need a database simply for straightforward data transactions. It is common, though, for MySQL to underperform when strained by a heavy loads or when attempting to complete complex queries.   MySQL performs well in OLAP/OLTP systems when only read speeds are required.   MySQL + InnoDB provides very good read/write speeds for OLTP scenarios. Overall, MySQL performs well with high concurrency scenarios.   MySQL is reliable and works well with Business Intelligence applications, as business intelligence applications are typically read-heavy.

Security

PostgreSQL	MySQL
PostgreSQL has ROLES and inherited roles to set and maintain permissions. PostgreSQL has native SSL support for connections to encrypt client/server communications. It also has Row Level Security.   In addition to this, PostgreSQL comes with a built-in enhancement called SE-PostgreSQL which provides additional access controls based on SELinux security policy. More details here.	MySQL implements security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers.

Cloud Hosting

PostgreSQL	MySQL
Supported by all major cloud service providers, including Amazon, Google, & Microsoft.	Supported by all major cloud service providers, including Amazon, Google, & Microsoft.

Community Support

PostgreSQL	MySQL
PostgreSQL has a very strong and active community that constantly improves existing features while its innovative committers strive to ensure it remains the most advanced database with new cutting-edge features and security.	MySQL has a large community of contributors who, particularly following the acquisition by Oracle, focus mainly on maintaining existing features with some new features emerging occasionally.

Concurrency Support

PostgreSQL	MySQL
PostgreSQL tackles concurrency efficiently with its MVCC implementation, which achieves very high levels of concurrency.	MySQL only has MVCC support in InnoDB.

NoSQL Features/JSON Support

PostgreSQL	MySQL
PostgreSQL supports JSON and other NoSQL features like native XML support and key-value pairs with HSTORE. It also supports indexing JSON data for faster access. (참고로 TimescaleDB 라는PostgreSQL 기반의 시계열 NoSQL DB도 있다.)	MySQL has JSON data type support but no other NoSQL feature. It does not support indexing for JSON.

Materialized Views/Temporary Tables

PostgreSQL	MySQL
Supports materialized views and temporary tables.	Supports temporary tables but does not support materialized views.

Geospatial Data Support

PostgreSQL	MySQL
PostgreSQL supports Geospatial data via the PostGIS extension. There are dedicated types and functions for geospatial data, available directly at database level, making analysis and coding easier for developers.	Geospatial data support is built in.

Programming Languages Support

PostgreSQL	MySQL
PostgreSQL supports a wide variety of programing languages including: C/C++, Java, JavaScript, .Net, R, Perl, Python, Ruby, Tcl and others; it’s even possible to run user-supplied code in separate processes (i.e. running as background workers).	Some support for server side programming in a single language which is not extensible.

Extensible Type System

PostgreSQL	MySQL
PostgreSQL has several features dedicated to extensibility. It is possible to add new types, new functions, new index types, etc.	No support for extensibility.

Comparison Summary

Feature	PostgreSQL	MySQL
Open Source	Completely Open source	Open source, but owned by Oracle and offers commercial versions
ACID Compliance	Complete ACID Compliance	Some versions are compliant
SQL Compliance	Almost fully compliant	Some versions are compliant
Concurrency Support	MVCC implementation supports multiple requests without read locks	Support in some versions.
Security	Secure from ground up with SSL support	SSL support in some versions
NoSQL/JSON Support	Multiple supported features	JSON data support only
Access Methods	Supports all standards	Supports all standards
Replication	Multiple replication technologies available:   Single master to one standby Single master to multiple standbys Hot Standby/Streaming Replication Bi-Directional replication Logical log streaming replication	Standard master-standby replication:   Single master to one standby Single master to multiple standbys Single master to one standby to one or more standbys Circular replication (A to B to C and back to A) Master to master
Materialized Views	Supported	Not supported
Temporary Tables	Supported	Supported
GeoSpatial Data	Supported	Supported
Programming Languages	Supported	Not supported
Extensible Type System	Supported	Not supported