Database indexing by data type: B-tree, GIN/GiST, columnstore

Most performance issues stem from choosing the wrong index or lacking one; in this article you'll learn how index types like B-tree, GIN/GiST and columnstore work and when to prefer each. You'll see measurable optimization steps applicable to relational systems (PostgreSQL, SQL Server) and NoSQL approaches (MongoDB, Elasticsearch).

Index types: how B-tree, GIN, GiST and columnstore work

B-tree indexes are suitable for unique values, range queries and ordering requirements; they are the best choice for most primary keys and frequently used WHERE columns. B-trees reduce page reads by searching with an internal-node to leaf-node structure, so they are effective on columns with high selectivity.

GIN and GiST indexes are designed for complex data types like documents, arrays or geographic data; GIN is commonly used for JSONB, array and full-text searches. Columnstore/OLAP-oriented indexes (e.g. cstore_fdw in PostgreSQL, SQL Server Columnstore) reduce I/O for large scans; because they can increase OLTP write costs, use them carefully in mixed workloads.

Index selection by query pattern and reading the plan

Index selection depends on understanding the query pattern: frequently used filter columns, ORDER BY and JOIN keys are priorities. Inspect the plan with EXPLAIN/EXPLAIN ANALYZE (PostgreSQL, MySQL), Query Store (SQL Server) or db.collection.explain() (MongoDB); check for sequential scans, index scans and bitmap usage. Do not decide without measuring the plan's I/O and CPU distribution.

Keeping statistics up to date is critical: use ANALYZE in PostgreSQL, update statistics in SQL Server and tune optimizer settings in MySQL as these significantly affect results. Creating full indexes on low-cardinality columns is often unnecessary; instead use partial indexes or expression indexes to reduce index size and restructuring cost.

Real-world scenario: applying GIN and partial indexes for an e-commerce catalog

Situation: On an e-commerce platform product attributes were stored in JSONB; filter and facet queries (e.g. color, size, technical specs) were slowing and full table scans increased. The system used PostgreSQL; query patterns used both matching and containment (jsonb @>).

Solution: We created a GIN index for JSONB and a partial index for active products; we also added expression indexes for frequently used keys. Measurement: average query time for specific filtered searches dropped from 1200ms to 95ms and seq scan rate fell from 80% to 10%; these numbers were obtained from EXPLAIN ANALYZE and production traces.

The example below creates a GIN and a partial index for a JSONB field in the products table. I first described the index's purpose and the query pattern it targets, then summarized why we used a partial index.

CREATE INDEX idx_products_attrs_gin
ON products
USING GIN (attributes);

-- Kısmi indeks: yalnızca aktif ve stokta olan ürünler
CREATE INDEX idx_products_attrs_gin_active
ON products
USING GIN (attributes)
WHERE active = true;

This structure sped up general containment queries while excluding inactive products from the index, reducing index size and update cost.

Advanced nuances: bloat, fillfactor, index-only scan and write amplification

Skipping index maintenance leads to bloat: in PostgreSQL autovacuum settings and in SQL Server regular rebuild/reorganize jobs preserve performance. You can control in-page free space with the fillfactor setting to reduce write amplification for update-heavy tables; for example applying fillfactor 80 to a high-update table can reduce page splits and index growth.

If you aim for index-only scans, ensure your index contains all referenced columns (covering index); use INCLUDE in PostgreSQL or INCLUDE columns in SQL Server to eliminate heap I/O. To address parameter sniffing and plan cache issues, use plan guides or optimization hints in SQL Server and evaluate prepared statement and bind parameter behavior in PostgreSQL.

The short example below shows creating a covering index for order queries; I explained why I used a covering index and why I set fillfactor.

CREATE INDEX idx_orders_user_status_amount
ON orders (user_id, status)
INCLUDE (total_amount)
WITH (fillfactor = 80);

This index enabled index-only scans for frequent user_id+status filtered reports, reducing main table I/O.

NoSQL and hybrid approaches: MongoDB, Elasticsearch and integration with relational index strategies

Indexing logic differs in NoSQL systems; MongoDB uses compound and multikey indexes, Elasticsearch uses inverted index and tokenization. In an architecture using both PostgreSQL and Elasticsearch, clearly separating which queries go to which system is a critical decision in database performance tuning.

Recommendation for hybrid solutions: keep OLTP and transactional workloads in the relational database, and delegate search and facet/aggregation queries to Elasticsearch; synchronize data using Change Data Capture or background indexing and measure consistency. This allows targeting both database optimization and search performance.

Why should you learn this?

Deepening database and SQL knowledge is a sought-after skill; job postings often require database optimization and indexing knowledge for mid and senior roles. With this ability you can reduce query costs, lower infrastructure expense and deliver faster experiences to users.

Also, knowing indexing strategies in both SQL Server and NoSQL contexts helps you choose the right technology in hybrid systems and read data from the right place; this reduces project risks and improves scalability.

Summary

• Choose index type based on data type and query pattern.
• Decisions without EXPLAIN and real monitoring metrics can be misleading.
• Partial and expression indexes reduce index size and cost.
• Neglecting maintenance (vacuum/reindex/fillfactor) causes index bloat and degrades performance.
• Hybrid approaches with NoSQL/Elasticsearch separate search and OLTP loads.

Frequently Asked Questions

What is the best database indexing method for JSONB?

GIN indexes are generally the best starting point for JSONB; GIN performs well for containment (@>) queries. However, if specific keys are accessed frequently, using expression indexes or partial indexes reduces index size and update cost, so measure with EXPLAIN ANALYZE.

What index topics should I focus on when learning SQL Server?

Focus areas for SQL Server: clustered vs nonclustered index design, creating covering indexes with INCLUDE columns, fillfactor and fragmentation management, monitoring plan changes with Query Store, and rebuild/reorganize strategies. These topics create direct performance improvements in daily operations.

How is database optimization different when learning NoSQL?

Indexing in NoSQL varies by data model and query type; for example MongoDB uses multikey and compound indexes, Elasticsearch uses an inverted index approach. If you need consistent transactions consider a hybrid architecture and design which queries go to which system.

What are the first measurement steps in database performance tuning?

First gather query patterns: identify heavy queries with EXPLAIN/Query Store/slow query log, then update statistics and target obvious seq scans. Apply changes in small steps and measure each change's effect so you can catch regressions quickly.