Introduction to PostgreSQL

PostgreSQL (often called "Postgres") is a powerful, open-source object-relational database system with over 35 years of active development. It has earned a strong reputation for reliability, feature richness, and performance — and it is the go-to choice for developers who need a production-grade database that won't cost a fortune.

A Brief History

PostgreSQL began in 1986 at the University of California, Berkeley, as the POSTGRES project led by Professor Michael Stonebraker. It was later renamed PostgreSQL to reflect its support for the SQL language. The first open-source release was in 1996, and the project has been maintained by the PostgreSQL Global Development Group ever since.

Why Use PostgreSQL?

vs MySQL

vs Oracle

PostgreSQL is free and open source. Oracle charges per core. PostgreSQL's syntax is close enough to Oracle that migration is feasible, and tools like ora2pg exist specifically for this. PostgreSQL lacks a few Oracle-specific features (like advanced partitioning in older versions), but modern PostgreSQL has closed most of those gaps.

vs SQLite

SQLite is embedded and serverless — great for mobile apps and local development. PostgreSQL is a full client-server database designed for concurrent multi-user access, network connections, and terabytes of data. Use SQLite for a phone app; use PostgreSQL for a web service.

Core Features

ACID Compliance

Every transaction in PostgreSQL is guaranteed to be:

• Atomic — either all changes commit or none do
• Consistent — the database moves from one valid state to another
• Isolated — concurrent transactions don't interfere with each other
• Durable — committed data survives crashes (written to WAL)

MVCC — Multi-Version Concurrency Control

PostgreSQL uses MVCC to handle concurrent reads and writes without locking readers. When you update a row, PostgreSQL creates a new version of that row rather than overwriting the old one. Readers see a consistent snapshot from when their transaction started, and writers don't block readers.

This is a fundamental architectural advantage — it means SELECT queries never block INSERT/UPDATE/DELETE operations, and vice versa.

Extensibility

PostgreSQL can be extended in ways most databases cannot:

• Custom data types — create your own types
• Custom functions — write functions in SQL, PL/pgSQL, Python, Perl, C, and more
• Custom operators — define operators like @> or <@
• Extensions — installable packages that add major functionality

-- Install the PostGIS extension for geographic data
CREATE EXTENSION postgis;

-- Install pg_trgm for trigram similarity search
CREATE EXTENSION pg_trgm;

-- Install uuid-ossp for UUID generation
CREATE EXTENSION "uuid-ossp";

Rich Data Types

PostgreSQL supports far more data types than most databases out of the box:

• Standard SQL types (INTEGER, TEXT, NUMERIC, BOOLEAN, DATE, TIMESTAMP...)
• JSONB — binary JSON with full indexing support
• Arrays — columns that hold multiple values
• Network addresses — INET, CIDR, MACADDR
• UUID — universally unique identifiers
• Full-text search — TSVECTOR, TSQUERY
• Geometric types — POINT, LINE, CIRCLE, POLYGON
• Ranges — DATERANGE, TSRANGE, INT4RANGE
• Enumerated types — custom ordered sets of values

Who Uses PostgreSQL?

PostgreSQL powers some of the world's largest applications:

• Apple — uses PostgreSQL for various services
• Reddit — migrated from MySQL to PostgreSQL in 2013
• Instagram — runs on PostgreSQL at massive scale
• Spotify — uses PostgreSQL for their data platform
• Twitch — uses PostgreSQL for their backend
• NASA — for scientific data management
• GitHub — uses PostgreSQL alongside MySQL
• Heroku — made PostgreSQL the default database for its platform

Licensing

PostgreSQL is released under the PostgreSQL License, a permissive open-source license similar to the MIT license. You can use it for free in commercial products without paying royalties, without publishing your source code, and without restrictions. This is a major advantage over MySQL (GPL or commercial) and Oracle (strictly commercial).

Installing PostgreSQL

# Ubuntu / Debian
sudo apt install postgresql postgresql-contrib

# macOS (via Homebrew)
brew install postgresql@16

# Windows
# Download the installer from https://www.postgresql.org/download/

After installation, the server starts automatically. The default superuser is postgres:

# Switch to the postgres system user and open psql
sudo -u postgres psql

# Or connect directly
psql -U postgres -h localhost

Creating Your First Database

-- Create a database
CREATE DATABASE myapp;

-- Connect to it
\c myapp

-- Create a table
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   TEXT NOT NULL UNIQUE,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert some data
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob',   'bob@example.com');

-- Query it
SELECT * FROM users;

Result:

PostgreSQL vs Other Databases — Decision Guide

Version Overview

Run this to check your PostgreSQL version:

SELECT version();
-- PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc ...

SHOW server_version;
-- 16.1

The PostgreSQL project releases one major version per year (e.g., 16, 17) with minor bugfix releases throughout the year (e.g., 16.1, 16.2). Major versions are supported for 5 years.