Exploring Postgres 101 : CONCURRENCY CONTROL IN POSTGRES

 

 You may have heard the term DEADLOCK, if you ever studied programming, OR I would call it RACE CONDITIONS

Deadlock is like a traffic jam in a database where transactions are stopped because they are waiting for each other to move.

Let's get to know MVCC before jumping into anything. 

One of the key features of Postgres that sets it apart from other databases is Multi version Concurrency Control (MVCC). MVCC allows multiple transactions to occur concurrently while ensuring data consistency and integrity.

Let’s take an example, 

I have created Employee table and inserted a value, So far so good... Everything seems NORMAL right??

I have 3 columns and 1 row and there is nothing to hide, that's what I thought. Guess what?

I WAS WRONG!

Postgres has been hiding some columns, which we can't see directly using SELECT.

In PostgreSQL, a table row layout (or tuple structure) defines how data is stored within a table at the physical level. Each row (or tuple) in a PostgreSQL table consists of multiple components, stored in heap pages (blocks of data). To know more, read this.

In Short, Every row in Postgres has special header and that header stores  various fields that are used by database engine internally.

emp_id, emp_name and emp_salary were already defined in  our table defination.

The first 3 columns are hidden from the header of this row. Let's get to know them one by one....