Review why server sometimes stuck to render prices or inserting data

[gzuuus]

Prices and insertion can get stuck

[gzuuus]

I have been researching about the configuration of sqlite dbs and we can optimise to have better performance and possibly avoid unresponsive servers. Right now the pragma configuration for journal and sync is

{ journal_mode: 'delete' }
{ synchronous: 2 }

Maybe we can use WAL mode for the journal and sync 1 for normal synchronous operations. More details here:

Journal Mode: DELETE In this mode, a rollback journal file is created to store a copy of the original data before any modifications. At the end of each transaction, this journal file is deleted. This mode is the default behavior in SQLite and ensures data integrity by allowing rollback in case of errors during transactions. Pros: Simple and reliable for basic use cases. Cons: It can be slower in high-concurrency environments, since it does not allow readers to access the database while a write operation is ongoing.

Synchronous: 2 (FULL) This setting ensures that SQLite will wait for all changes to be written to the disk before completing a transaction. This provides maximum durability, meaning that if a crash occurs, you are less likely to lose data. Pros: High data integrity and safety. Cons: Slower performance, especially during write operations, as it requires more disk I/O.

Switch to WAL Mode: Changing the journal mode from DELETE to WAL can significantly improve concurrency, as it allows multiple readers to access the database while a writer is active.

Adjust Synchronous Setting: If your application can tolerate some risk of data loss in exchange for better performance, consider changing the synchronous setting from FULL (2) to NORMAL (1). This will still provide a good level of durability while improving write speeds.