Relational Database At A Glance With PostgreSQL
Tags: database, webappdevelopment, postgres, softwareengineering
Published: 2022-07-11 01:32:28

---
title: Relational Database At A Glance With PostgreSQL
published: true
date: 2021-05-31 16:53:51 UTC
tags: database,webappdevelopment,postgres,softwareengineering
canonical_url: https://rajdeep-das.medium.com/relational-database-at-a-glance-with-postgresql-a9512a947fbf
---

According to Wikipedia A **relational database** is a digital [database](https://en.wikipedia.org/wiki/Database) based on the [relational model](https://en.wikipedia.org/wiki/Relational_model) of data, as proposed by [E. F. Codd](https://en.wikipedia.org/wiki/E._F._Codd) in 1970. A software system used to maintain relational databases is a [relational database management system](https://en.wikipedia.org/wiki/Relational_database_management_system) (RDBMS). Many relational database systems have an option of using the [SQL](https://en.wikipedia.org/wiki/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

![](https://cdn-images-1.medium.com/max/800/1*8NLs8AT-X1UzP1_NDscFBg.jpeg)
_Limitations_

**PostgreSQL terminology**

![](https://cdn-images-1.medium.com/max/800/1*I1_Zbr-DiwuyrlgPr2w16g.jpeg)
_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.