Powersync Persister flooding upload queue when using Tinybase schemas #262
Description
Describe the bug
Powersync maintains a local upload queue of write operations made on the client that should be sent to the server. When using Tinybase with schemas, on startup the data loaded by the Powersync persister is validated, added to the Tinybase store and written back to the db through the Powersync persister. Ordinarily (like in other persisters) this is fine because these updates are/should be no-ops. However because the Powersync persister here uses INSERT OR REPLACE to simulate upserts, every write replaces the entire row and is treated by Powersync as a new PUT op that should be replicated. This becomes a problem in apps with any real amount of data because on every startup 1000s of write ops get added to the upload queue which must all be processed before the client considers itself to have reached a state consistent with the server.
The/a solution here would be to perform slower but idiomatically (what Powerysnc expects) correct update-then-insert (with RETURNING… to check existence) writes so updates are sent as actual UPDATEs that can be diff’d instead of every write being an insert. Have this working and happy to submit a PR.
Powersync is working on raw table support, which would allow this persister to use ON CONFLICT and resolve this. But until then, the persister looks like it needs to be updated.
This also raises the question: should these no-op upserts be written by Tinybase to begin with? Even when the persister is correctly handling these upserts, these are thousands of write ops that must be sent to the DB on startup, which is particularly rough on performance in React Native. Is this the intended behavior? If data passes schema validation could we trust the loaded data and not perform the second write? Correct me if I’m wrong, I have a feeling my data is failing validation due to null value cells but I haven’t been able to confirm this.