PostgreSQL

-- Add a tsvector column for search
ALTER TABLE posts ADD COLUMN search_vector tsvector;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Now you can do full-text search
SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('postgresql & database');        

1. Word stemming (finding/find/finds all match)
2. Relevance ranking
3. Multiple languages
4. Complex queries with AND/OR/NOT

-- Add a JSONB column for post metadata
ALTER TABLE posts ADD COLUMN metadata JSONB;
CREATE INDEX idx_posts_metadata ON posts USING GIN(metadata);

-- Now we can efficiently query posts with specific metadata
SELECT * FROM posts 
WHERE metadata @> '{"type": "video"}' 
  AND metadata @> '{"hashtags": ["coding"]}';

-- Or find all posts that mention a specific user
SELECT * FROM posts 
WHERE metadata @> '{"mentions": ["user123"]}';

-- Enable PostGIS
CREATE EXTENSION postgis;

-- Add a location column to posts
ALTER TABLE posts 
ADD COLUMN location geometry(Point);

-- Create a spatial index
CREATE INDEX idx_posts_location 
ON posts USING GIST(location);

-- Find all posts within 5km of a user
SELECT * FROM posts 
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(-122.4194, 37.7749)::geography, -- SF coordinates
    5000  -- 5km in meters
);

• Different types of spatial data (points, lines, polygons)
• Various distance calculations (as-the-crow-flies, driving distance)
• Spatial operations (intersections, containment)
• Different coordinate systems

SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('food')
  AND metadata @> '{"type": "video", "hashtags": ["restaurant"]}'
  AND ST_DWithin(
    location::geography,
    ST_MakePoint(-122.4194, 37.7749)::geography,
    5000
  );

Query Optimization Essentials

Covering Indexes

1. Look up the value in the index to find the row's location
2. Fetch the actual row from the table to get other columns you need

-- Let's say this is a common query in our social media app:
SELECT title, created_at 
FROM posts 
WHERE user_id = 123 
ORDER BY created_at DESC;

-- A covering index that includes all needed columns
CREATE INDEX idx_posts_user_include 
ON posts(user_id) INCLUDE (title, created_at);

Partial Indexes

-- Standard index indexes everything
CREATE INDEX idx_users_email ON users(email);  -- Indexes ALL users

-- Partial index only indexes active users
CREATE INDEX idx_active_users 
ON users(email) WHERE status = 'active';  -- Smaller, faster index

Practical Performance Limits

1. Query Performance:

   • Simple indexed lookups: tens of thousands per second per core
   • Complex joins: thousands per second
   • Full-table scans: depends heavily on whether data fits in memory
2. Scale Limits:

   • Tables start getting unwieldy past 100M rows
   • Full-text search works well up to tens of millions of documents
   • Complex joins become challenging with tables >10M rows
   • Performance drops significantly when working set exceeds available RAM

Write Performance

1. Transaction Log (WAL) Write [Disk]: Changes are first written to the Write-Ahead Log (WAL) on disk. This is a sequential write operation, making it relatively fast. The WAL is critical for durability - once changes are written here, the transaction is considered durable because even if the server crashes, PostgreSQL can recover the changes from the WAL.
2. Buffer Cache Update [Memory]: Changes are made to the data pages in PostgreSQL's shared buffer cache, where the actual tables and indexes live in memory. When pages are modified, they're marked as "dirty" to indicate they need to be written to disk eventually.
3. Background Writer [Memory → Disk]: Dirty pages in memory are periodically written to the actual data files on disk. This happens asynchronously through the background writer, when memory pressure gets too high, or when a checkpoint occurs. This delayed write strategy allows PostgreSQL to batch multiple changes together for better performance.
4. Index Updates [Memory & Disk]: Each index needs to be updated to reflect the changes. Like table data, index changes also go through the WAL for durability. This is why having many indexes can significantly slow down writes - each index requires additional WAL entries and memory updates.

Throughput Limitations

• Simple inserts: ~5,000 per second per core
• Updates with index modifications: ~1,000-2,000 per second per core
• Complex transactions (multiple tables/indexes): Hundreds per second
• Bulk operations: Tens of thousands of rows per second

• Hardware: Write throughput is often bottlenecked by disk I/O for the WAL
• Indexes: Each additional index reduces write throughput
• Replication: If configured, synchronous replication adds latency as we wait for replicas to confirm
• Transaction Complexity: More tables or indexes touched = slower transactions

Write Performance Optimizations

1. Batch Processing
2. Vertical Scaling
3. Write Offloading
4. Table Partitioning
5. Sharding

-- Instead of 1000 separate inserts:
INSERT INTO likes (post_id, user_id) VALUES 
  (1, 101), (1, 102), ..., (1, 1000);

1. Send writes to a message queue (like Kafka)
2. Have background workers process these queued writes in batches
3. Optionally maintain a separate analytics database

CREATE TABLE posts (
    id SERIAL,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Create partitions by month
CREATE TABLE posts_2024_01 PARTITION OF posts
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Replication

1. Scaling reads by distributing queries across replicas
2. Providing high availability in case of node failures

Scaling reads

High Availability

1. Detecting that the primary is down
2. Promoting a replica to primary
3. Updating connection information
4. Repointing applications to the new primary

Data Consistency

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transactions and Concurrent Operations

BEGIN;
-- Get current max bid for item 123
SELECT maxBid from Auction where id = 123;

-- Place new bid if it's higher
INSERT INTO bids (item_id, user_id, amount) 
VALUES (123, 456, 100);

-- Update the max bid
UPDATE Auction SET maxBid = 100 WHERE id = 123;
COMMIT;

1. User A's transaction reads current max bid: $90
2. User B's transaction reads current max bid: $90
3. User A places bid for $100
4. User A commits
5. User B places bid for $95
6. User B commits

BEGIN;
-- Lock the item and get current max bid
SELECT maxBid FROM Auction WHERE id = 123 FOR UPDATE;

-- Place new bid if it's higher
INSERT INTO bids (item_id, user_id, amount) 
VALUES (123, 456, 100);

-- Update the max bid
UPDATE Auction SET maxBid = 100 WHERE id = 123;
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Same code as before...

COMMIT;

1. Read Committed (Default) is PostgreSQL's default isolation level that only sees data that was committed before the query began. As transactions execute, each query within a transaction can see new commits made by other transactions that completed after the transaction started. While this provides good performance, it can lead to non-repeatable reads where the same query returns different results within a transaction.
2. Repeatable Read in PostgreSQL provides stronger guarantees than the SQL standard requires. It creates a consistent snapshot of the data as of the start of the transaction, and unlike other databases, PostgreSQL's implementation prevents both non-repeatable reads AND phantom reads. This means not only will the same query return the same results within a transaction, but no new rows will appear that match your query conditions - even if other transactions commit such rows.
3. Serializable is the strongest isolation level that makes transactions behave as if they were executed one after another in sequence. This prevents all types of concurrency anomalies but comes with the trade-off of requiring retry logic in your application to handle transaction conflicts.

When to Use PostgreSQL (and When Not To)

1. Provides strong ACID guarantees while still scaling effectively with replication and partitioning
2. Handles both structured and unstructured data through JSONB support
3. Includes built-in solutions for common needs like full-text search and geospatial queries
4. Can scale reads effectively through replication
5. Offers excellent tooling and a mature ecosystem

• Complex relationships between data
• Strong consistency guarantees
• Rich querying capabilities
• A mix of structured and unstructured data (JSONB)
• Built-in full-text search
• Geospatial queries

• E-commerce platforms (inventory, orders, user data)
• Financial systems (transactions, accounts, audit logs)
• Content management systems (posts, comments, users)
• Analytics platforms (up to reasonable scale)

When to Consider Alternatives

• NoSQL databases (like Cassandra) for event streaming
• Key-value stores (like Redis) for real-time counters

• CockroachDB for global ACID compliance
• Cassandra for eventual consistency at global scale
• DynamoDB for managed global tables

• Redis for in-memory performance
• DynamoDB for managed scalability
• Cassandra for write-heavy workloads

Summary

Appendix: Basic SQL Concepts

Relational Database Principles

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
);

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

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)
);

1. Avoid duplicating data (we don't store user information in every post)
2. Maintain data integrity (if a user changes their username, it updates everywhere)
3. Make our data model flexible (we can add new user attributes without touching posts)

ACID Properties

Atomicity (All or Nothing)

1. Deduct $100 from savings
2. Add $100 to checking

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

Consistency (Data Integrity)

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

Isolation (Concurrent Transactions)

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Default level
-- or REPEATABLE READ
-- or SERIALIZABLE
COMMIT;

Durability (Permanent Storage)

1. Changes are first written to a log
2. The log is flushed to disk
3. Only then is the transaction considered committed

Why ACID Matters

• Financial transactions: You absolutely need ACID properties to prevent money from being lost or double-spent
• Social media likes: You might be okay with eventual consistency
• User authentication: You probably want ACID to prevent security issues
• Analytics data: You might prioritize performance over strict consistency

SQL Language

SQL Command Types

1. DDL (Data Definition Language)

   • Creates and modifies database structure
   • Examples: CREATE TABLE, ALTER TABLE, DROP TABLE

   CREATE TABLE users (
     id SERIAL PRIMARY KEY,
     email VARCHAR(255) UNIQUE
   );
   
   ALTER TABLE users ADD COLUMN username TEXT;

2. DML (Data Manipulation Language)

   • Manages data within tables
   • Examples: SELECT, INSERT, UPDATE, DELETE

   -- Find all users who joined in the last week
   SELECT * FROM users 
   WHERE created_at > NOW() - INTERVAL '7 days';
   
   -- Update a user's email
   UPDATE users SET email = 'new@email.com' 
   WHERE id = 123;

3. DCL (Data Control Language)

   • Controls access permissions
   • Examples: GRANT, REVOKE

   -- Give read access to a specific user
   GRANT SELECT ON users TO read_only_user;

4. TCL (Transaction Control Language)

   • Manages transactions
   • Examples: BEGIN, COMMIT, ROLLBACK

   BEGIN;
     -- Multiple operations...
   COMMIT;