Introduction to PostgreSQL

PostgreSQL is powerful open source RDBMS. It has gained strong confidence for reliability, data integrity and correctness.

It is known for most advanced open source database system.

It runs on all major operating system like Linux, UNIX, and Windows.

PostgreSQL includes most number of datatypes like INT, NUM, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP etc. It also supports binary large objects. One can define his own datatypes.

Total cost of implementation is very less as maintenance efforts are very minimum due to its stability feature.

PostgreSQL features: 

• User defined types

• Table inheritance

• Foreign key referential integrity

• Views, rules, subqueries

• Sophisticated locking system

• Nested transaction.

• Multi Version concurrently control(MVCC)

• Tablespaces

• Point in time recovery

Architectural fundamentals of PostgreSQL

PostgreSQL is first database system that implemented multi-version concurrency control (MVCC)

Recent version comes with support for NoSQL as well.

Useful constraints in PostgreSQL

NOT NULL, UNIQUE, PRIMARY KEY, CHECK, Foreign Key

PostgreSQL is based on client-server architecture

PostgreSQL structure consist of shared memory, few background process and datafiles present in data directory

Shared Memory: shared memory consist of shared buffer and wal buffer, memory used for transaction caching and database caching

PostgreSQL Processes:

Daemon Process: First process starts when we start PostgreSQL. Performs recovery, memory allocation and start background process

Background process: logger, checkpointer, writer, wal writer, archiver, stats collector

Backend Process: backend process performs sorting, query execution and transferring results to user process

Client Process: client process is request coming from user

You may also like to see introduction to mongodb