What is PostgreSQL? Introduction, Features, Installations, Advantages & Disadvantages

Introduction to PostgreSQL

PostgreSQL is a huge powerful open source object-relational database system. As it is open source that means it is freely available. PostgreSQL extends the SQL language combined with many features to store and scale the most complicated data workloads. PostgreSQL pronounced as ‘Post-gres-Q-L’ and commonly called as Postgres.
It was developed by a team of volunteer developers known as PGDG (PostgreSQL Global Development Group). PostgreSQL has written in c language. PostgreSQL is not controlled by any company. This is a community project.

There is no maximum limit on database sizes in PostgreSQL, and an individual table can extend up to 32 terabytes. The important thing to know about PostgreSQL is that it is an object-relational database. Like other relational databases, PostgreSQL stores data in tables and the language like SQL used to query them. However, the difference is that PostgreSQL introduces some concepts from object-oriented programmings such as user-defined objects and table inheritance that we do not find in other relational databases.

PostgreSQL supports real-time challenges like exotic data types (JSON Objects, geometric objects), complex data structures, the process of billions of events per day, storing petabyte worth of data, ACID compliance and so on.
PostgreSQL makes easy to handle some of the most complex and high volume data operations in the world. Because of this reason, PostgreSQL trusted by Yahoo!, Instagram, Facebook, Skype, and Apple as well as government agencies, telecom services and the number of financial institutions.
PostgreSQL works on all major operating system such as Linux, Windows, Solaris, Mac OS X, OpenBSD, FreeBSD, IRIX.

History of PostgreSQL

In 1986 DARPA, NSF, ARO, and ESL sponsored Berkeley Postgres Project.
First demo version of the project released in 1987.
In 1989 version 1 released for some external users.
Version 2 of the project released in 1990.
In 1991 version 3 of the project released and supports multiple storage managers, improved query executor, also rewritten the rule system.
After 1993, the user community started increasing around the project
In 1994, SQL language interpreter added to the Postgres and then it released under name Postgres95.

Key features of PostgreSQL

10 Key Features of PostgreSQL | TutorialandExample

  1. Procedural languages: PostgreSQL supports procedural languages. These procedural languages allow a user to write their code and database server to execute that code. PL/Python, PL/pgSQL, PL/Tcl, PL/Perl are procedure languages supported by PostgreSQL.
    PostgreSQL also supports non-standard procedural languages such as PL/Ruby, PL/Java, PL/PHP but all of them need to be install some additional packages.
  2. Indexes: PostgreSQL allows the user to create their own customized indexes. It supports hash, B+ tree, generalized search tree, etc.
  3. Triggers: Triggers initializes by DML commands like insert, update, and those are fully supported by PostgreSQL.
  4. Views: Views are virtual tables and produces based on the result set. Views are supported by PostgreSQL.
  5. MVCC: PostgreSQL allows many users (Readers, Writers) to work on a database at the same time rather enforce Read-Write Lock policy. It happens because of Multiversion Concurrency Control (MVCC). MVCC uses “snapshots” to represent the state of the database at a specific moment of time. User not able to see the other user transactions until they get committed and achieve the concurrency. In the case of reading operations, the reader sees an only current committed state of a database. MVCC puts safeguards in some places to ensure that multiple writers trying to modify the same data or row and that do not run into problems.
  6. Rules: PostgreSQL supports rules. Rules allow the hierarchy of incoming queries to be re-written.
  7. Data Types: PostgreSQL supports a wide range of data types such as text, char, varchar, Boolean, date/time, binary, variable length array and many more. PostgreSQL supports a custom data type that means it allows the user to create new data type. For example, a user can combine a column labeled ‘name’ of string type and another column labeled ‘salary’ of numeric type and result to this new composite type ‘employee’.
  8. User-defined objects: PostgreSQL supports the creation of new objects present inside database like table, indexes, views, domains, functions etc.
  9. Inheritance: In PostgreSQL, the table can inherit characteristics from the parent table and set to it. As in table inheritance, child table inherits properties of parent and add their own properties, in this way user able to create the much more complex data structure in a purely relational database. This can be a huge advantage if your application produces data, which do not fit into a relational database that time PostgreSQL help you out.
  10. ACID Property: PostgreSQL is ACID (Atomicity, Consistency, Isolation, and Durability) compliant by default.

Some tools like PSQL, phpPgAdmin, pgFouine used as a front-end tool for PostgreSQL.

Advantages of PostgreSQL

  1.  An open source database system: Source code is freely available. There is no concepts of ‘demo version’ or ‘dual licensing’. You will get entire thing free and stick to the standard version of PostgreSQL.
  2. Immunity to over-deployment: There is no licensing cost for the software and no one can take any legal action against you for breaking the licensing agreement.
  3. Designed for high volume environment: PostgreSQL uses multiple row data storage strategy using MVCC to make this database quick responsive in a high volume environment.
  4. Extensible: PostgreSQL source code is freely available, whereas any users want to customize or extend the PostgreSQL, that user is able to do it in an easy way, with no attached cost. PGDG and enthusiasts around the world actively extend the PostgreSQL on daily basis. A user has freedom for using, modifying and for implementing as per the business needs.
  5. Diversified Extension Functions: It supports different kinds of technique like DBlink, Key-Value storage and PostGIS for geographic data storage.
  6. Diverse indexing technique: PostgreSQL provides different kinds of indexing technique like B+ tree index technique, generalized search tree, Generalized inverted index.
  7. Diverse kinds of replication: Cascading, streaming replication and slony-I are a variety of replication methods available in PostgreSQL.
  8. Write-ahead logging: Write-ahead logging makes PostgreSQL highly fault-tolerant database.
  9. Supports Geographic objects: PostgreSQL supports geographic objects so that user can use geographic information system and a geospatial data store for location-based services.
  10. PostgreSQL can run dynamic websites and web applications.

Disadvantages of PostgreSQL

  1. MySQL is considerably faster than PostgreSQL. PostgreSQL does not perform fastest read operations.
  2. PostgreSQL partially support to the ANSI SQL 92′ and ANSI SQL 99′ standard.
  3. Due to its less popularity and lower performance than MySQL, it is hard to come by service providers or hosts to manage PostgreSQL instances.
  4. PostgreSQL over-kill the simple set-up environments.

Applications of PostgreSQL

  1. Financial Industry: PostgreSQL is a compliant database for financial industries. As PostgreSQL is out-of-box ACID compliant, it becomes an ideal choice for Online Transaction Processing (OLTP).
  2. Government GIS data: PostgreSQL has a technique called PostgreGIS, and that used to handle GIS (geometric information system) data. PostgreGIS has thousands of functions to process geometric data in different formats. Open source community offers the easiest way to handle Geo-data with the help of GeoServer or QGIS.
  3. Manufacturers: To speed up the overall business process, manufacturers use PostgreSQL, open source database. It optimizes the supply chain performance also. Result to this, and companies reduce their operation cost of business.
  4. Web Technology and no SQL: Scalability becomes a big issue for many websites, PostgreSQL provides the best solution for it. PostgreSQL works with all modern frameworks like Django, Node.js etc.
  5. Scientific data: Researchers and data analyst generate terabytes of data while working. Therefore, it becomes vital to find an efficient way to handle such massive data. PostgreSQL has analytical capabilities with a robust SQL engine. PostgreSQL can be combined with software like R, result to this cleaning, analyzing becomes very easy.
  6. Telecom Services: Main key-features required for telecom services are Fault-tolerance, Real-time transactions, concurrency control, and TINA, i.e. Telecommunications Information Networking Architecture are object-oriented. As PostgreSQL is an object-oriented database system, it achieves concurrency control using MVCC, and it is ACID compliant. Hence, PostgreSQL becomes the ideal choice for telecom services.
  7. Social sites: PostgreSQL handles complex data structures and a large volume of data such as billion events per day. Thousands of requests per second. There is no maximum limit on database size. Because of all this PostgreSQL trusted by YAHOO!, Facebook, Instagram, Skype.

10 Difference Between PostgreSQL Vs MySQL

PostgreSQL MySQL
Basic PostgreSQL is object-relational database system MySQL is a relational database system
Product This is the product of PGDG PostgreSQL Global Development Group This is the product of Oracle Corporation
Interface Graphical User Interface GUI provided by PgAdmin Tool Graphical User Interface GUI provided by PhpMyAdmin tool
Operating System Supported by windows, MAC OS X, Linux, BSD but not UNIX, z/OS, Symbian, AmigaOS Supported by windows, MAC OS X, Linux, BSD, UNIX, z/OS, Symbian, AmigaOS
Extensible PostgreSQL is highly extensible MySQL is not extensible
Backup It provides online backup Mysqldump and XtraBackup provides backup
Materialized View It provides a temporary table and materialized view It provides a temporary table but not materialized view
Custom Objects It supports custom objects It does not support custom objects
Custom indexes Allow the user to create customized indexes and use them Does not allow the user to create customized indexes
Popularity Less popular than MySQL More popular than PostgreSQL

 

How to install PostgreSQL in Windows Step by Step

postgresql install on window pc

Download and Installation Set-Up

To use PostgreSQL in your machine, you need to install:
1. Postgres database server
2. A graphical tool to administer and manage the database PgAdmin is the most popular GUI tool for Postgres.
Note: If you download and install the bundled installer that will take care of configuration settings.
You could download and install the individual database server and graphical tool but coupling the setting between these components will be a challenge.

Following steps indicate how to download and install PostgreSQL in windows machine:

Step 1 : 

Download PostgreSQL | TutorialandExample

 

 

 

 

 

 

 

 

Step 2:

  • Click on the download the installer

PostgreSQL Installation | TutorialandExample

Step 3:

  • Check the latest version of PostgreSQL for your operating system and click on a download

PostgreSQL Download and Installation | TutorialandExample

When you click on download, the download begins.

PostgreSQL Downloading | TutorialandExample

Step 4:

  • Double click on the downloaded .exe file that will open the setup wizard, click on Next to proceed with an installation

install PostgreSQL Setup | TutorialandExample

Step 5:

  • You can change the installation directory here if you want to
  • Click on Next

PostgreSQL Installation Directory | TutorialandExample

Step 6:

  • Select the components you want to install
  • Click on Next

PostgreSQL Component want to Installation | TutorialandExample

Step 7:

  • leave the default directory or change the directory in which you want to store data
  • Click on Next

PostgreSQL Data Directory | TutorialandExample

Step 8:

  • Enter the superuser (Postgres) password
  • Click on Next

PostgreSQL setting Password | TutorialandExample

Step 9:

  • Click on Next, don’t change the default port number

PostgreSQL Port | TutorialandExample

Step 10:

  • Leave the default option as it is
  • Click on Next

PostgreSQL advance options | TutorialandExample

Step 11:

  • This is the pre-installation summary
  • Click on Next

PostgreSQL Pre Installation Summery | tutorialandExample

Step 12:

  • The set-up is now ready to install
  • Click on Next

PostgreSQL Ready to Installation | TutorialandExample

Wait until set up installs entirely on your machine….

PostgreSQL wait Installation | TutorialandExample

Step 13:

You will see the stack builder stack after complete installation

  • Uncheck the stack builder option that will ask you for additional installation
  • Click on Finish

PostgreSQL Completing | TutorialandExample

Step 14:

To launch PostgreSQL, go to the START menu

  • Select PgAdmin 4

PostgreSQL Start Menu | TutorialandExample

Step 15

  • You will see PgAdmin homepage

PostgreSQL Pgadmin Home Page | TutorialandExample

Step 16:

  • Select Servers -> PostgreSQL (11)
  • Right click on it and select connect Server

PostgreSQL Connect Server | TutorialandExample

Step 17:

  • Enter the password to connect with the server
  • Click on OK

PostgreSQL Connect Server Password | tutorialandExample

Now you can interact with Postgres database in two ways

  1. Using PgAdmin, i.e. GUI tool
  2. SQL shell (psql) through command prompt

Step 18:

  • Open START menu
  • Click on SQL shell

PostgreSQL start | TutorialandExample

Step 19:

  • Press Enter Key until prompt for the password
  • Enter the password

PostgreSQL aa | TutorialandExample

Example:
Let us see, an example of create table, inserting data into the table and display the same.
Create table Query
Syntax:
Create table table_name (column_name1 data_type, column_name1 data_type, ……);
Example:
Create table EMP (emp_id int, emp_namevarchar(20), emp_salary int, dept varchar(10));
Insert into Query
Syntax:
Insert into table_name values (‘value1’, ‘value2’, …… );
Example:
Insert into EMP values (101, ‘Rohit Sharma’, 25000, ‘IT’);
Display table content
Syntax:
Select * from table_name;
Example:
Select * from EMP;

PostgreSQL Example | TutorialandExample

Summary

  • It is open-source object-oriented DBMS
  • The source code is freely available
  • It is a community project
  • It is written in C language
  • There is no maximum limit on database sizes

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