PostgreSQL Tutorial

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

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.