Module lessons (1/4)
Indexes and B-Trees
How can a database find a single record among millions of rows in fractions of a second?
By default, when we ask SELECT * FROM customers WHERE city = 'Milano', Postgres has to perform a Sequential Scan: it physically reads the entire table from disk row by row, comparing each city with "Milano". When a table holds millions of records, this process cripples performance.
The main structural answer to these bottlenecks are Indexes.
Indexes (B-Trees)
Indexes work exactly like the analytical index at the back of a large book. Instead of reading the whole book to find where it talks about "Milano", we look up the back pages where the word "Milano" redirects us to [p. 45, 114, 230].
Databases, behind the scenes, build this separate, very compact data structure (often a "B-Tree") ordered by the column or columns in question, containing direct pointers to the physical storage of the matching rows.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);This single operation triggers the Postgres engine, which will extract and index all city values in the background. Once complete, every subsequent SELECT ... WHERE city = '...' will fly!
Primary keys and uniqueness
Did you know that the primary key (PRIMARY KEY) and UNIQUE key rules secretly use indexes under the hood? Since the DB must ensure that an email or ID is never duplicated when inserting or altering rows, PostgreSQL automatically creates an implicit B-Tree index on top of them: this way uniqueness checks take microseconds.
That's why joins on the ID field are incredibly performant.
Dropping an index
If you realise that heavy queries have changed and an index is never consulted, it's wise to destroy it and reclaim vital space:
DROP INDEX idx_customers_city;Your turn
In our e-commerce we notice through dashboards that we very often filter customers by nationality (the country column in customers). \nCreate a new base index on the country column and name it 'idx_customers_country'.
Show hint
Use CREATE INDEX index_name ON table_name(column_name);
Solution available after 3 attempts
Exploring secondary unique indexes
A B-Tree index can also be used to simulate a UNIQUE constraint at creation time and speed up lookups, while making the logical intent explicit and enforced.\n\nCreate a new index where you declare the UNIQUE keyword. Define it so it blocks any unwanted duplicate sign-ups from other users, forcing uniqueness on the 'email' column of 'customers', and name it 'idx_unique_email_customer'.
Show hint
Write CREATE UNIQUE INDEX index_name ON table(column);
Solution available after 3 attempts