Who this module is for: You have never used PostgreSQL before, or you have used it briefly but never understood what it actually is under the hood. You might be a bootcamp graduate, a frontend engineer expanding into backend work, or a developer who has only ever used MongoDB or Firebase. This module assumes nothing. By the end, you will have a working PostgreSQL installation, a mental model of how it operates, and your first real database.
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
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.
text
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
Option 2: Linux (Ubuntu / Debian)
bash
Option 3: Docker (works identically on macOS, Linux, and Windows)
bash
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
If PostgreSQL is running and your credentials are correct, you will see a prompt like this:
text
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
Navigation commands (these start with backslash — they are not SQL)
sql
Useful settings for your psql session
sql
Creating Your First Database
Now that you are in psql, create a database:
sql
Now create your first table inside this database:
sql
Insert some data:
sql
Read it back:
sql
Congratulations — you have just:
Installed PostgreSQL
Connected to it with psql
Created a database
Created a table
Inserted rows
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:
text
It means the PostgreSQL server process is not running. Start it:
bash
When you see this error:
FATAL: password authentication failed for user "postgres"
It means you are using the wrong password. Reset it:
bash
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 →
Knowledge Check
A Senior Principal Software Engineer is designing a highly available, scalable backend application. They decide to deploy the PostgreSQL server on a separate machine from the application servers. What is the primary architectural advantage of this separation in a production environment?
During a critical production incident, an application fails to connect to its PostgreSQL database, reporting the error: "psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory". As a Senior Principal Software Engineer, what is the most immediate and fundamental cause you would investigate based on PostgreSQL's operational model?
A development team initially chose MongoDB for a new project due to its flexibility with JSON-like documents. However, after several months, they encounter significant challenges with data integrity and complex analytical queries involving multiple related data points. Based on the lesson's comparison, what fundamental architectural difference in PostgreSQL would address these challenges more effectively?
Test your knowledge with more question sets
Sign in to access a wider variety of questions and get notified when new practice sets are added to this module.
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)
# Install Homebrew if you don't have it/bin/bash -c"$(curl-fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"# Install PostgreSQL (replace 17 with whatever the current major version is)brew install postgresql@17
# Start the PostgreSQL service (runs automatically in the background)brew services start postgresql@17
# Verify it's runningbrew services list |grep postgresql
# → postgresql@17 started your-username ~/Library/LaunchAgents/...
# Add the official PostgreSQL apt repositorysudoaptinstall-y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# Install PostgreSQL 17sudoaptinstall-y postgresql-17
# Start the servicesudo systemctl start postgresql
sudo systemctl enable postgresql # start automatically on boot# Verify it's runningsudo systemctl status postgresql
# Pull and run a PostgreSQL containerdocker run --name my-postgres \-ePOSTGRES_PASSWORD=mypassword \-p5432:5432 \-d postgres:17
# Verify it's runningdockerps|grep postgres
# Connect to the PostgreSQL server using psqlpsql -U postgres
# If you used Homebrew on macOS, your username is your macOS username:psql -U$(whoami)# If you used Docker:dockerexec-it my-postgres psql -U postgres
psql (17.0)
Type "help" for help.
postgres=#
# Basic connection (prompts for password if required)psql -h localhost -p5432-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
\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
-- 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 userSELECT current_database(),current_user;
-- Create a new databaseCREATEDATABASE 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".
-- A simple table to store peopleCREATETABLE people ( id SERIALPRIMARYKEY, name TEXTNOTNULL, age INTEGER);-- Verify the table exists\dt
-- See its structure\d people
INSERTINTO people (name, age)VALUES('Alice',30);INSERTINTO people (name, age)VALUES('Bob',25);INSERTINTO people (name, age)VALUES('Carol',35);
SELECT*FROM people;-- id | name | age-- ----+-------+------- 1 | Alice | 30-- 2 | Bob | 25-- 3 | Carol | 35-- (3 rows)
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
No such file or directory
# macOS Homebrew — connect without password (trust auth)psql -U$(whoami) postgres
# Then set a password (Homebrew's initdb creates a superuser role named after# your OS user, not a role literally called "postgres")ALTER USER CURRENT_USER PASSWORD 'newpassword';