PostgreSQL

Prefered by developers ??

Origin …

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

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.PostgreSQL is a highly stable database backed by more than 20 years of development by the open-source community.

Architecture of PostgreSQL

Client-side Process :

When the user runs queries on PostgreSQL, the Client Application can connect to the PostgreSQL server (Postmaster Daemon Process) and submit queries through one of many Database Client Application program interface supported by PostgreSQL like JDBC, Perl DBD, ODBC, etc. that helps to provide client-side libraries. In the Client Process, the Communication between Client Application and Client Application library occurs with the help of Library API.

1. Postmaster Daemon Process :The system architecture of PostgreSQL is based on Process-Per-Transaction Model(Client/Server Model). A running PostgreSQL site is managed by Postmaster which is a central coordinating process. It is also known as Server Process.

Shared Memory: Shared memory is the memory that is simultaneously accessed by multiple programs in order to provide fast and efficient results with less redundancy. This is the memory that is reserved for Database Caching and transactional log caching.

Shared Tables: This approach involves using the same set of tables to host multiple client data.

2. Back-end process:

The Postmaster is responsible for handling initial client connections.For this, it constantly listens for new connections as a known port. After Performing an initialization process such as authentication of the user, the postmaster will give rise to a new backend server process to handle the new client. The client interacts only with the Backend server process like submitting queries and receiving queries result.

3. Shared Pool:

The Shared pool is a RAM area within the RAM Heap that is created during the starting time. A shared pool is a component of SGA (System Global Area). If Shared Pool is not available in RAM or it is not used then it results in high library cache reloads, high row cache reloads.

4. OID in PostgreSQL

OID stands for Object Identifier types. OID is used by PostgreSQL as a Primary Key for various system tables. It is implemented as an unsigned four bytes integer. We can also have an option to use OID in the user-defined table as” WITH OIDS ” but is discouraged to use because it is not large enough to provide uniqueness in a Large User-Defined Table.