PostgreSQL: An Overview

What is PostgreSQL?

PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management system (DBMS) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any corporation or other private entity and the source code is available free of charge. PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

What Makes PostgreSQL Stand Out?

• 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.
• PostgreSQL is a general-purpose object-relational database management system. It allows us to add custom functions developed using different programming languages such as C/C++, Java, etc.
• PostgreSQL is designed to be extensible. In PostgreSQL, one can define their own data types, index types, functional languages, etc.

Key Features of PostgreSQL

PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).

PostgreSQL supports a large part of the SQL standard and offers many modern features including the following −

• Complex SQL queries
• SQL Sub-selects
• Foreign keys
• Trigger
• Views
• Transactions
• Multiversion concurrency control (MVCC)
• Streaming Replication (as of 9.0)
• Hot Standby (as of 9.0)

You can check official documentation of PostgreSQL to understand the above-mentioned features. PostgreSQL can be extended by the user in many ways. For example by adding new −

• Data types
• Functions
• Operators
• Aggregate functions
• Index methods

Procedural Languages Support

PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. These procedural languages are - PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java, etc., are also supported.

DDL - data definition language

Data definition language (DDL) is used to define data structures, and includes the following commands:

• Data type declaration
• CREATE - creates a new database, table, index or procedure.
• ALTER - modifies an existing database object.
• DROP - deletes an existing database, table, index or procedure.
• TRUNCATE TABLE - empties a table for reuse.

DML - data manipulation language

On the other hand, data manipulation language is used to work with tables and their contents as in:

• INSERT - inserts new rows in a table.
• UPDATE - updates existing rows in the table.
• DELETE - deletes rows from a table.
• SELECT - the main method for querying data from the database.

Advantages of PostgreSQL

Below are the main advantages/benefits of PostgreSQL:

• PostgreSQL can run dynamic websites and web apps as a LAMP stack option.
• PostgreSQL’s write-ahead logging makes it a highly fault-tolerant database.
• PostgreSQL source code is freely available under an open source license. This allows you the freedom to use, modify, and implement it as per your business needs.
• PostgreSQL supports geographic objects so you can use it for location-based services and geographic information systems.
• PostgreSQL supports geographic objects so it can be used as a geospatial data store for location-based services and geographic information systems.
• To learn Postgres, you don’t need much training as its easy to use.
• Low maintenance and administration for both embedded and enterprise use of PostgreSQL.

Disadvantages of PostgreSQL

Below are the disadvantages/limitations of PostgreSQL:

• Postgres is not owned by one organization. So, it has had trouble getting its name out there despite being fully featured and comparable to other DBMS systems
• Changes made for speed improvement requires more work than MySQL as PostgreSQL focuses on compatibility
• Many open source apps support MySQL, but may not support PostgreSQL
• On performance metrics, it is slower than MySQL.

Wrapping up

In this article, we’ve looked at PostgreSQL: what it is, what it does, and how it does it. We’ve examined Data Definition Language (DDL), Data Manipulation Language (DML) and the many tools and compelling features that come with this open-source database. Finally, after looking at use cases, users and PostgreSQL’s industry position, we’ve considered the advantages of a hosted, managed solution, and looked at a few ways to get started.