PostgreSQL

What is PostgreSQL

PostgreSQL is an advanced, enterprise-class, and open-source relational database system. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying. [1]. PostgreSQL runs on all major operating systems, has been ACID-compliant (Atomicity, Consistency, Isolation, Durability) since 2001. PostgreSQL is highly stable database backed by more than 20 years of development by the open-source community. It is used as a primary database for many web applications as well as mobile and analytics applications.[2]

History

Started in 1986 at Berkeley Computer Science Department, University of California.​ Originally named POSTGRES, in reference to the older Ingres database which also developed at Berkeley.​ In 1996 renamed to PostgreSQL to clearly illustrate its support for SQL. ​ Since then, the PostgreSQL Global Development Group, a dedicated community of contributors continues to make the releases of the open-source and free database project [3].

PostgreSQL feature highlights

PostgreSQL has many advanced features that other enterprise-class database management systems offer, such as:

• User-defined types
• Table inheritance
• Sophisticated locking mechanism
• Foreign key referential integrity
• Views, rules, subquery
• Nested transactions (savepoints)
• Multi-version concurrency control (MVCC)
• Asynchronous replication

The recent versions of PostgreSQL support the following features:

• Native Microsoft Windows Server version
• Tablespaces
• Point-in-time recovery

And more new features are added in each new release.

PostgreSQL is designed to be extensible. PostgreSQL allows you to define your own data types, index types, functional languages, etc. If you don’t like any part of the system, you can always develop a custom plugin to enhance it to meet your requirements e.g., adding a new optimizer.

Syntax

 CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
 );

 SELECT
  select_list
 FROM
  table_name;

 BEGIN;
 -- insert a new row into the accounts table
 INSERT INTO accounts(name,balance)
 VALUES('Alice',10000);
 -- commit the change (or roll it back later)
 COMMIT;

Multiversion concurrency control (MVCC)

One of the big selling points of Postgres is how it handles concurrency. The promise is simple: reads never block writes and vice versa. Postgres achieves this via a mechanism called Multi Version Concurrency Control. This technique is not unique to Postgres: there are several databases that implement some form of MVCC including Oracle, Berkeley DB, CouchDB and many more.[4] Multiversion concurrency control (MVCC) gives each transaction a "snapshot" of the database, allowing changes to be made without affecting other transactions. This largely eliminates the need for read locks, and ensures the database maintains ACID principles. PostgreSQL offers three levels of transaction isolation: Read Committed, Repeatable Read and Serializable. Because PostgreSQL is immune to dirty reads, requesting a Read Uncommitted transaction isolation level provides read committed instead. PostgreSQL supports full serializability via the serializable snapshot isolation (SSI) method.[5]

Usage

PostgreSQL's popularity is still growing. Stack Overflow’s surveyors collect the votes of actual programmers and developers. Among systems used by professionals, PostgreSQL came second, ahead of giants like Microsoft SQL Server, MongoDB, Oracle, and Redis. It is still gaining new users and is catching up with the first on the list (MySQL).Here are just some of the major players who use PostgreSQL[6][7]:

• Microsoft
• Apple
• Netflix
• Uber
• IMDb
• Shopify
• Slack
• Instagram
• Amazon
• Spotify

Comparison to other DBMS

• SQL compliance: More so than SQLite or MySQL, PostgreSQL aims to closely adhere to SQL standards. PostgreSQL supports 160 out of the 179 features required for full core SQL:2011 compliance, in addition to a long list of optional features.
• Open-source and community-driven: A fully open-source project, PostgreSQL’s source code is developed by a large and devoted community. Similarly, the Postgres community maintains and contributes to numerous online resources that describe how to work with the DBMS, including the official documentation, the PostgreSQL wiki, and various online forums.
• Extensible: Users can extend PostgreSQL programmatically and on the fly through its catalog-driven operation and its use of dynamic loading. One can designate an object code file, such as a shared library, and PostgreSQL will load it as necessary.[8]

How to Install the PostgreSQL DBMS

The original Tutorial for the Install can be found on this page: https://www.guru99.com/download-install-postgresql.html.

Download Installer

Go to https://www.postgresql.org/download. Select PostgreSQL version. You will be prompted to desired PostgreSQL version and operating system. Select the latest PostgreSQL version and OS as per your environment. Click the Download Button. Download will begin.

Step by Step

1. Open exe file. Once you Download PostgreSQL, open the downloaded exe and Click next on the install welcome screen.
2. Update location. Change the Installation directory if required, else leave it to default.
3. Select components. You may choose the components you want to install in your system. You may uncheck Stack Builder.
4. Check data location. You may change the data location.
5. Enter super user password. Make a note of it.
6. Check port option. You can leave the port number default.
7. Check summary. Check the pre-installation.
8. Ready to install. Click the next button.
9. Once install is complete you click Finish.

Launch PostgreSQL

To launch PostgreSQL go to Start Menu and search pgAdmin 4.

1. Check pgAdmin. You will see pgAdmin homepage.
2. Find PostgreSQL 10. Click on Servers > PostgreSQL 10 in the left tree.
3. Enter super user password set during installation. Click OK.
4. Check Dashboard. That’s it to PostgreSQL installation.

Florian was here!