PostgreSQL

Indexes in PostgreSQL enhance the performance of database queries by allowing for faster retrieval of rows. By creating indexes, you can optimize search operations and reduce the time it takes to access specific data. PostgreSQL supports several index types, each suited for different types of queries and data. Understanding the various index types and their appropriate use cases is crucial for optimizing your database performance.

Here’s an overview of the common index types in PostgreSQL:

1. B-Tree Indexes
   • Default and most common type of index.
   • Efficient for equality and range queries.
   • Example code for a banking context:

     CREATE TABLE customers (
         customer_id SERIAL PRIMARY KEY,
         name VARCHAR(100) NOT NULL,
         email VARCHAR(100) UNIQUE
     );
     
     CREATE INDEX idx_customers_name ON customers (name);
     

2. Hash Indexes
   • Useful for equality comparisons.
   • Not as commonly used due to limitations compared to B-Tree indexes.
   • Example code:

     CREATE TABLE accounts (
         account_id SERIAL PRIMARY KEY,
         account_number VARCHAR(20) UNIQUE NOT NULL
     );
     
     CREATE INDEX idx_accounts_number_hash ON accounts USING HASH (account_number);
     

3. GIN (Generalized Inverted Index)
   • Effective for indexing composite values, such as arrays or full-text search.
   • Suitable for text search and array data types.
   • Example code:

     CREATE TABLE transactions (
         transaction_id SERIAL PRIMARY KEY,
         transaction_details TEXT
     );
     
     CREATE INDEX idx_transactions_details_gin ON transactions USING GIN (to_tsvector('english', transaction_details));
     

4. GiST (Generalized Search Tree)
   • Flexible index type that supports many types of queries, such as spatial data and ranges.
   • Used for complex data types and queries.
   • Example code:

     CREATE TABLE branches (
         branch_id SERIAL PRIMARY KEY,
         location GEOMETRY(Point, 4326)
     );
     
     CREATE INDEX idx_branches_location_gist ON branches USING GIST (location);
     

5. BRIN (Block Range INdexes)
   • Designed for very large tables with data that is naturally ordered.
   • Efficient for large data sets with physical ordering of rows.
   • Example code:

     CREATE TABLE large_transactions (
         transaction_id SERIAL PRIMARY KEY,
         transaction_date DATE
     );
     
     CREATE INDEX idx_large_transactions_date_brin ON large_transactions USING BRIN (transaction_date);
     

   By choosing the right index type for your queries, you can significantly enhance the performance of your PostgreSQL database.

Database Index Types with Code Examples

Indexes are critical for improving the speed of data retrieval operations on a database table. Here's a comprehensive list of index types along with their definitions and SQL code examples.

1. Primary Index

Ensures that the primary key is unique and improves search speed based on the primary key.

CREATE UNIQUE INDEX idx_primary ON Users (UserID);

2. Secondary Index

Improves search performance on columns other than the primary key.

CREATE INDEX idx_secondary ON Orders (OrderDate);

3. Unique Index

Ensures all values in a column are unique.

CREATE UNIQUE INDEX idx_unique_email ON Users (Email);

4. Composite Index

Created on two or more columns to improve performance on queries involving multiple columns.

CREATE INDEX idx_composite ON Orders (CustomerId, OrderDate);

5. Full-Text Index

Allows for complex searches involving words within text columns.

CREATE FULLTEXT INDEX idx_fulltext ON Articles (Content);

6. Bitmap Index

Efficient for columns with a limited number of distinct values.

-- Note: Bitmap indexes are specific to certain DBMS, example not universally applicable

7. Clustered Index

Defines the physical order of data in a table, aligning with the index order.

CREATE CLUSTERED INDEX idx_clustered ON Customers (CustomerID);

8. Non-Clustered Index

Stores the index structure separately from the table data.

CREATE NONCLUSTERED INDEX idx_nonclustered ON Orders (OrderDate);

9. Spatial Index

Optimized for spatial data, allowing for efficient querying of geographical locations.

CREATE SPATIAL INDEX idx_spatial ON Locations (GeographyColumn);

10. Hash Index

Best for high-performance equality searches.

-- Note: Hash indexes are specific to certain DBMS, example not universally applicable

11. Covering Index

Includes all columns needed to satisfy a query, eliminating the need to read the table data.

CREATE INDEX idx_covering ON Orders (OrderDate, CustomerID, OrderTotal);

12. Partial (Filtered) Index

Created on a subset of table rows, satisfying a specified filter condition.

CREATE INDEX idx_partial ON Orders (OrderDate) WHERE OrderStatus = 'Shipped';

13. Expression Index

Built on the result of an expression or function applied to table columns.

CREATE INDEX idx_expression ON Users (LOWER(Username));

14. B-Tree Index

A balanced tree structure that keeps data sorted for quick searches and access.

-- B-Tree is the default for many CREATE INDEX commands

15. GIN (Generalized Inverted Index)

Efficient for indexing composite values like arrays or full-text search vectors.

CREATE INDEX idx_gin ON Documents USING GIN (DocumentTokens);

16. GiST (Generalized Search Tree)

A flexible framework for indexing complex data types, supporting custom search functions.

CREATE INDEX idx_gist ON Locations USING GiST (GeographyColumn);