PostgreSQL Tutorial

What is 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.

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