BATCH: Writes That Succeed Together

Cassandra apps often store the same data in two tables, each with a different key (you will see why in the next section). Here, one table finds users by email and the other finds users by username:

CREATE TABLE users_by_email (
  email text PRIMARY KEY,
  username text
);
CREATE TABLE users_by_username (
  username text PRIMARY KEY,
  email text
);

Two tables means two writes for every new user. If the app crashes after the first write, the tables no longer agree. A batch groups writes so they succeed together.

Syntax

BEGIN BATCH
  statement1;
  statement2;
APPLY BATCH;

A batch can hold any mix of INSERT, UPDATE and DELETE statements.

Example: add a user to both tables

BEGIN BATCH
  INSERT INTO users_by_email (email, username)
  VALUES ('[email protected]', 'sam');
  INSERT INTO users_by_username (username, email)
  VALUES ('sam', '[email protected]');
APPLY BATCH;

SELECT * FROM users_by_email;
SELECT * FROM users_by_username;

Output:

 email           | username
-----------------+----------
 [email protected] |      sam

(1 rows)

 username | email
----------+-----------------
      sam | [email protected]

(1 rows)

Once Cassandra accepts a batch, it promises that all of its writes will be applied, even if a node fails in the middle. You never end up with one write saved and the other lost.

A batch is not a full transaction like in MySQL, though. There is no rollback, and for a short moment another query can see one table updated before the other.

Batches are not for speed

In other databases, people often put 1,000 inserts in one batch to make loading faster. In Cassandra, this is a mistake. Those rows belong to different partitions on different nodes. The whole batch goes to one node, which then has to send every write to the right place and keep track of all of them. That one node becomes the bottleneck. For loading lots of data, send normal inserts one by one, in parallel.

Use a batch when some writes must not get out of sync, like the same user in two tables. Keep batches small.

In the exercise, a new user signed up. Add them to both tables in one batch.