PostgreSQL

What it is

PostgreSQL is a mature, open-source relational database that stores data in typed tables with strong support for SQL, transactions, and data integrity. It is known for correctness, extensibility, and rich features like JSONB, full-text search, and window functions. It is the default choice for most application backends that need reliable structured data.

Strengths

• ACID transactions and strong consistency by default.
• Powerful SQL: joins, CTEs, window functions, and aggregates.
• Flexible types including JSONB, arrays, ranges, and custom types.
• Extensions like PostGIS (geospatial) and pgvector (embeddings).
• Strong constraints, foreign keys, and check rules enforce data quality.

Trade-offs

• Vertical scaling is easy; horizontal sharding requires extra tooling.
• Requires schema migrations as your model evolves.
• Heavier to operate than a single-file database for tiny projects.
• Connection-per-client model needs a pooler (PgBouncer) at high scale.

When to use it

Reach for PostgreSQL as your default for almost any app with relational data, transactions, or reporting needs: SaaS backends, e-commerce, analytics, and anything that benefits from constraints and joins.

Vibe coding fit

When directing AI, give it the entities and their relationships, then ask it to produce a normalized schema with primary keys, foreign keys, NOT NULL constraints, and sensible indexes. Insist on parameterized queries (never string-concatenated SQL) to prevent injection, and ask the AI to wrap multi-step writes in a transaction. A good tip: tell the AI to also generate a reversible migration file and to explain each index it adds, so you understand the read/write trade-offs before applying them.

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id  BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total    NUMERIC(10,2) NOT NULL CHECK (total >= 0),
  status   TEXT NOT NULL DEFAULT 'pending'
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Parameterized query (placeholders, not concatenation)
SELECT o.id, o.total
FROM orders o
WHERE o.user_id = $1 AND o.status = $2;