PostgreSQL (Postgres)

PostgreSQL (Postgres) is a popular SQL database used in many production web backends.

It’s often chosen because it’s:

• Correct and reliable (strong consistency + transactions)
• Flexible (powerful SQL + jsonb + extensions)
• Scalable (good indexing, replication options, mature tooling)

Tables, Relations, and Foreign Keys

Relational databases model data as tables and relationships.

Key idea: foreign keys connect rows across tables and protect integrity.

• A posts.user_id value must match an existing users.id.
• If you delete a user, you decide what happens to their posts (block deletion, cascade delete, set null, etc.).

Constraints (Data Integrity)

Constraints prevent invalid data from entering your database.

Common examples:

• PRIMARY KEY: unique identifier
• UNIQUE: prevent duplicates (e.g., email)
• NOT NULL: required field
• CHECK: enforce a rule (e.g., price >= 0)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

Indexes (Performance)

Indexes speed up lookups (at the cost of storage and slower writes).

Typical indexes:

• users.email for login / lookup
• posts.user_id for “all posts by a user”

CREATE INDEX idx_posts_user_id ON posts(user_id);

Joins (Querying Across Tables)

Joins let you combine related rows.

Example: get posts with author name

SELECT p.id, p.title, u.name AS author
FROM posts p
JOIN users u ON u.id = p.user_id
ORDER BY p.id DESC
LIMIT 20;

Transactions (Correctness)

A transaction groups multiple operations so they succeed or fail together.

Typical use cases:

• Create an order + order items
• Transfer money between accounts
• Decrement inventory when placing an order

Postgres transactions also support different isolation levels (how strongly concurrent requests are separated). You’ll often start with the default and go deeper only when you hit concurrency bugs.

JSONB (Relational + Document)

Postgres supports jsonb, which is useful when you need flexible fields while still keeping relational structure.

Example uses:

• store provider payloads (webhooks)
• store feature flags / preferences
• store “metadata” fields that evolves over time

References

• PostgreSQL Official Site