Installing PostgreSQL + pgAdmin

Download official installer:

• Includes PostgreSQL + pgAdmin

During install:

• Set postgres master user & password
• Default port: 5432

After install:

• Open pgAdmin
• Connect to server:
  • Host: localhost
  • Port: 5432
  • User: postgres
  • Password: (what you set)

Introduction

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

• Very powerful & production-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