Consistency

PowerSync: Designed for causal+ consistency

PowerSync is designed to have Causal+ Consistency, while providing enough flexibility for applications to perform their own data validations and conflict handling.

How it works: Checkpoints

PowerSync uses the concept of "checkpoints" to ensure the data is consistent. A checkpoint is a single point-in-time on the server (similar to an LSN in Postgres) with a consistent state — only fully committed transactions are part of the state.

The client only updates its local state when it has all the data matching a checkpoint, and then it updates the state to exactly match that of the checkpoint. There is no intermediate state while downloading large sets of changes such as large server-side transactions. Different tables and sync buckets are all included in the same consistent checkpoint, to ensure that the state is consistent over all data in the app.

Local client changes

Local changes are applied on top of the last checkpoint received from the server, as well as being persisted into an upload queue.

While changes are present in the upload queue, the client does not advance to a new checkpoint. This means the client never has to resolve conflicts locally.

Only once all the local changes have been acknowledged by the server, and the data for that new checkpoint is downloaded by the client, does the client advance to the next checkpoint. This ensures that the operations are always ordered correctly on the client.

Types of local operations

The client automatically records changes to the local database as PUT, PATCH or DELETE operations — corresponding to INSERT, UPDATE or DELETE statements. These are grouped together in a batch per local transaction.

Since the developer has full control over how operations are applied, more advanced operations can be modeled on top of these three. For example an insert-only "operations" table can be added, that records additional metadata for individual operations.

Validation and conflict handling

With PowerSync offering full flexibility in how changes are applied on the server, it is also the developer's responsibility to implement this correctly to avoid consistency issues.

Some scenarios to consider:

While the client was offline, a record was modified locally. By the time the client is online again, that record has been deleted. Some options for handling the change:

Discard the change.
Discard the entire transaction.
Re-create the record.
Record the change elsewhere, potentially notifying the user and allowing the user to resolve the issue.

Some other examples include foreign-key or not-null constraints, maximum size of numeric fields, unique constraints, and access restrictions (such as row-level security policies).

With an online-only application, the user typically sees the error as soon as it occurs, and can make changes as required. In an offline-capable application, these errors may occur much later than when the change was made, so more care is required to handle these cases.

Special care must be taken so that issues such as those do not block the upload queue — the queue cannot advance if the server does not acknowledge a change.

There is no single correct choice on how to handle write failures such as mentioned above — the best action depends on the specific application and scenario. However, we do have some suggestions for general approaches:

In general, consider relaxing constraints somewhat on the server where it is not absolutely important. It may be better to accept data that is somewhat inconsistent (e.g. a client not applying all expected validations), rather than discarding the data completely.
If it is critical to preserve all client changes and preserve the order of changes:
1. Block the client's queue on unexpected errors (don't acknowledge the change).
2. Implement error monitoring to be notified of issues, and resolve the issues as soon as possible.
If it is critical to preserve all client changes, but the exact order may not be critical:
1. On a constraint error, persist the transaction in a separate server-side queue, and acknowledge the change.
2. The server-side queue can then be inspected and retried asynchronously, without blocking the client-side queue.
If it is acceptable to lose some changes due to constraint errors:
1. Discard the change, or the entire transaction if the changes must all be applied together.
2. Implement error notifications to detect these issues.