Chapter 09: Indexes & Performance

Query Optimization and Index Strategy

9.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

9.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);

-- Event outbox (transactional outbox, append-only)
CREATE INDEX idx_event_outbox_created ON event_outbox 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;

9.3 Index Strategy by Domain

Domain 1-2: Catalog (Products, Categories)

-- ============================================================
-- PRODUCT LOOKUP INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

-- Product search by name (full-text — GIN does not use tenant_id prefix,
-- RLS policy handles tenant filtering automatically)
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_tenant_brand ON products(tenant_id, brand_id)
    WHERE is_active = TRUE AND deleted_at IS NULL;

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

-- ============================================================
-- VARIANT LOOKUP INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Variant lookup by SKU (unique per tenant)
CREATE UNIQUE INDEX idx_variants_tenant_sku ON variants(tenant_id, sku)
    WHERE deleted_at IS NULL;

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

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

-- ============================================================
-- CATEGORY NAVIGATION INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

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

-- ============================================================
-- COLLECTION & TAG INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

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

-- Products with tag
CREATE INDEX idx_product_tag_tenant_tag ON product_tag(tenant_id, tag_id);

Domain 3: Inventory

-- ============================================================
-- INVENTORY LEVEL INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Primary lookup: tenant + variant + location (covered)
CREATE UNIQUE INDEX idx_inventory_tenant_lookup ON inventory_levels(tenant_id, 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_tenant_location ON inventory_levels(tenant_id, location_id, variant_id)
    WHERE deleted_at IS NULL;

-- Low stock alerts (filtered, ordered by severity)
CREATE INDEX idx_inventory_tenant_low_stock ON inventory_levels(tenant_id, 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_tenant_out_of_stock ON inventory_levels(tenant_id, location_id)
    WHERE quantity_on_hand <= 0
      AND deleted_at IS NULL;

-- ============================================================
-- INVENTORY TRANSACTION INDEXES (BRIN + B-Tree, tenant_id for RLS)
-- ============================================================

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

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

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

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

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

Domain 4: Sales (Orders, Customers)

-- ============================================================
-- ORDER INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Order number lookup (receipt reprint)
CREATE UNIQUE INDEX idx_orders_tenant_number ON orders(tenant_id, order_number);

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

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

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

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

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

-- ============================================================
-- ORDER ITEMS INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Line items for order
CREATE INDEX idx_order_items_tenant_order ON order_items(tenant_id, order_id);

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

-- Returns tracking
CREATE INDEX idx_order_items_tenant_returned ON order_items(tenant_id, order_id)
    WHERE is_returned = TRUE;

-- ============================================================
-- CUSTOMER INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

-- Email lookup (unique per tenant)
CREATE UNIQUE INDEX idx_customers_tenant_email ON customers(tenant_id, email)
    WHERE email IS NOT NULL AND deleted_at IS NULL;

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

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

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

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

Domain 10: Offline Sync

-- ============================================================
-- SYNC QUEUE INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Idempotency check (unique per tenant, critical for exactly-once processing)
CREATE UNIQUE INDEX idx_sync_queue_tenant_idempotency ON sync_queue(tenant_id, idempotency_key);

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

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

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

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

-- ============================================================
-- SYNC CONFLICT INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

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

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

-- ============================================================
-- DEVICE INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Hardware ID (unique per tenant, device registration)
CREATE UNIQUE INDEX idx_devices_tenant_hardware ON devices(tenant_id, hardware_id);

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

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

-- ============================================================
-- EVENT OUTBOX INDEXES (Transactional Outbox pattern)
-- ============================================================

-- Pending events for background worker polling
CREATE INDEX idx_event_outbox_pending ON event_outbox(status, created_at)
    WHERE status = 'pending';

-- Aggregate event history
CREATE INDEX idx_event_outbox_tenant_aggregate ON event_outbox(tenant_id, aggregate_type, aggregate_id);

-- Time-series BRIN for append-only outbox
CREATE INDEX idx_event_outbox_created ON event_outbox USING BRIN (created_at);

-- Failed events eligible for retry
CREATE INDEX idx_event_outbox_failed ON event_outbox(status, retry_count)
    WHERE status = 'failed' AND retry_count < max_retries;

-- ============================================================
-- STATE TRANSITIONS INDEXES (DB-driven state machines)
-- ============================================================

-- State machine lookup (aggregate + current state)
CREATE INDEX idx_state_transitions_lookup ON state_transitions(tenant_id, aggregate_type, from_state)
    WHERE is_active = TRUE;

Domain 11-12: Cash & Payment

-- ============================================================
-- SHIFT INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Shift number lookup (unique per tenant)
CREATE UNIQUE INDEX idx_shifts_tenant_number ON shifts(tenant_id, shift_number);

-- Open shifts by drawer (prevent duplicates per tenant)
CREATE UNIQUE INDEX idx_shifts_tenant_drawer_open ON shifts(tenant_id, cash_drawer_id)
    WHERE status = 'open';

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

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

-- ============================================================
-- CASH MOVEMENT INDEXES (tenant_id leading column for RLS)
-- ============================================================

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

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

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

-- ============================================================
-- PAYMENT ATTEMPT INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Payments by order
CREATE INDEX idx_payment_attempts_tenant_order ON payment_attempts(tenant_id, order_id);

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

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

-- Daily payment activity
CREATE INDEX idx_payment_attempts_tenant_date ON payment_attempts(tenant_id, created_at DESC);

-- ============================================================
-- PAYMENT BATCH INDEXES (tenant_id leading column for RLS)
-- ============================================================

-- Batch number lookup (unique per tenant)
CREATE UNIQUE INDEX idx_payment_batches_tenant_number ON payment_batches(tenant_id, batch_number);

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

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

Domain 13: RFID Module (Counting Subsystem)

-- ============================================================
-- RFID TAG INDEXES (all include tenant_id for RLS performance)
-- ============================================================

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

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

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

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

-- Recently scanned (for scan recency queries)
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, idempotent uploads)
-- ============================================================

-- Idempotency: one row per (session, epc) — prevents duplicate uploads
CREATE UNIQUE INDEX idx_rfid_events_idempotent ON rfid_scan_events(session_id, epc);

-- Session events
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 by first_seen_at)
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);

-- ============================================================
-- RFID TAG TEMPLATES & MAPPINGS
-- ============================================================

-- Templates by tenant
CREATE INDEX idx_rfid_templates_tenant ON rfid_tag_templates(tenant_id);

-- Default template per type per tenant
CREATE UNIQUE INDEX idx_rfid_templates_default ON rfid_tag_templates(tenant_id, template_type)
    WHERE is_default = TRUE;

-- Tag mappings by SKU
CREATE INDEX idx_rfid_mappings_sku ON rfid_tag_mappings(tenant_id, sku);

-- Tag mappings by variant
CREATE INDEX idx_rfid_mappings_variant ON rfid_tag_mappings(variant_id);

-- ============================================================
-- SESSION OPERATORS (Multi-Operator Counting)
-- ============================================================

-- Operators by session
CREATE INDEX idx_session_operators_session ON session_operators(session_id);

-- Sessions by operator
CREATE INDEX idx_session_operators_user ON session_operators(operator_id);

9.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;

9.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 = 'public'
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 = 'public'
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;

9.6 Index Maintenance

Routine Maintenance Commands

-- Reindex a specific table (all tables in public schema)
REINDEX TABLE products;

-- Reindex entire public schema
REINDEX SCHEMA public;

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

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

-- Full vacuum (reclaim space, requires exclusive lock)
VACUUM FULL 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 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);

9.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

9.8 Quick Reference: Common Index Patterns

End of Part III: Database

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

Document Information

This chapter is part of the POS Blueprint Book. All content is self-contained.