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
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
