SQL vs NoSQL, indexing and transactions

How it works: SQL vs NoSQL

1. SQL (relational): data in tables with typed columns; relations via foreign keys and joins; constraints (unique, not null, checks); a powerful query language; strong transactions. Postgres is the common default; SQLite for local and embedded use.
2. NoSQL families:
   • Key-value (Redis, DynamoDB): very fast lookups by key (Redis caching).
   • Document (MongoDB, Firestore): flexible JSON-like records.
   • Wide-column (Cassandra): huge write volumes across many machines.
   • Graph (Neo4j): relationship-heavy queries (GraphRAG).
3. Choose by access pattern: how you read and write, consistency needs, scale. Most apps start well with Postgres and add specialised stores only for clear needs.
4. For AI: Postgres with pgvector stores relational data and embeddings together, with filters and transactions (RAG access control); JSONB columns hold flexible metadata like tool-call traces.

How it works: database indexing

1. Without an index, a query scans every row: fine for 1,000 rows, painful for 100 million.
2. An index (usually a B-tree) is a sorted structure on one or more columns, letting the database jump to matching rows.
3. Index what you filter, join and sort by: WHERE conversation_id = ? ORDER BY created_at wants a composite index on (conversation_id, created_at).
4. Check with EXPLAIN (see the code): look for scans on big tables.
5. Costs: storage and slower writes; don't index everything.
6. Special indexes: full-text search, JSONB, and vector indexes (HNSW, IVFFlat) for similarity search.

How it works: transactions and ACID

1. A transaction groups several statements: BEGIN → changes → COMMIT (or ROLLBACK).
2. ACID: Atomic (all or nothing), Consistent (constraints hold), Isolated (concurrent transactions don't see each other's half-done work), Durable (committed data survives crashes).
3. Use them whenever several writes must agree: deduct credits and record usage; save a message and update the conversation's timestamp; mark a job done and store its result.
4. Keep them short: never hold a transaction open while calling an LLM.
5. Concurrency: use constraints, row locks (SELECT ... FOR UPDATE) or idempotency keys (idempotency) to avoid race conditions.

import sqlite3
 
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE messages (id INTEGER PRIMARY KEY, conversation_id INTEGER, role TEXT, content TEXT)")
db.executemany("INSERT INTO messages (conversation_id, role, content) VALUES (?, ?, ?)",
               [(i % 5_000, "user" if i % 2 else "assistant", f"message {i}") for i in range(100_000)])
 
# INDEXING: without an index the database scans every row; with one it jumps straight to the matches.
query = "SELECT content FROM messages WHERE conversation_id = ? ORDER BY id"
plan = lambda: db.execute("EXPLAIN QUERY PLAN " + query, (42,)).fetchall()[0][-1]
print("before index:", plan())
db.execute("CREATE INDEX idx_messages_conversation ON messages (conversation_id, id)")
print("after index :", plan())
 
# TRANSACTIONS: several writes succeed together or not at all (atomicity).
db.execute("CREATE TABLE credits (user TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
db.executemany("INSERT INTO credits VALUES (?, ?)", [("alice", 100), ("bob", 0)])
db.commit()
def transfer(src, dst, amount):
    try:
        with db:                                                     # BEGIN ... COMMIT, or ROLLBACK on error
            db.execute("UPDATE credits SET balance = balance + ? WHERE user = ?", (amount, dst))
            db.execute("UPDATE credits SET balance = balance - ? WHERE user = ?", (amount, src))
        return "committed"
    except sqlite3.IntegrityError as e:
        return f"rolled back ({e})"
print(transfer("alice", "bob", 30), dict(db.execute("SELECT * FROM credits")))
print(transfer("alice", "bob", 500), dict(db.execute("SELECT * FROM credits")))

before index: SCAN messages
after index : SEARCH messages USING INDEX idx_messages_conversation (conversation_id=?)
committed {'alice': 70, 'bob': 30}
rolled back (CHECK constraint failed: balance >= 0) {'alice': 70, 'bob': 30}