PostgreSQL | Features & Architectural Concepts | User Interface

PostgreSQL

PostgreSQL is an open-source Object-Relational Database Management System (ORDBMS). PostgreSQL supports basic object-oriented features or characters including inheritance and complex data types along with special functions to deal with these data types.

HISTORY OF POSTGRESQL -

PostgreSQL was 1st developed as a database called Postgre which developed at UC Berkley in the early 80s. And around 1996, it was officially called PostgreSQL, to reflect the added ANSI SQL compliant translator.

PostgreSQL is one of the most feature-rich robust open-source databases.

CHARACTERISTICS/FEATURES OF POSTGRESQL -

PostgreSQL features make it a very valuable or acceptable DBMS as follows :

• Full ANSI-SQL 92 compliance: It supports -

1. most of ANSI 99 compliance as well.
2. extensive support for the transactions.
3. BEFORE and AFTER triggers.
4. implementation of stored procedures, constraints, referential integrity with cascade update/delete.

• Many high-level languages and native interfaces can be used for creating user-defined database functions.

• Users can use native SQL, Java, C, C++, Perl, and postgres counterpart to Oracle PL/SQL or MS SQL Server's/Sybase TransactSQL called PgSQL.

• Inheritance of table structures which one of the rarely used useful features.

• Built-in complex data types such as IP Address Geometrics (Points, lines, circles), arrays as a database field type and ability to define your own data types. 

• Ability to define Aggregate functions.

• Concepts of collections and sequences.

• It supports multiple operating systems like Linux, Windows, Unix, Mac.

• It may be considered to be one of the important databases for implementing Web-based applications because of the fact that it is fast and features rich.

• PostgreSQL is a fast database with proper support for web languages usable in other languages such as ODBC and JDBC drivers making it easily usable in ASP, ASP.net, and Java. 

ARCHITECTURAL CONCEPTS OF POSTGRESQL -

In this, we have discussed some basic concepts of PostgreSQL system architecture.

The Architecture of PostgreSQL

In the above figure, it shows the basic architecture of the PostgreSQL on the Unix operating system i.e. Postgres uses a simple process per-user client/server model.

The database consists of co-operating processes as follows - 

• A supervisory daemon process whose also referred to as postmaster.
• The front-end user application process.
• One or more backend server processes.

The Single postmaster process manages the collection of a database on a single host machine. The client applications that want access to a database stored at a particular installation make calls to the client interface library. The library sends user requests over the network to the postmaster, in turn, starts a new server. The postmaster connects the client processes to the new server. 

Note that the clients process and the backend server process communicate with each other without any intervention on the part of the postmaster. Thus, the postmaster process is always running - waiting for requests from the new clients. (The client and server processes will be created and destroyed over a period of time as the need arise).

Can a client process, make multiple connections to a backend server process?

The library allows a single client to make multiple connections to backend server processes. 

Note that, these client processes are not multi-threaded processes. At present, the multithreaded front-end/backend connections are not supported by the library. The clients' server or front-end/backend combination of processes on different machines files can be accessed on a client machine that permits may not be accessible on the database server machine.

POSTGRESQL USER INTERFACES - 

PostgreSQL has following interfaces for the access of information i.e. databases.

Postgres terminal monitor programs :

It is a SQL command level interface that allows you to enter, edit, and execute SQL commands interactively.

Programming Interface :

A user can write a C program using the libpq subroutine library. This allows a user to submit SQL commands from the host language - C and get responses and status messages back to user's program. 

But the question is that how would a user be referring to this interface? The solution is by installing PostgreSQL on a user machine.

After installing PostgreSQL on a user's machine, as a user, he/she need the client's portion of the installation.

One of the common directories where Postgres may be installed on Unix machines is /user/local/pgsql. Therefore, we will assume that Postgres has been installed in the directory/user/local/pgsql. 

If you have installed Postgres in a different directory then you should substitute this directory name with the name of that directory. All Postgres commands are installed in the directory /user/local/pgsql/bin. Thus, you need to add this directory to your shell command path in Unix.

For Example:

On the Berkeley C shell or its various like csh or tcsh, then you need to add -

                   % set path = (/user/local/pgsql/bin path)

in the .login file in the home directory.

SQL VARIATION AND EXTENSION -

The SQL was standardized by American National Standards Institute (ANSI) in 1986. In 1987, the ISO (International Standards Organisation) standardized it. The United States Government's Federal Information Processing Standard (called FIPS) adopted the ANSI/ISO standard in 1989 which is a revised standard known as SQL89 or SQL1, was published.

The SQL89 standard was intentionally left incomplete to contain commercial DBMS developer interests. However, the standard was strengthened by the ANSI committee with the SQL92 (also called SQL2) standard that was ratified in 1992. This set forth conceptual SQL features which at that time exceeded the capabilities of the RDBMSs of that time. This is because of the standard addressed some weakness in SQL89.

Since SQL92 was large because its standard was approx. 6 times the length of its predecessor. Therefore, the authors of the standards defined 3 levels of SQL92 compliance:-

Entry-level conformance - only the barest improvements to SQL89.

Intermediate-level conformance - generally achievable set of major advancements.

Full conformance - total compliance with the SQL92 features.

In 1999, the ANSI/ISO released the SQL99 standard which is also called SQL3. It addressed some of the more like object-oriented database concepts, call level interfaces, and integrity management. SQL99 defined degrees of conformance: Core SQL99 and Enhanced SQL99, and replace the levels of compliance with SQL92.

PostgreSQL presently conforms to most of the Entry-level SQL92 standard, many of the Intermediate-level, and Full-level features. 

PostgreSQL also provides several extensions to standard SQL as follows:

• PostgreSQL supports many non-standard types which include abstract data types such as complex, domains, cstring, record, trigger, void, etc. 

• PostgreSQL also includes polymorphic types like an array.

• PostgreSQL supports triggers which allow the creation of functions and can be stored and also executed on the server.

• PostgreSQL supports many procedural programming such as PL/pqSQL, PL/Tel, PL/Python, etc.