inflearn logo

60 Practical Lessons in Rock-Solid Database Design and Performance

We’ll get to the root of settlement discrepancies, negative inventory, lock failures, and slow queries caused by using AI- or ORM-generated tables as-is. Across SQLite and PostgreSQL, 60 lessons take you from error-proof schemas, normalization, and immutable ledger modeling to window functions, EXPLAIN, B-tree composite indexes, concurrency locks, WAL/MVCC, hybrid vector search (RRF), and zero-downtime migrations.

1 learners are taking this course

Level Basic

Course period Unlimited

Python
Python
SQL
SQL
PostgreSQL
PostgreSQL
SQLite
SQLite
database
database
Python
Python
SQL
SQL
PostgreSQL
PostgreSQL
SQLite
SQLite
database
database

What you will gain after the course

  • The ability to establish a clean initial schema using natural keys vs. surrogate keys (BIGINT·UUIDv7), foreign key policies, the four constraints, exact monetary types (integer·NUMERIC), UTC time zones, and SQLite STRICT tables (Section 1)

  • Ability to distinguish between the boundaries of first-, second-, and third normal forms and point-in-time price snapshot preservation, and to model junction tables, hierarchical trees, immutable settlement ledgers, and soft-delete partial unique indexes for practical domains (Section 2)

  • Ability to write queries using conditional aggregation (FILTER) instead of application-side for-loop aggregation, defend against NULL with three-valued logic, use EXISTS anti-joins, and use WITH CTEs, recursive CTEs, and window functions (Top-N, LAG, moving averages) (Section 3)

  • Tuning skills: understand disk pages and B-tree principles, read EXPLAIN execution plans directly, and design equality-to-sort/range composite indexes (tens of times faster), as well as covering, partial, and expression indexes (Section 4)

  • Control over atomic UPDATE ... RETURNING to prevent overselling from concurrent deductions; pessimistic and optimistic locking and deadlock prevention; and SQLite WAL, batch inserts, FTS5, PostgreSQL MVCC, JSONB, partitioning, and Keyset cursor pagination (100x+ faster) (Sections 5–7)

  • Implementation of RRF hybrid search combining HNSW vector indexes with keyword search, plus the operational capability to perform three-phase, zero-downtime schema migrations using CONCURRENTLY and Expand-and-Contract, and point-in-time recovery (PITR) (Section 8)

Recommended for
these people

Who is this course right for?

  • Backend and full-stack developers who have struggled with duplicate data, FLOAT rounding errors in monetary amounts, tampering with past order receipts, and errors when users re-register after soft deletion—all after creating tables by relying solely on AI coding tools or ORM auto-generation.

  • If your data has grown to hundreds of thousands of rows and your lookup queries and deep pagination have gotten dramatically slower, but you’re not sure how to read EXPLAIN execution plans or what order to use when creating composite indexes.

  • Solo SaaS and AI app makers dealing with concurrency race conditions in first-come, first-served coupons or inventory deductions, or SQLite “Database is locked” errors in local or single-user services, and who need criteria for a future migration to PostgreSQL.

  • An engineer who wants to learn practical techniques for cleanly combining keyword search (FTS) and embedding-based vector search (RRF) on top of an existing relational database, and changing columns and schemas in a live service without any downtime.

Need to know before starting?

  • If you’ve ever seen basic SELECT and INSERT statements, you can follow along step by step from Lesson 1, “Integrity Basics,” even without any specialized database knowledge.

  • The exercises use Python’s built-in sqlite3 (SQLite 3.45+) and standard PostgreSQL 17 SQL without requiring a separate paid cloud subscription. They do not cover click-by-click procedures in a specific cloud console or setting up Oracle or big data clusters.

Hello
This is catgpt

Career Verified

A through-and-through AI researcher, 'CatGPT', who busted their ass reading papers and fine-tuning models. Then one day, a bolt-from-the-blue mission landed in their lap: "You’ve finished building the model, right? Now write the front end and back end too so we can deliver it to the client."

To survive, I was forced to make a tearful career change into a full-stack developer. After successfully delivering the solution and making it back, AI had advanced so much that this time, instead of coding, I found myself taking on the role of a PM coordinating a team with AI...!

Not stopping there, this max-level engineer went all in and poured their full-stack soul into building a “lecture production automation pipeline” and even bringing an “AI instructor” to life. What fate awaits CatGPT, the full-stack AI engineer who has automated even their own livelihood(?)!

More

Curriculum

All

60 lectures ∙ (8hr 31min)

Published: 
Last updated: 

Reviews

Not enough reviews.
Please write a valuable review that helps everyone!

catgpt's other courses

Check out other courses by the instructor!

Similar courses

Explore other courses in the same field!

Limited time deal

$46,200.00

30%

$51.70