Intro to PostgreSQL Database

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jul 21, 2017

Intro to PostgreSQL Database

Data migration is very easy to PostgreSQL or from PostgreSQL to any other database. With the extensive SQL support and migration tools, Postgres users can easily migrate their databases over to the Postgres database with little effort.

Introduction:
PostgreSQL is an open source object-relation database (ORDBMS). It is the world’s most advanced open source database which is written in ANSI C. Nowadays, many companies are moving the data to PostgreSQL database due to many of its unique features. PostgreSQL is released under the OSI-approved PostgreSQL License and there is no fee, even for use in commercial software products. This blog gives you an overview of PostgreSQL databases and the features of PostgreSQL database. 

Overview:
As an object-relational database system, PostgreSQL database boasts a vast set of features and a growing community. The database has a solid architecture that makes it a popular choice providing reliability, data integrity, and correctness across the databases. It is developed by the worldwide team of volunteers and is not controlled by any private entity or any organization. Its comes free with many operating systems like UNIX, WINDOWS XP,VISTA,7,8,2003,2008 etc. Let’s take a look at some of the Features of PostgreSQL:

Open Source: As mentioned earlier PostgreSQL D is available for free and is built by passionate developers and it is supported by an active community.

Highly Scalable: It can work under a low and heavy load in order to meet a users' need.

True ACID-compliance: Its supports properties like atomicity, consistency, isolation and the durability of the database. With PostgreSQL users don’t have to worry about database consistency and integrity.

JSON support: JSON support allows for Postgres to transfer data as raw text and without attributes and markup overhead. This will result in new levels of speed, efficiency and flexibility for developers.

Multiple Language Interfaces: Many interfaces are available for PostgreSQL like Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme Qt etc.

Multi-Version Concurrency Control (MVCC): Postgres uses MVCC. For each user connected to the database the Postgres database shows a snapshot of the database at a particular instance. When the database needs to update an item it will add the newer version and mark the old version as obsolete. This allows the database to save overhead but requires a regulated sweep to delete the old, obsolete data.

Supports migration from other major proprietary and open source databases: Data migration is very easy to PostgreSQL or from PostgreSQL to any other database. With the extensive SQL support and migration tools, Postgres users can easily migrate their databases over to the Postgres database with little effort.

Architecture- PostgreSQL Database
sql database toolsImage Source:
https://www.packtpub.com/sites/default/files/Article-Images/B01781_01.png

Important Processes Use in PostgreSQL

Postmaster: Postmaster process acts as a supervisor. Several utility process performs background work. Postmaster starts the processes and restart them if they die. One backend process per user session, postmaster listens for new connections.

Parser: When user executes any query, then the parser has to check the query string for a valid syntax.

Traffic Cop: It sends simple commands to the executor and complex ones are sent to the planner / optimizer.

Planner/optimizer: The planner will choose the best path if there are many possibilities that exist and then pass on the path to the executor.

Executor: Receives the plan from the planner in the form of a tree and provide the output to the client.

Background Write (Like DB writer in Oracle) Writes dirty data blocks to disk, performs checkpoints.

Important Utilities use in PostgreSQL:   

Vacuum:
Remove the fragmentation from the table. This utility scan tables in the database(s) in order to release unused memory, update the statistics, and prevent loss of data.

WAL Write: (Like LGWR in Oracle) Flushes write-ahead log to disk.

Archiver: Archive Write-Ahead log files (Like Oracle, write the redo log files to the Archive log location).

Logging Collector: Routes log message to, eventlog, or log files.

Stats Collector: Collects Usage statistics by relation and block.

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. For any questions click below. You can also leave a comment in the field below.

Ask Atul