Understanding PostgreSQL Database CRUD Operations with ACID Transaction Control Principles

Introduction

Database CRUD represents four fundamental operations for creating and managing persistent data in relational database.

Database CRUD operations are performed through SQL (Structured Query Language) statements/commands.

	Represents Operation	SQL Statement	What it does
C	CREATE	INSERT	Adds data to the database in the current or specified schema.
R	READ	SELECT	Used to fetch or query data from the database.
U	UPDATE	UPDATE	Used to modify existing data in the database.
D	DELETE	DELETE	Used to remove existing data from the database.

CREATE Operation: It is represented by the INSERT SQL statement used to insert records into the database table.

Syntax: INSERT INTO tableName (column1Name, column2Name)

VALUES (column1Value, column2Value)

Table Name: customer

SQL Example:

READ operation: It is represented by the SELECT SQL statement used to fetch the existing record (s).

Syntax: SELECT column1Name, column2Name FROM tableName [WHERE Clause]

SQL Example:

UPDATE Operation: It is represented by UPDATE SQL statement used to modify the value of the existing record. It requires exclusive locks on the rows and their related resources, which ensures that when one more row is modified, they are not available to other processes or users for any CRUD operation. This is to provide the integrity of the data.

Syntax: UPDATE tableName SET column1Name=value1, column2Name=value2 [WHERE Clause]

SQL Example:

DELETE Operation: It is represented by DELETE SQL statement used to remove existing record (s).

Syntax: DELETE FROM tableName [WHERE Clause]

SQL Example:

ACID Principles

These CRUD operations are often performed within ACID transactional scopes to ensure data integrity and reliability.

Element	Represents	Description
A	Stands for Atomicity	Ensures that a transaction is treated as a single, indivisible unit of work. Either all operations within the transaction are completed successfully, or none of them are.
C	Stands for Consistency	Ensures that a transaction brings the database to a valid state. If the database was in a consistent state before the transaction, it remains consistent afterward.
I	stands for Isolation	Ensures that concurrent transactions do not interfere with each other. Each transaction appears to execute independently, as if no other transactions ran simultaneously.
D	stands for Durability	Guarantees that the changes are permanently stored once a transaction is committed and will survive system failures or crashes.

Atomicity

The writes in a transaction are executed all at once and cannot be broken into smaller parts. If there are faults when executing the transaction, the writes are rolled back. In short, atomicity means “all or nothing”.

Consistency

Unlike “consistency” in CAP theorem, which means every read receives the most recent write or an error, here, consistency means preserving database invariants. Any data written by a transaction must be valid according to all defined rules and maintain the database in a good state.

Isolation

When concurrent writes occur in two different transactions, they are isolated from each other. The strictest isolation is “serializability,” where each transaction acts like it is the only transaction running in the database. However, this is hard to implement in reality, so we often adopt a loser isolation level.

Durability

Data persists after a transaction is committed, even during a system failure. In a distributed system, this means the data is replicated to some other nodes.

TakeAways

Mastering CRUD operations and understanding how to use transactions with ACID principles is critical, and PostgreSQL makes this process reliable and robust out of the box.