Introduction

An open-source relational database (widely used in backend)

Powerful & prod-ready

ACID compliant (safe transactions)
Strong SQL standard support
JSON / JSONB support (great for modern apps)
Advanced indexing (GIN, GiST)
- Default primary key create B-tree index
- JSONB index (GIN index):

-- Flexible searching (General JSONB searching)
-- Indexes all keys and values inside JSONB
CREATE INDEX idx_users_profile
ON users USING GIN(profile);

-- Hot field (creates an expression B-tree index)
-- Indexes only extracted TEXT
CREATE INDEX idx_users_country
ON users ((profile->>'country'));

Powerful constraints & relations

Philosophy: Correct first, fast second (Very strict about data integrity)
Use MVCC (reads don’t block writes, writes create new versions)

Postgres architecture

Client (App / pgAdmin)
        ↓
PostgreSQL Server
 ├── Postmaster (main process)
 ├── Backend process (per connection)
 ├── Shared Buffers (cache)
 ├── WAL (Write Ahead Log)
 └── Background workers

JSON bs JSONB

JSON	JSONB
Stored as text	Stored as binary
Slow to query	Fast
No index	Can be indexed
Rarely used	Use this always

Example create & insert:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT,
  profile JSONB
);
INSERT INTO users (email, profile)
VALUES (
  '[email protected]',
  '{"age":22,"country":"MY","skills":["Java","Spring"]}'
);

Example select:

// Get the value
SELECT profile->>'country' FROM users;
// ->  returns JSON
// ->> returns TEXT

// JSON containment
SELECT *
FROM users
WHERE profile @> '{"country":"MY"}';

// Use in filter
SELECT *
FROM users
WHERE profile->>'country' = 'MY';

// Array contains
SELECT *
FROM users
WHERE profile->'skills' ? 'Java';

WAL (Write-Ahead Logging)

Before data changes:

Write to WAL