What PostgreSQL Is

Why teams choose it

PostgreSQL is an open-source relational database known for correctness and features: full ACID transactions, rich SQL (CTEs, window functions), strong constraint support, JSONB for semi-structured data, many index types, full-text search, row-level security, and an extension ecosystem (PostGIS for geospatial data, pgvector for embeddings, and more). It is available everywhere as a managed service (Amazon RDS and Aurora, Google Cloud SQL, Azure, Supabase, Neon and others) and runs well on a laptop for development.

A powerful open-source relational database

PostgreSQL stores data in tables, enforces rules, and answers SQL queries reliably.

The demo schema used in this course

Customers, products with JSONB attributes, orders and order items, with keys and constraints.

CREATE TABLE customers (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  email   text NOT NULL UNIQUE,
  city    text
);
CREATE TABLE products (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  price   numeric(10,2) NOT NULL CHECK (price > 0),
  attrs   jsonb NOT NULL DEFAULT '{}'
);
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'new' CHECK (status IN ('new','paid','shipped','cancelled')),
  ordered_on  date NOT NULL
);
CREATE TABLE order_items (
  order_id   bigint REFERENCES orders(id) ON DELETE CASCADE,
  product_id bigint REFERENCES products(id),
  qty        int NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers (name, email, city) VALUES
  ('Asha', 'asha@example.com', 'Pune'), ('Ravi', 'ravi@example.com', 'Delhi'),
  ('Meera', 'meera@example.com', 'Pune'), ('John', 'john@example.com', NULL);
INSERT INTO products (name, price, attrs) VALUES
  ('Notebook', 120, '{"color": "blue", "pages": 200}'),
  ('Fountain pen', 899, '{"color": "black", "ink": "refillable"}'),
  ('Desk lamp', 1499, '{"color": "white", "watts": 9}');
INSERT INTO orders (customer_id, status, ordered_on) VALUES
  (1, 'paid', '2026-09-01'), (1, 'shipped', '2026-09-15'), (2, 'paid', '2026-09-10'),
  (3, 'new', '2026-09-20'), (2, 'cancelled', '2026-09-21');
INSERT INTO order_items VALUES (1,1,3),(1,2,1),(2,3,1),(3,1,10),(4,2,2),(5,3,1);

Start with constraints

Let the database enforce rules (NOT NULL, UNIQUE, CHECK, foreign keys); application code alone will eventually let bad data in.

त्वरित जाँच: Which PostgreSQL feature stores semi-structured documents efficiently?

• BLOB-only tables
• CSV columns
• JSONB
• Spreadsheet links