# CockroachDB Schema Design Rule ## Overview This rule provides comprehensive guidance for designing efficient schemas in CockroachDB's distributed environment. It covers primary key selection, partitioning strategies, index optimization, and data distribution patterns that maximize performance while avoiding common pitfalls like hotspots and range splits. ## Implementation ### 1. Primary Key Design Patterns #### Avoid Sequential Primary Keys ```sql -- Bad: Sequential primary key causes hotspots CREATE TABLE orders_bad ( id SERIAL PRIMARY KEY, customer_id INT, order_date TIMESTAMP, total DECIMAL(10,2) ); -- Good: UUID primary key for distributed writes CREATE TABLE orders_good ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), customer_id INT, order_date TIMESTAMP, total DECIMAL(10,2) ); -- Better: Composite primary key for natural distribution CREATE TABLE orders_better ( customer_id INT, order_date DATE, order_id UUID DEFAULT gen_random_uuid(), total DECIMAL(10,2), PRIMARY KEY (customer_id, order_date, order_id) ); ``` #### Time-Based Primary Keys ```sql -- Good: Time-based UUID for chronological ordering CREATE TABLE events ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), event_time TIMESTAMPTZ DEFAULT NOW(), event_type STRING, payload JSONB, -- Index for time-based queries INDEX idx_events_time (event_time) ); -- Better: Combine time with hash for distribution CREATE TABLE events_distributed ( shard_id INT DEFAULT (random() * 100)::INT, event_time TIMESTAMPTZ DEFAULT NOW(), event_id UUID DEFAULT gen_random_uuid(), event_type STRING, payload JSONB, PRIMARY KEY (shard_id, event_time, event_id) ); ``` ### 2. Partitioning Strategies #### Range Partitioning ```sql -- Partition by date ranges CREATE TABLE sales ( sale_id UUID DEFAULT gen_random_uuid(), sale_date DATE, customer_id INT, amount DECIMAL(10,2), region STRING, PRIMARY KEY (sale_date, sale_id) ) PARTITION BY RANGE (sale_date) ( PARTITION p_2024_q1 VALUES FROM ('2024-01-01') TO ('2024-04-01'), PARTITION p_2024_q2 VALUES FROM ('2024-04-01') TO ('2024-07-01'), PARTITION p_2024_q3 VALUES FROM ('2024-07-01') TO ('2024-10-01'), PARTITION p_2024_q4 VALUES FROM ('2024-10-01') TO ('2025-01-01') ); -- Add future partitions ALTER TABLE sales ADD PARTITION p_2025_q1 VALUES FROM ('2025-01-01') TO ('2025-04-01'); ``` #### Hash Partitioning ```sql -- Partition by hash for even distribution CREATE TABLE user_sessions ( session_id UUID DEFAULT gen_random_uuid(), user_id INT, created_at TIMESTAMPTZ DEFAULT NOW(), last_activity TIMESTAMPTZ, session_data JSONB, PRIMARY KEY (user_id, session_id) ) PARTITION BY HASH (user_id) ( PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4, PARTITION p5, PARTITION p6, PARTITION p7 ); ``` #### List Partitioning ```sql -- Partition by specific values (regions, categories) CREATE TABLE products ( product_id UUID DEFAULT gen_random_uuid(), category STRING, name STRING, price DECIMAL(10,2), region STRING, PRIMARY KEY (region, product_id) ) PARTITION BY LIST (region) ( PARTITION p_us VALUES IN ('us-east', 'us-west'), PARTITION p_eu VALUES IN ('eu-west', 'eu-central'), PARTITION p_asia VALUES IN ('asia-pacific', 'asia-southeast') ); ``` ### 3. Index Optimization #### Covering Indexes ```sql -- Create covering indexes to avoid key lookups CREATE TABLE customers ( customer_id INT PRIMARY KEY, email STRING UNIQUE, name STRING, city STRING, country STRING, created_at TIMESTAMPTZ ); -- Covering index for common query patterns CREATE INDEX idx_customers_location_covering ON customers (country, city) STORING (name, email, created_at); -- Query benefits from covering index SELECT name, email, created_at FROM customers WHERE country = 'USA' AND city = 'San Francisco'; ``` #### Partial Indexes ```sql -- Create partial indexes for filtered queries CREATE TABLE orders ( order_id UUID PRIMARY KEY, customer_id INT, status STRING, order_date TIMESTAMPTZ, total DECIMAL(10,2) ); -- Partial index for active orders only CREATE INDEX idx_orders_active ON orders (customer_id, order_date) WHERE status IN ('pending', 'processing'); -- Partial index for high-value orders CREATE INDEX idx_orders_high_value ON orders (order_date DESC) WHERE total > 1000.00; ``` #### Expression Indexes ```sql -- Index on computed expressions CREATE TABLE users ( user_id INT PRIMARY KEY, email STRING, first_name STRING, last_name STRING, created_at TIMESTAMPTZ ); -- Index on computed full name CREATE INDEX idx_users_full_name ON users ((first_name || ' ' || last_name)); -- Index on extracted date part CREATE INDEX idx_users_created_month ON users (EXTRACT(MONTH FROM created_at)); ``` ### 4. Data Distribution Patterns #### Avoiding Hotspots ```sql -- Bad: Creates hotspots on single node CREATE TABLE metrics_bad ( id SERIAL PRIMARY KEY, timestamp TIMESTAMPTZ DEFAULT NOW(), metric_name STRING, value FLOAT, tags JSONB ); -- Good: Distributed primary key CREATE TABLE metrics_good ( metric_name STRING, timestamp TIMESTAMPTZ, instance_id STRING, value FLOAT, tags JSONB, PRIMARY KEY (metric_name, timestamp, instance_id) ); -- Better: Hash-distributed with time component CREATE TABLE metrics_better ( shard_key INT DEFAULT (random() * 1000)::INT, metric_name STRING, timestamp TIMESTAMPTZ DEFAULT NOW(), instance_id STRING, value FLOAT, tags JSONB, PRIMARY KEY (shard_key, metric_name, timestamp, instance_id) ); ``` #### Multi-Region Schema Design ```sql -- Configure database for multi-region ALTER DATABASE myapp SET PRIMARY REGION 'us-east1'; ALTER DATABASE myapp ADD REGION 'us-west1'; ALTER DATABASE myapp ADD REGION 'eu-west1'; -- Regional table for user data CREATE TABLE users_regional ( user_id UUID PRIMARY KEY, email STRING UNIQUE, name STRING, region STRING, created_at TIMESTAMPTZ ) LOCALITY REGIONAL BY ROW AS region; -- Global table for reference data CREATE TABLE countries ( country_code STRING PRIMARY KEY, country_name STRING, currency STRING ) LOCALITY GLOBAL; -- Regional by table for localized content CREATE TABLE user_preferences ( user_id UUID PRIMARY KEY, preferences JSONB, language STRING, timezone STRING ) LOCALITY REGIONAL BY TABLE IN 'us-east1'; ``` ### 5. JSON and Semi-Structured Data #### JSONB Schema Design ```sql -- Efficient JSONB column design CREATE TABLE events ( event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), event_type STRING, event_data JSONB, created_at TIMESTAMPTZ DEFAULT NOW(), -- Index on JSONB fields INDEX idx_events_type (event_type), INDEX idx_events_user_id ((event_data->>'user_id')), INDEX idx_events_timestamp ((event_data->>'timestamp')) ); -- GIN index for full JSONB search CREATE INVERTED INDEX idx_events_data_gin ON events USING GIN (event_data); -- Computed columns for frequently accessed JSON fields ALTER TABLE events ADD COLUMN user_id INT AS ((event_data->>'user_id')::INT) STORED; CREATE INDEX idx_events_user_id_computed ON events (user_id); ``` #### Array and Nested Data ```sql -- Design for array columns CREATE TABLE products ( product_id UUID PRIMARY KEY, name STRING, tags STRING[], categories INT[], metadata JSONB ); -- Index on array elements CREATE INVERTED INDEX idx_products_tags ON products USING GIN (tags); CREATE INVERTED INDEX idx_products_categories ON products USING GIN (categories); -- Query patterns for arrays SELECT * FROM products WHERE 'electronics' = ANY(tags); SELECT * FROM products WHERE categories @> ARRAY[1, 2]; ``` ### 6. Temporal Data Patterns #### Time-Series Data Design ```sql -- Time-series table with proper partitioning CREATE TABLE sensor_data ( sensor_id INT, timestamp TIMESTAMPTZ, reading_type STRING, value DECIMAL(10,4), metadata JSONB, PRIMARY KEY (sensor_id, timestamp, reading_type) ) PARTITION BY RANGE (timestamp) ( PARTITION p_2024_01 VALUES FROM ('2024-01-01') TO ('2024-02-01'), PARTITION p_2024_02 VALUES FROM ('2024-02-01') TO ('2024-03-01'), PARTITION p_2024_03 VALUES FROM ('2024-03-01') TO ('2024-04-01') ); -- TTL for automatic data cleanup ALTER TABLE sensor_data SET (ttl_expire_after = '90 days'); ``` #### Change Data Capture Schema ```sql -- Audit table design CREATE TABLE audit_log ( log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), table_name STRING, operation STRING, record_id STRING, old_values JSONB, new_values JSONB, changed_by STRING, changed_at TIMESTAMPTZ DEFAULT NOW(), INDEX idx_audit_table_time (table_name, changed_at), INDEX idx_audit_record (record_id) ); -- Trigger for change tracking CREATE OR REPLACE FUNCTION audit_trigger_function() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'UPDATE' THEN INSERT INTO audit_log (table_name, operation, record_id, old_values, new_values, changed_by) VALUES (TG_TABLE_NAME, 'UPDATE', OLD.id::STRING, row_to_json(OLD), row_to_json(NEW), current_user); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; ``` ## Best Practices ### 1. Primary Key Selection - **Avoid SERIAL/SEQUENCE**: Use UUID or composite keys for distributed writes - **Consider query patterns**: Design primary keys around most common access patterns - **Balance distribution**: Ensure even data distribution across nodes - **Include time component**: For time-based queries, include timestamp in primary key ### 2. Index Strategy - **Create covering indexes**: Include frequently selected columns in STORING clause - **Use partial indexes**: Filter indexes for specific query conditions - **Monitor index usage**: Regularly check and remove unused indexes - **Consider inverted indexes**: Use GIN indexes for JSONB and array columns ### 3. Partitioning Guidelines - **Partition large tables**: Use partitioning for tables > 100GB - **Align with query patterns**: Partition on columns used in WHERE clauses - **Automate partition management**: Create scripts for adding/dropping partitions - **Monitor partition sizes**: Keep partitions reasonably sized (< 64GB) ### 4. Data Types and Constraints - **Use appropriate data types**: Choose minimal types that fit your data - **Add constraints**: Use CHECK constraints for data validation - **Consider collation**: Use appropriate collation for string comparisons - **Use ENUM types**: For fixed sets of values ### 5. Multi-Region Considerations - **Choose appropriate locality**: REGIONAL BY ROW for user data, GLOBAL for reference data - **Consider latency**: Place data close to users - **Plan for consistency**: Understand consistency guarantees across regions - **Monitor cross-region traffic**: Minimize expensive cross-region operations ## Common Issues ### 1. Hotspot Detection **Problem**: Single node handling all writes **Solution**: ```sql -- Check for range hotspots SELECT range_id, start_key, end_key, lease_holder, replica_localities FROM crdb_internal.ranges WHERE table_name = 'your_table' ORDER BY range_id; -- Monitor query distribution SELECT node_id, count(*) as query_count, sum(service_lat) as total_latency FROM crdb_internal.node_statement_statistics GROUP BY node_id ORDER BY query_count DESC; ``` ### 2. Large Row Issues **Problem**: Rows exceeding recommended size (64KB) **Solution**: ```sql -- Check row sizes SELECT table_name, avg_size, max_size FROM crdb_internal.table_row_statistics WHERE max_size > 65536 ORDER BY max_size DESC; -- Normalize large columns CREATE TABLE documents ( doc_id UUID PRIMARY KEY, title STRING, summary STRING, created_at TIMESTAMPTZ ); CREATE TABLE document_content ( doc_id UUID REFERENCES documents(doc_id), content_type STRING, content BYTES, PRIMARY KEY (doc_id, content_type) ); ``` ### 3. Index Bloat **Problem**: Unused or redundant indexes consuming space **Solution**: ```sql -- Find unused indexes SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_tup_read = 0 AND idx_tup_fetch = 0; -- Check index sizes SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC; ``` ### 4. Partition Pruning Issues **Problem**: Queries scanning all partitions **Solution**: ```sql -- Verify partition pruning EXPLAIN (VERBOSE) SELECT * FROM sales WHERE sale_date >= '2024-01-01' AND sale_date < '2024-02-01'; -- Ensure partition key in WHERE clause SELECT * FROM sales WHERE sale_date = '2024-01-15' -- Good: uses partition key AND customer_id = 123; -- Avoid queries without partition key SELECT * FROM sales WHERE customer_id = 123; -- Bad: scans all partitions ``` ### 5. Foreign Key Performance **Problem**: Foreign key constraints causing contention **Solution**: ```sql -- Consider removing foreign keys in high-throughput scenarios ALTER TABLE orders DROP CONSTRAINT fk_orders_customer; -- Use application-level validation instead CREATE TABLE orders ( order_id UUID PRIMARY KEY, customer_id INT, -- No foreign key constraint order_date TIMESTAMPTZ, total DECIMAL(10,2) ); -- Add validation in application code func ValidateCustomerExists(customerID int) error { var exists bool err := db.QueryRow("SELECT EXISTS(SELECT 1 FROM customers WHERE id = $1)", customerID).Scan(&exists) if err != nil { return err } if !exists { return fmt.Errorf("customer %d does not exist", customerID) } return nil } ``` ## Schema Evolution ### Safe Schema Changes ```sql -- Add column (safe) ALTER TABLE users ADD COLUMN phone STRING; -- Add index concurrently (safe) CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone); -- Add constraint (requires validation) ALTER TABLE users ADD CONSTRAINT chk_phone_format CHECK (phone ~ '^[0-9-+()]+$'); -- Drop column (potentially unsafe) ALTER TABLE users DROP COLUMN old_field; ``` ### Migration Scripts ```sql -- Example migration script BEGIN; -- Create new table structure CREATE TABLE users_new ( user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email STRING UNIQUE, name STRING, phone STRING, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Copy data from old table INSERT INTO users_new (user_id, email, name, created_at) SELECT user_id, email, name, created_at FROM users; -- Rename tables ALTER TABLE users RENAME TO users_old; ALTER TABLE users_new RENAME TO users; -- Update application code here -- Drop old table after verification -- DROP TABLE users_old; COMMIT; ```