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
- 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.
- Indexes: PostgreSQL allows the user to create their own customized indexes. It supports hash, B+ tree, generalized search tree, etc.
- Triggers: Triggers initializes by DML commands like insert, update, and those are fully supported by PostgreSQL.
- Views: Views are virtual tables and produces based on the result set. Views are supported by PostgreSQL.
- 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.
- Rules: PostgreSQL supports rules. Rules allow the hierarchy of incoming queries to be re-written.
- 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’.
- User-defined objects: PostgreSQL supports the creation of new objects present inside database like table, indexes, views, domains, functions etc.
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- Diversified Extension Functions: It supports different kinds of technique like DBlink, Key-Value storage and PostGIS for geographic data storage.
- Diverse indexing technique: PostgreSQL provides different kinds of indexing technique like B+ tree index technique, generalized search tree, Generalized inverted index.
- Diverse kinds of replication: Cascading, streaming replication and slony-I are a variety of replication methods available in PostgreSQL.
- Write-ahead logging: Write-ahead logging makes PostgreSQL highly fault-tolerant database.
- Supports Geographic objects: PostgreSQL supports geographic objects so that user can use geographic information system and a geospatial data store for location-based services.
- PostgreSQL can run dynamic websites and web applications.
Disadvantages of PostgreSQL
- MySQL is considerably faster than PostgreSQL. PostgreSQL does not perform fastest read operations.
- PostgreSQL partially support to the ANSI SQL 92′ and ANSI SQL 99′ standard.
- Due to its less popularity and lower performance than MySQL, it is hard to come by service providers or hosts to manage PostgreSQL instances.
- PostgreSQL over-kill the simple set-up environments.
Applications of PostgreSQL
- 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).
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
|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
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 :
- Visit the link https://www.postgresql.org/download to download the latest release of PostgreSQL.
- Select the Windows option.
- Click on the download the installer
- Check the latest version of PostgreSQL for your operating system and click on a download
When you click on download, the download begins.
- Double click on the downloaded .exe file that will open the setup wizard, click on Next to proceed with an installation
- You can change the installation directory here if you want to
- Click on Next
- Select the components you want to install
- Click on Next
- leave the default directory or change the directory in which you want to store data
- Click on Next
- Enter the superuser (Postgres) password
- Click on Next
- Click on Next, don’t change the default port number
- Leave the default option as it is
- Click on Next
- This is the pre-installation summary
- Click on Next
- The set-up is now ready to install
- Click on Next
Wait until set up installs entirely on your machine….
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
To launch PostgreSQL, go to the START menu
- Select PgAdmin 4
- You will see PgAdmin homepage
- Select Servers -> PostgreSQL (11)
- Right click on it and select connect Server
- Enter the password to connect with the server
- Click on OK
Now you can interact with Postgres database in two ways
- Using PgAdmin, i.e. GUI tool
- SQL shell (psql) through command prompt
- Open START menu
- Click on SQL shell
- Press Enter Key until prompt for the password
- Enter the password
Let us see, an example of create table, inserting data into the table and display the same.
Create table Query
Create table table_name (column_name1 data_type, column_name1 data_type, ……);
Create table EMP (emp_id int, emp_namevarchar(20), emp_salary int, dept varchar(10));
Insert into Query
Insert into table_name values (‘value1’, ‘value2’, …… );
Insert into EMP values (101, ‘Rohit Sharma’, 25000, ‘IT’);
Display table content
Select * from table_name;
Select * from EMP;
- 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