PostgreSQL

Contents

Quick Snapshot

• Type: Relational Database Management System (RDBMS)
• Developer: The PostgreSQL Global Development Group (open source community)
• Initial Release: 1996 (as PostgreSQL 6.0), roots go back to the 1980s POSTGRES project at UC Berkeley
• License: PostgreSQL License (permissive open source)
• Latest Version: 17 (as of 2025)
• Written In: C
• Supported Platforms: Linux, macOS, Windows, BSD, and more
• Mascot: Slonik 🐘 — the friendly blue elephant
• Website: postgresql.org

PostgreSQL (or simply Postgres) is the open source, feature-rich database.

---

🧬 Origins

PostgreSQL’s lineage starts with POSTGRES, a research project at the University of California, Berkeley led by Michael Stonebraker in the 1980s.
Its goal: to move beyond the relational model — and it did.

In 1996, it evolved into PostgreSQL, merging the stability of traditional SQL databases with advanced extensibility — becoming the open-source powerhouse it is today.

Think of it as “the world’s most advanced open-source database” — and yes, it’s earned that tagline.

---

⚙️ Core Features

• 💾 ACID Compliance — Atomicity, Consistency, Isolation, Durability.
• 🧩 Extensible — define your own data types, operators, and functions.
• 🧠 SQL + JSON — relational and document-based data in one engine.
• 🔐 MVCC (Multi-Version Concurrency Control) — zero read locks, high concurrency.
• 🚀 Indexing Power — B-tree, Hash, GiST, GIN, BRIN, SP-GiST — it’s a buffet of performance.
• 🧰 Stored Procedures — with PL/pgSQL, Python, JavaScript, and more.
• 🌐 Replication — built-in physical and logical replication.
• 🔒 Security — role-based access, SSL/TLS, row-level security, and more.
• 📈 Scalable — handles terabytes gracefully and parallel queries like a champ.

Postgres isn’t flashy — it’s just consistently excellent.

---

🧱 Architecture Overview

---

💾 Data Types

PostgreSQL supports nearly every data type under the sun:

🔤 Text & Numeric

VARCHAR(n), TEXT, INTEGER, BIGINT, NUMERIC, REAL, SERIAL

📅 Date & Time

DATE, TIME, TIMESTAMP, INTERVAL

💡 Boolean

BOOLEAN

🧩 JSON & Arrays

JSON, JSONB, ARRAY, HSTORE

🌍 Geometry & Spatial

POINT, LINE, CIRCLE, BOX

🧬 Custom Types

You can even define your own:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Postgres is famously type-rich — and lets you invent new ones when reality isn’t enough.

---

🧮 Basic Commands

Check out the full cheat sheet of PostgreSQL!

Create a Database

CREATE DATABASE mydb;

Create a Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Insert Data

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

Query Data

SELECT * FROM users WHERE name = 'Alice';

Update & Delete

UPDATE users SET email='new@ex.com' WHERE id=1;
DELETE FROM users WHERE id=1;

---

🧠 Indexing

Indexes make lookups fast — like a table of contents for your data.

CREATE INDEX idx_users_email ON users(email);

Specialized indexes:

• GIN: Great for JSONB and full-text search.
• GiST: For geometric and range data.
• BRIN: For very large, sequential data sets.

Without indexes, Postgres reads every row. With indexes, it just knows.

---

🔍 Joins

Postgres handles all the usual suspects:

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

• INNER JOIN — match both.
• LEFT JOIN — include all left side.
• RIGHT JOIN — include all right side.
• FULL JOIN — include everything.

It’s like a family reunion where everyone’s invited, even if they don’t get along.

---

🧩 JSON & NoSQL Power

PostgreSQL handles structured and semi-structured data.

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO profiles (data) VALUES ('{"name": "Bob", "age": 32}');
SELECT data->>'name' FROM profiles;

You can index and query JSON efficiently:

CREATE INDEX idx_profiles_json ON profiles USING gin (data);

Postgres does NoSQL so well, MongoDB has trust issues.

---

🔄 Transactions

All or nothing — that’s the PostgreSQL way.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Rollback like it never happened:

ROLLBACK;

---

📊 Aggregations & Analytics

SELECT department, AVG(salary)
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;

Window functions:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC)
FROM employees;

Postgres turns SQL into a data analyst’s playground.

---

🔁 Replication & High Availability

Built-in Tools:

• Streaming replication
• Logical replication
• Point-in-time recovery
• Hot standby

Cluster managers like Patroni or pgpool-II make it production-ready for failover setups.

---

🧰 Extensions

PostgreSQL is modular — think of extensions as power-ups.

Enable extensions:

CREATE EXTENSION postgis;

---

🔒 Security Features

• Role-based access control
• SSL/TLS for client connections
• Row-level security
• Fine-grained permissions (GRANT, REVOKE)
• Encrypted connections and optional data encryption
• Auditing via logging and third-party extensions

Postgres treats your data like royalty — no leaks, no nonsense.

---

🧰 Tools & Ecosystem

• psql: Command-line client (powerful and scriptable).
• pgAdmin: GUI management tool.
• DBeaver, DataGrip, Beekeeper Studio: Popular third-party GUIs.
• pg_dump / pg_restore: Backup and restore utilities.
• pgcli: Fancy terminal client with auto-completion.
• docker run postgres: Instant test database in a container.

---

🧾 Useful CLI Commands

# Connect
psql -U postgres -d mydb

# List databases
\l

# List tables
\dt

# Describe table
\d users

# Execute SQL file
\i script.sql

# Quit
\q

---

⚡ Performance Tips

• Use EXPLAIN ANALYZE to profile queries.
• Create indexes wisely — not too many, not too few.
• Use connection pooling (e.g., pgBouncer).
• Keep autovacuum enabled.
• Optimize work_mem, shared_buffers, and effective_cache_size for hardware.

Fast Postgres is happy Postgres.

---

🧭 When to Use PostgreSQL

• You need strong consistency and relational integrity.
• You love open-source but require enterprise-grade reliability.
• You want advanced data features (JSON, GIS, time series) without switching databases.
• You believe in SQL and freedom.

---

🧾 TL;DR Mind Map

---

🐘 Fun Facts

• The mascot “Slonik” has been around since the 1990s.
• Postgres predates MySQL — and most of the web, for that matter.
• Every major tech company (from Apple to NASA) uses PostgreSQL somewhere.
• You can even run embedded Postgres inside your applications.
• Some call it “Postgres,” others “PostgreSQL.” Both are right — but one is easier to pronounce.
• StableWiki Engine, the backbone of SidWiki, uses PostgreSQL as its primary database.