PostgreSQL

What is it?

PostgreSQL is an open-source relational database management system (RDBMS) with over 35 years of active development. It is known for its robustness, SQL standard compliance, and a rich extension ecosystem that pushes its capabilities well beyond classic relational storage. It is free software under the PostgreSQL License (similar to BSD/MIT).

What is it used for?

• Applications and OLTP. Stores transactional data for web apps, APIs, and business systems. Supports ACID transactions, foreign keys, advanced indexes, and rich data types (JSON, arrays, geometries via PostGIS).
• Analytics with extensions. Tools like TimescaleDB (time-series) or columnar storage extensions let PostgreSQL handle moderate analytical workloads entirely within SQL.
• Source for data pipelines. One of the most common origins in modern data stacks: dbt uses it as a transformation target, tools like Airbyte replicate it, and Trino federates it alongside other sources.

When to use it / when not to?

Use it when:

• you need ACID transactional consistency and well-defined relationships between entities,
• your application already speaks SQL and you want a solid backend without managing distributed infrastructure,
• you need a reliable destination for dbt transformations or ETL pipeline loads.

Think twice when:

• your workload is pure analytics on files (Parquet, CSV, S3) — DuckDB is faster for that and needs no server,
• you need distributed scale (petabytes, massively parallel queries) — that's where Trino, BigQuery, or similar tools fit,
• access is mostly read-heavy on historical data — a columnar data warehouse will be significantly more efficient.

Start in 1 minute

Spin up a local instance with Docker:

docker run --name pg-demo \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -d postgres:16

Connect and run a quick test:

docker exec -it pg-demo psql -U postgres

-- Create a sample table
CREATE TABLE tools (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  layer  TEXT
);

INSERT INTO tools (name, layer)
VALUES ('PostgreSQL', 'storage'),
       ('dbt', 'transformation'),
       ('Trino', 'query');

SELECT name, layer FROM tools ORDER BY name;

To go deeper, head straight to the official documentation.