Indexing Strategy and Design Questions

Stakeholders:

• Executive Sponsor (Head of Analytics) — budget/prioritization
• Product/Operations owners — define SLA and dashboard expectations
• Data Engineering — implement indexing/partitioning/denorm schemas
• BI Developers (myself + peers) — dashboard changes, queries, UX
• DBAs — performance tuning, backups
• QA/Testing — validation scripts
• Security & Compliance — data governance
• End-users (representative power users) — acceptance testing

Phases:

1. Discovery (1–2 weeks)

• Measure baseline latency per dashboard/query; collect slow-query logs and cardinality stats
• Prioritize top 5 dashboards by business impact and load
• Define success metrics (50% median latency, 95th pct SLA), rollback criteria, and window for rollout

2. Prototype (2–3 weeks)

• Data Eng builds indexed/partitioned/denormalized variants in a staging copy (small-scale)
• BI re-point sample dashboards to prototypes; capture explain plans and traces

3. Benchmark (1 week)

• Run synthetic and production-like load tests; compare median/95th latencies, resource use, and staleness
• Validate correctness (row counts, aggregates) and freshness

4. Pilot Rollout (2 weeks)

• Deploy to a subset of users during low-risk window; monitor metrics and user feedback

5. Full Rollout & Monitoring (ongoing)

• Gradual rollout with monitoring dashboards, alerts, and a runbook

Risk assessment:

• Data correctness risk from denormalization — mitigate with unit tests, reconciliation queries, and checksum comparisons
• Increased storage and ETL time — estimate costs, schedule ETL windows, and consider incremental updates
• Query plan regressions — keep explain plan baselines; involve DBA to tune indexes
• Access or governance gaps — ensure compliance reviews before write schemas

Rollback strategy:

• Maintain immutable backups and snapshot of production schemas before each change
• Feature-flag BI datasource endpoints to switch back to original tables instantly
• For schema changes: apply non-destructive changes first (create new tables/indexes), only switch endpoints after validation
• Predefine SLA thresholds that trigger immediate rollback and communication plan

Key validation steps:

• Automated reconciliation: row-level and aggregate comparisons between old and new sources
• Performance validation: compare median and 95th latency under identical query/load
• Resource checks: CPU, I/O, query concurrency impacts
• User acceptance: selected power users run typical workflows and sign off
• Observability: production monitors, alerts, and post-deploy postmortem within 72 hours

This plan balances speed, correctness, and risk control while involving all necessary stakeholders and measurable success criteria.

B-tree (balanced-tree)

• How it works: Ordered tree of keys with pointers to rows/pages; supports range scans and point lookups by traversing log(N) levels.
• Query patterns: Equality and range predicates (WHERE id = X, WHERE date BETWEEN A AND B), ORDER BY, prefix scans, and joins on indexed columns.
• Best for: Row stores and some columnar engines with per-column B-trees. In BI: useful for selective filters and primary-key lookups on transactional dims.

Bitmap index

• How it works: For each distinct value of a low-cardinality column, a bitmap (bitset) marks rows containing that value; bitwise ops combine filters extremely fast.
• Query patterns: Low-cardinality filters (gender, status), multi-attribute combinational filtering (AND/OR), ad-hoc slice-and-dice.
• Best for: Columnar and read-optimized analytic stores. Very effective in BI for dashboards with many categorical filters and high read concurrency.

Inverted index

• How it works: Token → posting list of row/record IDs (and positions); used for text, arrays, tags.
• Query patterns: Full-text search, token matching, wildcards, multi-valued attributes (search across product descriptions or tags).
• Best for: Columnar/JSON stores or dedicated search engines (Elasticsearch). In BI: useful for free-text search in reports or filtering unstructured fields.

Zone map (min/max) index

• How it works: Storage-level metadata records min/max (or small synopsis) per physical block/segment; engine skips entire blocks that can't satisfy predicate.
• Query patterns: Large-range scans, selective range/date filters, aggregation over recent partitions.
• Best for: Columnar storage where data is sorted/partitioned (time-series). In BI: accelerates time-window queries, big aggregations, and reduces I/O for dashboard queries scanning recent data.

Practical guidance for BI analysts:

• Use B-tree for selective lookups and join keys; rely on columnar engines’ zone maps and bitmaps for fast aggregations and dashboard filters.
• Combine with partitioning/sorting: zone maps shine when data is sorted by common BI predicates (date, customer_id).

For date-range filters on order_date a single-column index is appropriate. For an online production BI table use CONCURRENTLY to avoid long locks. Example B-tree (general purpose):

CREATE INDEX CONCURRENTLY idx_sales_order_date
ON sales_fact (order_date);

For very large, mostly append-only time-series data a BRIN index can be far smaller and faster for wide range scans:

CREATE INDEX CONCURRENTLY idx_sales_order_date_brin
ON sales_fact USING BRIN (order_date)
WITH (pages_per_range = 16);

Recommendations and rationale:

• B-tree: best for selective ranges and point lookups; precise ordering. Good if queries often target narrow windows.
• BRIN: ideal when data is clustered by time (append-only) and table is huge — extremely compact and fast for large-range scans; tune pages_per_range to control granularity.
• Use CREATE INDEX CONCURRENTLY to avoid blocking writes.
• Consider partitioning the table by RANGE (order_date) for maintenance and pruning older partitions — improves query performance and makes vacuum/reindex cheaper.
• Maintain stats (ANALYZE) and consider CLUSTER or periodic REINDEX if data is not naturally clustered for B-tree efficiency.

Clustered vs nonclustered index — short definition:

• Clustered index: physically orders table rows by the index key (table is stored in that order). One clustered index per table.
• Nonclustered index: separate structure containing key values and pointers (row locators) to the actual rows; can have many per table.

Key implications:

• Storage: clustered index stores data inline (no extra pointer), so the table is the index; nonclustered stores duplicates of key columns + pointer → extra storage and maintenance cost.
• Range scans: clustered indexes excel at range scans because adjacent key values are stored contiguously on disk (fast sequential I/O). Nonclustered range scans are slower if they require jumping to many row locators.
• Sort operations: queries needing ordered output on clustered key often avoid explicit sort (ORDER BY covered by index). Nonclustered can also avoid sort if it exactly matches the index, but may require lookups or additional sorting.

Three BI-focused situations preferring clustered index:

1. Time-series fact tables (timestamp/date as clustered key): BI queries often filter or aggregate over time ranges (last 7 days, monthly). Clustered ordering yields efficient range scans, compact storage for sequential writes, and avoids sorts for time-ordered reports.
2. Large append-only event/fact tables where most queries select contiguous blocks (e.g., customer_id + event_time with clustering on customer_id then time): reduces I/O for per-customer trends and improves cache locality.
3. When supporting ORDER BY or GROUP BY on a frequently used dimension key in key dashboards (e.g., product_id then sales_date): clustered index can eliminate costly sort operations and speed dashboards with heavy aggregations.

When to prefer nonclustered index:

• Dimension tables or lookup queries needing many different access patterns (search by name, code): nonclustered indexes let you create multiple access paths with less impact on physical row order.
• Wide tables where clustering by a nonselective column would bloat storage and increase maintenance; putting a narrow key as nonclustered index reduces storage overhead.

Best practice for BI: cluster on the most common range-filtered or ordering key (often date/time or a natural partitioning key); add nonclustered covering indexes for high-cardinality selective filters used by dashboards to avoid lookups.

Cardinality is the number of distinct values in a column. Selectivity is the fraction (or inverse) of rows matched by a predicate — a highly selective predicate returns few rows (close to 0%), a low-selectivity predicate returns many rows.

In your customers table (1,000,000 rows):

• country has cardinality = 5 (very low); any equality filter country = 'X' matches ~200,000 rows → low selectivity.
• customer_id has cardinality = 1,000,000 (unique); customer_id = 123 matches 1 row → very high selectivity.

Which to index: customer_id is the better index candidate for point-lookup queries because its high selectivity means an index seek returns very few rows, giving large performance gains. Indexing country is usually not helpful for equality filters (returns ~20% of table); the optimizer will often prefer a full table scan or a parallel scan rather than use the index due to the overhead of many random I/O operations.

How selectivity affects optimizer choice:

• High-selectivity predicates favor index seeks (and index nested-loop joins).
• Low-selectivity predicates often lead the optimizer to choose a table/partition scan or a bitmap/index scan that is cheaper overall.
• Up-to-date statistics on cardinality/selectivity are critical so the optimizer can estimate row counts and pick the right plan.

Practical BI notes: keep primary keys and foreign keys indexed (high cardinality), consider composite or covering indexes for common filtered+selected columns, and avoid indexing low-cardinality columns unless used in composite indexes or for bitmap indexing in analytic systems.