Searching thousands of records one by one is slow. An index is how the
database stops doing that.
THE IDEA
An index works like the index in a book. Instead of reading every page,
you jump straight to the right one. The database keeps a sorted structure
on a column, with pointers back to the rows.
FULL SCAN vs INDEXED LOOKUP
Without an index, finding one email means reading every row in the table.
With an index on that column, the database walks the sorted structure and
goes directly to the matching rows — far less work for that query.
IN RAILS
class AddIndexToUsersEmail ActiveRecord::Migration[8.0]
def change
add_index :users, :email, unique: true
end
end
Then User.find_by(email: "dev@example.com") can use it.
`unique: true` does two jobs: it speeds up lookups AND enforces uniqueness
at the database level — a real constraint, not just a model validation.
Your Rails validation can race; the database constraint cannot.
THE TRADE-OFF
Indexes are not free. Every index costs storage and makes writes slower,
because each INSERT and UPDATE has to maintain it. An index only helps
queries that actually use it. Index where your queries need it — then
measure with EXPLAIN ANALYZE rather than guessing.
—
#Astmation — #software #systems , #automation , and #AI-powered #applications .
Build. Automate. Evolve.
https://astmation.com/
#Shorts #Database #PostgreSQL #RubyOnRails #Rails #SQL #BackendDevelopment #DatabaseIndexing #SystemDesign #SoftwareEngineering
Watch on YouTube