Postgre SQL – An Introduction

PostgreSQL is one of the most advanced database available now in open source category. It is basically an ORDBMS [Object Relational DBMS] incepted almost 30 years ago and being improvised and updated year by year. It supports ANSI SQL and SQL/MED like standards. Support to more than ten procedural languages makes it highly extensible with GIN and GIST indexes, NoSQL like features etc,

Ease of Use:

As an open source database, it’s free to use and can have any number of development or staging / production installations. High Quality User Interactive database design and administration tools are available, which makes the interaction with database easier.

Along with the support offerings from the developers, there are well established vibrant online communities of PG professionals and enthusiasts to support you in any scenario.

Performance and Stability:

PostgreSQL has high Stability and performance. It has a very low maintenance and a very less, practically nil crash report history. As PostgreSQL is an open source platform, the source code is available for developers to perform any kind of customizations and extensions.

Cross platform Compatibility:

PostgreSQL is compatible with almost all Unix Platforms. Version 8 and above has native Windows compatibility, and other windows versions support can be managed using Cygwin framework.

High Volume and Complex data processing:

PostgreSQL is extremely responsive to High volume environments using multiple row data storage. The data consistency is maintained using Multi Version Concurrency Control (MVCC). PostgreSQL  have a superior query optimizer, which gives an upper hand on complex data models. The normal PostgreSQL query optimizer performs a near-exhaustive search over the space of alternative strategies.

Rich variety of data types :

PostgreSQL supports rich variety of  data types. There are special data types like JSON, UUIDs, IP addresses, Geometric… etc, which helps the users to manage real life data. Comparing to other relational databases, PostgreSQL have large sizes allocated to the objects.

Advanced Query Types and Indexes:

PostgreSQL supports much of the SQL and have advanced features like sub selects, transactional integrity (ACID[Atomic,Consistent,Isolated,Durable] ) etc… PostgreSQL supports different indexing types like B-Tree, R-Tree, Hash and GiST, among these usually B-tree will be the default one.

Write – Ahead Logging [W-AL]

A change in the datafile will be written only after the change is logged and the log file is created / updated. This ensures the consistency of the data files as well as enables online backup and recovery.

Other Notable Features :

1. Tablespaces : allows the database administrator to define locations in the file system  to store the files representing database objects.
2. SavePoints : Allows to handle error conditions within a transaction. It also allows to selectively discard parts of the transaction if needed.
3. Point-In-Time Recovery : Supports continuous backup of the server, which enables recovery from the time of crash,or from any chosen point of time.
4. Table Inheritance : A table can inherit from zero or more other tables
5. Full-text search: search with full-text, when searching for strings

How to setup PostgreSQL

For Linux

Step 1 : Download the correct version of the database you want and the platform [Eg: postgresql-9.2.4-1-linux-x64.run]

Step 2 : Execute the below commands

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
————————————————————————
Welcome to the PostgreSQL Setup Wizard.
————————————————————————
Please specify the directory where PostgreSQL will be installed.
Installation Directory [/opt/PostgreSQL/9.2]:

Step 3 : Answer  few basic questions like location of the installation, password, port number, etc. and wait for the setup to install

Step 4 : After Installation run the below commands to create database

[root@host]# su – postgres
Password:
bash-4.1$ createdb testdb
bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)
test=#

Step 5 : You can start/restart postgres server in case it is not running

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

For Windows

Step 1 : Download the correct version of the database you want and the platform [Eg: postgresql-9.2.4-1-windows.exe]

Step 2 : Double click the exe file to start installation, Select the installation directory

Step 3 : Select the directory where the data to be stored

Step 4 :  Set the passwords when prompted

Step 5 : Select the port, better to keep it default

Step 6 : Select the language

Step 7 : Wait the setup to get installed, In the final screen, Uncheck the Stack Builder checkbox (If asked) and finish the installation.

Even if PostgreSQL has its own advantages, the Installation and configuration can be bit difficult for the beginners. For a person from the MySQL background, the PostgreSQL command line may look like alien.

Every RDBMS has their own pros and cons, but if you are looking for a database for your business application with lots of reporting and data mining operations; Postgres SQL is the solid choice.

Conclusion:

Due to some unique features of  PostgreSQL many companies are using PostgreSql database. It’s an open source database with no hidden control. One needs to pay for an annual subscription for support. You can modify the software to meet our needs, and we can plug in anything at any point, by adhering to well-defined open interfaces. There is no need of license and can be installed on many servers. There is no issue of how many cores and how many users are connecting at a given point in time.

Know More About This Topic from our Techies

Other Blogs:

• LARAVEL 5.5  What’s New?
• Get Ready for Laracon EU, 2017
• Comparing Top 3 PHP Frameworks : Laravel vs Yii vs Symfony
• Why Laravel 5.4 is the right Backend for your Web Application?