Stop 06 · SQL DatabasesTopics · 8/14

PostgreSQL

In short

PostgreSQL is free, open source and packed with features, from JSON columns to full-text search. Many teams pick it as their default database, which makes it a safe one to learn first.

A powerful, open-source relational database known for standards compliance, extensibility (JSON support, full-text search) and being the default choice for most new backend projects today.

The blob opens a huge multi-tier toolbox packed with tools.

Think of it like this

The giant toolbox

Some toolboxes have a hammer and two screwdrivers. Postgres is the one where every drawer has another surprise: a spirit level, a tiny saw, a stud finder. Tables, JSON, arrays, full-text search, all in one box.

Watch it happen

A few Postgres tricks

psql

Postgres

CREATE TABLE … JSONB

1/5Create a products table with a normal name column and a flexible JSONB specs column.

The big ideas

What you need to know

JSONB columns

Store flexible JSON next to normal columns, query inside it and index it. Half SQL, half document store.

RETURNING

INSERT, UPDATE and DELETE can send back the changed rows, saving a second query.

Full-text search built in

tsvector and tsquery give decent search for simple cases, without adding another service.

Extensions

Add features like PostGIS for locations or pgvector for AI embeddings with CREATE EXTENSION.

See it in code

Walk through the code

sql

1CREATE TABLE products (2  id    SERIAL PRIMARY KEY,3  name  TEXT NOT NULL UNIQUE,4  specs JSONB NOT NULL DEFAULT '{}'5);6 7INSERT INTO products (name, specs)8VALUES ('Phone X', '{"ram": "8GB", "5g": true}')9RETURNING id;10 11SELECT name FROM products WHERE specs @> '{"ram": "8GB"}';12 13CREATE INDEX idx_products_specs ON products USING GIN (specs);

1/4specs is JSONB: binary JSON that Postgres can search and index.

Use real columns for data every row has, and JSONB for the odd, changing extras.

Save yourself the pain

Common mistakes

Oops

Putting everything into one JSONB column because it feels like MongoDB.

Do this instead

Fields you filter, join or constrain belong in real columns. JSONB is for the genuinely variable bits.

Oops

Opening a new database connection for every request.

Do this instead

Use a pool, like pg's Pool or PgBouncer in front. Postgres connections are fairly heavy to create.

Your mission

Postgres in Docker

~20 min

Quick check

What does RETURNING id do on an INSERT?

Go deeper

1Install PostgreSQL locally and connect to it with a GUI client (e.g. TablePlus, pgAdmin).
2Practice PostgreSQL-specific features like JSONB columns and array types.
3Learn `psql`, the command-line client, for quick queries and debugging.