Notes from https://www.hellointerview.com/learn/system-design/core-concepts/db-indexing

• Indexes are used to make database queries faster
  • Without an index, finding a user by email means scanning every single row in the users table
  • With an index, database can jump straight to the right row
• B-tree
  • Most common index
  • Keeps data stored in a tree structure that supports both exact lookups (find user with email X) and range queries (find all orders between date A and date B)
  • Most relational databases create B-tree indexes by default
• Hash indexes
  • Less common
  • Faster for exact matches but can’t do range queries
• Specialized indexes
  • Full-text indexes
    • For search (finding documents containing specific words) and geospatial indexes
  • Geospatial indexes
    • Location queries (find restaurants within 5 miles)
• Interviews
  • Think about query patterns and propose indexes on fields that are queried frequently
    • If looking up users by email for authentication, index the email column
    • If fetching a user’s orders, index the user_id column on the orders table
    • Composite queries
      • “Find events in San Francisco on December 25th” might need compound index on both city and date
• Specialized needs
  • Elasticsearch
    • Go-to full-text search (searching tweets or documents)
  • Geospatial queries in Postgres
    • PostGIS is a popular extension
  • External indexes
    • Typically sync from primary database via change data capture (CDC)
      • Search index will lag slightly behind the primary database
    • Data read from search index is going to be stale by some small amount
      • Almost always acceptable for search use cases
      • Tradeoff worth it because it lets you search in ways the main database can’t handle

Notes from https://www.hellointerview.com/learn/system-design/core-concepts/db-indexing