Cassandra and ScyllaDB in Go With the gocql Driver

Cassandra and ScyllaDB, which speaks the same protocol and the same query language, are databases for a particular kind of problem: very high write rates, data spread over many machines and datacentres, and a load that must keep going when nodes fail. They are not general purpose. Queries must be known in advance, joins do not exist and a mistake in the data model cannot be fixed by an index afterwards. For Go the driver is gocql. This guide explains the model first, because that is where the work is, and then the driver's configuration and idioms.

It was not run. No Cassandra or ScyllaDB server was available in the lab behind this series, and a node is heavy to start for a single article. The Go program shown was compiled and passed go vet against the current driver, so the API usage is real, but nothing was executed against a cluster and no output is quoted. Statements about database behaviour come from the Apache Cassandra documentation and are marked where they matter. Check them against the version you deploy.

It belongs to the database cornerstone; the choice between stores is discussed in choosing a database, and a comparable key designed store is DynamoDB.

The model: one table per query

A Cassandra table has a primary key made of two parts. The partition key is hashed to decide which nodes store the row, so all rows with the same partition key live together and can be read together. The clustering columns order the rows inside a partition. A query must name the whole partition key, and may restrict the clustering columns in order. A query that does not is rejected, or it needs ALLOW FILTERING, which scans and should be treated as an error in production.

That produces the central discipline: design tables from queries, not from entities. If the question is "the latest orders of one customer", the table is keyed that way:

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	gocql "github.com/apache/cassandra-gocql-driver/v2"
)

type Order struct {
	CustomerID gocql.UUID
	OrderID    gocql.UUID
	Placed     time.Time
	Total      int
}

func newSession() (*gocql.Session, error) {
	cluster := gocql.NewCluster("127.0.0.1")
	cluster.Keyspace = "shop"
	cluster.Consistency = gocql.LocalQuorum
	cluster.Timeout = 2 * time.Second
	cluster.ConnectTimeout = 5 * time.Second
	cluster.PoolConfig.HostSelectionPolicy = gocql.TokenAwareHostPolicy(gocql.RoundRobinHostPolicy())
	cluster.RetryPolicy = &gocql.ExponentialBackoffRetryPolicy{NumRetries: 3, Min: 100 * time.Millisecond, Max: 2 * time.Second}
	return cluster.CreateSession()
}

// The table is built for one question: the latest orders of one customer.
const schema = `
create table if not exists shop.orders_by_customer (
    customer_id uuid,
    placed      timestamp,
    order_id    uuid,
    total       int,
    primary key ((customer_id), placed, order_id)
) with clustering order by (placed desc, order_id asc)`

func place(ctx context.Context, s *gocql.Session, o Order) error {
	return s.Query(`insert into orders_by_customer (customer_id, placed, order_id, total) values (?, ?, ?, ?)`,
		o.CustomerID, o.Placed, o.OrderID, o.Total).ExecContext(ctx)
}

func latest(ctx context.Context, s *gocql.Session, customer gocql.UUID, n int) ([]Order, error) {
	iter := s.Query(`select placed, order_id, total from orders_by_customer where customer_id = ? limit ?`, customer, n).IterContext(ctx)
	var out []Order
	o := Order{CustomerID: customer}
	for iter.Scan(&o.Placed, &o.OrderID, &o.Total) {
		out = append(out, o)
	}
	return out, iter.Close()
}

// claim uses a lightweight transaction: the insert happens only if nobody has the key yet.
func claim(ctx context.Context, s *gocql.Session, email string, id gocql.UUID) (bool, error) {
	var existing gocql.UUID
	m := map[string]any{}
	applied, err := s.Query(`insert into users_by_email (email, user_id) values (?, ?) if not exists`, email, id).MapScanCASContext(ctx, m)
	if !applied {
		if v, ok := m["user_id"].(gocql.UUID); ok {
			existing = v
		}
	}
	_ = existing
	return applied, err
}

// loadBatch groups writes that share a partition key, which is the only kind of batch that is cheap.
func loadBatch(ctx context.Context, s *gocql.Session, orders []Order) error {
	b := s.Batch(gocql.UnloggedBatch)
	for _, o := range orders {
		b.Query(`insert into orders_by_customer (customer_id, placed, order_id, total) values (?, ?, ?, ?)`, o.CustomerID, o.Placed, o.OrderID, o.Total)
	}
	return b.ExecContext(ctx)
}

func main() {
	s, err := newSession()
	if err != nil {
		log.Fatal(err)
	}
	defer s.Close()
	ctx := context.Background()
	if err := s.Query(schema).ExecContext(ctx); err != nil {
		log.Fatal(err)
	}
	customer := gocql.TimeUUID()
	if err := place(ctx, s, Order{customer, gocql.TimeUUID(), time.Now(), 4200}); err != nil {
		log.Fatal(err)
	}
	orders, err := latest(ctx, s, customer, 10)
	fmt.Println(len(orders), err)
	_, _ = claim(ctx, s, "[email protected]", customer)
	_ = loadBatch(ctx, s, orders)
}

The schema constant makes customer_id the partition key and placed and order_id the clustering columns, with newest first. One read, where customer_id = ? limit 10, goes to the replicas that own that customer and reads the first ten rows of one partition in disk order. That is why it is fast at any scale: it never looks at another customer's data. If the application also needs "orders by status", that is a second table with the same data written again under a different key. Duplicating data on write to serve each read is normal here. Joins and secondary lookups are replaced by more tables.

Partition design has two failure modes. A partition that is too big (hundreds of megabytes, millions of rows) is slow to read and compact; add a bucket, such as the month, to the partition key to bound it. A partition that is too hot, with one key taking most of the traffic, overloads its replicas whatever the cluster size. Pick keys with many distinct values and even traffic.

Configuring the driver

gocql.NewCluster takes the addresses of a few contact nodes, from which the driver discovers the rest. The settings in newSession are the ones that matter:

• Keyspace names the keyspace, the equivalent of a database, with its replication settings set when it was created. Use NetworkTopologyStrategy with a replication factor of 3 per datacentre in production.
• Consistency sets how many replicas must answer a request. gocql.LocalQuorum needs a majority of the replicas in the local datacentre: with a replication factor of 3, two. Reading and writing both at local quorum means a read sees the latest acknowledged write, and one replica can be down. One is faster and can return stale data; All fails when any replica is down.
• Timeout and ConnectTimeout bound requests. The defaults are generous.
• PoolConfig.HostSelectionPolicy picks which node receives each request. TokenAwareHostPolicy(RoundRobinHostPolicy()) computes the partition token on the client and sends the request to a replica that owns it, skipping a hop through a coordinator that does not hold the data. In multi datacentre setups, wrap a datacentre aware policy instead so requests stay local.
• RetryPolicy controls retries on timeouts and unavailability. Retries are safe for idempotent statements; an insert with a fixed primary key is idempotent, and a counter update is not.

A *gocql.Session is created once, is safe for concurrent use and owns connections to every node. Close it on shutdown. Creating one per request would open dozens of connections each time.

Queries with a context

The v2 driver offers context aware variants. ExecContext(ctx) runs a statement that returns nothing, IterContext(ctx) returns an iterator for rows, and the context's deadline and cancellation apply to the request:

• place inserts a row with Query(...).ExecContext(ctx). Values are bound with ? placeholders, which the driver types and sends separately from the statement, so there is no injection risk and the statement can be prepared once by the server.
• latest reads with IterContext, scans each row into variables with iter.Scan(...) in a loop, and returns iter.Close() as the error. Always check the error from Close: it is where a failure in the middle of the result appears, as with rows.Err() in database/sql.

Pass the request context so a cancelled request stops its queries. Without it a slow node ties up a goroutine for the full timeout.

Writes that need a condition

Cassandra has no transactions in the relational sense, and it has lightweight transactions (LWT) for compare and set: insert ... if not exists and update ... if column = ?. They run Paxos between the replicas, which takes several round trips, so they cost many times more than a normal write and serialise on the partition. The claim function uses one to reserve an email address: MapScanCASContext returns whether the insert was applied and, when it was not, the existing row. Use LWTs for rare, important decisions such as unique registration, never on a hot path. If you need many of them, the data model is fighting the database.

Mixing LWTs and ordinary writes on the same row gives undefined ordering. If a row is ever written conditionally, write it conditionally every time.

Batches are not a bulk loader

loadBatch groups inserts in an unlogged batch. In SQL databases a batch is a speed up. In Cassandra it is a statement that the coordinator must route, and a batch that spans many partitions makes one node coordinate writes to many others, which loads that node and can fail as a unit. The guidance in the Cassandra documentation is that batches are for atomicity across tables for one logical change, such as updating the same order in two query tables, and for sending several rows that share a partition key in one request. For bulk loading, send single statements concurrently, with a bounded number in flight, and let the token aware policy spread them.

A LoggedBatch gives atomicity across partitions by writing a batch log first, at a cost; an UnloggedBatch does not, and is only safe within one partition.

Deletes, TTLs and tombstones

A delete in Cassandra writes a marker, a tombstone, that is kept for gc_grace_seconds (ten days by default) so it can reach replicas that were down. Reads must skip tombstones, and a partition with many of them is slow to read and can make queries fail with a tombstone warning or error. Patterns that create many: deleting rows from a partition you then scan, using a table as a queue, and setting a column to null in bulk (a null write is a tombstone). Prefer a TTL on the insert (using ttl 86400) for expiring data, which also leaves tombstones, but of a kind compaction handles well when whole time windows expire together, and use a time bucketed partition key so old buckets can be dropped as a whole.

Reading safely

• Always give the full partition key. Check queries in code review for a missing key part or an ALLOW FILTERING.
• Page large results with the iterator's page size (Query(...).PageSize(n)) and the paging state, rather than loading a partition at once.
• Do not use count(*) over big partitions or tables; it reads everything. Keep counters in a counter table or elsewhere.
• Prefer prepared statements, which gocql does by default for queries with arguments.
• Mind the clock. Writes are resolved by timestamp, last write wins. Servers with drifting clocks produce surprising winners; the driver can supply a timestamp, and NTP on every node is not optional.

Operations that reach the application

Even if you never run the cluster, its health shows up in your code, and a few signals are worth handling deliberately.

Unavailable and timeout errors. gocql returns gocql.RequestErrUnavailable when too few replicas are alive to satisfy the consistency level, and gocql.RequestErrReadTimeout or RequestErrWriteTimeout when replicas did not answer in time. They mean different things. Unavailable fails fast and says the request was not attempted; a write timeout says the write may have been applied on some replicas. Because the write may have landed, retrying is safe only for idempotent statements, and with a fixed primary key an insert is. Log the error type and the consistency level it failed at.

Downgrading consistency. Some teams retry a failed LocalQuorum read at LocalOne to keep serving during an incident. It trades correctness for availability, and it should be a visible, metered decision for read paths where stale data is acceptable, never a silent default. The driver has a retry policy hook for this, and the Cassandra documentation describes the trade-off.

Schema agreement. Creating or altering a table takes a moment to reach every node. Running DDL from many instances at start-up produces conflicts and disagreement; run schema changes from one place, as with other migrations, and wait for session.AwaitSchemaAgreement before using the new table.

Topology changes. Nodes are added and removed while your service runs. The driver learns about it from cluster events and rebalances its pools, so do not cache node addresses, and set ReconnectInterval to something sensible so it recovers when a node returns.

Counters, time series and bucketing

Two kinds of data fit this database naturally. Counters have their own column type, updated with update ... set views = views + 1. They are not idempotent, so a retry after a timeout can count twice, and they cannot be mixed with other column types in a table; use them where small errors are acceptable, such as page views, and keep an exact count elsewhere if it matters.

Time series are the classic fit: readings from devices, events per user. The key is a source id plus a time bucket for the partition, and the timestamp as a clustering column in descending order. A bucket of one day or one month keeps each partition bounded, and "the last hour of device 42" reads the end of one partition. Writing is a plain insert, so a very high write rate is fine, and a TTL expires old data. Choosing the bucket size is the main design decision: too small and a range query touches many partitions, too large and a partition grows without limit. Estimate rows per partition from write rate times bucket length, and aim for tens of thousands, not millions.

ScyllaDB and testing

ScyllaDB implements the same protocol, so gocql connects to it unchanged, and it offers a fork of the driver (scylladb/gocql) with shard aware routing, which sends a request to the CPU core that owns the data. Use it when you run Scylla at scale and read its documentation for the current version. For tests, a single node container works for functional tests, and the schema, the primary key and the exact queries should be exercised against it, since a query rejected for needing a filter is a bug you want to find in CI. A single node tells you nothing about consistency levels or failures.

When to choose it

Cassandra suits write heavy time series, event logs, messaging and per user data at a scale and availability level that a single PostgreSQL primary cannot provide, with queries known up front. It is a poor fit for ad hoc queries, for data with many relationships, for small data sets and for teams that cannot afford to operate it; a managed offering removes much of the operations work. If the data fits on one machine, a relational database does more with less. The driver is documented on pkg.go.dev, and the data model rules in the Apache Cassandra data modeling documentation.