PostgreSQL vs. mysql comparison

Characteristics	Mysql	PostgreSQL
Instance	Launch the instance by executing the MySQL command (MYSQLD). One instance can manage one or more databases. A single server can run multiple mysqld instances. An instance manager can monitor individual instances of mysqld.	Launch the instance by executing the Postmaster process (PG_CTL). An instance can manage one or more databases, which form a cluster. A cluster is an area on a disk that is initialized and composed of a directory at installation time, and all data is stored in this directory. Use Initdb to create the first database. Multiple instances can be started on a single machine.
Database	A database is a named collection of objects and is a separate entity from other databases in the instance. All databases in a MySQL instance share the same system catalog.	A database is a named collection of objects, and each database is a separate entity from other databases. Each database has its own system catalog, but all databases share pg_databases.
Data buffers	Sets the data buffer through the innodb_buffer_pool_size configuration parameter. This parameter is the number of bytes in the memory buffer, and InnoDB uses this buffer to cache the table's data and indexes. On a dedicated database server, this parameter can be set to a maximum of 80% of the amount of physical memory in the machine.	shared_buffers Cache. By default, 64 buffers are allocated. The default block size is 8K. You can update the buffer cache by setting the Shared_buffers parameter in the postgresql.conf file.
Database connection	The client connects to the database using the Connect or use statement, specifying the database name and specifying the user ID and password. Use roles to manage users and user groups in the database.	The client uses the Connect statement to connect to the database, specifying the database name and specifying the user ID and password. Use roles to manage users and user groups in the database.
Identity verification	MySQL manages authentication at the database level. Basic only supports password authentication.	PostgreSQL supports a wide range of authentication methods: Trust authentication, password authentication, Kerberos authentication, Ident based authentication, LDAP authentication, PAM authentication
Encryption	You can specify a password at the table level to encrypt the data. You can also use the Aes_encrypt and Aes_decrypt functions to encrypt and decrypt column data. Network encryption can be achieved through SSL connections.	You can use functions in the Pgcrypto library to encrypt/decrypt columns. Network encryption can be achieved through SSL connections.
Audit	grep can be performed on QueryLog.	You can use the Pl/pgsql trigger on a table for auditing.
Query explanation	Use the EXPLAIN command to view the interpretation plan for the query.	Use the EXPLAIN command to view the interpretation plan for the query.
Backup, recovery, and logging	InnoDB uses write-before (write-ahead) logging. Supports both online and offline full backups, as well as crashes and transactional recoveries. Third-party software is required to support hot backup.	Maintains a pre-write log in a subdirectory of the data directory. Supports both online and offline full backups as well as crashes, point-in-time, and transactional recoveries. can support hot backup.
JDBC Driver	You can download the JDBC driver from resources .	You can download the JDBC driver from resources .
Table type	Depends on the storage engine. For example, the NDB storage engine supports partitioned tables, and the memory engine supports memory tables.	Supports temporary tables, regular tables, and partition tables for scope and list types. Hash partitioned tables are not supported. Because PostgreSQL table partitioning is done through table inheritance and the rules system, you can implement more complex partitioning methods.
Index type	Depends on the storage engine. Myisam:btree,innodb:btree.	Supports B-tree, hash, R-Tree, and Gist indexes.
Constraints	Supports primary key, foreign key, unique and non-null constraints. The check constraint is parsed, but not enforced.	Supports primary key, foreign key, unique, non-null, and CHECK constraints.
Stored procedures and user-defined functions	Create PROCEDURE and create FUNCTION statements are supported. Stored procedures can be written in SQL and C + +. User-defined functions can be written in SQL, C, and C + +.	There are no separate stored procedures, all of which are implemented through functions. User-defined functions can be written in pl/pgsql (specialized procedural languages), PL/TCL, Pl/perl, Pl/python, SQL, and C.
Trigger	Pre-line triggers, post-line triggers, and statement triggers are supported, and trigger statements are written in a procedural language compound statement.	Pre-line triggers, post-line triggers, and statement triggers are supported, and the trigger process is written in C.
System configuration File	My.conf	Postgresql.conf
Database configuration	My.conf	Postgresql.conf
Client Connection File	My.conf	Pg_hba.conf
XML Support	Limited XML support.	Limited XML support.
Data access and Management Servers	OPTIMIZE TABLE -Reclaim unused space and eliminate fragmentation of data files myisamchk-analyze --Update the statistics used by the query optimizer (MyISAM storage engine) MySQL --command-line tools MySQL Administrator --Client GUI tool	Vacuum -Reclaim unused space Analyze --Update the statistics used by the query optimizer psql --command-line tools pgadmin --Client GUI Tools
concurrency control	Table-level and row-level locks are supported. The InnoDB storage engine supports read_committed, read_uncommitted, Repeatable_read, and SERIALIZABLE. Use the SET TRANSACTION isolation LEVEL statement to set the isolation levels at the transaction level.	Table-level and row-level locks are supported. The supported ANSI isolation levels are Read Committed (Default--you can see a snapshot of the database when the query starts) and serialization (similar to repeatable Read-only see results submitted before the transaction starts). Set the isolation level at the transaction level using the SET TRANSACTION statement. Use set session to set at the session level.

MySQL's disadvantage relative to PostgreSQL:

Mysql	PostgreSQL
The most important engine InnoDB is very early in the control of Oracle Corporation. Currently, the entire MySQL database is controlled by Oracle.	The BSD protocol was not monopolized by big companies.
Weak processing of complex queries, the query optimizer is not mature enough	Very powerful query optimizer that supports very complex query processing.
There is only one type of table connection: nested loop join (nested-loop), sorting-merge Join (Sort-merge join) and Hash join (hash joins) are not supported.	All support
Insufficient performance optimization tools and metrics	Provides a performance view that makes it easy to see SELECT, Delete, UPDATE, insert statistics that occur on a table and index, and also to see the cache hit rate. There is an open source Pgstatspack tool on the web.
InnoDB tables and indexes are stored in the same way. This means that the table is an indexed organization table. This generally requires that the primary key cannot be too long and that the primary key at the time of insertion is preferably ascending sequentially, otherwise there is a significant impact on performance.	There is no such problem.
Most queries use only a single index on the table, and in some cases, queries that use multiple indexes, but the query optimizer often underestimates its costs, which are often slower than table scans.	There's no such thing.
Table adds columns, basically rebuilding tables and indexes, which can take a long time.	Table adds columns, but adds table definitions to the data dictionary and does not rebuild the table
Stored procedures and triggers have limited functionality. Language functions that can be used to write stored procedures, triggers, scheduled events, and stored functions are weak	In addition to supporting pl/pgsql write stored procedures, Perl, Python, tcl type stored procedures are also supported: PL/PERL,PL/PYTHON,PL/TCL. also supports writing stored procedures in C language.
Sequence is not supported.	Support
function indexes are not supported and can only be created on specific column-based indexes. Materialized views are not supported.	Support function index, and also support partial data index, through the rule system can realize the function of materialized view.
The execution plan is not shared globally and is shared only within the connection.	Perform plan sharing
MySQL supports a small subset of SQL syntax (ANSI SQL Standard). Recursive queries, common table expressions (with statements from Oracle), or window functions (analytic functions) are not supported.	All support

User-defined types or domains (domain) are not supported	Support.
For time, date, interval, and other time types, there are no storage types below the second level	Can be accurate to the following seconds.
The authentication feature is fully built-in, does not support operating system authentication, Pam authentication, and does not support LDAP and other similar external authentication features.	Supports OS authentication, Kerberos authentication, Ident authentication, LDAP authentication, PAM authentication
Database link is not supported. There is a storage engine called federated that can pass query statements to a table on a remote server as a relay, but it is very coarse and has many vulnerabilities.	There are dblink, and there is a dbi-link thing that can be connected to both Oracle and MySQL.
Mysql cluster may differ greatly from your imagination. Open source cluster software is less. The copy (Replication) feature is asynchronous and has great limitations. For example, it is single-threaded (single-threaded), so a more capable slave recovery is difficult to keep up with the relatively slow master.	There are rich open source cluster software support.
Explain see the results of the execution plan are simple.	Explain returns rich information.
Operations such as ALTER TABLE or CREATE table are non-transactional. They commit uncommitted transactions and cannot roll back or do disaster recovery	DDL also has a business.

PostgreSQL Main Advantages:
1. PostgreSQL is completely free, and it's BSD, it's important that if you change PostgreSQL and then sell it and nobody cares, it means that the PostgreSQL database will not be controlled by other companies. The Oracle database does not have to be a commercial database and is not open. Although the MySQL database is open source, but now with the acquisition of Sun by Oracle Company, is basically controlled by Oracle, in fact, before sun was acquired, the most important InnoDB engine in MySQL is controlled by Oracle company, And in MySQL, a lot of important data are placed in the InnoDB engine, anyway, our company is like this. So if MySQL has a market range that conflicts with the Oracle database market, there is no doubt that Oracle will sacrifice MySQL.
2. Many open source software with PostgreSQL, there are many distributed cluster software, such as Pgpool, Pgcluster, Slony, Plploxy and so on, it is easy to do read and write separation, load balancing, data level split and other programs, and this is more difficult under MySQL.
3. PostgreSQL source code is very clear, easy to read more than MySQL strong, suspected MySQL source code has been confused. So many companies are basic PostgreSQL do two times to develop.
4. PostgreSQL is more powerful than MySQL in many ways, such as execution of complex SQL, stored procedures, triggers, indexes. At the same time PostgreSQL is multi-process, and MySQL is a thread, although concurrency is not high, MySQL processing speed, but when the concurrency is high, for now multicore single machine, MySQL overall processing performance than PostgreSQL, The reason is that MySQL threads are unable to take full advantage of the CPU capacity.
At present only think of these, later thought to add, welcome everyone to shoot bricks.


The difference between PostgreSQL and the multi-version implementation of Oracle or InnoDB

The biggest difference between PostgreSQL and Oracle or INNODB's multi-version implementations is whether the latest and historical versions are separated from storage, PostgreSQL is not, and Oracle and InnoDB, and InnoDB only separates the data, and the index itself is not separate.
The main advantages of PostgreSQL are:
1. PostgreSQL does not have a rollback segment, and Oracle and INNODB have rollback segments, and both Oracle and INNODB have rollback segments. For Oracle and InnoDB, the rollback segment is very important, and the rollback segment is corrupted, causing data loss or even a serious problem that the database cannot start. Because PostgreSQL does not have a rollback segment, the old data is recorded in the original file, so when the database is abnormally crash, it will not recover as complex as Oracle and InnoDB database. Redo and undo are required for the synchronization of Oracle and InnoDB recovery. As a result, the PostgreSQL database is less likely to get out of the database after an abnormal crash than Oracle and MySQL.
2. Because the old data is recorded directly in the data file, not the rollback segment, ora-01555 errors are not reported as often as Oracle.
3. Rollback can be completed very quickly, because rollback does not delete data, while Oracle and InnoDB, rollback is very complex, when the transaction rollback must clean up the changes made by the firm, the inserted records to be deleted, updated records to be updated back (see Row_undo function), The process of rolling back also generates a large number of redo logs again.
4. The Wal logs are simpler than Oracle and InnoDB, and for Oracle you need to record not only the changes to the data files, but also the changes to the rollback segments.
The main disadvantage of the multiple versions of PostgreSQL is:
1, the latest version and the historical version does not separate storage, resulting in the cleanup of older versions need to do more scanning, the cost is relatively large, but the general database has a peak, if we properly arrange vacuum, this is not a big problem, and in the PostgreSQL9.0 vacuum is further strengthened.
2, because there is no version information in the index, the coverage index scan cannot be implemented, that is, the query scans the index only, returns the desired property directly from the index, and requires access to the table. and Oracle and InnoDB can;


comparison of process mode and threading mode
PostgreSQL and Oracle are process modes, and MySQL is thread mode.
The process pattern is relatively high for multi-CPU utilization.
Process mode shared data requires shared memory, while thread-mode data itself is shared within the process space, and different thread accesses need only control the synchronization between the threads.
The thread pattern is less resource-intensive.
So MySQL can support a lot more connections than Oracle.
For PostgreSQL, there is also this problem if you do not use the connection pooling software, but PostgreSQL has excellent connection pooling software, such as Pgbouncer and Pgpool, so a connection pool can also support a lot of connections.

Comparison of heap table and Index organization table

Oracle Support heap table, also support index organization table
PostgreSQL supports only heap tables and does not support indexed organization tables
InnoDB only supports indexed organization tables
Advantages of index Organization tables:
The data in the table is organized by index, the data is orderly, if the data are accessed by the primary key, the access data is faster. In the case of heap tables, when data is accessed by primary key, the physical location of the data must first be found by the primary key index.
Disadvantages of index Organization tables:
When you add another index to the Index organization table, the data location of the other index records is no longer the physical location, but the primary key value, so the value of the primary key cannot be too large for the index organization table, otherwise it takes up more space.
For an indexed organization table, if you insert data in the middle each time, it can cause the index to split, and index splitting can greatly reduce the performance of the insert. So for the use of InnoDB, it is generally best to make the primary key a meaningless sequence, so that the insertion happens at the end every time to avoid this problem.
Because the Index organization table is an index tree, generally it accesses data blocks that must be accessed in relation to the data blocks, rather than by physical blocks, so it is much slower than the heap table for full table scans, which may not be noticeable in OLTP, but may be an issue in the application of the Data Warehouse.




Featured Features in PostgreSQL9.0:
the hot standby feature in PostgreSQL
That is, standby can also provide read-only services when applying log synchronization, which is useful for reading and writing separations. This function is a function of oracle11g.

features of PostgreSQL asynchronous commit (asynchronous commit)：
This feature is also the function of Oracle to ORACLE11G R2. Because in many scenarios, this is especially true in scenarios where a small amount of data is allowed to be lost when the outage is in use. Setting Synchronous_commit to False in PostgreSQL9.0 opens this function. It is important to note that, although set up in order to commit asynchronously, PostgreSQL only loses a small amount of data when the host is down, asynchronous commit does not cause data corruption and the database does not get out of the situation. MySQL has not heard of this feature.

featured features of the index in PostgreSQL：

PostgreSQL can have a partial index, that is, only some of the data in the table is indexed, CREATE index can take the where condition. While the indexes in PostgreSQL can be scanned backwards, there is no need to create a special descending index in PostgreSQL.

PostgreSQL vs. mysql comparison