What makes PostgreSQL special?

PostgreSQL (often called "Postgres") has been around since 1986, making it one of the most mature open-source databases. But don't let its age fool you, it's packed with modern features that rival commercial databases costing thousands of dollars.

Think of SQLite as a bicycle, perfect for learning and small projects. PostgreSQL is a high-performance car. It requires more setup, but it handles heavy loads, complex queries, and concurrent users without breaking a sweat.

Rock-solid reliability

PostgreSQL follows ACIDWhat is acid?Four guarantees a database makes about transactions: changes are all-or-nothing, data stays valid, concurrent users don't interfere, and saved data survives crashes. Ask AI for more principles religiously:

• Atomicity: Transactions either complete fully or not at all
• Consistency: Your data always follows defined rules
• Isolation: Concurrent operations don't interfere with each other
• Durability: Once committed, data survives crashes

This isn't just theory. Banks, healthcare systems, and governments trust PostgreSQL with their most critical data.

Rich data types

While basic SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more databases give you integers and strings, PostgreSQL offers a type system that matches how modern apps actually work:

-- Standard types (these work everywhere)
id INTEGER,
name TEXT,
is_active BOOLEAN,
created_at TIMESTAMP

-- PostgreSQL specialties
tags TEXT[],                          -- Array of strings
settings JSONB,                       -- Binary JSON (indexable!)
location GEOGRAPHY(POINT, 4326),     -- Geospatial coordinates
search_vector TSVECTOR               -- For full-text search

Extensible superpowers

PostgreSQL's extension system lets you add new capabilities without changing core code:

-- Enable an extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Now you can use UUIDs
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL
);

Installing PostgreSQL

You have several options depending on your operating system and preferences.

macOS with Homebrew

The easiest way on macOS:

# Install PostgreSQL 15
brew install postgresql@15

# Start the service
brew services start postgresql@15

# Create a database matching your username
createdb $(whoami)

Now you can connect with just psql, no password needed for local development.

Linux (Ubuntu/Debian)

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# Start the service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Set a password for the postgres user
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'your_password';"

DockerWhat is docker?A tool that packages your application and all its dependencies into a portable container that runs identically on any machine. Ask AI for more (recommended for consistent environments)

Docker gives you a clean, isolated PostgreSQL instance:

docker run --name postgres-dev \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  -d postgres:15

This command:

• Names the containerWhat is container?A lightweight, portable package that bundles your application code with all its dependencies so it runs identically on any machine. Ask AI for more "postgres-dev"
• Sets the password to "password"
• Creates a database called "myapp"
• Exposes port 5432 for connections
• Persists data in a Docker volume
• Runs in detached mode

Your first PostgreSQL sessionWhat is session?A server-side record that tracks a logged-in user. The browser holds only a session ID in a cookie, and the server looks up the full data on each request. Ask AI for more

Once installed, connect to your database:

# Connect to default database
psql

# Or connect to a specific database
psql -d myapp

# Or with full connection string
psql postgres://user:password@localhost:5432/myapp

Try these commands to get oriented:

-- List all databases
\\l

-- Connect to a database
\\c myapp

-- List all tables
\\dt

-- Describe a table
\\d users

-- Get help
\\h

-- Quit
\\q

PostgreSQL vs other databases

Wondering why you'd choose PostgreSQL over alternatives?

Quick reference: PostgreSQL basics

PostgreSQL isn't just a database, it's a foundation. Once you understand it, you can handle data storage for almost any application you'll ever build.