Relational Database At A Glance With PostgreSQL

According to Wikipedia A relational database is a digital database based on the relational model of data, as proposed by E. F. Codd in 1970. A software system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems have an option of using the SQL (Structured Query Language) for querying and maintaining the database

Database 101

A database stores data

Plain text files have limited functionality for basic processes, including:

1. Searches
2. Simultaneous updates and reads
3. Access control

Access the text file via a management system to:

1. Traffic management for the data
2. Manage concurrency
3. Provide other features

ACID compliance

Atomicity  — Store data in an all-or-nothing approach.

Consistency  — Give me a consistent picture of the data.

Isolation  — Prevent concurrent data updates from incorrect reads/writes.

Durability  — When User say ‘ COMMIT ;’ the data, make sure it is safe until User explicitly destroy it.

Database transactions

A transaction is a unit of work.

A transaction is all or nothing:

Beginning (BEGIN;)
Work (INSERT/UPDATE/DELETE/SELECT)
Ending (END;) results in one of the following:
COMMIT; (save everything)
ROLLBACK; (undo all changes, and save nothing)

Once the transaction ends, it either makes ALL of the changes between BEGIN; and COMMIT; or NONE of them (if there is an error, for example).

PostgreSQL 101

PostgreSQL meets all the requirements to be a fully ACID-compliant, transactional database. To accomplish this, many database and computer science concepts are implemented. The focus of the course is to illustrate PostgreSQL internals and the effects they have on the end user.

PostgreSQL relational database management system (RDBMS) serves an instance that:

Serves one (and only one) TCP/IP port

Has a dedicated data directory

Contains at least one database

PostgreSQL features

ACID compliant

Transactional (uses WAL/REDO)

Partitioning

Multi version concurrency control (readers don’t block writers)

Online maintenance operations

Hot/warm Standby

Full-text search

Rich geospatial (PostGIS)

Procedural languages

Postgres Database limitations

Limitations

PostgreSQL terminology

Terminology

Summary

PostgreSQL is an enterprise class, open source relational database management system.

PostgreSQL has a rich history and was created at UC Berkeley. PostgreSQL is flexible, and can scale into the future.

The PostgreSQL engine adds new features and functionality to appeal to new use cases.