sql 122 lines · 2 tabs

Database indexing strategies for performance

Maria Garcia Feb 2026

2 tabs

-- Create basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status_date
  ON orders(user_id, status, created_at);

-- This index helps queries like:
-- WHERE user_id = ? AND status = ? AND created_at > ?
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ?
-- But NOT: WHERE status = ? (doesn't use index efficiently)

-- Partial index (PostgreSQL): Index subset of rows
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';

-- Covering index: Include extra columns
CREATE INDEX idx_users_email_covering
  ON users(email)
  INCLUDE (name, created_at);  -- PostgreSQL 11+

-- Or in MySQL:
CREATE INDEX idx_users_email_name
  ON users(email, name, created_at);

-- Expression index
CREATE INDEX idx_users_lower_email
  ON users(LOWER(email));

-- Helps with case-insensitive searches:
-- WHERE LOWER(email) = 'user@example.com'

-- Descending index for ORDER BY DESC
CREATE INDEX idx_posts_created_desc
  ON posts(created_at DESC);

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as index_scans,
  idx_tup_read as tuples_read,
  idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Full-text search index (PostgreSQL)
CREATE INDEX idx_articles_search
  ON articles
  USING GIN(to_tsvector('english', title || ' ' || content));

-- Query using full-text index:
SELECT title, content
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
  @@ to_tsquery('english', 'database & performance');

-- GiST index for geometric data
CREATE INDEX idx_locations_point
  ON locations
  USING GIST(coordinates);

-- JSON index (PostgreSQL)
CREATE INDEX idx_users_metadata
  ON users
  USING GIN(metadata jsonb_path_ops);

-- Query JSON with index:
SELECT * FROM users
WHERE metadata @> '{"premium": true}';

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_large_table_column
  ON large_table(column_name);

-- Drop unused indexes
DROP INDEX idx_old_unused_index;

-- Reindex for maintenance
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Find missing indexes (PostgreSQL)
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Find duplicate/redundant indexes
SELECT
  indrelid::regclass AS table_name,
  array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

-- Index bloat check
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indexrelid::regclass::text NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

2 files · sql Explain with highlit

Indexes dramatically speed up queries but slow down writes. B-tree indexes handle equality and range queries—default for most databases. I create indexes on foreign keys, frequently queried columns, and WHERE/ORDER BY clauses. Composite indexes order matters—most selective column first. Partial indexes filter rows, reducing index size. Covering indexes include all query columns, avoiding table lookups. UNIQUE indexes enforce constraints while providing lookup speed. Full-text indexes enable text search. Analyze query plans with EXPLAIN to identify missing indexes. Over-indexing wastes space and slows writes. Regular REINDEX maintains performance. Understanding index types—B-tree, Hash, GiST, GIN—optimizes different workloads.

Related snips

sql

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions

by Maria Garcia 2 tabs

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

ruby

Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = false
    Bullet.bullet_logger = true
    Bullet.console = true

N+1 query detection with Bullet gem

rails performance activerecord

by Alex Kumar 2 tabs

ruby

# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

SQL injection prevention with unsafe and safe query patterns

sql-injection owasp database

by Kai Nakamura 3 tabs

ruby

# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord

by Sarah Mitchell 3 tabs

ruby

json.array! @posts do |post|
  json.cache! ['v1', post], expires_in: 1.hour do
    json.id post.id
    json.title post.title
    json.excerpt post.excerpt
    json.published_at post.published_at

Fragment caching for expensive JSON serialization

rails caching performance

by Alex Kumar 1 tab

Share this code

Here's the card — post it anywhere.

Database indexing strategies for performance — share card

Link copied