Chapter 14: Indexes & Performance

Query Optimization and Index Strategy

14.1 Overview

This chapter provides the complete indexing strategy for the POS Platform database, organized by query pattern. Proper indexing is critical for a multi-tenant POS system where response times directly impact customer experience.

Performance Targets

14.2 Index Types and When to Use Them

B-Tree Indexes (Default)

Best for: Equality comparisons, range queries, sorting

-- Equality lookup (most common)
CREATE INDEX idx_products_sku ON products(sku);

-- Range query support
CREATE INDEX idx_orders_date ON orders(created_at);

-- Composite for multiple conditions
CREATE INDEX idx_inventory_lookup ON inventory_levels(variant_id, location_id);

BRIN Indexes (Block Range)

Best for: Time-series data, append-only tables, large datasets

-- Inventory transactions (append-only, ordered by time)
CREATE INDEX idx_inventory_trans_date ON inventory_transactions USING BRIN (created_at);

-- RFID scan events (high-volume, time-ordered)
CREATE INDEX idx_rfid_events_created ON rfid_scan_events USING BRIN (first_seen_at);

-- Sync queue (sequential inserts)
CREATE INDEX idx_sync_queue_created ON sync_queue USING BRIN (created_at);

BRIN Benefits:

• 100x smaller than B-tree for time-series
• Faster inserts (less index maintenance)
• Perfect for audit/event tables

BRIN Limitations:

• Only useful when data is physically ordered
• Less precise (scans blocks, not rows)

GIN Indexes (Generalized Inverted)

Best for: JSONB columns, full-text search, arrays

-- JSONB configuration columns
CREATE INDEX idx_devices_settings ON devices USING GIN (settings);
CREATE INDEX idx_tenant_modules_config ON tenant_modules USING GIN (configuration);

-- Full-text product search
CREATE INDEX idx_products_search ON products USING GIN (
    to_tsvector('english', name || ' ' || COALESCE(description, ''))
);

-- Array columns
CREATE INDEX idx_cash_pickups_bags ON cash_pickups USING GIN (bag_numbers);

Partial Indexes

Best for: Queries with consistent WHERE clauses, reducing index size

-- Only active products (common filter)
CREATE INDEX idx_products_active ON products(name)
    WHERE is_active = TRUE AND deleted_at IS NULL;

-- Only pending sync items
CREATE INDEX idx_sync_queue_pending ON sync_queue(device_id, priority, created_at)
    WHERE status = 'pending';

-- Only open shifts
CREATE INDEX idx_shifts_open ON shifts(location_id, cash_drawer_id)
    WHERE status = 'open';

-- Only unresolved conflicts
CREATE INDEX idx_sync_conflicts_unresolved ON sync_conflicts(entity_type, created_at)
    WHERE resolved_at IS NULL;

Covering Indexes (INCLUDE)

Best for: Avoiding table lookups for frequently accessed columns

-- Product lookup returns name and price without table access
CREATE INDEX idx_products_sku_covering ON products(sku)
    INCLUDE (name, base_price, is_active)
    WHERE deleted_at IS NULL;

-- Inventory lookup includes quantity
CREATE INDEX idx_inventory_covering ON inventory_levels(variant_id, location_id)
    INCLUDE (quantity_on_hand, quantity_reserved)
    WHERE deleted_at IS NULL;

-- Customer lookup by loyalty number
CREATE INDEX idx_customers_loyalty_covering ON customers(loyalty_number)
    INCLUDE (first_name, last_name, loyalty_points)
    WHERE loyalty_number IS NOT NULL AND deleted_at IS NULL;

14.3 Index Strategy by Domain

Domain 1-2: Catalog (Products, Categories)

-- ============================================================
-- PRODUCT LOOKUP INDEXES
-- ============================================================

-- Primary product lookup by SKU (unique, filtered for soft delete)
CREATE UNIQUE INDEX idx_products_sku ON products(sku)
    WHERE deleted_at IS NULL;

-- Product search by name (full-text)
CREATE INDEX idx_products_name_search ON products
    USING GIN (to_tsvector('english', name));

-- Filter by brand (common in category pages)
CREATE INDEX idx_products_brand ON products(brand_id)
    WHERE is_active = TRUE AND deleted_at IS NULL;

-- Filter by product group (department browsing)
CREATE INDEX idx_products_group ON products(product_group_id)
    WHERE is_active = TRUE AND deleted_at IS NULL;

-- ============================================================
-- VARIANT LOOKUP INDEXES
-- ============================================================

-- Variant lookup by SKU (unique)
CREATE UNIQUE INDEX idx_variants_sku ON variants(sku)
    WHERE deleted_at IS NULL;

-- POS barcode scan (unique, critical for checkout speed)
CREATE UNIQUE INDEX idx_variants_barcode ON variants(barcode)
    WHERE barcode IS NOT NULL AND deleted_at IS NULL;

-- Product's variants list
CREATE INDEX idx_variants_product ON variants(product_id, size, color)
    WHERE is_active = TRUE AND deleted_at IS NULL;

-- ============================================================
-- CATEGORY NAVIGATION INDEXES
-- ============================================================

-- Category hierarchy traversal
CREATE INDEX idx_categories_parent ON categories(parent_id)
    WHERE is_active = TRUE;

-- Category sort order for UI
CREATE INDEX idx_categories_display ON categories(display_order, name)
    WHERE is_active = TRUE;

-- ============================================================
-- COLLECTION & TAG INDEXES
-- ============================================================

-- Active collections (marketing pages)
CREATE INDEX idx_collections_active ON collections(is_active, start_date, end_date)
    WHERE is_active = TRUE;

-- Products in collection
CREATE INDEX idx_product_collection_coll ON product_collection(collection_id, display_order);

-- Products with tag
CREATE INDEX idx_product_tag_tag ON product_tag(tag_id);

Domain 3: Inventory

-- ============================================================
-- INVENTORY LEVEL INDEXES
-- ============================================================

-- Primary lookup: variant + location (covered)
CREATE UNIQUE INDEX idx_inventory_lookup ON inventory_levels(variant_id, location_id)
    INCLUDE (quantity_on_hand, quantity_reserved, reorder_point)
    WHERE deleted_at IS NULL;

-- Location inventory list (for inventory screens)
CREATE INDEX idx_inventory_by_location ON inventory_levels(location_id, variant_id)
    WHERE deleted_at IS NULL;

-- Low stock alerts (filtered, ordered by severity)
CREATE INDEX idx_inventory_low_stock ON inventory_levels(location_id, quantity_on_hand)
    WHERE quantity_on_hand <= reorder_point
      AND deleted_at IS NULL
      AND reorder_point > 0;

-- Out of stock items
CREATE INDEX idx_inventory_out_of_stock ON inventory_levels(location_id)
    WHERE quantity_on_hand <= 0
      AND deleted_at IS NULL;

-- ============================================================
-- INVENTORY TRANSACTION INDEXES (BRIN + B-Tree)
-- ============================================================

-- Time-series primary index (BRIN for efficiency)
CREATE INDEX idx_inventory_trans_date ON inventory_transactions
    USING BRIN (created_at);

-- Variant history (for product page history)
CREATE INDEX idx_inventory_trans_variant ON inventory_transactions(variant_id, created_at DESC);

-- Location activity (for location reports)
CREATE INDEX idx_inventory_trans_location ON inventory_transactions(location_id, created_at DESC);

-- Reference document lookup
CREATE INDEX idx_inventory_trans_ref ON inventory_transactions(reference_type, reference_id)
    WHERE reference_type IS NOT NULL;

-- Transaction type filtering
CREATE INDEX idx_inventory_trans_type ON inventory_transactions(transaction_type, created_at DESC);

Domain 4: Sales (Orders, Customers)

-- ============================================================
-- ORDER INDEXES
-- ============================================================

-- Order number lookup (receipt reprint)
CREATE UNIQUE INDEX idx_orders_number ON orders(order_number);

-- Orders by date (primary reporting index)
CREATE INDEX idx_orders_date ON orders(created_at DESC);

-- Orders by location + date (store reports)
CREATE INDEX idx_orders_location_date ON orders(location_id, created_at DESC);

-- Customer order history
CREATE INDEX idx_orders_customer ON orders(customer_id, created_at DESC)
    WHERE customer_id IS NOT NULL;

-- Shift reconciliation
CREATE INDEX idx_orders_shift ON orders(shift_id, status)
    WHERE shift_id IS NOT NULL;

-- Order status filtering
CREATE INDEX idx_orders_status ON orders(status, created_at DESC)
    WHERE status != 'completed';  -- Completed is default, filter for exceptions

-- ============================================================
-- ORDER ITEMS INDEXES
-- ============================================================

-- Line items for order
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- Sales by variant (product performance)
CREATE INDEX idx_order_items_variant ON order_items(variant_id, created_at DESC);

-- Returns tracking
CREATE INDEX idx_order_items_returned ON order_items(order_id)
    WHERE is_returned = TRUE;

-- ============================================================
-- CUSTOMER INDEXES
-- ============================================================

-- Loyalty card lookup (POS checkout)
CREATE UNIQUE INDEX idx_customers_loyalty ON customers(loyalty_number)
    WHERE loyalty_number IS NOT NULL AND deleted_at IS NULL;

-- Email lookup (unique)
CREATE UNIQUE INDEX idx_customers_email ON customers(email)
    WHERE email IS NOT NULL AND deleted_at IS NULL;

-- Phone lookup
CREATE INDEX idx_customers_phone ON customers(phone)
    WHERE phone IS NOT NULL AND deleted_at IS NULL;

-- Name search (partial match supported)
CREATE INDEX idx_customers_name ON customers(last_name, first_name)
    WHERE deleted_at IS NULL;

-- Customer value ranking
CREATE INDEX idx_customers_value ON customers(total_spent DESC)
    WHERE deleted_at IS NULL;

-- Recent visitors
CREATE INDEX idx_customers_last_visit ON customers(last_visit DESC)
    WHERE deleted_at IS NULL;

Domain 10: Offline Sync

-- ============================================================
-- SYNC QUEUE INDEXES
-- ============================================================

-- Idempotency check (unique, critical for exactly-once processing)
CREATE UNIQUE INDEX idx_sync_queue_idempotency ON sync_queue(idempotency_key);

-- Device sync sequence (primary sync ordering)
CREATE INDEX idx_sync_queue_device_seq ON sync_queue(device_id, sequence_number);

-- Pending queue (worker polling)
CREATE INDEX idx_sync_queue_pending ON sync_queue(status, priority, created_at)
    WHERE status = 'pending';

-- Failed items for retry
CREATE INDEX idx_sync_queue_failed ON sync_queue(status, attempts, created_at)
    WHERE status = 'failed' AND attempts < 5;

-- Entity lookup for conflict detection
CREATE INDEX idx_sync_queue_entity ON sync_queue(entity_type, entity_id);

-- ============================================================
-- SYNC CONFLICT INDEXES
-- ============================================================

-- Unresolved conflicts (admin dashboard)
CREATE INDEX idx_sync_conflicts_unresolved ON sync_conflicts(created_at DESC)
    WHERE resolved_at IS NULL;

-- Conflicts by entity
CREATE INDEX idx_sync_conflicts_entity ON sync_conflicts(entity_type, entity_id);

-- Conflict type distribution
CREATE INDEX idx_sync_conflicts_type ON sync_conflicts(conflict_type)
    WHERE resolved_at IS NULL;

-- ============================================================
-- DEVICE INDEXES
-- ============================================================

-- Hardware ID (unique, device registration)
CREATE UNIQUE INDEX idx_devices_hardware ON devices(hardware_id);

-- Devices by location
CREATE INDEX idx_devices_location ON devices(location_id, status);

-- Stale devices (monitoring)
CREATE INDEX idx_devices_last_seen ON devices(last_seen_at)
    WHERE status = 'active';

Domain 11-12: Cash & Payment

-- ============================================================
-- SHIFT INDEXES
-- ============================================================

-- Shift number lookup
CREATE UNIQUE INDEX idx_shifts_number ON shifts(shift_number);

-- Open shifts by drawer (prevent duplicates)
CREATE UNIQUE INDEX idx_shifts_drawer_open ON shifts(cash_drawer_id)
    WHERE status = 'open';

-- Shifts by location + date (reports)
CREATE INDEX idx_shifts_location_date ON shifts(location_id, opened_at DESC);

-- Unreconciled shifts
CREATE INDEX idx_shifts_unreconciled ON shifts(location_id, opened_at)
    WHERE status IN ('closed', 'closing');

-- ============================================================
-- CASH MOVEMENT INDEXES
-- ============================================================

-- Movements by shift (reconciliation)
CREATE INDEX idx_cash_movements_shift ON cash_movements(shift_id, created_at);

-- Movements by type (auditing)
CREATE INDEX idx_cash_movements_type ON cash_movements(movement_type, created_at DESC);

-- Reference lookup
CREATE INDEX idx_cash_movements_ref ON cash_movements(reference_type, reference_id)
    WHERE reference_type IS NOT NULL;

-- ============================================================
-- PAYMENT ATTEMPT INDEXES
-- ============================================================

-- Payments by order
CREATE INDEX idx_payment_attempts_order ON payment_attempts(order_id);

-- Payment status monitoring
CREATE INDEX idx_payment_attempts_status ON payment_attempts(status, created_at DESC);

-- Processor transaction lookup (chargebacks)
CREATE INDEX idx_payment_attempts_processor ON payment_attempts(processor_transaction_id)
    WHERE processor_transaction_id IS NOT NULL;

-- Daily payment activity
CREATE INDEX idx_payment_attempts_date ON payment_attempts(created_at DESC);

-- ============================================================
-- PAYMENT BATCH INDEXES
-- ============================================================

-- Batch number lookup
CREATE UNIQUE INDEX idx_payment_batches_number ON payment_batches(batch_number);

-- Open batches (auto-close job)
CREATE INDEX idx_payment_batches_open ON payment_batches(location_id, batch_date)
    WHERE status = 'open';

-- Pending settlement
CREATE INDEX idx_payment_batches_pending ON payment_batches(submitted_at)
    WHERE status = 'pending';

Domain 13: RFID Module

-- ============================================================
-- RFID TAG INDEXES
-- ============================================================

-- EPC lookup (unique, critical for scan performance)
CREATE UNIQUE INDEX idx_rfid_tags_epc ON rfid_tags(epc);

-- Tags by variant (product inventory)
CREATE INDEX idx_rfid_tags_variant ON rfid_tags(variant_id, status)
    WHERE status = 'active';

-- Tags by location (location inventory)
CREATE INDEX idx_rfid_tags_location ON rfid_tags(current_location_id, status)
    WHERE status = 'active';

-- Serial number sequence
CREATE INDEX idx_rfid_tags_serial ON rfid_tags(serial_number);

-- Recently scanned (for duplicate detection)
CREATE INDEX idx_rfid_tags_scanned ON rfid_tags(last_scanned_at DESC)
    WHERE last_scanned_at IS NOT NULL;

-- ============================================================
-- RFID SCAN EVENT INDEXES (High-Volume)
-- ============================================================

-- Session events (BRIN for time-series)
CREATE INDEX idx_rfid_events_session ON rfid_scan_events(session_id);

-- EPC lookup (match to tag)
CREATE INDEX idx_rfid_events_epc ON rfid_scan_events(epc);

-- Unknown tags (for investigation)
CREATE INDEX idx_rfid_events_unknown ON rfid_scan_events(session_id)
    WHERE rfid_tag_id IS NULL;

-- Time-based partition key (if partitioning)
CREATE INDEX idx_rfid_events_time ON rfid_scan_events USING BRIN (first_seen_at);

-- ============================================================
-- RFID PRINT JOB INDEXES
-- ============================================================

-- Job queue (worker polling)
CREATE INDEX idx_rfid_jobs_queue ON rfid_print_jobs(status, priority, created_at)
    WHERE status IN ('queued', 'printing');

-- Jobs by printer
CREATE INDEX idx_rfid_jobs_printer ON rfid_print_jobs(printer_id, created_at DESC);

14.4 Query Optimization Examples

Example 1: Product Lookup by Barcode

Query:

SELECT v.id, v.sku, p.name, p.base_price + v.price_adjustment AS price
FROM variants v
JOIN products p ON v.product_id = p.id
WHERE v.barcode = '012345678901'
  AND v.deleted_at IS NULL
  AND p.deleted_at IS NULL;

Optimization:

-- Covering index avoids table lookup for common columns
CREATE INDEX idx_variants_barcode_covering ON variants(barcode)
    INCLUDE (sku, product_id, price_adjustment)
    WHERE barcode IS NOT NULL AND deleted_at IS NULL;

-- Result: Index-only scan, < 1ms

EXPLAIN ANALYZE:

Index Only Scan using idx_variants_barcode_covering on variants v
  Index Cond: (barcode = '012345678901'::character varying)
  Heap Fetches: 0
Planning Time: 0.1 ms
Execution Time: 0.05 ms

Example 2: Daily Sales Report

Query:

SELECT
    l.name AS location,
    COUNT(o.id) AS transactions,
    SUM(o.total_amount) AS sales,
    SUM(o.tax_amount) AS tax
FROM orders o
JOIN locations l ON o.location_id = l.id
WHERE o.created_at >= '2025-01-01'
  AND o.created_at < '2025-01-02'
  AND o.status = 'completed'
GROUP BY l.name
ORDER BY sales DESC;

Optimization:

-- Composite index for date range + status + location
CREATE INDEX idx_orders_reporting ON orders(created_at, location_id)
    INCLUDE (total_amount, tax_amount)
    WHERE status = 'completed';

-- Result: Index scan with aggregate pushdown

EXPLAIN ANALYZE:

HashAggregate  (cost=150..155 rows=5)
  Group Key: l.name
  ->  Nested Loop  (cost=0.5..140 rows=1200)
        ->  Index Scan using idx_orders_reporting on orders o
              Index Cond: (created_at >= '...' AND created_at < '...')
              Filter: (status = 'completed')
        ->  Index Scan using idx_locations_pkey on locations l
              Index Cond: (id = o.location_id)
Planning Time: 0.5 ms
Execution Time: 12 ms

Example 3: Inventory Low Stock Alert

Query:

SELECT
    v.sku,
    p.name,
    l.code AS location,
    il.quantity_on_hand,
    il.reorder_point,
    il.reorder_quantity
FROM inventory_levels il
JOIN variants v ON il.variant_id = v.id
JOIN products p ON v.product_id = p.id
JOIN locations l ON il.location_id = l.id
WHERE il.quantity_on_hand <= il.reorder_point
  AND il.reorder_point > 0
  AND il.deleted_at IS NULL
  AND l.is_active = TRUE
ORDER BY (il.reorder_point - il.quantity_on_hand) DESC
LIMIT 100;

Optimization:

-- Partial index for low stock condition
CREATE INDEX idx_inventory_low_stock_alert ON inventory_levels(
    location_id,
    (reorder_point - quantity_on_hand) DESC
)
INCLUDE (variant_id, quantity_on_hand, reorder_point, reorder_quantity)
WHERE quantity_on_hand <= reorder_point
  AND reorder_point > 0
  AND deleted_at IS NULL;

Example 4: Sync Queue Processing

Query:

SELECT id, device_id, operation_type, entity_type, entity_id, payload
FROM sync_queue
WHERE status = 'pending'
ORDER BY priority ASC, created_at ASC
LIMIT 50
FOR UPDATE SKIP LOCKED;

Optimization:

-- Partial index for pending items only
CREATE INDEX idx_sync_queue_worker ON sync_queue(priority, created_at)
    INCLUDE (device_id, operation_type, entity_type, entity_id)
    WHERE status = 'pending';

-- Result: Index-only scan, no lock contention

Example 5: RFID Tag Count by Location

Query:

SELECT
    l.code,
    l.name,
    COUNT(*) FILTER (WHERE rt.status = 'active') AS active_tags,
    COUNT(*) FILTER (WHERE rt.status = 'sold') AS sold_tags,
    COUNT(*) AS total_tags
FROM locations l
LEFT JOIN rfid_tags rt ON rt.current_location_id = l.id
WHERE l.is_active = TRUE
GROUP BY l.id, l.code, l.name
ORDER BY active_tags DESC;

Optimization:

-- Pre-aggregated materialized view for dashboard
CREATE MATERIALIZED VIEW rfid_tag_counts AS
SELECT
    current_location_id,
    status,
    COUNT(*) AS tag_count
FROM rfid_tags
GROUP BY current_location_id, status;

CREATE UNIQUE INDEX ON rfid_tag_counts(current_location_id, status);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY rfid_tag_counts;

14.5 Performance Monitoring Queries

Index Usage Statistics

-- Most used indexes
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname LIKE 'tenant_%'
ORDER BY idx_scan DESC
LIMIT 20;

Unused Indexes

-- Indexes never used (candidates for removal)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname LIKE 'tenant_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Slow Queries

-- Enable pg_stat_statements extension first
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 slowest queries by mean time
SELECT
    substring(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Table Bloat Check

-- Tables with significant dead tuples
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Index Bloat Estimation

-- Estimate index bloat
SELECT
    current_database() AS db,
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
JOIN pg_index ON indexrelid = pg_index.indexrelid
WHERE NOT indisunique  -- Exclude unique indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

14.6 Index Maintenance

Routine Maintenance Commands

-- Reindex a specific table
REINDEX TABLE tenant_0001.products;

-- Reindex entire schema
REINDEX SCHEMA tenant_0001;

-- Concurrent reindex (no lock, PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY tenant_0001.products;

-- Vacuum and analyze (update statistics)
VACUUM ANALYZE tenant_0001.products;

-- Full vacuum (reclaim space, requires exclusive lock)
VACUUM FULL tenant_0001.products;

Automated Maintenance Configuration

-- postgresql.conf settings
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1      -- Vacuum when 10% of rows are dead
autovacuum_analyze_scale_factor = 0.05    -- Analyze when 5% of rows change
autovacuum_vacuum_cost_delay = 2ms        -- Reduce I/O impact
autovacuum_max_workers = 4                -- Parallel workers

-- For high-update tables (sync_queue, rfid_scan_events)
ALTER TABLE tenant_0001.sync_queue SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.01
);

Index Creation Best Practices

-- Create indexes concurrently (no table lock)
CREATE INDEX CONCURRENTLY idx_orders_new ON orders(created_at);

-- Check for invalid indexes after concurrent creation
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- If invalid, drop and recreate
DROP INDEX CONCURRENTLY idx_orders_new;
CREATE INDEX CONCURRENTLY idx_orders_new ON orders(created_at);

14.7 Performance Checklist

Before Deployment

• All primary key columns have indexes (automatic)
• All foreign key columns have indexes (manual)
• Unique constraints have backing indexes (automatic)
• High-frequency query patterns have covering indexes
• BRIN indexes on time-series tables
• Partial indexes for filtered queries
• GIN indexes on JSONB columns with queries
• Full-text indexes if text search is used

Regular Monitoring

• Check pg_stat_statements for slow queries weekly
• Review unused indexes monthly (remove if truly unused)
• Monitor table bloat (vacuum if > 20% dead tuples)
• Verify index usage after schema changes
• Run ANALYZE after bulk data loads

Query Optimization Workflow

1. Identify slow query via pg_stat_statements or application logs
2. Run EXPLAIN ANALYZE to see execution plan
3. Check for sequential scans on large tables
4. Identify missing indexes or suboptimal index choice
5. Create index (CONCURRENTLY for production)
6. Verify improvement with EXPLAIN ANALYZE
7. Monitor for regression

14.8 Quick Reference: Common Index Patterns

End of Part III: Database

Next: Part IV: Backend - API design and service layer implementation.

Chapter 14 | Indexes & Performance | POS Platform Blueprint v1.0.0