What is PostgreSQL?

Tomasz Bogusz

Inception

Currently Postgres is an open-source software developed at the University of California at Berkeley. The original POSTGRES implementation begun in 1986 and was headed by Professor Michael Stonebraker and sponsored by several military and non-military organizations. The project pivoted to an open-source software in 1994 under the name Postgres95. Then the name was changed to the current one i.e. PostgreSQL with the starting version of 6.0. In present times, PostgreSQL is often referred to by its nickname Postgres.

RDBMS

PostgreSQL (aka Postgres) is a relational database management system (RDBMS).

ACID

Postgres transactions observe ACID properties aimed at ensuring data validity despite adverse circumstances such as errors or power failures. Atomicity ensures that a sequence of DB operations constituting a single logical operation i.e. a transaction succeeds or fails completely. Consistency ensures that a transaction can only transform the DB from one valid state to another. Isolation ensures that the DB state is the same after some transactions irrespective of whether they were executed concurrently or sequentially. Durability means that the effects of successful transactions are preserved even in cases of system failures.

Referential Integrity

Referential integrity ensures that all references built within a DB in Postgres with primary and foreign keys are valid at all times. In other words Postgres does not allow for a non-NULL value to exist in a foreign key column that has no actual reference in the DB.

Client / Server Model

Postgres uses the client / server model. The model consists of two parts:

  • the server process called postgres - the core PostgreSQL program that accepts one or many simultaneous connections from a client process or processes and directly manages and retrieves databases objects, and

  • one or many client processes - a program or programs that make connections to the server process with the purpose of indirect management and retrieval of database objects - such a client app can be for example a Python / Rails / NodeJS application or a command line iterface such as psql.

psql: Command Line Interface

psql is a primary command-line interface for Postgres allowing for users and databases management and entering SQL commands and queries.

In addition to executing SQL commands psql features many its own internal commands which start with \.

For example to quit psql use the \q command, to get help the \h command and to list other internal commands use the \? command.

Postgres Version

To verify the Postgres version currently installed in your system use the postgres --version shell command or the SELECT version(); SQL command.