Crate: Eventual consistency

Some projects at work use a Crate database as kind of caching layer for aggregated, unnormalized data that helps reducing requests from frontend servers. Because of its eventual consistency model, filling data into it has some issues.

Eventual consistency means that at some point in the future - not now - data will be consistent. You write into Crate, but it is not guranteed that the next read will contain the data written earlier, as described in its documentation.

The following PHP code uses Laravel's Eloquent with the Crate adapter, and is pretty standard in the sloppy and inefficient world of nice-to-look-at ORMs:

$obj = Model::firstOrCreate(['id' => $id]);
$obj->fill($transformer->transform($data));
$obj->save();

That code handles both fresh inserts and updates in only three lines, which is nice. But it makes three SQL queries for new data:

  1. SELECT for checking if the row exists
  2. INSERT to create the row (saving the ID only)
  3. UPDATE to actually write the new data

With Crate, the UPDATE will fail because the row inserted in step 2 is not available in step 3.

Eloquent's firstOrCreate method fortunately supports a second parameter that may contain additional data to be inserted:

$newdata = $transformer->transform($data);
$obj = Model::firstOrCreate(['id' => $id], $newdata);
$obj->fill($newdata);
$obj->save();

This code works with Crate, even if it is terrible inefficient - when inserting a new row, it sends the same data again in the UPDATE.

Eventual consistency will also bite you when using TRUNCATE and then immediately inserting data. Create has a REFRESH TABLE command that ought to support you with such cases, but in Crate 1.0.1 we had issues with that, and had to resort to a sleep(1) call in our import script :/

Written by Christian Weiske.

Comments? Please send an e-mail.