Database Caching Strategies

Discussion of PostgreSQL's built-in caching benefits versus SQLite requiring custom read caching, Redis/memcached integration, and CDN layer caching

While SQLite offers architectural simplicity, its lack of built-in shared RAM caching often forces developers in write-heavy environments to implement manual, language-specific read caches to prevent database congestion. This creates a significant trade-off compared to PostgreSQL, which provides a shared cache out of the box that remains consistent across multiple processes and instances. Although some argue that caching is better handled at the CDN layer to reduce overall server load, others contend that database-level caching is indispensable for internal logic and processing statistics where user-facing traffic isn't the primary bottleneck. Ultimately, the discussion highlights how the choice between these strategies hinges on whether a project values the portability of a serverless database or the robust, integrated performance features of a dedicated database server.

View on HN · Topics

After 2 years in production with a small (but write heavy) web service... it's a mixed bag. It definitely does the job, but not having a DB server does have not only benefits, but also drawbacks. The biggest being (lack of) caching the file/DB in RAM. As a result I have to do my own read caching, which is fine in Rust using the mokka caching library, but it's still something you have to do yourself, which would otherwise come for free with Postgres.
This of course also makes it impossible to share the cache between instances, doing so would require employing redis/memcached at which point it would be better to use Postgres.

It has been OK so far, but definitely I will have to migrate to Postgres at one point, rather sooner than later.

View on HN · Topics

How would caching on the db layer help with your web service?

In my experience, caching makes most sense on the CDN layer. Which not only caches the DB requests but the result of the rendering and everything else. So most requests do not even hit your server. And those that do need fresh data anyhow.

View on HN · Topics

As I said, my app is write heavy. So there are several separate processes that constantly write to the database, but of course, often, before writing, they need to read in order to decide what/where to write. Currently they need to have their own read cache in order to not clog the database.

The "web service" is only the user facing part which bears the least load. Read caching is useful there too as users look at statistics, so calculating them once every 5-10 minutes and caching them is needed, as that requires scanning the whole database.

A CDN is something I don't even have. It's not needed for the amount of users I have.

If I was using Postgres, these writer processes + the web service would share the same read cache for free (coming from Posgres itself). The difference wouldn't be huge if I would migrate right now, but now I already have the custom caching.

View on HN · Topics

I am no expert, but SQLite does have in memory store? At least for tables that need it..ofc sync of the writes to this store may need more work.

Summarizer