This article demonstrates how to detect full table scans in SQLite using its statement statistics API, and suggests integrating this into Rails to warn or raise in test/development environments.
# Detecting Full Table Scans With SQLite
Source: [https://tenderlovemaking.com/2026/07/15/detecting-full-table-scans-with-sqlite/](https://tenderlovemaking.com/2026/07/15/detecting-full-table-scans-with-sqlite/)
I’m at[RubyConf](https://rubyconf.org/)this week, and it’s great\!
I recently read that[lobste\.rs](https://lobste.rs/)is[now running on SQLite](https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite)\. One part from the post caught my attention:
> I wish we could say in a test, “Fail if you encounter any full table scans”\. Which would have caught the perf issues we experienced during the first deploy\.
SQLite collects information about prepared statements and[exposes those statistics though an API](https://sqlite.org/c3ref/stmt_status.html)\. The upshot of this is that we can tell whether a statement did a full table scan after executing the statement without using an`EXPLAIN`\.
Here’s an example program that demonstrates detecting a query did a full table scan:
```
db = SQLite3::Database.new(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
# Insert a bunch of records
1_000.times do |i|
db.execute("INSERT INTO users (name, age) VALUES (?, ?)",
["user#{i}", i % 100])
end
# Prepare a statement and query it
stmt = db.prepare("SELECT * FROM users WHERE age = ?")
stmt.bind_param(1, 42)
stmt.to_a
# Check the number of full scan steps, we see a bunch because the query
# did a full table scan
fullscan_steps = stmt.stat(:fullscan_steps)
puts "fullscan_steps: #{fullscan_steps}"
if fullscan_steps > 0
puts "=> query performed a full table scan"
end
# Create an index
db.execute("CREATE INDEX idx_users_age ON users(age)")
# Now the query won't do a full table scan
stmt2 = db.prepare("SELECT * FROM users WHERE age = ?")
stmt2.bind_param(1, 42)
stmt2.to_a
puts "after adding index, fullscan_steps: #{stmt2.stat(:fullscan_steps)}"
```
Feels like we could integrate this in to Rails and warn or raise in test / development\. I’m not sure if we’d want to check this all the time in production, but maybe it would be fine?
A practical guide to six SQL patterns for detecting transaction fraud in financial data, including velocity checks, impossible travel detection, and other methods. The author shares real-world examples and tuning advice.
A thread discussing Cursor's experiment where a team of AI agents rebuilt SQLite from its manual in Rust, achieving 100% test pass rate with significant cost variation depending on model mix. Takeaways include using frontier models for decomposition and cheaper workers for implementation.