# PostgreSQL: A Practical Guide to the Database Engineers Agree On

Ask ten experienced engineers which database a new project should start with, and most of them will give the same one-word answer before you finish the question: Postgres.

That is unusual. Engineers rarely agree on anything. So what does a database first released in the 1990s do so well that it has become the default choice for startups, banks and hobby projects alike? And if you pick it, how do you use it properly?

This guide answers both questions, with commands you can run today.

## What PostgreSQL is

PostgreSQL (most people say "Postgres") is an open-source relational database. Relational means your data lives in tables with rows and columns, tables can refer to each other, and you ask questions in SQL.

It is free to use, including commercially, and it is not owned by any single company. A worldwide community develops it and ships one major version every year.

Three ideas explain most of its reputation:

- **Correctness first.** Postgres would rather reject bad data than store it quietly.
- **Standards.** It follows the SQL standard closely, so what you learn carries over to other databases.
- **Extensibility.** You can add new data types, index types and whole features without waiting for the core team.

## Why teams choose it

### It keeps your data safe

Postgres is fully ACID compliant. In plain words: a transaction either happens completely or not at all, and once the database says "saved", the data survives a crash.

It also takes your rules seriously. Declare a column `NOT NULL`, add a foreign key, or write a `CHECK` constraint, and the database enforces it on every write, from every application, forever.

```sql
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  status      text NOT NULL DEFAULT 'pending',
  created_at  timestamptz NOT NULL DEFAULT now()
);
```

No order can exist without a real customer, and no total can be negative. Bugs in application code cannot sneak past.

### It handles documents too

Many projects reach for a document database because part of their data has no fixed shape. Postgres has an answer for that: the `jsonb` type stores JSON in a binary form that you can query and index.

```sql
ALTER TABLE orders ADD COLUMN details jsonb NOT NULL DEFAULT '{}';

-- Orders that were gift wrapped
SELECT id, details->>'gift_note' AS note
FROM orders
WHERE details @> '{"gift_wrap": true}';

-- Make that search fast
CREATE INDEX orders_details_idx ON orders USING gin (details);
```

You get structured tables where structure helps, and flexible JSON where it does not, in one database with one backup.

### Extensions turn it into other databases

This is the feature that makes Postgres hard to outgrow. An extension is a plug-in you switch on with one command:

| Extension | What it adds |
|---|---|
| PostGIS | Maps and location queries ("shops within 2 km") |
| pgvector | Vector search for AI features such as semantic search |
| TimescaleDB | Fast storage and queries for time-series data |
| pg_trgm | Fuzzy text matching, tolerant of typos |
| pg_stat_statements | A record of which queries use the most time |

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

Before adding a second database for search, locations or embeddings, check whether an extension covers it. One system is far easier to run than two.

### Readers never block writers

Postgres uses a design called MVCC (multi-version concurrency control). Each transaction sees a consistent snapshot of the data, so a long report does not stop customers from placing orders, and the other way round.

## Where PostgreSQL fits, and where it does not

**A strong choice for:**

- Web and mobile application backends
- Financial and order data, where correctness matters most
- Software-as-a-service products with many customers
- Location data, with PostGIS
- Reporting on small and medium data sets
- AI features that need vector search next to ordinary data

**Look elsewhere when:**

- **You analyse billions of rows for dashboards.** A column store such as ClickHouse, BigQuery or Snowflake is built for that job.
- **You need a sub-millisecond cache.** Use Redis next to Postgres, not instead of it.
- **You need writes accepted in many regions at once.** Consider a distributed SQL database such as CockroachDB.
- **The database must live inside a mobile or desktop app.** That is SQLite's home ground.

For nearly everything else, "start with Postgres and move only when you have a measured reason" is sound advice.

## Using it the right way

### Choose good types

- Use `timestamptz` for moments in time, not `timestamp`. It stores an exact instant and converts to each user's time zone.
- Use `text` for strings. In Postgres there is no speed benefit to `varchar(255)`.
- Use `numeric` for money, or store whole cents in an `integer`. Never use `float` for money, because it rounds.
- Use `bigint` identity columns or UUIDs for primary keys.

### Index what you filter and join on

An index is to a table what the index at the back of a book is to its pages. Without one, Postgres reads every row.

```sql
-- Speeds up "all orders for this customer, newest first"
CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);
```

`CONCURRENTLY` builds the index without blocking writes, which matters on a live system. Note that Postgres does not create indexes on foreign key columns for you, so add them yourself.

Do not index everything, though. Each index slows down writes and takes disk space.

### Ask the database how it runs your query

When a query is slow, do not guess. Ask:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

Read the output from the innermost line outward. A `Seq Scan` on a large table usually means a missing index. A big gap between estimated and actual row counts usually means statistics are out of date, which `ANALYZE orders;` fixes.

### Pool your connections

Each Postgres connection is a full operating system process and uses real memory. A few hundred is fine. A few thousand is not.

If your application opens many short connections, which serverless functions tend to do, put a pooler such as PgBouncer in front, or use the pooled connection string your cloud provider offers.

### Let autovacuum do its job

Because of MVCC, updated and deleted rows leave old versions behind. A background process called autovacuum cleans them up. Leave it switched on. If one busy table grows faster than it is cleaned, tune autovacuum for that table instead of disabling it.

### Give every application its own role

Do not let your application connect as the `postgres` superuser. Create a role with only the permissions it needs:

```sql
CREATE ROLE app_user LOGIN PASSWORD 'use-a-long-random-one';
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
```

A separate read-only role for reporting tools is a cheap and effective safety net.

### Back up, then prove the backup works

`pg_dump` is enough for small databases:

```bash
pg_dump --format=custom --file=shop.dump "postgresql://user@host/shop"
pg_restore --dbname=shop_restored shop.dump
```

For anything that matters, add continuous archiving so you can restore to any point in time. Most managed services do this for you. Whichever method you use, restore a backup on a schedule. A backup you have never restored is only a hope.

## Common mistakes

- **Running as superuser everywhere.** One leaked password then exposes everything.
- **`SELECT *` in application code.** It breaks when columns change and moves data you do not need.
- **Forgetting indexes on foreign keys.** Deletes and joins on the parent table become slow.
- **A transaction left open.** An idle transaction blocks cleanup and can hold locks for hours. Set `idle_in_transaction_session_timeout`.
- **Skipping major upgrades for years.** Each major version is supported for about five years. Plan the upgrade before support ends, not after.
- **A giant `UPDATE` in one statement.** Change large tables in batches so you do not lock out everyone else.

## Quick reference

| Task | Command |
|---|---|
| Connect | `psql "postgresql://user@host:5432/dbname"` |
| List tables | `\dt` in psql |
| Describe a table | `\d orders` |
| See running queries | `SELECT pid, state, query FROM pg_stat_activity;` |
| Stop a query | `SELECT pg_cancel_backend(pid);` |
| Database size | `SELECT pg_size_pretty(pg_database_size(current_database()));` |
| Refresh statistics | `ANALYZE;` |

PostgreSQL rewards the time you put into learning it. The habits above, good types, the right indexes, small roles and tested backups, will carry a project from its first user to its millionth.

## Exploring PostgreSQL with CreamDB

Everything in this guide can be done from a terminal. When you would rather see your database than type at it, [CreamDB](https://www.creamdb.com) gives you a PostgreSQL workspace in a browser tab, with nothing to install.

- Browse schemas, tables, views, functions and indexes in a tree, and open any table in a grid with paging, sorting and filtering that run in the database.
- Write SQL in an editor with autocomplete, formatting and history, and export results to CSV, JSON or Excel.
- See your schema as an ER diagram, watch live sessions in the Status panel, and review users and roles.
- Ask in plain English, for example "which customers ordered more than three times this month?", and get the SQL, the result and a chart.

The assistant only reads on its own. Any change waits for your approval, and read-only mode blocks writes completely. A private database can be reached through an SSH tunnel.

It is free to start, and the same tab also holds your MySQL, MongoDB, Redis and 40+ other connections.

