3 ms·
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement
by kijin 3y ago
> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query.
Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing Postgres on the same machine, whether inside a container or not.
I have some simple apps on EC2 with MariaDB on localhost, and well-tuned queries rarely take more than 100-200 microseconds. That's total query execution time, not just communication latency. RDS just sucks for this kind of use case. It's not a useful comparison.
> As much as I love tuning SQL queries, it’s becoming a dying art for most application developers. Even poorly tuned queries can execute in under a second for ordinary databases.
Didn't you just say that milliseconds matter?
- morelisp 3y agoBut why take on the operational overhead of a separate DB server (not to mention 200 more microseconds), plus the EC2 surcharge? I would rather run app+SQLite + dumb object storage, than app + MySQL + MySQL incremental backup and restore.
- kijin 3y agoWhat separate DB server? I was talking about installing the RDBMS on localhost, right inside the server where your application runs. No other EC2 instance, no extra charges. Preferably connect to it over a Unix domain socket instead of TCP. That's the only way to compare SQLite performance with an RDBMS in an apples-to-apples way. The point about operational overhead makes sense, though, and IMO it's the only point in this unnecessarily long article that's actually worth considering. I do have a couple of other apps running on SQLite, so I appreciate the simplicity.
- giantrobot 3y ago> No other EC2 instance, no extra charges. Think Lambda (or equivalent) instead of EC2.
- Dan42 3y agoThank you for saying that, it save me the trouble of writing it. This apples-to-oranges comparison (strawman? motivated reasoning?) unfortunately makes me mistrust this entire article.
- morelisp 3y ago> What separate DB server? It's still normal to refer to e.g. "database server", "application server", etc. processes even when running on a single machine. Re. EC2, I'm referring to the surcharge of having a dedicated instance at all, vs. working at the container / object storage level of abstraction.
- kijin 3y agoSure, you can download an SQLite database from S3 and use it in your Lambda if your dataset is small enough and, more importantly, you either don't do any writes or have access to a highly reliable solution that synchronizes writes across copies and persists them back to S3. Unfortunately, Litestream (which OP is promoting in TFA) needs to run as an independent process inside a VM and/or container in order to sync your writes. It's not very different from a traditional RDBMS in that regard. Even the title says "server-side" SQLite, not "server-less" SQLite.
- giantrobot 3y ago> Why compare the latency of a remote Postgres database with a local SQLite database? The SQLite DB is just a flat file that can be packaged in a lambda or whatever cloud's object store. It makes sense for fast access to read-heavy or read-only data. Even a shitty query can return rows in less than a millisecond where a remote DB that same amount of data is tens of milliseconds away.
- benbjohnson 3y agoAuthor here. The comparison was meant to be about how Postgres (or any client/server RDBMS) is typically deployed. Yes, you can deploy Postgres on the same machine but I wouldn't say it's common. Maybe I could have expanded more on that point or simply referenced client/server architecture rather than Postgres so it didn't seem like a straw man argument.
- marcosdumay 3y agoIf I had to guess, I'd say that single-machine (with cold backups) is the most common way to use Postgres with a web server.