F-1 — What Is PostgreSQL and Why Does It Exist?

The Problem Databases Solve

Before talking about PostgreSQL specifically, it helps to understand the problem it solves.

Imagine you are building a to-do app. You store your tasks in a JavaScript array:

javascript
const tasks = [
  { id: 1, title: 'Buy groceries', done: false },
  { id: 2, title: 'Pay rent', done: true },
];

This works while the application is running. The moment it stops — the server restarts, the user closes the tab, the process crashes — that array disappears. Everything is gone.

The first problem databases solve: persistence. Data that outlives the program that created it.

Now imagine your app has 100,000 users. You could store all their tasks in a single file, but:

• Reading one user's tasks means reading the entire file
• Two users saving at the same moment could corrupt the file
• Searching for all overdue tasks means scanning every single record

The second problem databases solve: efficient access at scale. Finding, filtering, and sorting millions of records quickly without reading everything.

Now imagine your team has five engineers, and three of them are updating user data simultaneously from different services. One engineer reads a user's balance as $500, another reads it as $500 at the same moment, and they both try to debit $300. The user ends up $100 short because neither knew the other was also writing.

The third problem databases solve: concurrent access without corruption. Multiple readers and writers operating simultaneously without stepping on each other.

PostgreSQL solves all three of these problems. It is a database management system — a piece of software that stores your data durably, retrieves it efficiently, and manages concurrent access safely.

Why PostgreSQL Specifically?

There are many database systems. Here is the honest comparison for someone just starting:

SQLite — a database that lives in a single file. Perfect for mobile apps, local development, or small embedded applications. Not designed for multiple users or high traffic. This is often what tutorials use because it requires zero setup.

MySQL / MariaDB — PostgreSQL's closest competitor. Both are production-grade relational databases. MySQL has a longer history in web development (it powered most early PHP applications). PostgreSQL has historically been more standards-compliant and feature-rich. In practice, both are excellent choices.

MongoDB — a document database. Instead of rows and columns, it stores JSON-like documents. Flexible, but trades the ability to do complex queries and guarantee data consistency for that flexibility. Many engineers start with MongoDB because it feels more like JavaScript objects, then discover PostgreSQL later.

PostgreSQL — what this course teaches. Open-source, free, battle-tested at petabyte scale, and the database that serious backend engineers tend to gravitate toward because of its combination of reliability, features, and performance.

The practical answer for 2025: if you are building a backend application and don't have a strong reason to use something else, use PostgreSQL. It handles everything from a side project with 10 users to production systems with billions of rows.

The Client-Server Model

PostgreSQL operates as a server. It runs as a separate process on your machine (or a remote machine), listening for connections on a port (default: 5432).

Your application — or psql, the command-line tool — is the client. It connects to the PostgreSQL server, sends queries, and receives results.

Your Application (client)
         │
         │  sends: "SELECT * FROM users WHERE id = 1"
         ▼
PostgreSQL Server (port 5432)
         │
         │  returns: { id: 1, name: 'Alice', email: 'alice@example.com' }
         ▼
Your Application (client)

This separation matters because:

• The database server handles all the hard work: storing data on disk, managing memory, handling concurrent requests
• Your application only needs to send SQL and handle results
• Multiple applications can connect to the same database server simultaneously

When you deploy a real application, the database server typically runs on a separate machine from your application server. They communicate over a network. This is why "connecting to the database" involves a host, port, username, and password.

Installing PostgreSQL

Option 1: macOS with Homebrew (recommended for local development)

bash
# Install Homebrew if you don't have it
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install PostgreSQL (installs the latest stable version)
brew install postgresql@17

# Start the PostgreSQL service (runs automatically in the background)
brew services start postgresql@17

# Verify it's running
brew services list | grep postgresql
# → postgresql@17   started  your-username  ~/Library/LaunchAgents/...

Option 2: Linux (Ubuntu / Debian)

bash
# Add the official PostgreSQL apt repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install PostgreSQL 17
sudo apt install -y postgresql-17

# Start the service
sudo systemctl start postgresql
sudo systemctl enable postgresql  # start automatically on boot

# Verify it's running
sudo systemctl status postgresql

Option 3: Docker (works identically on macOS, Linux, and Windows)

bash
# Pull and run a PostgreSQL container
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mypassword \
  -p 5432:5432 \
  -d postgres:17

# Verify it's running
docker ps | grep postgres

Option 4: Windows

Download the installer from postgresql.org/download/windows and run it. The installer includes pgAdmin (a graphical interface) and adds PostgreSQL to your system.

Verifying Your Installation

Regardless of how you installed PostgreSQL, verify it works:

bash
# Connect to the PostgreSQL server using psql
psql -U postgres

# If you used Homebrew on macOS, your username is your macOS username:
psql -U $(whoami)

# If you used Docker:
docker exec -it my-postgres psql -U postgres

If PostgreSQL is running and your credentials are correct, you will see a prompt like this:

psql (17.0)
Type "help" for help.

postgres=#

That postgres=# is the psql prompt. You are now connected to the default postgres database as the postgres superuser. You can type SQL queries here.

To exit: type \q and press Enter.

psql: The Command You Will Use Every Day

psql is PostgreSQL's interactive command-line client. Every PostgreSQL engineer uses it constantly. Here are the commands you must know:

Connecting

bash
# Basic connection (prompts for password if required)
psql -h localhost -p 5432 -U postgres -d mydb

# Connection string format (common in tutorials and documentation)
psql postgresql://username:password@localhost:5432/mydb

# Once connected, switch to a different database
\c mydb

Navigation commands (these start with backslash — they are not SQL)

sql
\l              -- list all databases
\c dbname       -- connect to a database
\dt             -- list all tables in the current database
\d tablename    -- describe a table (columns, types, indexes, constraints)
\di             -- list all indexes
\dn             -- list all schemas
\du             -- list all users/roles
\timing         -- toggle query execution time display
\e              -- open queries in your default text editor
\q              -- quit psql
\?              -- help: list all backslash commands
\h SELECT       -- help on a specific SQL command

Useful settings for your psql session

sql
-- Make output easier to read for wide tables
\x              -- toggle expanded display (vertical format)

-- Turn on query timing
\timing on

-- Show the current database and user
SELECT current_database(), current_user;

Creating Your First Database

Now that you are in psql, create a database:

sql
-- Create a new database
CREATE DATABASE learning_postgres;

-- Verify it was created
\l

-- Connect to it
\c learning_postgres

-- You should see:
-- You are now connected to database "learning_postgres" as user "postgres".

Now create your first table inside this database:

sql
-- A simple table to store people
CREATE TABLE people (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  age  INTEGER
);

-- Verify the table exists
\dt

-- See its structure
\d people

Insert some data:

sql
INSERT INTO people (name, age) VALUES ('Alice', 30);
INSERT INTO people (name, age) VALUES ('Bob', 25);
INSERT INTO people (name, age) VALUES ('Carol', 35);

Read it back:

sql
SELECT * FROM people;

--  id | name  | age
-- ----+-------+-----
--   1 | Alice |  30
--   2 | Bob   |  25
--   3 | Carol |  35
-- (3 rows)

Congratulations — you have just:

1. Installed PostgreSQL
2. Connected to it with psql
3. Created a database
4. Created a table
5. Inserted rows
6. Read them back

This is the foundation every more complex operation is built on.

What the PostgreSQL Server Is Actually Doing

When you run CREATE DATABASE learning_postgres, PostgreSQL creates a directory on your filesystem to store that database's data. When you run INSERT, it writes that row to a file on disk and records the write in a log (for crash safety). When you run SELECT, it reads from those files and returns the matching rows.

You do not need to know the details yet — they are the subject of the advanced modules later in this course. But knowing that PostgreSQL is a process that reads and writes files on disk demystifies what feels like magic.

When you see this error:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
No such file or directory

It means the PostgreSQL server process is not running. Start it:

bash
# macOS Homebrew
brew services start postgresql@17

# Linux
sudo systemctl start postgresql

# Docker
docker start my-postgres

When you see this error:

FATAL: password authentication failed for user "postgres"

It means you are using the wrong password. Reset it:

bash
# macOS Homebrew — connect without password (trust auth)
psql -U $(whoami) postgres

# Then set a password
ALTER USER postgres PASSWORD 'newpassword';

A Note on postgres, Users, and Security

Out of the box, PostgreSQL has a superuser called postgres (or your macOS username on Homebrew). In development, it is fine to use this directly. In production, you should never connect your application as a superuser.

We will cover roles and access control properly in Module P-6. For now, use the default superuser to learn. When you deploy, we will create a dedicated user with only the permissions your application needs.

What's Next

You now have a working PostgreSQL installation and know how to:

• Start and stop the PostgreSQL server
• Connect with psql
• Create a database and a table
• Insert and read rows
• Use the essential psql commands

Module F-2 covers the relational mental model in depth — tables, rows, columns, data types, and every fundamental SQL operation from CREATE TABLE through DELETE. Everything from this module carries forward.

Next: F-2 — Tables, Rows, and the Relational Mental Model →