What Is PostgreSQL (Postgres)? A Beginner's Guide

PostgreSQL (pronounced "post-gress-Q-L", almost always shortened to Postgres) is a free, open-source relational database. It stores your app's data in tables — users, orders, posts — and lets you query it with SQL. (What is a database?)

If you've built an app with an AI tool recently, there's a good chance it uses Postgres: Supabase, Neon and most hosting platforms are built on it.

A quick history

Postgres grew out of a research project at the University of California, Berkeley in the 1980s and has been developed in the open ever since by a global community. It isn't owned by any one company, which is part of why so many companies build on it. A new major version comes out every year.

What "relational" means

Data lives in tables with columns and rows, and tables connect through keys:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint REFERENCES users(id),
  total numeric(10,2) NOT NULL
);

Each order points at a user. The database guarantees that relationship stays valid. (Primary key vs foreign key, Database relationships)

Why developers like it

Reliable. Your data is safe even if the server crashes mid-write. (Database transactions)
Strict. It refuses data that breaks your rules — wrong types, missing required fields, duplicates where you said unique — instead of silently accepting it.
Feature-rich. JSON columns, full-text search, geographic data, arrays, and extensions like pgvector for AI embeddings. (Postgres JSONB, Full-text search, pgvector)
Row-level security — rules in the database itself about who can see which rows. This is what Supabase builds on. (Supabase RLS)
Free, with no lock-in. The same database runs on your laptop, a $5 server or a giant cloud.

Postgres vs the alternatives

MySQL — also popular and reliable; Postgres has more features and stricter defaults. (Postgres vs MySQL)
SQLite — a database in a single file, great for small apps and local tools. (Is SQLite good enough for production?)
MongoDB — stores flexible documents instead of tables. (What is MongoDB?, SQL vs NoSQL)

For a typical web app, Postgres is the safe default.

How your app connects

Your app connects with a connection string:

postgresql://app_user:password@db.example.com:5432/myapp

It contains the user, password, host, port (5432 by default) and database name. Keep it in an environment variable, never in code. (Postgres connection strings)

Most apps talk to Postgres through an ORM like Prisma or Drizzle rather than writing SQL by hand. (What is an ORM?)

Looking inside

Tools for browsing your data: psql (the command line), pgAdmin, DBeaver, TablePlus, or your platform's dashboard. (How to view your Postgres database)

Where to run it

Managed — Supabase, Neon, AWS RDS and others run it for you, with backups. (Neon vs Supabase)
On your own server — install it next to your app. Cheaper and simple, but backups and updates are your job. (Postgres backup and restore)
Locally — with Docker for development. (Docker Compose for local development)

Errors you'll probably meet

ECONNREFUSED 127.0.0.1:5432 — can't connect
password authentication failed
relation does not exist
duplicate key value violates unique constraint

EasySpawn gives every app its own PostgreSQL database on your server, with daily backups and Claude Code ready to write queries and migrations for you. See how it works or join the waitlist.

What Is PostgreSQL? The Database Behind Most New Apps, Explained