SQLite or PostgreSQL? It's Complicated

I think we can assume on his Macbook it was SQLite running on NVMe, and even so SQLite was out monstered by PostgreSQL by a large margin according to his figures.

It would be interesting to know why. I would have thought for single queries the IPC overhead would put PostgreSQL at a disadvantage. Apparently not.

In fact a lot of things are puzzling. I would have expected PostgreSQL to win when there are lots of independent queries because it can aggressive caching, whereas SQLite has to assume lots of independent process are having their way the the database. Again that didn't happen - the rate speed dropped as the query rate increased was similar for both.

> SQLite with appropriate configuration for WAL, et. al. can run circles around hosted SQL providers for many classes of non-queries. SQLite directly on top of NVMe storage is a monster.

Are there any benchmarks that put hard numbers on these observations? I expect sqlite to outperform postgres for the fact alone that it doesn't need to make a network call to touch the db, but it would be important to know what these observations actually mean in the real world.

Heh Heh Heh. On the same kind of note, about 20 years ago the defaults for PostgreSQL were to use something like 8MB (in total) of shared buffer memory, so it would work out of the box on very low memory systems.

What that directly caused was a reputation for being slow and pegging the cpu (as it shuffled everything through that buffer), because people would benchmark this default config vs other databases that had better defaults.

Once we fixed that "tiny shared buffers by default" problem, then low-and-behold PostgreSQL was reported to have decent performance. ;)

Thanks for saving me a click

> Person who has never used a database before

This 'Person' is Miguel Grinberg. I don't blindly accept that any reputation is earned, but in his case from his writing and his tutorials he knows what he's doing.

> completely unoptimized

And if he's choosing unoptimised, that's fine. After all, you can tell a lot about the general case that way and a surprisingly large volume of installations, whether they should be or not, are in fact exactly that (with a little bit of extra security thrown in). So it's more relevant than it might appear.

> content marketing post for Twilio

It's a corporate blog post, yes. But full of numbers, code, and explanations. Hardly the useless waffle most imagine when they hear 'content marketing post'.

> two years ago

I know, right! Both SQLite and Postgres have left the market, and database technology is now massively different. (/s just in case it isn't obvious)