High Performance
SQLite
Introduction
Introduction to this course
Introduction to SQLite
SQLite is safe
It's just a file
Running SQLite locally
Good uses for SQLite
SQLite's limitations
The documentation
SQLite Internals
SQLite's structure
SQLite's file format
Dot commands
Pragmas
Virtual tables
Schema
Flexible types
Types
Strict types
Dates
Booleans
Floating point
Rowid tables
Auto increments
Without rowid
Generated columns
Optimizing SQLite
Locking
Rollback mode
WAL mode
WAL vs Journal benchmarks
Busy timeout
Transaction modes
Vacuum
Analyze & Optimize
Suggested pragmas
Faster inserts
Indexes
Introduction to indexes
B+ trees
Primary, secondary, and clustered indexes
Without rowid tables
Benchmarking without rowid tables
Imposter tables
Primary key data types
Where to add indexes
Index selectivity
Composite indexes
Composite ordering
Covering indexes
Partial indexes
Indexes on expressions
Automatic indexes
Duplicate indexes
Indexing joins
Advanced SQL
Explain
Index obfuscation
Joins
Subqueries
Unions
CTEs
Recursive CTEs
Window functions
Dealing with NULLs
Row value syntax
Indexed sorting
Upserts
Returning
Aggregates
Triggers
Operating SQLite
Altering schema
Altering schema with tools
Multi-database
Multi-tenancy
Backups
Exports
How to corrupt SQLite
Advanced JSON
Intro to JSON
JSON vs. JSONB
Argument types: path, value, JSON
JSON5
Valid JSON
Creating JSON objects + arrays
JSON Extraction
Updating JSON
JSON aggregates
JSON table functions
Indexing JSON
Full Text Search
Creating FTS tables
Performing searches
Ranking results
Highlighting matches
Bonus Interviews
DHH discusses SQLite in Ruby on Rails
Distributed SQLite with Litestream and LiteFS
Offline-first, multiplayer SQLite
Production SQLite with Turso and libSQL
Migrating from Postgres to SQLite with Kent C. Dodds
Ruby on Rails with SQLite
Moving from Redis to SQLite with Mike Buckbee
Locked video

Please purchase the course to watch this video.

Video thumbnail
Operating SQLite
Multi-database

Full Course

$
179
$229
USD, one-time fee
Some people stray away from a course like this because they find it too niche, but really there are great takeaways that you can apply to other databases. There are sections that cover optimization like how to treat indexing and how to write better queries SQLite is also relatively smaller, so it makes it easier to digest and really understand the engine that drives your application layer. I think this course really hit the nail on all points and I highly recommend it to anyone even curious about databases in general. Also of course the Aaron Francis/Try Hard touch always makes it a great time and interactive.
Dylan Hackworth
Dylan Hackworth

SQLite for Production

Build with and integrate SQLite into your production applications.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Turso

Summary

In this video, I share a tip on using multiple SQLite databases to improve performance by splitting them based on function, like queues and caches, alongside your main application database. This approach helps manage write contention since SQLite allows only one writer at a time, especially when handling more write-heavy tasks like caching and queueing. By having separate databases, you can achieve better efficiency and prepare for concepts like multi-tenancy, which I'll discuss in the next video.

Video Transcript

This is a quick tip. I'm not even gonna show my screen because there's nothing to show. The tip here is use multiple databases. I'm not talking here about multi tenancy which we'll talk about in the next video. What I'm talking about is splitting up your database by function.

So, in a traditional web app, you would likely have some sort of cute job system and some sort of caching system. In most major frameworks you can have a database persistent layer that backs both of those things. So your queued jobs are all stored in the database and your cached values are all stored in the database as well. Those are pretty common drivers. With any other type of database, that's that's probably fine because you're not, you're not having global right contention.

But with SQLite, remember there can only be 1 writer at a time. And something like a cache or a queue may be a lot higher than your traditional web app. It may have a lot higher percentage of writes than a traditional web app. A traditional web app is like 80 to 90 to 95% reads, but a queue is gonna be a lot higher. A cache is gonna be a lot higher because you're constantly putting jobs in or putting new cached items in.

So when you're using SQLite, to get around that, you should consider creating a queue dot SQLite and a cache dot SQLite database alongside your regular application database that holds, you know, your users and your comments and your to do's and all of that stuff. So this is very this is a very common pattern. I think this is what the light stack for rails does. But when you separate, when you break out those databases, remember databases are are cheap and easy to create with SQLite. When you create that out, or when you, when you break that out, you functionally get the benefit of having 3 separate, like, 3 separate concurrent writers.

1 to cache, 1 to queue and 1 to your app database. And this can serve us, this this framework can serve us quite well. When we move into multi tenancy and we start thinking about having a database per user, you realize that, oh, maybe one tenant is not that busy. All the tenants combined are quite busy, but one tenant is not that busy and maybe we should create a database per tenant which is what we're gonna talk about in the next video.