PostgreSQL

1. Overview

PostgreSQL is a general-purpose and object-relational database management system, the most advanced open source database system. PostgreSQL was developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.

PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was then also designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows.

PostgreSQL is free and open-source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems.

2. PostgreSQL features highlights

PostgreSQL has many advanced features that other enterprise database management systems offer, such as:

• User-defined types
• Table inheritance
• Sophisticated locking mechanism
• Foreign key referential integrity
• Views, rules, subquery
• Nested transactions (savepoints)
• Multi-version concurrency control (MVCC)
• Asynchronous replication

The recent versions of PostgreSQL support the following features:

• Native Microsoft Windows Server version
• Tablespaces
• Point-in-time recovery

And more new features are added in each new release.

3. Foreign key referential integrity

3.1 Parent Table (Referenced Table)

The table to which the foreign key references is called referenced table or parent table

3.2 Child Table (Reference Table)

The table that contains the foreign key is called referencing table or child table. 

3.3 Referential Integrity between child and parent tables

In PostgreSQL, you define a foreign key through a foreign key constraint. A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table. We say that a foreign key constraint maintains referential integrity between child and parent tables.

4. Stand out features

4.1 Multi-version Concurrency Control (MVCC) 

PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle. The MVCC feature is known as snapshot isolation in Oracle.

4.2 Add custom functions

PostgreSQL is a general-purpose object-relational database management system. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.

4.3 Define your own data types, index types, functional languages, etc

PostgreSQL is designed to be extensible. In PostgreSQL, you can define your own data types, index types, functional languages, etc. If you don’t like any part of the system, you can always develop a custom plugin to enhance it to meet your requirements e.g., adding a new optimizer.

4.4 Community

If you need any support, an active community is available to help. You can always find the answers from the PostgreSQL’s community for the issues that you may have when working with PostgreSQL. Many companies offer commercial support services in case you need one.

5. Usages

• Apple
• Fujitsu
• Red Hat
• Cisco
• Juniper Network

3. Reference

https://severalnines.com/database-blog/top-ten-reasons-migrate-oracle-postgresql

https://www.postgresqltutorial.com/postgresql-foreign-key/

https://en.wikipedia.org/wiki/PostgreSQL

https://www.guru99.com/introduction-postgresql.html

https://www.postgresqltutorial.com/what-is-postgresql/

https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres

https://www.xplenty.com/blog/postgresql-vs-mysql-which-one-is-better-for-your-use-case/

https://www.xplenty.com/blog/postgresql-vs-mysql-which-one-is-better-for-your-use-case/