Thread by @AlexYarotsky

9 #PostgreSQL Indexing Tips Every #rails Dev Should Know 🚀

Indexes are your first #database optimization tactic. Get them right, and everything else gets easier.

1️⃣ BTREE – The default. Rock solid for lookups.
2️⃣ GIN – JSONB & full-text search on steroids.
3️⃣ BRIN – Huge, sequential tables? Use this.
4️⃣ Partial – Only index what actually matters.
5️⃣ Covering – Store extra columns, skip table lookups.
6️⃣ Unique – Constraints, but without the overhead.
7️⃣ Concurrent – Add indexes without downtime.
8️⃣ Expression – Index computed values, query smarter.
9️⃣ Multi-Column – Optimize multiple WHEREs.

Let’s break these down 🧵👇

1️⃣ BTREE Indexes – The default and most commonly used in PostgreSQL. Perfect for:

✅ Equality queries (id = ?)
✅ Range queries (created_at > ?)
✅ Sorting (ORDER BY name ASC)

🔎 PostgreSQL uses BTREE for indexing primary keys by default, so no need to manually create one. But for frequently queried columns, adding an index manually can speed things up significantly.

CREATE INDEX idx_users_lastname ON users (last_name);

This will improve performance for queries like:

SELECT * FROM users WHERE last_name = 'Doe';

2️⃣ GIN Indexes – For JSONB, full-text search, and arrays.

CREATE INDEX idx_users_data ON users USING GIN (data);

This makes queries like:
SELECT * FROM users WHERE data @> '{"role": "admin"}';

Much faster!

🔹 Full-text search? Use GIN with tsvector

CREATE INDEX idx_users_bio ON users USING GIN (to_tsvector('english', bio));

Now searching for keywords is instant:

SELECT * FROM users WHERE to_tsvector('english', bio) @@ to_tsquery('developer');

3️⃣ BRIN Indexes – Best for large, sequential tables (think logs, time-series, analytics).

🔹 Example: Index logs efficiently

CREATE INDEX idx_logs_time ON logs USING BRIN (created_at);

Now queries like:
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-02-01';

Runs way faster with minimal index size.

✅BRIN indexes don’t store individual values, just value ranges.
✅Perfect for tables with billions of rows that grow sequentially.

4️⃣ Partial Indexes – Why index everything when you only query part of it?

🔹 Example: Only index active users

CREATE INDEX idx_users_active ON users (email) WHERE active = true;

This speeds up queries like:

SELECT * FROM users WHERE active = true AND email = '[e-mail]';

✅ Smaller index size = faster writes
✅ Queries ignore unnecessary data

5️⃣ Covering Indexes – Index extra columns so PostgreSQL doesn’t have to fetch from the table.

🔹 Example: Avoid unnecessary lookups

CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_price);

Now this query runs entirely from the index:

SELECT user_id, total_price FROM orders WHERE user_id = 123;

🔍 More on index-only scans?
x.com/AlexYarotsky/s…

6️⃣ Unique Indexes – Faster and safer than Rails validations.

🔹 Example: Prevent duplicate emails

CREATE UNIQUE INDEX idx_users_email ON users (email);

✅ Much faster than validates_uniqueness_of
✅ Prevents race conditions in concurrent inserts

7️⃣ Concurrent Index Creation – Add indexes without locking your table.

🔹 Problem: Normally, adding an index locks writes.
🔹 Solution: Use CONCURRENTLY for zero downtime:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

This ensures your app keeps running while the index is created.

🚀 Best practice? Always use this in production to avoid blocking writes!

8️⃣ Expression Indexes – Index computed values for faster queries.

🔹 Example: Speed up case-insensitive email lookups:

CREATE INDEX idx_lower_email ON users (LOWER(email));

Now, this query uses the index:
SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

✅Faster lookups on computed values.
✅Eliminates full-table scans on expressions.

9️⃣ Multi-Column Indexes – Optimize queries that filter on multiple WHERE clauses.

🔹 Example: If queries filter by last_name, first_name, and email, index them together:

CREATE INDEX idx_users_name_email ON users (last_name, first_name, email);

Now, this query fully utilizes the index:

SELECT * FROM users WHERE last_name = 'Doe' AND first_name = 'John';

⚡ Key Considerations:

PostgreSQL can combine single-column indexes using a bitmap scan, so multi-column indexes aren’t always needed.

They only optimize queries that reference columns in order ((a, b) works for WHERE a = x or WHERE a = x AND b = y, but not WHERE b = y).

Benchmark before adding—multi-column indexes can be redundant if a single-column index on the first column already exists.

Use them when queries always filter by the leading column! 🚀

If you made it this far, you care about speed. 🚀

I share PostgreSQL tips every day. Hit follow. 🔥