You can hit 40000-80000+writes/s with sqlite on a 10$ VPS just by batching transactions (i.e wrapping all inserts/updates in a single transaction every 100ms). This is easy to do at the application level, then you also avoid BUSY/LOCK.

I'd argue writes scale better wtih sqlite than postgresql.

▲

hu3 4 days ago | parent [-]

I love SQLite but how would batching work in CRUD apps where you need to rollback a dozen SQL inserts/updates in case of error in a request?

Also I often need to read-after-write during the same request, using transactions.

And rails apps are often CRUDy.

▲

andersmurphy 4 days ago | parent [-]

With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence.

Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg:

- read -> Does the account have enough funds?

- write -> transfer 100$ from this user to another account

This is also much simpler to write than in other databases as you don't have to worry about n+1.

▲

graemep 4 days ago | parent [-]

I definitely want transactions and rollbacks even if writes happen in sequence.

To go with your example, take something like

1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these

Must all happen or none.

▲

andersmurphy 4 days ago | parent [-]

The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash).

You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.

▲

hu3 4 days ago | parent [-]

But then rolling back the entire batch would potentially rollback inserts/updates/deletes from multiple independent requests.

I need to bne able to rollback just the queries of a single request.

	▲	andersmurphy 4 days ago \| parent [-]
		You don't need to rollback, because you have already checked the invariants and the system is a single writer. Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.