I. Overview

CockroachDB is powerful, but like any database, poor query patterns or schema design can bottleneck your application. In this final part of the series, we'll look at the tools and techniques for understanding and optimizing query performance.

II. EXPLAIN & EXPLAIN ANALYZE

The starting point for any performance investigation is EXPLAIN.

EXPLAIN — Shows the query execution plan without running the query:

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

1    tree     |        field       |      description
2-------------+--------------------+------------------------
3             | distributed        | false
4             | vectorized         | true
5  index join |                    |
6   │         | table              | users@primary
7   └── scan  |                    |
8             | table              | users@users_email_key
9             | spans              | [/'alice@example.com']

EXPLAIN ANALYZE — Actually runs the query and shows real execution stats:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

1planning time: 1ms
2execution time: 45ms
3
4• filter
5│ actual row count: 12
6│ filter: status = 'pending'
7│
8└── • scan
9      actual row count: 8,432
10      KV rows read: 8,432    ← reading too many rows!
11      table: orders@orders_user_idx

The output above shows a problem: we're scanning 8,432 rows to return 12. That's a sign we need a better index.

III. Index Strategies

Basic Composite Index

1-- Without index: full table scan on status
2CREATE INDEX ON orders (user_id, status);
3
4-- Now the query above scans only 12 rows

Covering Index (STORING)

Avoid going back to the primary index by storing needed columns in the index itself:

1CREATE INDEX ON orders (user_id, status) STORING (total, created_at);
2
3-- This query is served entirely from the index — no table lookup needed
4SELECT total, created_at FROM orders WHERE user_id = 123 AND status = 'pending';

Partial Index

Index only the rows that match a condition — smaller and faster:

1-- Only index pending orders (much smaller than indexing all orders)
2CREATE INDEX ON orders (user_id, created_at)
3  WHERE status = 'pending';

Hash-Sharded Indexes

A common CockroachDB-specific problem: hotspot ranges. If your primary key is sequential (like an auto-increment ID or timestamp), all inserts go to the same Range — creating a write hotspot.

1-- Distribute inserts evenly across 8 shards
2CREATE INDEX ON events (created_at) USING HASH WITH BUCKET_COUNT = 8;

IV. Common Pitfalls When Migrating from PostgreSQL

If you're migrating an existing app from PostgreSQL to CockroachDB, watch out for these:

1. Sequential Primary Keys — creates write hotspots

1-- ❌ Avoid: all inserts hit the same Range
2CREATE TABLE orders (id SERIAL PRIMARY KEY, ...);
3
4-- ✅ Better: distributes writes evenly
5CREATE TABLE orders (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), ...);

2. Large Transactions — increases conflict chance

1-- ❌ Avoid: one giant transaction
2DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
3
4-- ✅ Better: batch deletes in a loop
5DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' LIMIT 1000;

3. Schema Changes Are Online

Unlike PostgreSQL, CockroachDB performs all schema changes online — adding a column, creating an index, or dropping a table never locks the table. You can safely run migrations in production without downtime.

4. Large Imports

CockroachDB's IMPORT command is much faster than psql COPY for large datasets:

1IMPORT INTO users (id, name, email)
2CSV DATA ('gs://my-bucket/users.csv');

V. Monitoring Performance

Beyond EXPLAIN ANALYZE, CockroachDB provides several built-in tools:

Statement Statistics — See the slowest queries across your cluster:

1SELECT query, mean_latency_seconds, execution_count
2FROM crdb_internal.statement_statistics
3ORDER BY mean_latency_seconds DESC
4LIMIT 10;

Admin UI — The built-in dashboard at :8080 shows:

• SQL Statement Fingerprints with P50/P90/P99 latency

• Hot ranges (ranges with disproportionate traffic)

• Index usage recommendations

• Network latency heatmap across nodes

VI. Conclusion

Performance optimization in CockroachDB follows many of the same principles as PostgreSQL — good indexes, avoiding full table scans, batching large operations — but with a few distributed-specific considerations like hotspot avoidance and transaction retries.

This wraps up our CockroachDB series! Here's what we covered:

• Part 1: What CockroachDB is and its core ACID guarantees

• Part 2: Internal architecture — Ranges, Raft, Leaseholders, HLC

• Part 3: Getting started with a local cluster

• Part 4: Transactions and serializable isolation

• Part 5: Geo-distribution and multi-region deployments

• Part 6: Performance and query optimization

I hope this series gives you a solid foundation for using CockroachDB in production. If you have questions or feedback, feel free to reach out!

Thank you! 🙌