Introduction to PostgreSQL

What is PostgreSQL?

PostgreSQL (often called Postgres) is an open-source, object-relational database management system that has been actively developed for over 35 years. It began as the POSTGRES project at UC Berkeley in 1986 and has since become one of the most advanced, standards-compliant, and feature-rich databases available. Companies of every size — from early-stage startups to Fortune 500 enterprises — use Postgres to power everything from simple CRUD applications to data warehouses processing billions of rows.

Unlike simpler databases such as SQLite, Postgres is designed for multi-user, multi-process workloads. It handles concurrent reads and writes safely using a technique called Multi-Version Concurrency Control (MVCC), which means readers never block writers and vice versa.

Core Concepts at a Glance

Postgres organizes data hierarchically: a server runs one or more databases, each database contains schemas (namespaces), and schemas contain tables, views, functions, and other objects. The default schema is called public.

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

INSERT INTO users (name, email)
VALUES (‘Ada Lovelace’, ‘ada@example.com’),
       (‘Alan Turing’,  ‘alan@example.com’);

-- Basic SELECT with filtering and sorting
SELECT id, name
FROM   users
WHERE  email LIKE ‘%@example.com’
ORDER  BY created_at DESC
LIMIT  10;

Postgres vs. Other Databases

• vs. MySQL/MariaDB: Postgres has stricter SQL standards compliance, better support for complex queries, and native JSONB. MySQL has a larger legacy install base.
• vs. SQLite: SQLite is file-based and great for embedded or local apps; Postgres is networked and built for concurrent access at scale.
• vs. MongoDB: Postgres stores structured rows but also supports flexible JSON via JSONB, giving you the best of both worlds without sacrificing ACID.
• vs. Amazon RDS/Aurora: Those are hosted Postgres-compatible services — you still write the same SQL, just without managing the server yourself.