SQL Resources

Knowledge

• Docs: "PostgreSQL Documentation", PostgreSQL Global Development Group, postgresql.org
  The reference engine's complete manual, and the most thorough free description of how a real database behaves. Use for: the authoritative answer on anything this workspace teaches.

• Docs: "Tutorial", PostgreSQL Global Development Group, postgresql.org
  A guided start covering tables, queries, joins and aggregation. Use for: stage 1, and the first working queries.

• Docs: "Queries", PostgreSQL Global Development Group, postgresql.org
  Join types, grouping, and the order in which the clauses of a SELECT are actually evaluated. Use for: stage 2, and for why WHERE cannot see an alias from SELECT.

• Docs: "5.5 Constraints", PostgreSQL Global Development Group, postgresql.org
  Every constraint kind with its syntax and its caveats: CHECK, NOT NULL, UNIQUE, primary and foreign keys with their referential actions, and exclusion constraints. Use for: stage 4, and for the exact wording of a diagnostic.

• Docs: "Chapter 8 Data Types", PostgreSQL Global Development Group, postgresql.org
  Every type with its range, its storage and the trade it makes, including the numeric, character, date and time families the arc argues about. Use for: stages 1 and 4, whenever a column type is being chosen rather than accepted.

• Docs: "SELECT", PostgreSQL Global Development Group, postgresql.org
  The full syntax of one statement, clause by clause, including the grouping and set-operation forms the tutorial pages leave out. Use for: stage 2, when the question is what is legal where rather than what it means.

• Docs: "Appendix A, Error Codes", PostgreSQL Global Development Group, postgresql.org
  Every SQLSTATE the server can raise, grouped by class. Use for: turning a five-character code in a log line into a named condition, from stage 2 onward.

• Docs: "Release Notes", PostgreSQL Global Development Group, postgresql.org
  What changed in each release, which is the only place to settle when a behaviour became true. Use for: any claim that depends on a version, and stage 2 needed it twice.

• Docs: "Window Functions", PostgreSQL Global Development Group, postgresql.org
  Partitions and frames explained with worked examples. Use for: stage 3.

• Docs: "9.22 Window Functions", PostgreSQL Global Development Group, postgresql.org
  Every window function with what it returns and, crucially, which of them respect the frame. Use for: stage 3, and the answer to why last_value returned the current row.

• Docs: "7.8 WITH Queries", PostgreSQL Global Development Group, postgresql.org
  Common table expressions, the recursive form with its evaluation described round by round, and the SEARCH and CYCLE options. Use for: stages 2 and 3, and for how a recursive query terminates.

• Docs: "8.14 JSON Types" and "9.16 JSON Functions", PostgreSQL Global Development Group, postgresql.org
  json against jsonb, the operators, the path language and JSON_TABLE. Use for: stage 3, and for what a document column does not enforce.

• Docs: "Window Functions", SQLite contributors, sqlite.org
  The same feature in the no-server engine, including its frame syntax. Use for: checking whether a stage 3 query travels.

• Docs: "Indexes", PostgreSQL Global Development Group, postgresql.org
  Index types, multicolumn and partial indexes, and when the planner will ignore one. Use for: stage 6, choosing an index rather than adding one.

• Docs: "Using EXPLAIN", PostgreSQL Global Development Group, postgresql.org
  How to read a plan, and what the estimated and actual numbers each mean. Use for: stage 6, every time.

• Docs: "EXPLAIN", PostgreSQL Global Development Group, postgresql.org
  Every option the command takes, which matters because release 18 changed what it prints by default. Use for: stage 6, and for the exact meaning of a line you have not seen before.

• Docs: "14.2 Statistics Used by the Planner", PostgreSQL Global Development Group, postgresql.org
  What pg_stats holds, what each column is used to estimate, and how extended statistics fix a correlation the planner otherwise assumes away. Use for: stage 6, when an estimate is wrong rather than a plan.

• Docs: "19.7 Query Planning", PostgreSQL Global Development Group, postgresql.org
  The planner's cost constants and the enable_* switches, with their defaults. Use for: stage 6, both for what a plan's choice rests on and for the switches that exist only to investigate one.

• Docs: "25.1 Routine Vacuuming", PostgreSQL Global Development Group, postgresql.org
  Why dead row versions accumulate, what a plain vacuum reclaims against what it returns to the operating system, and what autovacuum decides on its own. Use for: stage 6, when the table rather than the query is the problem.

• Docs: "pg_stat_statements", PostgreSQL Global Development Group, postgresql.org
  Per-statement call counts and totals, which turn "the database is slow" into a named list. Use for: stage 6, and note that it needs a server restart to load, so this arc cites it rather than demonstrating it.

• Docs: "Performance Tips", PostgreSQL Global Development Group, postgresql.org
  Statistics, planner cost constants, and how estimation goes wrong. Use for: stage 6, when the plan is bad because the estimate was.

• Docs: "Concurrency Control", PostgreSQL Global Development Group, postgresql.org
  Multiversion concurrency control, lock modes and deadlocks, from the implementation. Use for: stage 5, and for what a writer does to a reader.

• Docs: "13.3 Explicit Locking", PostgreSQL Global Development Group, postgresql.org
  Every lock mode with its conflict table, the row-level locks and their wait behaviour, deadlocks, and advisory locks. Use for: stage 5, and whenever a session is waiting and you need to know on what.

• Docs: "13.4 Data Consistency Checks at the Application Level", PostgreSQL Global Development Group, postgresql.org
  What the engine expects an application to do for itself, including the retry a Serializable transaction demands. Use for: stage 5, and for the argument that a retry has to re-decide rather than replay.

• Docs: "Reliability and the Write-Ahead Log", PostgreSQL Global Development Group, postgresql.org
  How a commit becomes durable, which is the one ACID promise this arc cannot demonstrate on a running server. Use for: stage 5, where durability is cited rather than shown.

• Docs: "Transaction Isolation", PostgreSQL Global Development Group, postgresql.org
  Each isolation level with the anomalies it permits and the errors it raises instead. Use for: stage 5, choosing a level on purpose.

• Docs: "ALTER TABLE", PostgreSQL Global Development Group, postgresql.org
  Every form of the statement with the lock each one takes and which of them rewrite the table, which is the authoritative half of migration safety. Use for: stage 7, before writing any schema change against a live table.

• Reference: "strong_migrations", Andrew Kane, github.com
  A maintained catalogue of migrations that are unsafe on a live database, each with the safe rewrite, and a PostgreSQL-specific section. Use for: stage 7, as the practitioner half of a subject with no canonical source. It is a Ruby library's documentation, so the catalogue travels and the code does not.

• Docs: "SQLAlchemy Documentation", SQLAlchemy contributors, docs.sqlalchemy.org
  The one ORM this arc uses as its worked example, cited for how to see the SQL it emits rather than for how to use it. Use for: stage 7, and nowhere else, since ORMs are out of scope as subjects.

• Cheat sheet: "SQL Injection Prevention Cheat Sheet", OWASP, cheatsheetseries.owasp.org
  Why string-built SQL is unsafe, and the defences ranked from parameterised statements down to escaping, discouraged. Use for: stage 8, and nowhere a database's own manual documents an application-layer vulnerability instead.

• Docs: "Chapter 21. Database Roles", PostgreSQL Global Development Group, postgresql.org
  Roles, attributes, membership and inheritance, and the predefined roles shipped with the engine. Use for: stage 8, roles and least privilege.

• Docs: "5.8. Privileges", PostgreSQL Global Development Group, postgresql.org
  Every privilege kind, what ownership grants for free, and what PUBLIC gets by default. Use for: stage 8, deciding exactly what to GRANT.

• Docs: "ALTER DEFAULT PRIVILEGES", PostgreSQL Global Development Group, postgresql.org
  Setting privileges for objects a role has not created yet, and the exact scope of "future" it covers. Use for: stage 8, keeping an application role's access from silently lapsing after a migration.

• Docs: "5.9. Row Security Policies", PostgreSQL Global Development Group, postgresql.org
  The default-deny model, USING versus WITH CHECK, how policies for the same command combine, and who bypasses them by default. Use for: stage 8, row-level security.

• Docs: "CREATE POLICY", PostgreSQL Global Development Group, postgresql.org
  The full syntax a policy accepts, including FOR a specific command and TO a specific role. Use for: stage 8, the exact clause a policy needs.

• Docs: "Chapter 12. Full Text Search", PostgreSQL Global Development Group, postgresql.org
  tsvector and tsquery, why LIKE lacks linguistic support and ranking, and the tables, indexes and configuration chapters that follow the introduction. Use for: stage 8, full-text search.

• Docs: "Chapter 37. Triggers", PostgreSQL Global Development Group, postgresql.org
  Trigger timing and level, and exactly what a BEFORE row trigger's return value does. Use for: stage 8, reviewing a trigger.

• Docs: "36.4. User-Defined Procedures", PostgreSQL Global Development Group, postgresql.org
  The precise differences from a function: no RETURNS clause, called with CALL, and transaction control a function cannot use. Use for: stage 8, reviewing a stored procedure.

• Docs: "Don't Do This", PostgreSQL contributors, wiki.postgresql.org
  A maintained list of choices that look reasonable and are regretted, with the reason for each. Use for: stage 4 type and schema decisions, and for review vocabulary.

• Docs: "Slow Query Questions", PostgreSQL contributors, wiki.postgresql.org
  What information a plan diagnosis actually requires, which doubles as a checklist for doing it yourself. Use for: stage 6, structuring an investigation.

• Book: "Use The Index, Luke", Markus Winand, use-the-index-luke.com
  Free web edition on indexing and query tuning, covering PostgreSQL, MySQL, Oracle and SQL Server side by side. Use for: stage 6, and for indexing advice that is not engine-specific.

• Book: "SQL Performance Explained", Markus Winand, sql-performance-explained.com
  The print edition of the same material, organised as a book. Use for: working through indexing systematically rather than by lookup.

• Site: "Modern SQL", Markus Winand, modern-sql.com
  What standard SQL has gained since SQL-92, feature by feature, with a table of which engines implement each. Use for: writing portable SQL, and for what the standard actually says.

• Docs: "Query Language Understood by SQLite", SQLite contributors, sqlite.org
  The full syntax of the second engine used here, including its deliberate divergences. Use for: reps that should need no server.

• Docs: "Query Planning", SQLite contributors, sqlite.org
  How a deliberately simple planner uses indexes, which makes the mechanism easier to see. Use for: stage 6, before the same ideas get harder in PostgreSQL.

• Docs: "Appropriate Uses For SQLite", SQLite contributors, sqlite.org
  A candid statement of what the engine is and is not for, from its author. Use for: stage 7, and for choosing an engine honestly.

• Book: "Designing Data-Intensive Applications", Martin Kleppmann, O'Reilly
  Chapter 7 derives the isolation anomalies from first principles, independently of any engine. Use for: stage 5 when a mental model is missing rather than a fact.

• Paper: "A Critique of ANSI SQL Isolation Levels", Berenson, Bernstein, Gray, Melton, O'Neil, O'Neil, Microsoft Research
  Shows that the standard's levels are defined by the anomalies they forbid, and that the definitions are incomplete. Use for: stage 5, and for why two engines can both be right about "repeatable read".

• Analyses: "Jepsen", Kyle Kingsbury, jepsen.io
  Published tests of what real systems actually guarantee under fault, database by database. Use for: stage 5 and stage 7, and for treating a vendor's isolation claim as a hypothesis.

Wisdom (Communities)

• Archive: "PostgreSQL Mailing Lists", PostgreSQL Global Development Group, postgresql.org
  Decades of public archives where planner behaviour and design decisions are explained by the people who wrote them, readable without subscribing. Use for: behaviour the manual states without justifying.

Gaps

• Normalisation has no PostgreSQL source. The manual documents constraints and types thoroughly and never teaches the normal forms, so stage 4 states first to third normal form and Boyce-Codd from ordinary relational theory and cites an encyclopaedia for the forms it deliberately does not teach. Any claim about a normal form in this workspace rests on a demonstration against the engine rather than on a citation.
• The ISO SQL standard is paywalled, so no lesson can cite it directly. Modern SQL is the substitute for what the standard requires, and it is a secondary source; a lesson that says "the standard says" is leaning on it.
• Cross-engine differences have no single reference. MySQL, Oracle and SQL Server behaviours have to be checked in their own documentation, and stage 7 portability material will need sources this list does not have.
• Zero-downtime migration has no canonical source, and stage 7 settled how to live with that. The authoritative half is PostgreSQL's own ALTER TABLE and CREATE INDEX pages plus the release notes dating the two optimisations the subject turns on. The practitioner half, the catalogue of which operation is unsafe and what to write instead, exists only in maintained community lists, of which strong_migrations is the most complete and is now listed above. The most-cited historical write-up, Braintree's, answers a scripted request with a 403 and cannot be linked here. Where the halves disagree, the stage verifies the mechanics against the engine rather than citing either.
• Reading ORM output is now sourced, once. The arc keeps ORMs out of scope as subjects, and stage 7 needed one concrete example, so it uses SQLAlchemy over this workspace's own driver and quotes SQL that library really emitted. Its documentation is listed above and is cited for how to see the statements rather than for how to use the library. Any other ORM's equivalents are named by pattern rather than by API.