Skip to main content

PostgreSQL

Overview​

PostgreSQL is an advanced open-source relational database with ACID transactions, rich SQL, JSON/JSONB, full-text search, extensions (PostGIS, pgvector), and strong indexing options (B-tree, GiST, GIN, BRIN). It is a default choice for web backends requiring complex queries and data integrity.

Key concepts​

  • Schemas & tables — Namespace organization; migrations manage evolution.
  • Constraints — PK, FK, unique, check, not null.
  • Indexes — Match access patterns; avoid over-indexing writes.
  • Transactions — Isolation levels, BEGIN/COMMIT, deadlock retries.
  • Extensions — CREATE EXTENSION for optional features.

Query execution (simplified)​

Sample: schema + query​

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

SELECT id, email
FROM users
WHERE created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

References​