Hacker News Reader

Comment by bob1029 | Hacker News Reader

> Execution Time: 0.128 ms

This is fantastic. Wouldn't it be nice if that was the end of it and your business logic was running with the data after that exact delay?

It doesn't really detract from the value of query optimization (i.e. system load reduction), but I do find it a little contradictory in principle to push for sub-millisecond execution and then decide to send those results to a completely different computer across a network.

The biggest bottleneck we ever identified with any database vendor was the network stack sitting between database server and application server. I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query.

View on HN

For OLAP-style use-cases, I wouldn't recommend SQLite, but rather something equally (apparently) unfashionable: stored procedures.

In both cases, the goal is to save the time spent round-tripping queries and responses to the DB. But with OLAP, you more-than-likely have a large dataset that needs its own dedicated beefy DB to process. So rather than moving the data to the code (with e.g. SQLite), move the code to the data, by moving as much of your business layer as possible into the DB. (Or possibly all of it, ala https://postgrest.org/.)

And I got queries that dig through 2TB of data and return aggregations in 30 secs. Good luck doing that with SQLite.

Off topic, but never underestimate the read performance of an SQLite database with all of its indexes warm in the page cache. :)

> I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query.

So? My in-memory hashmap can do all that in a fraction of the time it takes SQLite to do its thing. Does that mean that hashmaps are superior to SQLite?

SQLite is a different database with a different concept, different goals, different guarantees, different capabilities, and different tradeoffs than PostgreSQL. Choose the best tool for the job. Sometimes it's a hashmap, sometimes it's SQLite, and sometimes it's PostgreSQL.

> Choose the best tool for the job.

Totally agree. Just trying to get the community thinking about this stuff. Most business apps I have seen over the years do not have any actual value-add proposition for existing as 2+ separate computer systems.

Reply on news.ycombinator.com