Databases Intermediate: Advanced SQL & NoSQL

A subquery is a SELECT inside another query. In WHERE, it filters using another query's result (find users with above-average salary). In FROM, it acts as a temporary table. Subqueries are readable and solve problems joins can't express easily — like comparing against an aggregate. The trade-off: they can be slower than joins, so use them for clarity first, optimize later.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);

A CTE (WITH clause) names a temporary result set, making complex queries readable step by step. Instead of nesting subqueries, you define named CTEs and reference them. Recursive CTEs even handle hierarchical data (org charts, category trees) that plain SQL can't. CTEs are the modern standard for readable, maintainable complex SQL.

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 80000
)
SELECT department, COUNT(*) AS count
FROM high_earners
GROUP BY department;

A named temporary result set (CTE) usable in the main query.

Window functions compute values ACROSS rows without collapsing them — unlike GROUP BY. ROW_NUMBER ranks rows, LAG/LEAD access previous/next rows, and SUM() OVER() gives running totals. The OVER(PARTITION BY ... ORDER BY ...) clause defines the 'window'. Window functions are the most powerful SQL feature for analytics — rankings, moving averages, period-over-period comparison.

SELECT
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
  SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

RANK() or ROW_NUMBER().

Beyond INNER/LEFT, advanced joins: SELF JOIN (join a table to itself — find employees and their managers), CROSS JOIN (every combination — Cartesian product), FULL OUTER JOIN (all rows from both, matched where possible). Knowing all join types lets you express any relationship. Self-joins power org charts; full outer joins find unmatched data on both sides.

-- Self join: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

FULL OUTER JOIN.

A stored procedure is a saved block of SQL you can call repeatedly with parameters — like a function for your database. They encapsulate business logic (complex multi-step operations), reduce network traffic, and improve consistency. Instead of sending 10 queries, you call one procedure. They're controversial (some prefer logic in app code), but essential to know for legacy and enterprise systems.

CREATE PROCEDURE transfer_money(
    from_id INT, to_id INT, amount DECIMAL
)
LANGUAGE SQL
AS $$
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
$$;

CALL transfer_money(1, 2, 100);

Reusable, parameterized SQL logic executed server-side.

A trigger automatically runs when data changes (INSERT/UPDATE/DELETE). Use them for auditing (log who changed what), enforcing complex rules, and maintaining derived data. Example: automatically update an 'updated_at' timestamp before every row update. Triggers run inside the database, guaranteeing they always fire — regardless of which app writes the data.

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Just before the row is updated — can modify the new values.

Isolation levels control how much concurrent transactions interfere. Read Uncommitted (fastest, dirtiest), Read Committed (default), Repeatable Read, Serializable (strictest, slowest). Higher isolation = fewer anomalies (dirty reads, non-repeatable reads, phantoms) but more blocking. Choosing the right level is a real trade-off between consistency and throughput.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- ... concurrent-safe operations ...
COMMIT;

Reading another transaction's uncommitted (dirty) changes.

Advanced indexing: composite indexes (multiple columns) for multi-column queries, covering indexes (include all needed columns), partial indexes (where clause), and unique indexes (constraint + speed). The key insight: column ORDER in a composite index matters — put the most selective column first. Bad indexing is the #1 cause of slow production databases.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- This query uses the composite index
SELECT * FROM orders
WHERE customer_id = 42 AND order_date > '2026-01-01';

a alone, or a and b together — but not b alone.

EXPLAIN shows HOW the database executes a query — which indexes it uses, whether it scans or seeks, estimated costs. It's the single most important tool for query optimization. Learn to read it: look for 'Seq Scan' (full table scan = slow, needs index) vs 'Index Scan' (fast). EXPLAIN turns guesswork into evidence-based optimization.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42;

The database is scanning every row — likely missing an index.

Optimization rules: SELECT only needed columns (not *), avoid functions on indexed columns in WHERE, use EXISTS instead of IN for large sets, avoid OR with indexes (use UNION), paginate with keyset not OFFSET. Most slow queries stem from a few anti-patterns. Learning these transforms a 10-second query into milliseconds.

-- BAD: function on indexed column prevents index use
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- GOOD: index-friendly comparison
SELECT * FROM users WHERE email = '[email protected]';

It transfers unneeded columns, wasting bandwidth and preventing index-only scans.

Concurrent transactions need locking to stay consistent — shared locks (readers) and exclusive locks (writers). Too much locking = blocking; too little = data corruption. Deadlocks happen when two transactions each hold a lock the other needs. Prevention: consistent lock ordering, short transactions, and retry logic. This is production database discipline.

-- Deadlock scenario: two transactions lock opposite order
-- T1: UPDATE accounts SET ... WHERE id=1; UPDATE ... id=2
-- T2: UPDATE accounts SET ... WHERE id=2; UPDATE ... id=1
-- Both wait on each other = deadlock

Two transactions each waiting for a lock the other holds.

Partitioning splits a huge table into smaller physical pieces by range (by date), list (by category), or hash. Queries filtering on the partition key scan only relevant partitions — massive speedup on billion-row tables. It also simplifies archiving old data (drop a partition vs delete millions of rows). Essential for time-series and log data at scale.

CREATE TABLE orders (
    id INT, order_date DATE, total DECIMAL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

By date (range partitioning) — queries filter on time.

Migrations version-control your database schema — each change (add column, create table) is a versioned file applied in order. Tools like Alembic (Python) track applied migrations, support rollbacks, and make schema changes reproducible across environments. Without migrations, schema changes are manual, untracked, and error-prone across dev/staging/production.

# Alembic migration
# def upgrade():
#     op.add_column('users', sa.Column('age', sa.Integer()))
# def downgrade():
#     op.drop_column('users', 'age')

To reproduce changes across environments and roll back safely.

HAVING filters groups (after GROUP BY) — WHERE filters rows before grouping. ROLLUP/CUBE generate subtotals and grand totals for reporting. This is how dashboards produce hierarchical summaries in one query. Mastering HAVING + ROLLUP turns raw data into decision-ready reports directly in SQL.

SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000
ORDER BY total DESC;

WHERE filters individual rows; HAVING filters aggregated groups.

MongoDB stores data as JSON-like documents in collections — schemaless, flexible, and naturally matching objects in code. No migrations needed for new fields; each document can differ. It's the most popular NoSQL database, ideal for rapid development and unstructured data. The mental model: collections (like tables) of documents (like rows, but flexible).

db.users.insertOne({
    name: 'Ali',
    email: '[email protected]',
    tags: ['python', 'sql'],
    address: { city: 'Lahore' }
});
db.users.find({ 'address.city': 'Lahore' });

JSON-like BSON documents in collections.

Redis is an in-memory key-value store — blazingly fast because data lives in RAM. It's used for caching (store frequent query results), sessions, rate limiting, and pub/sub messaging. Data types: strings, hashes, lists, sets, sorted sets. The killer use: cache database results in Redis, and your app gets 100x faster reads.

import redis
r = redis.Redis()
r.set('user:42', 'Ali', ex=3600)  # cache with 1-hour expiry
name = r.get('user:42')  # O(1) read
print(name)

In-memory speed — O(1) operations, microseconds latency.

NoSQL modeling inverts relational thinking: you model for QUERIES, not normalization. Embed related data (order with its items) when read together; reference when data is shared or unbounded. The guiding question: how will this data be accessed? Denormalization is fine — duplicate for read speed. Access patterns drive the entire design.

// Embed: order with items (read together)
{
  _id: 1,
  customer: 'Ali',
  items: [
    { product: 'Book', qty: 2 },
    { product: 'Pen', qty: 5 }
  ],
  total: 30
}

Embed when read together; reference when shared or unbounded.

Caching stores frequently-read data in fast storage (Redis). Cache-aside: check cache → hit? return : miss? query DB, store in cache, return. The hard part is invalidation — when data changes, the cache must update or go stale. Strategies: TTL (expiry), write-through, write-behind. Caching is the highest-leverage performance optimization for read-heavy apps.

def get_user(user_id):
    cached = r.get(f'user:{user_id}')
    if cached: return cached  # cache hit
    user = db.query(user_id)  # cache miss
    r.set(f'user:{user_id}', user, ex=300)  # store 5 min
    return user

Check cache, on miss load from DB and populate cache.

The CAP theorem: in a distributed system, you can guarantee only two of Consistency, Availability, and Partition tolerance. During a network partition, you choose: stay consistent (reject some requests) or stay available (serve possibly stale data). Most systems choose AP (NoSQL like Cassandra) or CP (some SQL setups). This is why 'perfect' distributed databases don't exist.

# CAP: pick two of three
# - Consistency: all nodes see same data
# - Availability: every request gets a response
# - Partition tolerance: system works despite network splits
print('You can only guarantee two')

Availability + Partition tolerance (AP) — eventual consistency.

Vertical scaling: bigger machine (more RAM/CPU) — simple but has limits. Horizontal scaling: more machines — complex but unlimited. For databases, horizontal = sharding (split data across servers by a shard key) or replication (copies for reads). Most modern systems scale horizontally for reliability and cost. The trade-off: distributed complexity vs single-machine simplicity.

# Vertical: add RAM/CPU to one server
# Horizontal: shard by user_id across 4 servers
#   server_0: user_id % 4 == 0
#   server_1: user_id % 4 == 1
#   server_2: user_id % 4 == 2
#   server_3: user_id % 4 == 3

Splitting data across multiple servers by a shard key.

Replication copies data from a primary (master) to replicas (slaves). Reads scale across replicas; writes go to the primary. It also provides failover — if the primary dies, a replica promotes. This is how production databases achieve read scalability AND high availability. The trade-off: replicas are eventually consistent (slight lag behind the primary).

# Primary: handles writes
# Replica 1: handles reads (customers A-M)
# Replica 2: handles reads (customers N-Z)
# If primary fails → promote a replica to primary

Asynchronous replication has lag — replicas trail the primary slightly.

Opening a database connection is expensive. A connection pool maintains a set of reusable connections, so app requests grab one, use it, return it — no repeated open/close overhead. Without pooling, apps exhaust connections under load. PgBouncer (PostgreSQL) and built-in poolers in frameworks solve this. Essential for any web app with a database.

# PgBouncer config: pool 20 connections
# 100 app requests share 20 DB connections
# Requests queue when all busy — no exhaustion

Opening connections is costly; pooling reuses them, preventing exhaustion.

Production databases need monitoring: CPU, memory, disk I/O, connection count, slow queries, replication lag, cache hit ratio. Tools: pg_stat_statements, Prometheus + Grafana, cloud monitors. Key metrics reveal problems before they become outages. A monitored database is a reliable one; an unmonitored one is a ticking time bomb.

-- PostgreSQL: find slow queries
SELECT query, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Slow queries, connection count, replication lag, cache hit ratio.

Full-text search finds documents by relevance, not just exact match — handling stemming ('running' matches 'run'), ranking, and multiple words. PostgreSQL has built-in full-text search (tsvector, tsquery); Elasticsearch is the dedicated search engine. Unlike LIKE '%word%', full-text search is indexed and relevance-ranked — the difference between 'find' and 'search'.

CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', content));

SELECT title
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('database & performance');

Full-text — it's indexed and handles language; LIKE does full scans.

Database security: least privilege (app accounts can only do what they need), parameterized queries (SQL injection), encryption at rest and in transit, strong authentication, audit logging, and regular patching. The principle: an attacker with your app credentials shouldn't own your entire database. Defense in depth — multiple layers, no single point.

-- Least privilege: app account can only query, not drop
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
REVOKE DROP, DELETE ON orders FROM app_user;

Granting only the minimum permissions needed for a task.

Time-series databases are optimized for timestamped data — sensor readings, metrics, logs, stock prices. They excel at: high write throughput (millions of points/second), time-based queries (last hour, downsampling), retention policies (auto-delete old data). InfluxDB is the dedicated choice; TimescaleDB adds time-series to PostgreSQL. If your data is overwhelmingly timestamped, a TSDB beats a relational DB.

-- TimescaleDB (PostgreSQL extension)
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INT,
    temperature DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');

Time-range queries and high-volume timestamped writes.

Advanced ORM: relationships (one-to-many, many-to-many) map to Python attributes — user.orders returns related rows. Eager loading (joinedload, selectinload) avoids the N+1 query problem (one query per row). Knowing ORM advanced features means you write clean Python while the ORM generates efficient SQL. The N+1 problem is the classic ORM performance trap.

from sqlalchemy.orm import relationship, joinedload

class User(Base):
    orders = relationship('Order', back_populates='user')

users = session.query(User).options(joinedload(User.orders)).all()
for u in users:
    print(u.name, len(u.orders))  # no N+1 — orders eager-loaded

One query for the list, then one extra query per row for related data.

Production app architecture separates layers: controllers → services → repositories → database. Repositories encapsulate all SQL (no queries scattered in views). The database is behind an API of repository functions, making it swappable, testable, and consistent. This clean separation is the difference between a prototype and a maintainable system.

class UserRepository:
    def __init__(self, session):
        self.session = session
    def get_by_email(self, email):
        return self.session.query(User).filter_by(email=email).first()
    def save(self, user):
        self.session.add(user); self.session.commit()

# Services use repositories — no raw SQL outside this layer

Testability, swappable databases, and consistent data access.

In practice, systems choose a consistency model: strong (every read sees latest write) vs eventual (reads may be stale briefly but converge). Eventual consistency powers NoSQL and replicas; strong consistency is needed for banking. The design question: can your app tolerate brief staleness? If yes, eventual consistency unlocks massive scale; if no, you pay the coordination cost.

# Strong: every read after write sees the new value (banking)
# Eventual: reads may lag briefly, then converge (social feeds)
# Which does your app need?

Strong consistency — no stale balances allowed.

Your capstone: design a scalable blog database. Schema for users, posts, comments, tags (many-to-many). Add indexes for hot queries, write advanced queries (window functions for trending posts, full-text search), design a Redis caching layer, and document your scaling plan. This integrates everything — schema design, optimization, caching, and production thinking.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT, body TEXT,
    author_id INT REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_posts_created ON posts(created_at DESC);
-- Cache top posts in Redis
-- Full-text search on title/body
-- Replication for read scaling

Indexes, caching, full-text search, replication — all covered in this course.