[masklinn]

> Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really.

"Lots of readers few writers" is an extremely common application property tho. Your average HN has significantly more reads than it has writes, especially if you bump the "worthless" writes (e.g. vote counts) out of using the DB and only flush them to the durable DB one in a while, for instance.

And with SQLite's WAL support it's supported even better: while it still has a singular writer, the writer doesn't block the readers anymore, which is a workload issue in the exclusive case (as the single writer would bring read concurrency down to 0).

[littlecranky67]

Another pattern to avoid "worthless" writes is using statistical sampling. I.e. if you have votes in the >100.000 range, generate a random number p in [0, 1], and only perform a write if p < 0.01 - when reading votes, multiply by 100 etc. Of course, you have to assess individually if its feasible for your operation, hence the "worthless".

[pc86]

Is there something you can point to that explains this "flush them to the durable DB once in a while" pattern in more detail?

[onelesd]

Instead of running each small write query as the event occurs (a vote), collect them in memory and at some time interval (1m, 5m, or whatever your UX will tolerate) write them to the DB in batch with one (or a few) writes.

[jiux]

Thank you. This fits a current need of mine perfectly.

Do you have any other helpful tips you’d like to share? Any thoughts between Redis and Memcache?

[hnfong]

Not the GP, but if the GP described a scenario that is useful to you, redis can #1 act as the memory cache if the processes themselves don't last long enough for the optimization to be useful, and #2 can act as a cheap message queue to funnel similar requests into a single (or small number of) processes for aggregation before writing to disk.