Comparison between MySQL and PostgreSQL

Characteristics	Mysql	PostgreSQL
Instance	Start 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.	Start 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 during installation and consists of a directory, all of which are 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 the other databases in the instance. All databases in a MySQL instance share the same system catalog.	A database is a collection of named objects, each of which is a separate entity from other databases. Each database has its own system catalog, but all databases share pg_databases.
Data buffers	Set the data buffer with the Innodb_buffer_pool_size configuration parameter. This parameter is the number of bytes in the memory buffer that the InnoDB uses to cache the table's data and index. 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. 64 buffers are allocated by default. 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 uses the Connect or use 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.	The client connects to the database using the Connect statement, specifying the database name and specifying the user ID and password. Use roles to manage users and user groups in the database.
Authentication	MySQL manages authentication at the database level. Basically only password authentication is supported.	PostgreSQL support rich authentication methods: Trust authentication, password authentication, Kerberos authentication, based on Ident 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. You can implement network encryption over an SSL connection.	You can use functions in the Pgcrypto library to encrypt/decrypt a column. You can implement network encryption over an SSL connection.
Audit	You can perform grep on QueryLog.	You can use PL/PGSQL triggers 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 use pre-write (Write-ahead) logging. Supports both online and offline full backups, as well as crash and transaction recovery. Third party software is required to support hot backup.	Maintains a write-ahead log in a subdirectory of the data directory. Supports both online and offline full backups, as well as crashes, point-in-time, and transaction recovery. can support hot backups.
JDBC Driver	You can download the JDBC driver from resources.	You can download the JDBC driver from resources.
Table Types	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 partitioned tables of range and list types. Hash partition table is not supported. Because PostgreSQL table partitions are done through table inheritance and rule systems, more complex partitioning methods can be implemented.
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, which are implemented through functions. User-defined functions can be written in Pl/pgsql (dedicated process language), PL/TCL, Pl/perl, Pl/python, SQL, and C.
Trigger	Supports row-front triggers, row-behind triggers, and statement triggers, and trigger statements are written in process-language compound statements.	Supports row-front triggers, row-behind triggers, and statement triggers, which are 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 Server	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 Tool
concurrency control	Supports table-level and row-level locks. The InnoDB storage engine supports read_committed, read_uncommitted, Repeatable_read, and SERIALIZABLE. Set the isolation level at the transaction level using the SET TRANSACTION isolation levels statement.	Supports table-level and row-level locks. The supported ANSI isolation level is read Committed (default--a snapshot of the database when the query starts) and serialization (similar to repeatable read--only to see results submitted before the transaction starts). Set the isolation level at the transaction level using the SET TRANSACTION statement. Set at the session level using set sessions.