Part 3: SQL & PostgreSQL Compatibility
It speaks SQL, but it's not Postgres. Understanding the wire protocol and key differences.
One of CockroachDB’s smartest moves was adopting the PostgreSQL wire protocol. This means you can use almost any Postgres tool or driver to talk to CockroachDB.
The Wire Protocol
CockroachDB doesn’t have its own custom driver. It speaks pgwire. This means you can use psql, DBeaver, or the standard Postgres drivers for Go, Python, Java, etc.
# Connecting with psql
psql "postgresql://root@localhost:26257/defaultdb?sslmode=disable"It’s Not Postgres
While it looks like Postgres, it’s not. It’s a distributed system written in Go.
Key Differences
- No Stored Procedures: (Mostly). Logic should live in your app layer.
- No Triggers: Use Change Data Capture (CDC) instead.
- Serializable Isolation: CockroachDB runs at the highest isolation level by default. No dirty reads, ever.
Standard SQL Support
It supports standard SQL syntax: JOIN, GROUP BY, Window Functions, CTEs, and JSONB operations.
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
metadata JSONB
);
INSERT INTO users (name, metadata) VALUES ('Alice', '{"role": "admin"}');Conclusion
The Postgres compatibility is a huge productivity booster. You don’t need to learn a new query language. But you DO need to learn how to model your data for a distributed system. That’s next.