Skip to main content

PostgreSQL: A Practical Guide to the Database Engineers Agree On

What Postgres is good at, when to pick something else, and the habits that keep it fast and safe

Updated 9 min readMarkdown
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.

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.

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
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.

-- 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:

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:

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:

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 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.

About the author

  • CreamDB is a web-based database explorer with an AI assistant. Connect your databases, ask questions in plain English and get answers, tables and charts from any browser. Product news and tips here.

The section on letting the database enforce the rules, not only the application, is a useful starting point. A NOT NULL, a foreign key, or a CHECK constraint will still apply when a new path writes to the same table. I also appreciated the habit of proving a backup actually restores, rather than only confirming that a backup job ran. The "where it does not fit" part is just as valuable as the praise. Knowing when to pick something else keeps this from being a one-tool answer.