Postgresql

  • History Of PostgreSQL :

Postgresql which is simply named as Postgress is Widely used Object Relational Database Management System for Large web applications.Here In this article we will discuss History Of Postgresql in detail.In MAC OS Postgresql is default database.Michel Stonebraker is Father of Postgresql who has started Post Ingres project for supporting Contemparary Database systems.PostgreSQL’s developers pronounce PostgreSQL as It is abbreviated as Postgres because of ubiquitous support for the SQL Standard among most relational databases.PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker, who went on to become the CTO of Informix Corporation. Stonebraker started Postgres in 1986 as a followup project to its predecessor, Ingres, now owned by Computer Associates. The name Postgres thus plays off of its predecessor (as in “after Ingres”). Ingres, developed from 1977 to 1985, had been an exercise in creating a database system according to classic RDBMS theory. Postgres, developed between 1986-1994, was a project meant to break new ground in database concepts such as exploration of “object relational” technologies.An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance.

History of Postgresql
History of Postgresql

Postgresql is pronounced as postgress because of its  ubiquitous support of SQL standards

Postgresql is Derived from Postgress Package written in University of California at 1982.It has converted to Database management system in 1995 and first version of PostgreSQL has been launched named ‘POSTGRESS95’.Postgresql is most Advanced open source database management system used in world wide.In 1996 Postgress supported with SQL so the Postgress95 has been changed to POSTGRESQL Database management system.Because of its security and open source DBMS it is one of the widely used database management system in world.

  • Advantages of Postgresql:
  • Reliable
  • Stable
  • Open source
  • Extensible
  • Easy to learn
  • Cross Platform
  • Designed for High Volume Enviroments
  • Flexible
  • Better Support

Due to above advantages of PostgreSQL ;This becomes the most used database management systems for the mid scale applications. It is open source database so that lot of mid scale companies are using this Database management system as back end. PostgreSQL is Easy to learn database because all the syntax in PostgreSQL is like the SQL.It is working on Cross-Platform means user can deploy the PostgreSQL in Unix as well as Windows also.This websites gives the idea about SQL Tutorials. There are so many similarities between Oracle syntax’s and PostgreSQL syntax.There are so many Oracle functions are available in PostgreSQL like Rank function is same in Oracle as well as PostgreSQL. The indexing techniques are also similar in Oracle as well as PostgreSQL.These SQL Tutorials are important for PostgreSQL Users also.

Click here to get PostgreSQL Interview Questions with Answers

Procedural languages:

PostgreSQL supports four standard procedural languages (which allows the users to write their own code which can be executed by database server) – 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. But all these and many more non-standard procedural languages need additional packages to be installed, unlike standard procedural languages.

Indexes:

PostgreSQL supports B+-tree, hash, generalized search trees (GiST) and generalized inverted indexes (GIN). Users can also create their own customized indexes.

Triggers:

Triggers, which initializes an action by an SQL Data Management Language statement (DML), mostly by INSERT and UPDATE statement are fully supported.

Views:

Views can create virtual tables. It is supported by PostgreSQL.

MVCC:

Multiversion concurrency control (MVCC) is a system to manage concurrency where each user’s action is not visible to others until a transaction is committed and thus multiple users can work simultaneously.

Rules:

Rules, which allow a hierarchy of an incoming query to be re-written, are supported by PostgreSQL.

Data Types :

The following data types are supported by PostgreSQL : Boolean, Arbitrary precision numeric, Character (text, varchar, char), Binary, Date/time (timestamp/time with/without timezone, date, interval), Money Enum, Bit strings, Text search type, Composite Variable length arrays (including text and composite types) up to 1 GB in total storage size, Geometric primitives, IPv4 and IPv6 addresses, CIDR blocks and MAC addresses, XML supporting XPath queries (as of 8.3), UUID (as of 8.3).

User defined objects:

It supports the creation of almost all new objects inside the database like Casts, Conversions, Data types, Domains, Functions including aggregate functions and window functions, Indexes including custom indexes for custom types Operators.

Inheritance:

In PostgreSQL, a table can be set to inherit their characteristics from a “parent” table.

Extensions:

To add additional functionality to PostgreSQL, several extensions can be installed.

Other important features:

Referential integrity constraints, Inner, outer (full, left and right), and cross joins, Sub-selects, Transactions, SSL, Domains, Tablespaces, Savepoints, Point-in-time recovery, implemented using Write-ahead logging, Two-phase commit, TOAST (The Oversized-Attribute Storage Technique) is used to transparently store large table attributes (such as big MIME attachments or XML messages) in a separate area, with automatic compression, Regular expressions, Common table expressions, Embedded SQL, Full text search, Per-column collation (from 9.1).

PostgreSQL supports most of the major features of SQL:2008 standard.

Hope these articles on Postgresql as well as interview questions of Postgresql will help the users to gain the knowledge as well as to face the PostgreSQL interviews easily.