PostgreSQL Interview Questions

Postgresql Interview Questions Download Postgresql Interview Questions PDF

Postgresql Interview Questions

Before you can have access to the database, you must be able to start the database server. The server program of the database is called Postgres. The Postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is:

  1. /usr/local/etc/rc.d/010.pgsql.sh start
  2. /usr/local/etc/rc.d/PostgreSQL start

Postgres or simply known as Postgresql in SQL world is one of the widely and popularly used for Object-Relational Database Management System that is used mainly in large web applications. It is one of the open source object-relational database system which also powerful. It provides additional and substantial power by incorporating four basic concepts in such way that the user can extend the system without any problem. It extends and uses the SQL language that is combined with various features to safely scale and store the intricate data workloads.

The origin of PostgreSQL dates back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. It runs on all the major operating systems and has been ACID compliant since 2001. It also has add-on like PostGIS database extender. In MAC OS Postgresql is default database. Michel Stonebraker is Father of Postgresql who has started Post Ingres project for supporting Contemporary 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 in 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.

 There are new, different data-types supported by Postgresql. Following are those data-types:
  •    Geometric primitives
  •     Arbitrary precision numeric
  •    XML
  •    Arrays etc.

Users can also create their indexes and get them indexed.

During the process of updating the project, one can never be certain what features will go in and which ones won’t make the cut. The project has precise and stringent standards for quality, and some patches may or may not match them before the set deadline. Currently, the 9.1 version is working on some important features which include JSON support, synchronous replication, nearest-neighbor geographic searches, collations at the column level, SQL/MED external data connections, security labels as well as index-only access. However, this list has a high chance of changing completely by the time Postgre 9.1 is released.
WAL or write-ahead logging is a standard method to ensure data integrity. It is a protocol or the correct rule to write both actions and changes into a transaction log. This feature is known to increase the reliability of the database by logging changes before any changes or updating to the database. This provides the log of the database in case of a database crash. This helps to start the work from the point it was discontinued.