CS50 SQL — Lecture 6: Scaling, Servers & Security (with MySQL & PostgreSQL)

TL;DR

• Move beyond SQLite: learn how server DBMSs (MySQL, PostgreSQL) differ and why they scale better.
• Types matter at scale: proper integer/text/date/time/numeric types improve performance and safety.
• Indexes speed reads; trade off with write speed and disk space. Use EXPLAIN to verify.
• Procedures & functions: automate multi-step logic in the database layer (MySQL PROCEDURE; Postgres FUNCTION & PROCEDURE).
• Prepared statements prevent SQL injection. Prefer parameters over string concatenation—always.
• Scale-out patterns: replication (leader/follower), synchronous vs asynchronous, and sharding.
• Security: roles, GRANT/REVOKE, least privilege.

---

1) From SQLite to Server DBMSs

SQLite is embedded and great for local apps and teaching. Production workloads typically use MySQL or PostgreSQL servers:

• Run as a network service (own process), support concurrent clients.
• Keep hot data in memory; provide robust indexing, procedures, roles, replication, backup tooling.
• Offer richer type systems and advanced features (e.g., PL/pgSQL, window functions, CROSS/ lateral joins, etc.).

Connecting

MySQL (CLI):

mysql -u root -h 127.0.0.1 -P 3306 -p
# then:  SHOW DATABASES;  USE db;  SHOW TABLES;

PostgreSQL (CLI):

psql -U postgres -h 127.0.0.1 -p 5432
-- then:  \l   -- list DBs
--       \c db -- connect
--       \dt  -- list tables

---

2) Data Types Cheat Sheet

Integers

Concept	MySQL	PostgreSQL
Small	TINYINT, SMALLINT	SMALLINT
Regular	INT	INTEGER (alias INT)
Big	BIGINT	BIGINT
Auto-increment PK	INT AUTO_INCREMENT	SERIAL / BIGSERIAL (or identity columns)

Tip (PG): Prefer modern identity columns: id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY.

Text / Strings

• MySQL: CHAR(n) (fixed), VARCHAR(n) (variable), TEXT/MEDIUMTEXT/LONGTEXT, ENUM, SET.
• PostgreSQL: CHAR(n), VARCHAR(n), TEXT, custom ENUM types.

Dates & Times

• MySQL: DATE, TIME, DATETIME, TIMESTAMP.
• PostgreSQL: DATE, TIME [WITH/WITHOUT TIME ZONE], TIMESTAMP [WITH/WITHOUT TIME ZONE], INTERVAL.

Decimals / Money

• MySQL: DECIMAL(p,s) (fixed-precision), FLOAT, DOUBLE.
• PostgreSQL: NUMERIC(p,s) (fixed-precision), REAL, DOUBLE PRECISION, and MONEY (be mindful of locales).

---

3) Schema Examples (MBTA mini)

MySQL

CREATE TABLE `cards` (
  `id` INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE `stations` (
  `id`   INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(32) NOT NULL UNIQUE,
  `line` ENUM('blue','green','orange','red') NOT NULL
);

CREATE TABLE `swipes` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `card_id`    INT NOT NULL,
  `station_id` INT NOT NULL,
  `type`       ENUM('enter','exit','deposit') NOT NULL,
  `datetime`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `amount`     DECIMAL(5,2) NOT NULL,
  CONSTRAINT chk_amount_nonzero CHECK (`amount` <> 0),
  FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`),
  FOREIGN KEY (`station_id`) REFERENCES `stations`(`id`)
);

Alter ENUM (add new line):

ALTER TABLE `stations`
  MODIFY `line` ENUM('blue','green','orange','red','silver') NOT NULL;

PostgreSQL

CREATE TABLE "cards" (
  "id" SERIAL PRIMARY KEY
);

CREATE TYPE "line_t" AS ENUM ('blue','green','orange','red');
CREATE TYPE "swipe_t" AS ENUM ('enter','exit','deposit');

CREATE TABLE "stations" (
  "id"   SERIAL PRIMARY KEY,
  "name" VARCHAR(32) NOT NULL UNIQUE,
  "line" line_t NOT NULL
);

CREATE TABLE "swipes" (
  "id"         SERIAL PRIMARY KEY,
  "card_id"    INT NOT NULL REFERENCES "cards"("id"),
  "station_id" INT NOT NULL REFERENCES "stations"("id"),
  "type"       swipe_t NOT NULL,
  "datetime"   TIMESTAMP NOT NULL DEFAULT NOW(),
  "amount"     NUMERIC(5,2) NOT NULL,
  CONSTRAINT chk_amount_nonzero CHECK ("amount" <> 0)
);

Alter ENUM (add value safely in PG):

ALTER TYPE line_t ADD VALUE IF NOT EXISTS 'silver';

---

4) Indexing & Query Plans

• Create only indexes you use. They speed reads, but slow writes and consume disk.
• Verify with MySQL: EXPLAIN SELECT ...; and PostgreSQL: EXPLAIN [ANALYZE] SELECT ...;

Example composite index (MySQL):

CREATE INDEX idx_enrollments_student_course
  ON enrollments(student_id, course_id);

Example composite index (PostgreSQL):

CREATE INDEX idx_enrollments_student_course
  ON enrollments(student_id, course_id);

Patterns: filter columns in WHERE, join keys (... ON a.k = b.k), and sometimes order-by columns.

---

5) Transactions & Concurrency

• ACID: Atomicity, Consistency, Isolation, Durability.
• Group dependent changes with BEGIN/COMMIT (MySQL) or BEGIN/COMMIT (PostgreSQL). Use ROLLBACK on error.
• Locks & isolation levels protect consistency; higher isolation reduces anomalies but may cost throughput.

MySQL:

START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;  -- Alice
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- Bob
COMMIT;

PostgreSQL:

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

---

6) Stored Routines: MySQL vs PostgreSQL

6.1 MySQL Stored Procedures (control flow, loops)

Delimiter tip: Change delimiter while defining to avoid ending early on ;.

DELIMITER //

CREATE PROCEDURE pay_user(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2))
BEGIN
  DECLARE from_bal DECIMAL(10,2);

  SELECT balance INTO from_bal FROM accounts WHERE id = from_id FOR UPDATE;

  IF from_bal < amt THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  ELSE
    UPDATE accounts SET balance = balance - amt WHERE id = from_id;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  END IF;
END //

DELIMITER ;
CALL pay_user(1, 2, 10.00);

If / ElseIf / Else, Case, Loops:

DELIMITER //

CREATE PROCEDURE demo_flow(IN n INT)
BEGIN
  -- IF / ELSEIF / ELSE
  IF n < 0 THEN
    SELECT 'negative' AS kind;
  ELSEIF n = 0 THEN
    SELECT 'zero' AS kind;
  ELSE
    SELECT 'positive' AS kind;
  END IF;

  -- CASE
  CASE
    WHEN n % 2 = 0 THEN SELECT 'even' AS parity;
    ELSE               SELECT 'odd'  AS parity;
  END CASE;

  -- WHILE loop (print 1..n)
  DECLARE i INT DEFAULT 1;
  WHILE i <= n DO
    SELECT i;
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

6.2 MySQL Prepared & Dynamic SQL

• Prepared: sanitize values (precompiled plan)

PREPARE get_by_id FROM 'SELECT * FROM accounts WHERE id = ?';
SET @id := 1;
EXECUTE get_by_id USING @id;
DEALLOCATE PREPARE get_by_id;

• Dynamic: build SQL string, then PREPARE/EXECUTE (escape identifiers carefully).

---

7) PostgreSQL: Functions, Procedures & Dynamic SQL

7.1 Functions (CREATE FUNCTION ... RETURNS ... LANGUAGE plpgsql)

In PostgreSQL, procedural logic typically lives in functions (returning rows/scalars or void).

CREATE OR REPLACE FUNCTION pay_user(from_id INT, to_id INT, amt NUMERIC)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
  from_bal NUMERIC;
BEGIN
  SELECT balance INTO from_bal FROM accounts WHERE id = from_id FOR UPDATE;

  IF from_bal < amt THEN
    RAISE EXCEPTION 'Insufficient funds';
  ELSE
    UPDATE accounts SET balance = balance - amt WHERE id = from_id;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  END IF;
END;
$$;

SELECT pay_user(1, 2, 10.00);

Control flow in PL/pgSQL:

CREATE OR REPLACE FUNCTION demo_flow(n INT)
RETURNS VOID
LANGUAGE plpgsql AS $$
DECLARE
  i INT := 1;
BEGIN
  -- IF / ELSIF / ELSE
  IF n < 0 THEN
    RAISE NOTICE 'negative';
  ELSIF n = 0 THEN
    RAISE NOTICE 'zero';
  ELSE
    RAISE NOTICE 'positive';
  END IF;

  -- CASE
  CASE WHEN n % 2 = 0 THEN
    RAISE NOTICE 'even';
  ELSE
    RAISE NOTICE 'odd';
  END CASE;

  -- Basic loops
  WHILE i <= n LOOP
    RAISE NOTICE 'i=%', i;
    i := i + 1;
  END LOOP;
END; $$;

7.2 PostgreSQL Procedures (CREATE PROCEDURE ... CALL)

Postgres procedures (PG 11+) are invoked with CALL and can manage transactions internally.

CREATE OR REPLACE PROCEDURE transfer_amount(from_id INT, to_id INT, amt NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  -- each CALL runs in its own transaction context;
  -- you can START/COMMIT subtransactions with BEGIN/EXCEPTION blocks
  PERFORM pay_user(from_id, to_id, amt);
END;
$$;

CALL transfer_amount(1, 2, 10.00);

When to use: Prefer functions for reusable logic callable in queries; use procedures when you need CALL semantics and explicit transaction control inside the routine.

7.3 PostgreSQL Dynamic SQL with EXECUTE + format()

• Build safe dynamic SQL using format() with placeholders:
  • %I — identifier (table/column names; properly quoted)
  • %L — literal (strings/numbers; safely quoted)
  • %s — already-safe string (use sparingly)

CREATE OR REPLACE FUNCTION count_by_col(tbl regclass, col name, val TEXT)
RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
  sql TEXT;
  out_count BIGINT;
BEGIN
  sql := format('SELECT count(*) FROM %s WHERE %I = %L', tbl, col, val);
  EXECUTE sql INTO out_count;
  RETURN out_count;
END; $$;

SELECT count_by_col('users', 'username', 'alice');

Avoid concatenation; prefer format() and USING:

CREATE OR REPLACE FUNCTION exists_by_id(tbl regclass, id_val INT)
RETURNS BOOLEAN
LANGUAGE plpgsql AS $$
DECLARE res BOOLEAN;
BEGIN
  EXECUTE format('SELECT EXISTS(SELECT 1 FROM %s WHERE id = $1)', tbl)
    INTO res
    USING id_val;
  RETURN res;
END; $$;

7.4 MySQL FORMAT() vs PostgreSQL to_char() + format()

• MySQL has FORMAT(number, decimals [, locale]) → returns a string with separators.

  SELECT FORMAT(12345.6789, 2);       -- '12,345.68'
  SELECT FORMAT(12345.6789, 2, 'de'); -- '12.345,68'

• PostgreSQL uses:
  • to_char(number, 'FM999,999,990D00') for numeric/date presentation.
  • format('Hello %s, table %I', 'world', 'users') for string templating.

SELECT to_char(12345.6789, 'FM9,999,990.00');   -- '12,345.68'
SELECT to_char(CURRENT_DATE, 'YYYY-MM-DD');     -- '2025-10-19'
SELECT format('Table: %I, Val: %L', 'Users', 'Alice');

---

8) Prepared Statements & SQL Injection

The risk

-- UNSAFE (string concatenation)
EXECUTE 'SELECT * FROM accounts WHERE id = ' || user_input;

Safer patterns

MySQL:

PREPARE stmt FROM 'SELECT * FROM accounts WHERE id = ?';
SET @id := 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

PostgreSQL (SQL-level):

PREPARE get_acct(INT) AS SELECT * FROM accounts WHERE id = $1;
EXECUTE get_acct(1);
DEALLOCATE get_acct;

PostgreSQL (PL/pgSQL dynamic):

EXECUTE format('SELECT * FROM %s WHERE id = $1', tbl)
INTO acct
USING id_val;  -- parameters are sanitized

Rule of thumb: use placeholders (? in MySQL, $1..$n in Postgres) and avoid string concatenation. For identifiers, use %I in PG’s format(); for literals use %L or USING.

---

9) Replication & Sharding (Scale-Out)

Replication (copies of your DB)

• Single-leader: all writes go to leader; reads can fan out to followers (read-replicas).
• Synchronous: leader waits for follower → strong consistency, slower.
• Asynchronous: leader doesn’t wait → faster, but transient inconsistency possible.

Sharding (partition your data)

• Split large datasets across servers (e.g., by ID ranges, hash of key, geography).
• Watch out for hotspots; consider replication + sharding for resilience.

---

10) Roles & Access Control

MySQL:

CREATE USER 'analyst' IDENTIFIED BY 'strong-password';
GRANT SELECT ON rideshare.analysis TO 'analyst';
REVOKE INSERT, UPDATE, DELETE ON rideshare.* FROM 'analyst';

PostgreSQL:

CREATE ROLE analyst LOGIN PASSWORD 'strong-password';
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON TABLE rideshare.analysis TO analyst;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM analyst;

Principle of least privilege: only grant what is required.

---

11) MySQL ⇄ PostgreSQL Quick Map

Topic	MySQL	PostgreSQL
Auto-increment	AUTO_INCREMENT	SERIAL / identity
Boolean	TINYINT(1) or BOOLEAN	BOOLEAN
Decimal	DECIMAL(p,s)	NUMERIC(p,s)
Date/Time	DATETIME, TIMESTAMP	TIMESTAMP [TZ]
Enum	ENUM('a','b')	CREATE TYPE ... AS ENUM ...
Procedure	CREATE PROCEDURE ... CALL	CREATE PROCEDURE ... CALL (PG 11+)
Function	CREATE FUNCTION ... (no transactions)	CREATE FUNCTION ... (PL/pgSQL)
Prepared stmt	PREPARE ...; EXECUTE ...;	PREPARE ...; EXECUTE ...; + $1..$n
String format	FORMAT(num, d, locale)	to_char, format()
Dynamic SQL	PREPARE/EXECUTE strings	EXECUTE format(...) USING ...

---

Appendix: Tiny Demos

Postgres – generate numbered rows in PL/pgSQL:

CREATE OR REPLACE FUNCTION demo_numbers(n INT)
RETURNS SETOF INT LANGUAGE plpgsql AS $$
DECLARE i INT := 1;
BEGIN
  WHILE i <= n LOOP
    RETURN NEXT i;
    i := i + 1;
  END LOOP;
END; $$;

SELECT * FROM demo_numbers(5); -- 1..5

MySQL – repeat insert with a loop inside a procedure (demo only):

DELIMITER //
CREATE PROCEDURE seed_users(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= n DO
    INSERT INTO users(username) VALUES (CONCAT('user', i));
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

CALL seed_users(1000);