Tech Notes

Basic SQL Concepts

5 min read

Why these SQL fundamentals matter

Before diving into PostgreSQL-specific features, it’s critical to understand how SQL databases think.

These concepts:

  • Apply to all relational databases
  • Are fundamental to system design
  • Determine correctness, performance, and data integrity

Summary

SQL fundamentals define how data is structured, related, protected, and modified.

Relational database principles

Tables (relations)

At its core, a relational database stores data in tables.

Mental model:

  • Table → spreadsheet
  • Row → one complete record
  • Column → attribute with a fixed type

Each column has:

  • A name
  • A data type
  • Optional constraints

Example: users table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What this enforces:

  • Each user has a unique ID
  • Usernames and emails must be unique
  • Missing critical fields is impossible

Important

Constraints are enforced by the database, not your application.

Rows = real-world entities

Each row represents one real-world thing.

Example rows:

  • One user
  • One post
  • One transaction

When a user signs up:

  • A new row is inserted
  • Constraints are validated automatically

Relationships – what makes databases “relational”

Relational databases shine because they model relationships explicitly.

Foreign keys

Foreign keys create links between tables.

Example: posts belonging to users

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Meaning:

  • Every post must belong to a valid user
  • Invalid references are rejected automatically

Important

Foreign keys enforce data integrity at the database level.

Relationship types

One-to-one

  • User → profile settings
  • Passport → person

One-to-many

  • User → posts
  • Order → order items

Example:

  • One user
  • Many posts

Many-to-many

  • Users ↔ liked posts
  • Students ↔ courses

Requires an explicit join table.

Join tables (many-to-many)

Example: likes

CREATE TABLE likes (
    user_id INTEGER REFERENCES users(id),
    post_id INTEGER REFERENCES posts(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

Key ideas:

  • Each row represents a relationship
  • Composite primary key prevents duplicates
  • Timestamps capture interaction history

Summary

Join tables model relationships as first-class data.

Normalization – structuring data correctly

Normalization means:

  • Splitting data into logical tables
  • Minimizing duplication
  • Enforcing consistency

Benefits:

  • No repeated user data in posts
  • Single source of truth
  • Easier schema evolution

Important

Normalization prioritizes correctness and flexibility.

Denormalization – intentional rule breaking

Sometimes we break normalization for performance.

Example:

  • Storing like_count directly in posts

Why:

  • Counting likes every time is expensive
  • Read performance matters

Trade-off:

  • Faster reads
  • More complex writes
  • Risk of inconsistency

Summary

Denormalization trades consistency for performance — a classic design decision.

ACID properties – database guarantees

PostgreSQL strictly follows ACID:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

This is a major differentiator from many NoSQL systems.

Important

ACID ensures correctness even under failures and concurrency.

Atomicity – all or nothing

A transaction either:

  • Fully succeeds
  • Or fully fails

Example: money transfer

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'savings';
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'checking';
COMMIT;

If anything fails:

  • All changes are rolled back

Summary

Atomicity prevents partial failure and data corruption.

Consistency – rules are never violated

Consistency means:

  • Database constraints always hold
  • Transactions move data between valid states

Example constraint:

  • Balance cannot go negative

    CREATE TABLE accounts ( account_id TEXT PRIMARY KEY, balance DECIMAL CHECK (balance >= 0), owner_id INTEGER REFERENCES users(id) );

If a transaction violates this:

  • Entire transaction fails

Important

ACID consistency ≠ CAP consistency. Don’t confuse them in system design.

Isolation – concurrent transactions

Isolation controls how transactions interact when running at the same time.

PostgreSQL uses MVCC (multi-version concurrency control).

Isolation Levels (PostgreSQL View)

PostgreSQL effectively supports:

  • Read Committed (default)
  • Repeatable Read
  • Serializable

Read Uncommitted behaves the same as Read Committed in PostgreSQL.

Anomalies overview

Isolation LevelDirty ReadsNon-repeatable ReadsPhantom ReadsSerialization Anomalies
Read Committednot possiblepossiblepossiblepossible
Repeatable Readnot possiblenot possiblepossiblepossible
Serializablenot possiblenot possiblenot possiblenot possible

Important

PostgreSQL never allows dirty reads.

Durability – once committed, it stays

Durability means:

  • Committed data survives crashes
  • Power loss doesn’t lose acknowledged writes

PostgreSQL achieves this via Write-Ahead Logging (WAL):

  1. write changes to log
  2. flush log to disk
  3. commit transaction

Summary

WAL guarantees durability by logging before modifying data files.

Durability vs performance tradeoff

Some systems relax durability for speed:

  • Synchronous_commit = off
  • Risk losing recent writes on crash

Warning

Relaxing durability is dangerous for financial or security-critical systems.

Why ACID matters

Use cases:

  • Financial transactions → ACID required
  • Authentication → ACID preferred
  • Analytics → ACID often relaxed
  • Social media likes → eventual consistency acceptable

Summary

Database choice depends on correctness requirements, not popularity.

SQL language – conceptual overview

You may not write complex SQL every day, but it helps to understand what SQL can express.

SQL command categories

DDL – data definition language

Defines structure.

CREATE TABLE users (...);
ALTER TABLE users ADD COLUMN username TEXT;
DROP TABLE users;

DML – data manipulation language

Works with data.

SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days';
INSERT INTO users (...);
UPDATE users SET email = 'new@email.com' WHERE id = 123;
DELETE FROM users WHERE id = 123;

DCL – data control language

Manages permissions.

GRANT SELECT ON users TO read_only_user;
REVOKE INSERT ON users FROM read_only_user;

TCL – transaction control language

Manages transactions.

BEGIN;
  -- operations
COMMIT;

ROLLBACK;

Practical perspective on SQL

Focus areas:

  • Access patterns
  • Indexing strategy
  • Normalization vs denormalization
  • Transactional guarantees

Lower priority:

  • Memorizing syntax
  • Writing complex queries

Summary

SQL knowledge is about reasoning, not syntax.

Graph View

Backlinks