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:
- Searches
- Simultaneous updates and reads
- Access control
Access the text file via a management system to:
- Traffic management for the data
- Manage concurrency
- 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
PostgreSQL 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.
