16 ms·

SQLite or PostgreSQL? It's Complicated

297 points by makaimc 4y ago

rhabarba 4y ago
Honestly, the blogging dude wrote a web "application" in Python, known for its not-quite-good performance, and wonders whether he can make the application faster by... changing the database?
ed25519FUUU 4y ago
The fact that the performance is so close between a database that requires NO SERVER and one that requires a dedicated server + ops management is a signal to me that all projects should start on SQLite unless there is a compelling reason not to.
- jayd16 4y ago
  Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups. If you plan to never use a shared DB, SQLite is great though.
  - mikepurvis 4y ago
    "never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
    - msbarnett 4y ago
      > Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
      - ElectricalUnion 4y ago
        > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
        msbarnett 4y ago
        Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
        323 4y ago
        > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
  - robonerd 4y ago
    I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.
    - ed25519FUUU 4y ago
      Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
      - dx034 4y ago
        Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.
  - riku_iki 4y ago
    > you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
    - urda 4y ago
      You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
      - riku_iki 4y ago
        > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
    - eropple 4y ago
      You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
      - riku_iki 4y ago
        I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.
- rco8786 4y ago
  yes! It seems to me (or at least, I'm hoping this is happening) that the pendulum is swinging back to simple deployments and away from AWS/Cloud/K8s for every tiny little app. One thing I'd love to see is that the 'default' MVP deployment is your code + SQLite running on a single VM. That will scale well into "traction" for the vast majority of applications people are building.
  - namecheapTA 4y ago
    I have a fully working SaaS with 30+ companies paying, with 20 employees each on average. I wrote it as a self taught programmer via YouTube and stackoverflow. It is hosted on a $2 a month namecheap sharedhosting. Php back, jQuery front. I use cpanel to set things up. It runs faster than 90% of webapps on the internet.
    - lazide 4y ago
      Yup, I believe it. As an industry 90% of companies are spending 90% of their time on crap only the remaining 10% of the companies actually need. Good resume fodder though I guess.
    - rco8786 4y ago
      I believe it!
    - downut 4y ago
      Do you implement something akin to Role Based Access Control for those 30x20 employees? If so where do you store and manage that data? I have zero experience with this but I am very curious how people do it in sqlite.
      - ElectricalUnion 4y ago
        Not OP, but like any other database? Even if the database supports it, you usually should not have users directly logged in the database anyways, so whenever the database "has" or "lacks" access control, it doesn't matter. What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions. https://xkcd.com/327/ https://xkcd.com/327/
- masklinn 4y ago
  It's funny because to me it says pretty much the opposite: because sqlite has no server it works entirely in-process, whereas the client will be talking to postgres over a unix socket, or possibly a full-blown network socket. Plus it seems to be a readonly workload, something for which posgres is not ultra relevant (not that it doesn't work, but...).
- Beltiras 4y ago
  There are enough SQL oopsies in this article to show that you should NEVER use sqlite for any production except as filter databases or other sort of throwaways. RDBMSs are there to enforce constraints on data.
  - spopejoy 4y ago
    RDBMS constraints are oversold usually. For instance it is usually the case that things like foreign-key constraints are avoided in production because they cause performance and maintenance nightmares. Widely-used constraints like UNIQUE are production-quality in SQLite.
  - ElectricalUnion 4y ago
    > NEVER use sqlite for any production > RDBMSs are there to enforce constraints on data. If you did not ask SQLite to enforce type constraints on relations (with STRICT), it will work in the relaxed, backwards-compatible expected behavior of previous versions of SQLite. That being said, if you want actual validation, you probably need more complex CHECK expressions anyways with your business rules, and those work by default on any database. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html * unless you're a madman that runs things with "PRAGMA ignore_check_constraints = false;" enabled or equivalent; in that case, no DB can help you.
  - fmajid 4y ago
    That’s silly, SQLite is the single most widely deployed database engine in the world, possibly the most deployed software, period. It’s unit test suite is second to none. It’s reliable enough that SMF, Solaris’ boot and service management (sort of like systemd) depends on it. That does not mean it is applicable to all databases use-cases, but production-grade it undoubtedly is.
    - ThinkBeat 4y ago
      How many installations SQLite has is not that relevant in a discussion of SQLite vs full RDMS. By your example, a bank should use SQLite because it is deployed widely. You have to look where they are deployed and the use cases. They also come with dramatically different tooling which make sa huge difference. More tooling does not mean that a full RDMS is a better choice: It is just about what you need, what use case you need to cover and what industry you are in.
- avinassh 4y ago
  hmm in that case I would probably pick Postgres because despite the added network call, it is still faster than an embedded DB.
  - ormaaj 4y ago
    As I understand it the actual low-level database interface may be a shared library mechanism in either case. At least, for a local database I'm pretty sure that's a function of the connection type. You're still dlopen()-ing an ODBC library and the underlying glue between that and the rest of the userspace DB engine parts can be implemented in many ways. If there's a socket involved it would probably be AF_UNIX
- lazide 4y ago
  Uh, most distributed environments you have no choice but to use a server, because you’ll have clients on a (relatively) uncontrollable set of other machines. If you’re writing something to run in an embedded/client app environment, then yeah why would you use Postgres for your one machine? You could, but it’ll add a lot of moving parts you’ll never need and probably don’t want (like remotely accessible network ports you’ll need to secure)
- BeefWellington 4y ago
  Sure, you can start there. Once you scale though you're going to have lots of fun.
  - neverartful 4y ago
    For many applications you're right. However, in the case of a dashboard for blog articles I don't think it could ever come to that.
  - ed25519FUUU 4y ago
    I’ve never worked on a project that was scaled out that didn’t require at least moderate DB maintenance, such as a large migration or sharding. A migration is all but expected. Might as well make things easy on yourself for the first year and start with SQLite.
  - marginalia_nu 4y ago
    We're talking about data from a blog, though. It's not like it's going to scale to have terabytes of entries anytime soon.
- WuxiFingerHold 4y ago
  I disagree: Postgres and SQLite are very different in several aspects, e.g. - types and enforcing type safety - handling concurrency and transactions (available isolation levels) - functions / procedures - array support Postgres can be much more valuable than just using it for "relational data storage". Its features can be a good reason for choosing it from the beginning.
neverartful 4y ago
Interesting article. It should be noted that the testing done is with 100% read/query workload (not surprising since the application is a website of blogs).
- miguelgrinberg 4y ago
  This is 100% correct. This is a dashboard application that is mostly read-only for users. There is a cron job that imports and writes new data once per day.
zinekeller 4y ago
I have a question that wasn't answered on the blog, and might be silly for me to ask. Is SQLite running in WAL mode or not? I usually found that WAL mode usually allows better performance even in read-heavy workloads as it doesn't need to exclusively lock the database each time. Otherwise, the post is a nice reminder for SQLite users that foresee a database switch to use the newly-introduced strict mode.
- miguelgrinberg 4y ago
  SQLite isn't running in WAL mode. This application isn't just read-heavy, it is ready-only for users, so I wouldn't think this will make a difference. The writes occur while everybody is sleeping, from a cron job that runs once a day to upload the traffic for the previous 24 hs.
- dgb23 4y ago
  Yes, WAL mode can have a drastic impact. Also this is apparently benchmarking Postgres and SQLite _with Python on top_. Some of the overhead here might not have much to do with either SQLite or Postgres at all. Just because one uses an ORM doesn't mean the same code is running and especially considering how sluggish Python is, I'm not convinced by any of this either way outside of this being an improvement for the very specific use-case presented. But we don't know _why_ that is.
  - miguelgrinberg 4y ago
    I explain this in the post. The goal is to optimize the application for users. I really don't care if Postgres is faster than SQLite or viceversa, I just want users to have a snappier application. The experiment was to swap out the database while leaving everything else the same to see if that affects the overall user experience in a positive or negative way. Also note that I'm not saying Postgres is faster the SQLite in absolute terms. That happened to be the case for this particular application and how it was deployed. If you perform a similar test with a different app or stack you will likely get different results. A main point I'm trying to make in the article is that people should do their own benchmarking instead of making decisions on stuff you read online.
ehutch79 4y ago
Is it just me or was testing done with only four concurrent clients? In a mostly read only environment? Wouldn't moving to a static site generator been a better direction in that case? If you're maxing out at four people reading your blog, I can't imagine more infrastructure is better.
- tpetry 4y ago
  They not benchmarking the blog, they are benchmarking an analytics application aggregating statistics about the blogs.
philliphaydon 4y ago
It’s difficult to accept the results without looking at the query plans to see how the queries are running and if they are optimal. Seems like it’s just a straight dump of data into PostgreSQL and letting SQLAlchemy handle some queries and indexes but no analysis of the indexes at all. Could be able to squeeze more perf out of both SQLite and PostgreSQL.
- jayski 4y ago
  exactamundo
- miguelgrinberg 4y ago
  The point of this exercise was to determine how these database perform with same schema, and if it makes sense to make the jump from SQLite to PostgreSQL or not. As a side note and since you ask, the indexes on this database are fairly well thought out, I have exhausted all ideas on indexing improvements to help with performance. I do mention a major implementation enhancement which is to precalculate monthly totals. Also, another important point I tried to make is that this benchmark is useful for this particular deployment. I don't really want to convince you the numbers that I've got will also apply to you, my proposal is that you should test your application and make decisions based on your own results, which can certainly be different than mine.
  - philliphaydon 4y ago
    While the indexes /may/ be pretty well thought out. That doesn’t mean that they are useful. Indexes don’t work the same between SQLite and PostgreSQL so a 1:1 transition between the two databases on indexes probably isn’t going to yield the best results. For example UUIDs were stored incorrectly in PostgreSQL. SQLite does not enforce varchar lengths, and typically in PostgreSQL you just use text type and not impose any length restrictions unless absolutely necessary. PostgreSQL can use multiple indexes in a single query which may help with the 2gb ram limits. PostgreSQL supports partitioning which could help with the range queries. All I’m saying is that I believe the performance you get from SQLite imo could be better, and the performance from PostgreSQL a lot better.
msbarnett 4y ago
> Once again, SQLite happily stored my UUIDs on this column and even indexed them, but to get these across to PostgreSQL I needed to fix the schema and properly declare these keys as strings. In PostgreSQL you're leaving performance on the table if you store UUIDs as strings instead of as the built in UUID type (128-bit value under the hood)
- masklinn 4y ago
  There's also no word about indexing or analysis (and postgres is very sensitive to having up-to-date statistics). And on sqlite you might be leaving memory / storage on the table if you use a UUID as PK and don't remove the implicit ROWID.
  - miguelgrinberg 4y ago
    I did not feel that was a relevant topic for this article. I have spent a good amount of time over the life of this application optimizing indexes, though. But the article is about how I designed a test to benchmark these two databases, not a general discussion on all the optimizations you can make.
    - phibz 4y ago
      In postgres there usually no good reason to use varchar unless you specifically want to enforce size limits. If you don't care use TEXT, the performance is the same.
- miguelgrinberg 4y ago
  For this particular application I discuss in the article it would make no difference, since there is only one not heavily used table that uses UUIDs. All the other tables use integer primary keys. Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. Things such as using SQLAlchemy and sticking to basic types. Sure it may affect performance some, but thanks to having done that I was able to port the app from SQLite to PostgreSQL in like an hour.
mikece 4y ago
I really wish WordPress supported using SQLite as it's database instead of MySQL as this could make backups as simple as rsync'ing from prod server to backups.
- lazide 4y ago
  Wouldn’t you run the (real) risk of a corrupted backup?
  - edbaskerville 4y ago
    Yeah. SQLite's backup API is the right way to do this: https://sqlite.org/backup.html https://sqlite.org/backup.html
    - ElectricalUnion 4y ago
      And, if you can't use the backup API for some reason, try VACUUM INTO: https://www.sqlite.org/lang_vacuum.html#vacuum_with_an_into_clause https://www.sqlite.org/lang_vacuum.html#vacuum_with_an_into_...
    - mwcampbell 4y ago
      Would creating a ZFS snapshot, then either copying from that snapshot or sending it to another machine with zfs send, also be reliable? IIUC, ZFS snapshots are point-in-time consistent.
  - Tostino 4y ago
    Beat me to mentioning this. Rsync is not a safe database backup tool. Period. It can be used to move around backups created by whatever specific database tool generated them, but just blindly using rsync is a recipe for unrecoverable corruption
- forgotpwd16 4y ago
  Not only SQLite. I would've expected WordPress be database agnostic.
cryptonector 4y ago
My advice: go straight to PG. And avoid ORMs.
- rco8786 4y ago
  My advice: advice with no reasoning or context is not useful to anyone.
  - cryptonector 4y ago
    Much much e-ink has been spilled on the subject of ORMs-good-or-bad. I shan't always reprise that.
    - rco8786 4y ago
      Literally you are the person who brought up ORMs.
      - cryptonector 4y ago
        https://news.ycombinator.com/item?id=31909806 https://news.ycombinator.com/item?id=31909806
- philliphaydon 4y ago
  ORMs are fine but they are not a silver bullet that means you never need to look at the database or the queries generated. I think too many people use ORMs and rely on them to do everything, then blame the database when things are slow. :(
  - cryptonector 4y ago
    Devs start with ORMs because they promise to make everything easy. Then their apps grow complex enough that the ORMs get in the way. Then the devs get sad.
    - 4y ago
xsc 4y ago
It seems a solid first step would be to use EXPLAIN to understand query planning/scanning issues. Assuming 6.5M rows/day*1.5yr=~3.5B rows, how large is the dataset in both SQLite and pg? With indexes, is it fitting in RAM? Linode 2GB plan is shared vcpu, so results will vary depending on who is sharing it with your service.
- miguelgrinberg 4y ago
  This isn't the topic of the article so I haven't included it, but I have taken several "solid steps" to optimize this application throughout its year and a half of being in production. This article is about one of them only, how I designed a test system to evaluate the two databases head to head.
  - yellowapple 4y ago
    > This isn't the topic of the article Query planning seems like it should be the topic of the article. SQLite and PostgreSQL do their query planning very differently - not to mention that SQLAlchemy is probably generating different queries. I can't really fathom how one can meaningfully "evaluate the two databases head to head" while seeming to actively avoid how they plan/optimize/execute queries.
    - dataspun 4y ago
      This sounds like a great topic for your research, your article, and your passion to share with others if it exists. Get to work.
      - yellowapple 4y ago
        I don't have access to the code used in the article. Therefore, no amount of research on my part is capable of answering why PostgreSQL was faster than SQLite in the author's situation - certainly not without the queries and the plans / EXPLAINs thereof, those being the specific thing I and multiple others have suggested as something the article could've provided and which would've made it an actually interesting head-to-head. Meanwhile, if all you have to offer in response to such a suggestion is "hurr durr do it yourself", then why bother commenting at all?
RedShift1 4y ago
Use the right tool for the right job.
- ormaaj 4y ago
  Agreed. Performance comparison is interesting in a synthetic benchmark context but the premise of the title isn't that.
jeffdn 4y ago
> The solution was to grow the slug column to 512 characters and retry. No, the solution is to always use `text` as your column type, and use a check constraint if you need to enforce a limit. It's much easier and safer to alter a check constraint than it is to change a column type live in production. The `text` type and `varchar(x)` are identical under the hood, `text` takes up no more space on disk.
- masklinn 4y ago
  Note: that's true for postgres and sqlite (which ignores the length limit entirely as they discovered anyway), not necessarily for other database systems.
  - coolsunglasses 4y ago
    How convenient then that he's comparing PostgreSQL and SQLite then!
- SigmundA 4y ago
  I thought as of PG 9.2 expanding a varchar column was metadata only operation and therefore low overhead. I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes. Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.
  - masklinn 4y ago
    > PG seems to have this issue as well but the limit is 2712 bytes. Note that this is only for bytes indexes. Hash is not so limited. Tho at this sort of sizes it seems unlikely such simple indexes are of much use. I guess the range matching abilities of btrees could be but that seems unlikely, a 2700 bytes path-like datum is a lot.
rammy1234 4y ago
If you say using SQLite, there is always risk of losing that data file in disk right. How is that managed. is this a dumb question , which PostgreSQL do not ??
- lazide 4y ago
  Not a dumb question, but some people don’t care for their use case (either the data is cheap, or they don’t think it will happen)
- stonemetal12 4y ago
  Postgresql stores data to disk, so it has the same risk of loss. Both setups need a backup solution to mitigate the problem of disk failure. My understanding of the situation is sqlite's solution is a bit more low level than postgresql but should be workable.
  - GordonS 4y ago
    In combination with a base backup, you can use streaming WAL backups with Postgres, shipping compressed WAL files to S3 or wherever. I like WAL-G for this. There are lower-level approaches built-in to SQLite, such as the Backup API, but there is at least one 3rd party project, Litestream, which does streaming WAL backups too.
- neverartful 4y ago
  The big question here would be whether the filesystem that the data file resides on is mounted on a single-disk volume or a RAID-volume (or even a clustered SDS volume such as Ceph). On a single-disk volume, if the disk dies your DB is gone (at least until you can replace the disk and restore the backup). On a sensibly designed RAID-volume, if one of the disks dies the application's admin probably would never even know. The sysadmin would see it in their logging/alerting infrastructure and replace the dead disk. There would not be any loss of data or availability of the data (just a temporary degradation in the overall resiliency). Same is true for a clustered SDS volume such as Ceph.
  - 4y ago
xwdv 4y ago
No one ever got fired for just using postgtreSQL, maybe burned though.
ormaaj 4y ago
This is totally uncomplicated. The choice of database is a function of your application and its architecture, not a matter of all-things-equal performance comparison. "I want an extensible versitile database platform for use as a component in an infrastructure that is part of a larger system" - postgres. "I want an embeddable lightweight SQL storage backend for a narrow scope, single-purpose, probably single-user, probably single-system application" - sqlite Postgres should perform well in any role in the hands of an expert - it's a question of whether or not that is overkill. Sqlite can also perform well in a subset of those roles.
mjw1007 4y ago
I had a look at SQLite around the time sqlite3 appeared, and my notes say I decided I didn't like it because of the lax and sometimes inconsistent way it can treat its input. I thought I'd have another go at it today (sqlite 3.34) and I'm mildly surprised that the things I didn't like are still there: sqlite> select '1 apple' + '2 oranges'; 3 sqlite> select count(*) where 0.9; 1 sqlite> select count(*) where 1.1; 1 sqlite> select count(*) where not 0.9; 1 sqlite> select count(*) where not 1.1; 0
- gunapologist99 4y ago
  The debate over type conversion seems somewhat like arguing language features (such as weak typing vs strong), which has been a spirited debate since forever. The fact that this is debatable at all means that there are arguments and counter-arguments that serve both sides well, which further implies that there are use cases where some pros outweigh the cons on each side. (Of course, this entirely neglects the point that it's usually fun and even educational to have a technical argument.)
  - mjw1007 4y ago
    I don't have any great objection to a database that lets you store a value of any type in any column. And if they want to say '3' + 5 = 8, I could imagine why that might be fine. But I don't think that's a good reason to have behaviour like the above. I think it's more likely that they wouldn't choose to do those things again today: I think this is what fell out of the initial TCL-based implementation and they feel they're stuck with keeping it backwards-compatible.
- simonw 4y ago
  "I'm mildly surprised that the things I didn't like are still there" That's because SQLite doesn't ship changes that break existing applications.
- infogulch 4y ago
  You may be interested in STRICT Tables [1], introduced just 6 months ago: > Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html Related past discussions: Strict Tables – Column type constraints in SQLite - Draft | 2021-08-21 | 331 points, 108 comments | https://news.ycombinator.com/item?id=28259104 https://news.ycombinator.com/item?id=28259104 SQLite Release 3.37.0 | 2021-11-27 | 333 points, 112 comments | https://news.ycombinator.com/item?id=29363054 https://news.ycombinator.com/item?id=29363054
Thaxll 4y ago
I mean SQLite is living on a single node, what exactly do you do when you have failure, when more than one process need to acces the DB, when you need RBAC etc ...
- warmwaffles 4y ago
  > what exactly do you do when you have failure Depends on the failure. What do you have in mind? > when more than one process need to acces the DB Easy, you use `journal_mode` set to `WAL` when you connect to the sqlite database. > when you need RBAC If you need RBAC, you aren't going to be using sqlite. In theory you could have your application handle the RBAC part since sqlite is embedded in your application. Another solution is to leverage unix users and groups and set permissions appropriately for the database file and the application running.
  - ju-st 4y ago
    >> when more than one process need to acces the DB > Easy, you use `journal_mode` set to `WAL` when you connect to the sqlite database. and set isolation level and check_same_thread and PRAGMA synchronous and then put all queries in retry loops because the DB is maybe locked https://sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode https://sqlite.org/wal.html#sometimes_queries_return_sqlite_... I wasted so much time trying to get multiple sqlite queries running at the same time
    - warmwaffles 4y ago
      > I wasted so much time trying to get multiple sqlite queries running at the same time You were trying to write at the same time right?
      - ju-st 4y ago
        no, although the DB was not opened readonly mode. I tested with apachebench with many parallel requests on endpoints using only SELECTs
- 4y ago
stormdennis 4y ago
I don't know about now but at one time Firebird made this question moot. It never received the love it deserved in my view.
- kstrauser 4y ago
  Many years ago, I was hired at a web shop running Firebird in protection. It had the interesting property that if you ran the client software on your desktop to connect to the server software on another host, and used ^Z to stop the client, then the server process would hang until you resumed the client. I'm not entirely sure how I'd implement that as a feature if I wanted to.
  - stormdennis 4y ago
    Not good!
umvi 4y ago
Can SQLite be used outside of single threaded contexts? Last I checked having multiple concurrent readers and writers is very difficult. Multiple concurrent readers only is probably ok if that's all you need.
- neverartful 4y ago
  It can, but as the concurrency increases you'll start to see errors indicating that database is locked.
  - euroderf 4y ago
    This sounds like: so there is some point where a feedback loop tends to lock it all up, but it's fixable with faster hardware.
- simonw 4y ago
  In practice, most SQLite writes take less than a few ms, so concurrent writes are only likely to be a problem for you if you need to handle thousands of writes per second. I've implemented the pattern where writes sit in an in-memory queue and are applied to a dedicated write connection in series. I've not run into any problems with this so far, but I've also not tried to handle thousands of writes per second with it yet.
- shrimpx 4y ago
  I’ve done this with retry logic in each concurrent writer (retry if you can’t lock the file with a short timeout) and it works fine enough.
  - umvi 4y ago
    If threads have to contend for db with retry loops, does concurrency actually buy you anything?
    - shrimpx 4y ago
      It definitely doesn't buy you performance. But I thought you were asking if it's possible, or how difficult it is. A use case might be if you have an existing architecture of N processes and want to collect stuff from them in a central file.
mcdonje 4y ago
>If you are going to take one thing away from this article, I hope it is that the only benchmarks that are valuable are those that run on your own platform, with your own stack, with your own data, and with your own software. And even then, you may need to add custom optimizations to get the best performance. Good advice, contextually.
bob1029 4y ago
I think it's even more complicated. The reduction in latency brought on by in-process databases, combined with modern NVMe storage, means that SQLite is a substantially faster approach than any other solution which requires a trip through the network stack. I've got services in production for several financial institutions right now that are resolving most SQL queries within 100-1000 micro seconds by simply using SQLite on reasonable hardware. How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude? Obvious caveats here being the resilience angle. We addressed this with application-level protocols and additional instances.
- srcreigh 4y ago
  Can you share more info about resilience, app protocols and additional instances?
- infogulch 4y ago
  Another angle is unbounded horizontal scalability across tenants. If your application can be divided cleanly between different customers and one customer can be served with one instance (see "How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude?") then scaling to more customers is extremely simple and linear.
- abrookewood 4y ago
  Agreed, but a fairer comparison would be SQLite vs PostgreSQL installed on the same machine.
  - spopejoy 4y ago
    Even there you'll see the same: micros in SQLite vs millis for PostgreSQL. PostgreSQL is simply not the right choice if you want in-process speeds.
- WuxiFingerHold 4y ago
  > How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude? None - or practically not many, when using async (or sort of green threading) backend stacks. The waiting connections / users are just waiting and don't block any new connections. If the network round trip to Postgres (in my experience around 3-10 ms, but of course highly depending on your server infra) was a concern Postgres could be placed on the same server as the backend, though I would not recommend it. But this relatively small IO overhead usually is not a concern for many apps.
mritchie712 4y ago
Do clickhouse next!
yread 4y ago
I understand and partially agree with all the criticism of the article (and could add my own like why did you tweak work_mem out of all the hundreds of settings on postgres) but it's a blog post, not a scientific article - I still think it's a useful post. I might be in a similar situation at some point and it's nice to know that you haven't encountered any major gotchas and made it even 2x faster. Thanks for sharing your experience.
andy_ppp 4y ago
Why is this limited to four clients? Maybe I missed something but it would be good to see what happens with higher numbers of connections (something both of these dbs are rumoured to be bad at).
KronisLV 4y ago
You know, apart from some of the details in regards to what data types to better use etc., I think that both PostgreSQL and SQLite are excellent choices for most use cases! Of course, SQLite might be the best choice for embedded or local usage, they even have this lovely page about using it as an application file format: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html And PostgreSQL might scale better for multiple writers and situations where you need it on a separate VM/server/container that might be shared between different apps through a network connection of some sort. Though it's also curious, because you never hear a lot about MySQL or MariaDB (comparatively, in my subjective opinion) on HN or many other places for that matter. And yet, it's still a decent option, has good tooling (MySQL Workbench is great), is widely supported and backs a significant amount of production systems out there. Do people just view it as old/legacy, or is there the belief that for many use cases PostgreSQL might simply be the better option, e.g. also because of projects like PostGIS and support for more complex setups, like custom data types, more advanced scripting language for in-database processing and whatnot?
- danenania 4y ago
  I’m very happy with MySQL currently. I started using it because AWS Aurora supports it a bit better than postgres. I haven’t had any regrets since. It’s great when you just want simple relational storage without a lot of features or ceremony, but need a bit more than SQLite. It’s fast, predictable, and gets the job done. Postgres is great too and offers more, but sometimes that can tempt you to try to do too much in the DB layer.
- tomtheelder 4y ago
  I think it's more the latter: using Postgres for relatively simple workloads really isn't any more challenging than using something like MySQL, but then Postgres also supports more complex setups when you need it. So there would need to be a reason to lock yourself out of what PG offers, and I just don't think such a reason exists.
polyrand 4y ago
Great blog post. A few observations (considering the blog post skips numerous details): SQLite really shines when compared to running a database in a separate server from the app. This is when the network latency starts being noticeable. Latency between you and the app is usually constant. The latency between the app and the database is what matters. If in this case, Postgres can run in the same host as the main app, I think it's a good thing to do. But very often that's not the case. Even though not surprising, it's great to see how SQLite handles concurrency. From my experience, SQLite would be able to handle much more read concurrency. It would have been great to see more benchmarks (like 1000 concurrent users, even if the app would never see such traffic). I was surprised that SQLite didn't beat Postgres on simpler queries. Without seeing the queries, if they contain multiple joins, that could be another reason why SQLite is slower since it can only do "nested loop" JOINs. The workloads also seem mostly analytical, we'll see where DuckDB goes in this space (looking promising for now). One thing not mentioned in the blog post is "easy to maintain and update". If the app is deployed with Docker, adding Postgres means you go from worrying about: 1 docker container + 1 docker volume to: 2 docker containers (docker-compose?) + 1 docker volume + internal docker networking The fact that SQLite is so simple to operate also has some advantages. You can usually go back to an SQLite-based app 1 year later and understand everything. With other databases, you may need to review users, roles, permissions, connection string, etc. Another cool thing about SQLite is that it scales mostly linearly as you add more CPU cores. It can scale as long as you can also scale the number of processes in your app. In other databases, scaling means adding connection pools and other tools in the middle. Lastly, and related to the latency topic. The low latency in SQLite means that you can sometimes convert a big query into 5 separate, small and reusable queries without any performance hit. It's really remarkable home much cleaner your code can become with this. In any case, I love working both with Postgres and SQLite, and we need more blog posts like this one showing real-world cases.
- ptrik 4y ago
  Ditto on DuckDB point. This looks like OLAP workloads to me and a columnar database would work wonders. DuckDB if you going embedded, Clickhouse if you going with a server.
danaugrs 4y ago
How about PostgreSQLite? Joke aside, is anyone building something that takes a stab at fixing the issues with both?
mattxxx 4y ago
Some context: I've worked with massive PostgreSQL db's, containing millions of daily records spanning multiple years (allowing for potentially massive numbers of returned rows). I also exclusively MVP simple db architectures in SQLite (think: no massive joins or need to partition data). Best takeaways here: - SQLite kills it. It appears untuned PostgreSQL is only 2 seconds (???) faster than untuned SQLite; always try to kickoff a project with SQLite - PostgreSQL has a performance cliff when query responses leave memory. This is extra true when an index no longer fits in memory. worker_mem is critical for pushing back this performance cliff Side note: Tuning postgres can be really painful, but it's also a really dope tried-and-true db.
KingOfCoders 4y ago
From my experience SQLLite shines (using it in Go) when in-process, local, SQLlite-API and from an NVMe. It's probably not the right choice over networks and with ORMs.
Labo333 4y ago
The major drawback I encountered when building upon SQLite is concurrency of writes. I have a lot of worker processes writing new data. With SQLite, I was getting a lot of "Database locked" errors and ended up having to use pg.
cocoflunchy 4y ago
According to https://www.linode.com/pricing/ https://www.linode.com/pricing/ a shared instance with 2GB of RAM and 1CPU is $10/month. I don't know how much a principal engineer at twilio makes but I'm pretty sure this wasn't a cost effective optimization... It's also a bit weird not to talk about indexing? Are sqlite and Postgresql similar enough that the same indexing strategy should be used?
adityapatadia 4y ago
Am I the only one who is thinking why they have developed in-house system for such tracking in first place? This problem (of analysis of traffic) could easily be achieved with Segment (a twillio product) and analytics tool like Mixpanel or Amplitude.
vivzkestrel 4y ago
You are comparing the wrong databases together! Compare MySQL and PostgreSQL instead
rglullis 4y ago
Here is a comparison that I'd like to see next: how much time was spent on the following... - porting the application, making all these benchmarks and writing a blog post vs - simply getting a VM with more RAM and more CPUs. In the conclusion the author goes on to talk about the options "if I get the budget" vs "without throwing more money at the problem". I'd guess that the cost of the day of a Principal Engineer would far surpass the cost of years running a 4CPU server.
nojvek 4y ago
It seems the queries are aggregate based and they’d be served better by DuckDB instead of SQLite. DuckDB performance is astounding for doing group by, sum/count/avg with filters and sorts.
johnthescott 4y ago
is the app used by a single user or shared across many users? what question could be simpler?

297 points by makaimc 4y ago

Honestly, the blogging dude wrote a web "application" in Python, known for its not-quite-good performance, and wonders whether he can make the application faster by... changing the database?

ed25519FUUU 4y ago

The fact that the performance is so close between a database that requires NO SERVER and one that requires a dedicated server + ops management is a signal to me that all projects should start on SQLite unless there is a compelling reason not to.

jayd16 4y ago
Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups. If you plan to never use a shared DB, SQLite is great though.
- mikepurvis 4y ago
  "never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
  - msbarnett 4y ago
    > Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
    - ElectricalUnion 4y ago
      > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
      - msbarnett 4y ago
        Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
        323 4y ago
        > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
- robonerd 4y ago
  I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.
  - ed25519FUUU 4y ago
    Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
    - dx034 4y ago
      Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.
- riku_iki 4y ago
  > you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
  - urda 4y ago
    You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
    - riku_iki 4y ago
      > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
  - eropple 4y ago
    You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
    - riku_iki 4y ago
      I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.
rco8786 4y ago
yes! It seems to me (or at least, I'm hoping this is happening) that the pendulum is swinging back to simple deployments and away from AWS/Cloud/K8s for every tiny little app. One thing I'd love to see is that the 'default' MVP deployment is your code + SQLite running on a single VM. That will scale well into "traction" for the vast majority of applications people are building.
- namecheapTA 4y ago
  I have a fully working SaaS with 30+ companies paying, with 20 employees each on average. I wrote it as a self taught programmer via YouTube and stackoverflow. It is hosted on a $2 a month namecheap sharedhosting. Php back, jQuery front. I use cpanel to set things up. It runs faster than 90% of webapps on the internet.
  - lazide 4y ago
    Yup, I believe it. As an industry 90% of companies are spending 90% of their time on crap only the remaining 10% of the companies actually need. Good resume fodder though I guess.
  - rco8786 4y ago
    I believe it!
  - downut 4y ago
    Do you implement something akin to Role Based Access Control for those 30x20 employees? If so where do you store and manage that data? I have zero experience with this but I am very curious how people do it in sqlite.
    - ElectricalUnion 4y ago
      Not OP, but like any other database? Even if the database supports it, you usually should not have users directly logged in the database anyways, so whenever the database "has" or "lacks" access control, it doesn't matter. What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions. https://xkcd.com/327/ https://xkcd.com/327/
masklinn 4y ago
It's funny because to me it says pretty much the opposite: because sqlite has no server it works entirely in-process, whereas the client will be talking to postgres over a unix socket, or possibly a full-blown network socket. Plus it seems to be a readonly workload, something for which posgres is not ultra relevant (not that it doesn't work, but...).
Beltiras 4y ago
There are enough SQL oopsies in this article to show that you should NEVER use sqlite for any production except as filter databases or other sort of throwaways. RDBMSs are there to enforce constraints on data.
- spopejoy 4y ago
  RDBMS constraints are oversold usually. For instance it is usually the case that things like foreign-key constraints are avoided in production because they cause performance and maintenance nightmares. Widely-used constraints like UNIQUE are production-quality in SQLite.
- ElectricalUnion 4y ago
  > NEVER use sqlite for any production > RDBMSs are there to enforce constraints on data. If you did not ask SQLite to enforce type constraints on relations (with STRICT), it will work in the relaxed, backwards-compatible expected behavior of previous versions of SQLite. That being said, if you want actual validation, you probably need more complex CHECK expressions anyways with your business rules, and those work by default on any database. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html * unless you're a madman that runs things with "PRAGMA ignore_check_constraints = false;" enabled or equivalent; in that case, no DB can help you.
- fmajid 4y ago
  That’s silly, SQLite is the single most widely deployed database engine in the world, possibly the most deployed software, period. It’s unit test suite is second to none. It’s reliable enough that SMF, Solaris’ boot and service management (sort of like systemd) depends on it. That does not mean it is applicable to all databases use-cases, but production-grade it undoubtedly is.
  - ThinkBeat 4y ago
    How many installations SQLite has is not that relevant in a discussion of SQLite vs full RDMS. By your example, a bank should use SQLite because it is deployed widely. You have to look where they are deployed and the use cases. They also come with dramatically different tooling which make sa huge difference. More tooling does not mean that a full RDMS is a better choice: It is just about what you need, what use case you need to cover and what industry you are in.
avinassh 4y ago
hmm in that case I would probably pick Postgres because despite the added network call, it is still faster than an embedded DB.
- ormaaj 4y ago
  As I understand it the actual low-level database interface may be a shared library mechanism in either case. At least, for a local database I'm pretty sure that's a function of the connection type. You're still dlopen()-ing an ODBC library and the underlying glue between that and the rest of the userspace DB engine parts can be implemented in many ways. If there's a socket involved it would probably be AF_UNIX
lazide 4y ago
Uh, most distributed environments you have no choice but to use a server, because you’ll have clients on a (relatively) uncontrollable set of other machines. If you’re writing something to run in an embedded/client app environment, then yeah why would you use Postgres for your one machine? You could, but it’ll add a lot of moving parts you’ll never need and probably don’t want (like remotely accessible network ports you’ll need to secure)
BeefWellington 4y ago
Sure, you can start there. Once you scale though you're going to have lots of fun.
- neverartful 4y ago
  For many applications you're right. However, in the case of a dashboard for blog articles I don't think it could ever come to that.
- ed25519FUUU 4y ago
  I’ve never worked on a project that was scaled out that didn’t require at least moderate DB maintenance, such as a large migration or sharding. A migration is all but expected. Might as well make things easy on yourself for the first year and start with SQLite.
- marginalia_nu 4y ago
  We're talking about data from a blog, though. It's not like it's going to scale to have terabytes of entries anytime soon.
WuxiFingerHold 4y ago
I disagree: Postgres and SQLite are very different in several aspects, e.g. - types and enforcing type safety - handling concurrency and transactions (available isolation levels) - functions / procedures - array support Postgres can be much more valuable than just using it for "relational data storage". Its features can be a good reason for choosing it from the beginning.

neverartful 4y ago

Interesting article. It should be noted that the testing done is with 100% read/query workload (not surprising since the application is a website of blogs).

miguelgrinberg 4y ago
This is 100% correct. This is a dashboard application that is mostly read-only for users. There is a cron job that imports and writes new data once per day.

zinekeller 4y ago

I have a question that wasn't answered on the blog, and might be silly for me to ask. Is SQLite running in WAL mode or not? I usually found that WAL mode usually allows better performance even in read-heavy workloads as it doesn't need to exclusively lock the database each time. Otherwise, the post is a nice reminder for SQLite users that foresee a database switch to use the newly-introduced strict mode.

miguelgrinberg 4y ago
SQLite isn't running in WAL mode. This application isn't just read-heavy, it is ready-only for users, so I wouldn't think this will make a difference. The writes occur while everybody is sleeping, from a cron job that runs once a day to upload the traffic for the previous 24 hs.
dgb23 4y ago
Yes, WAL mode can have a drastic impact. Also this is apparently benchmarking Postgres and SQLite _with Python on top_. Some of the overhead here might not have much to do with either SQLite or Postgres at all. Just because one uses an ORM doesn't mean the same code is running and especially considering how sluggish Python is, I'm not convinced by any of this either way outside of this being an improvement for the very specific use-case presented. But we don't know _why_ that is.
- miguelgrinberg 4y ago
  I explain this in the post. The goal is to optimize the application for users. I really don't care if Postgres is faster than SQLite or viceversa, I just want users to have a snappier application. The experiment was to swap out the database while leaving everything else the same to see if that affects the overall user experience in a positive or negative way. Also note that I'm not saying Postgres is faster the SQLite in absolute terms. That happened to be the case for this particular application and how it was deployed. If you perform a similar test with a different app or stack you will likely get different results. A main point I'm trying to make in the article is that people should do their own benchmarking instead of making decisions on stuff you read online.

ehutch79 4y ago

Is it just me or was testing done with only four concurrent clients? In a mostly read only environment? Wouldn't moving to a static site generator been a better direction in that case? If you're maxing out at four people reading your blog, I can't imagine more infrastructure is better.

tpetry 4y ago
They not benchmarking the blog, they are benchmarking an analytics application aggregating statistics about the blogs.

philliphaydon 4y ago

It’s difficult to accept the results without looking at the query plans to see how the queries are running and if they are optimal. Seems like it’s just a straight dump of data into PostgreSQL and letting SQLAlchemy handle some queries and indexes but no analysis of the indexes at all. Could be able to squeeze more perf out of both SQLite and PostgreSQL.

jayski 4y ago
exactamundo
miguelgrinberg 4y ago
The point of this exercise was to determine how these database perform with same schema, and if it makes sense to make the jump from SQLite to PostgreSQL or not. As a side note and since you ask, the indexes on this database are fairly well thought out, I have exhausted all ideas on indexing improvements to help with performance. I do mention a major implementation enhancement which is to precalculate monthly totals. Also, another important point I tried to make is that this benchmark is useful for this particular deployment. I don't really want to convince you the numbers that I've got will also apply to you, my proposal is that you should test your application and make decisions based on your own results, which can certainly be different than mine.
- philliphaydon 4y ago
  While the indexes /may/ be pretty well thought out. That doesn’t mean that they are useful. Indexes don’t work the same between SQLite and PostgreSQL so a 1:1 transition between the two databases on indexes probably isn’t going to yield the best results. For example UUIDs were stored incorrectly in PostgreSQL. SQLite does not enforce varchar lengths, and typically in PostgreSQL you just use text type and not impose any length restrictions unless absolutely necessary. PostgreSQL can use multiple indexes in a single query which may help with the 2gb ram limits. PostgreSQL supports partitioning which could help with the range queries. All I’m saying is that I believe the performance you get from SQLite imo could be better, and the performance from PostgreSQL a lot better.

msbarnett 4y ago

> Once again, SQLite happily stored my UUIDs on this column and even indexed them, but to get these across to PostgreSQL I needed to fix the schema and properly declare these keys as strings. In PostgreSQL you're leaving performance on the table if you store UUIDs as strings instead of as the built in UUID type (128-bit value under the hood)

masklinn 4y ago
There's also no word about indexing or analysis (and postgres is very sensitive to having up-to-date statistics). And on sqlite you might be leaving memory / storage on the table if you use a UUID as PK and don't remove the implicit ROWID.
- miguelgrinberg 4y ago
  I did not feel that was a relevant topic for this article. I have spent a good amount of time over the life of this application optimizing indexes, though. But the article is about how I designed a test to benchmark these two databases, not a general discussion on all the optimizations you can make.
  - phibz 4y ago
    In postgres there usually no good reason to use varchar unless you specifically want to enforce size limits. If you don't care use TEXT, the performance is the same.
miguelgrinberg 4y ago
For this particular application I discuss in the article it would make no difference, since there is only one not heavily used table that uses UUIDs. All the other tables use integer primary keys. Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. Things such as using SQLAlchemy and sticking to basic types. Sure it may affect performance some, but thanks to having done that I was able to port the app from SQLite to PostgreSQL in like an hour.

mikece 4y ago

I really wish WordPress supported using SQLite as it's database instead of MySQL as this could make backups as simple as rsync'ing from prod server to backups.

lazide 4y ago
Wouldn’t you run the (real) risk of a corrupted backup?
- edbaskerville 4y ago
  Yeah. SQLite's backup API is the right way to do this: https://sqlite.org/backup.html https://sqlite.org/backup.html
  - ElectricalUnion 4y ago
    And, if you can't use the backup API for some reason, try VACUUM INTO: https://www.sqlite.org/lang_vacuum.html#vacuum_with_an_into_clause https://www.sqlite.org/lang_vacuum.html#vacuum_with_an_into_...
  - mwcampbell 4y ago
    Would creating a ZFS snapshot, then either copying from that snapshot or sending it to another machine with zfs send, also be reliable? IIUC, ZFS snapshots are point-in-time consistent.
- Tostino 4y ago
  Beat me to mentioning this. Rsync is not a safe database backup tool. Period. It can be used to move around backups created by whatever specific database tool generated them, but just blindly using rsync is a recipe for unrecoverable corruption
forgotpwd16 4y ago
Not only SQLite. I would've expected WordPress be database agnostic.

cryptonector 4y ago

My advice: go straight to PG. And avoid ORMs.

rco8786 4y ago
My advice: advice with no reasoning or context is not useful to anyone.
- cryptonector 4y ago
  Much much e-ink has been spilled on the subject of ORMs-good-or-bad. I shan't always reprise that.
  - rco8786 4y ago
    Literally you are the person who brought up ORMs.
    - cryptonector 4y ago
      https://news.ycombinator.com/item?id=31909806 https://news.ycombinator.com/item?id=31909806
philliphaydon 4y ago
ORMs are fine but they are not a silver bullet that means you never need to look at the database or the queries generated. I think too many people use ORMs and rely on them to do everything, then blame the database when things are slow. :(
- cryptonector 4y ago
  Devs start with ORMs because they promise to make everything easy. Then their apps grow complex enough that the ORMs get in the way. Then the devs get sad.
  - 4y ago

xsc 4y ago

It seems a solid first step would be to use EXPLAIN to understand query planning/scanning issues. Assuming 6.5M rows/day*1.5yr=~3.5B rows, how large is the dataset in both SQLite and pg? With indexes, is it fitting in RAM? Linode 2GB plan is shared vcpu, so results will vary depending on who is sharing it with your service.

miguelgrinberg 4y ago
This isn't the topic of the article so I haven't included it, but I have taken several "solid steps" to optimize this application throughout its year and a half of being in production. This article is about one of them only, how I designed a test system to evaluate the two databases head to head.
- yellowapple 4y ago
  > This isn't the topic of the article Query planning seems like it should be the topic of the article. SQLite and PostgreSQL do their query planning very differently - not to mention that SQLAlchemy is probably generating different queries. I can't really fathom how one can meaningfully "evaluate the two databases head to head" while seeming to actively avoid how they plan/optimize/execute queries.
  - dataspun 4y ago
    This sounds like a great topic for your research, your article, and your passion to share with others if it exists. Get to work.
    - yellowapple 4y ago
      I don't have access to the code used in the article. Therefore, no amount of research on my part is capable of answering why PostgreSQL was faster than SQLite in the author's situation - certainly not without the queries and the plans / EXPLAINs thereof, those being the specific thing I and multiple others have suggested as something the article could've provided and which would've made it an actually interesting head-to-head. Meanwhile, if all you have to offer in response to such a suggestion is "hurr durr do it yourself", then why bother commenting at all?

RedShift1 4y ago

Use the right tool for the right job.

ormaaj 4y ago
Agreed. Performance comparison is interesting in a synthetic benchmark context but the premise of the title isn't that.

jeffdn 4y ago

> The solution was to grow the slug column to 512 characters and retry. No, the solution is to always use `text` as your column type, and use a check constraint if you need to enforce a limit. It's much easier and safer to alter a check constraint than it is to change a column type live in production. The `text` type and `varchar(x)` are identical under the hood, `text` takes up no more space on disk.

masklinn 4y ago
Note: that's true for postgres and sqlite (which ignores the length limit entirely as they discovered anyway), not necessarily for other database systems.
- coolsunglasses 4y ago
  How convenient then that he's comparing PostgreSQL and SQLite then!
SigmundA 4y ago
I thought as of PG 9.2 expanding a varchar column was metadata only operation and therefore low overhead. I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes. Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.
- masklinn 4y ago
  > PG seems to have this issue as well but the limit is 2712 bytes. Note that this is only for bytes indexes. Hash is not so limited. Tho at this sort of sizes it seems unlikely such simple indexes are of much use. I guess the range matching abilities of btrees could be but that seems unlikely, a 2700 bytes path-like datum is a lot.

rammy1234 4y ago

If you say using SQLite, there is always risk of losing that data file in disk right. How is that managed. is this a dumb question , which PostgreSQL do not ??

lazide 4y ago
Not a dumb question, but some people don’t care for their use case (either the data is cheap, or they don’t think it will happen)
stonemetal12 4y ago
Postgresql stores data to disk, so it has the same risk of loss. Both setups need a backup solution to mitigate the problem of disk failure. My understanding of the situation is sqlite's solution is a bit more low level than postgresql but should be workable.
- GordonS 4y ago
  In combination with a base backup, you can use streaming WAL backups with Postgres, shipping compressed WAL files to S3 or wherever. I like WAL-G for this. There are lower-level approaches built-in to SQLite, such as the Backup API, but there is at least one 3rd party project, Litestream, which does streaming WAL backups too.
neverartful 4y ago
The big question here would be whether the filesystem that the data file resides on is mounted on a single-disk volume or a RAID-volume (or even a clustered SDS volume such as Ceph). On a single-disk volume, if the disk dies your DB is gone (at least until you can replace the disk and restore the backup). On a sensibly designed RAID-volume, if one of the disks dies the application's admin probably would never even know. The sysadmin would see it in their logging/alerting infrastructure and replace the dead disk. There would not be any loss of data or availability of the data (just a temporary degradation in the overall resiliency). Same is true for a clustered SDS volume such as Ceph.
- 4y ago

xwdv 4y ago

No one ever got fired for just using postgtreSQL, maybe burned though.

ormaaj 4y ago

This is totally uncomplicated. The choice of database is a function of your application and its architecture, not a matter of all-things-equal performance comparison. "I want an extensible versitile database platform for use as a component in an infrastructure that is part of a larger system" - postgres. "I want an embeddable lightweight SQL storage backend for a narrow scope, single-purpose, probably single-user, probably single-system application" - sqlite Postgres should perform well in any role in the hands of an expert - it's a question of whether or not that is overkill. Sqlite can also perform well in a subset of those roles.

mjw1007 4y ago

I had a look at SQLite around the time sqlite3 appeared, and my notes say I decided I didn't like it because of the lax and sometimes inconsistent way it can treat its input. I thought I'd have another go at it today (sqlite 3.34) and I'm mildly surprised that the things I didn't like are still there: sqlite> select '1 apple' + '2 oranges'; 3 sqlite> select count(*) where 0.9; 1 sqlite> select count(*) where 1.1; 1 sqlite> select count(*) where not 0.9; 1 sqlite> select count(*) where not 1.1; 0

gunapologist99 4y ago
The debate over type conversion seems somewhat like arguing language features (such as weak typing vs strong), which has been a spirited debate since forever. The fact that this is debatable at all means that there are arguments and counter-arguments that serve both sides well, which further implies that there are use cases where some pros outweigh the cons on each side. (Of course, this entirely neglects the point that it's usually fun and even educational to have a technical argument.)
- mjw1007 4y ago
  I don't have any great objection to a database that lets you store a value of any type in any column. And if they want to say '3' + 5 = 8, I could imagine why that might be fine. But I don't think that's a good reason to have behaviour like the above. I think it's more likely that they wouldn't choose to do those things again today: I think this is what fell out of the initial TCL-based implementation and they feel they're stuck with keeping it backwards-compatible.
simonw 4y ago
"I'm mildly surprised that the things I didn't like are still there" That's because SQLite doesn't ship changes that break existing applications.
infogulch 4y ago
You may be interested in STRICT Tables [1], introduced just 6 months ago: > Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html Related past discussions: Strict Tables – Column type constraints in SQLite - Draft | 2021-08-21 | 331 points, 108 comments | https://news.ycombinator.com/item?id=28259104 https://news.ycombinator.com/item?id=28259104 SQLite Release 3.37.0 | 2021-11-27 | 333 points, 112 comments | https://news.ycombinator.com/item?id=29363054 https://news.ycombinator.com/item?id=29363054

Thaxll 4y ago

I mean SQLite is living on a single node, what exactly do you do when you have failure, when more than one process need to acces the DB, when you need RBAC etc ...

warmwaffles 4y ago
> what exactly do you do when you have failure Depends on the failure. What do you have in mind? > when more than one process need to acces the DB Easy, you use `journal_mode` set to `WAL` when you connect to the sqlite database. > when you need RBAC If you need RBAC, you aren't going to be using sqlite. In theory you could have your application handle the RBAC part since sqlite is embedded in your application. Another solution is to leverage unix users and groups and set permissions appropriately for the database file and the application running.
- ju-st 4y ago
  >> when more than one process need to acces the DB > Easy, you use `journal_mode` set to `WAL` when you connect to the sqlite database. and set isolation level and check_same_thread and PRAGMA synchronous and then put all queries in retry loops because the DB is maybe locked https://sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode https://sqlite.org/wal.html#sometimes_queries_return_sqlite_... I wasted so much time trying to get multiple sqlite queries running at the same time
  - warmwaffles 4y ago
    > I wasted so much time trying to get multiple sqlite queries running at the same time You were trying to write at the same time right?
    - ju-st 4y ago
      no, although the DB was not opened readonly mode. I tested with apachebench with many parallel requests on endpoints using only SELECTs
4y ago

stormdennis 4y ago

I don't know about now but at one time Firebird made this question moot. It never received the love it deserved in my view.

kstrauser 4y ago
Many years ago, I was hired at a web shop running Firebird in protection. It had the interesting property that if you ran the client software on your desktop to connect to the server software on another host, and used ^Z to stop the client, then the server process would hang until you resumed the client. I'm not entirely sure how I'd implement that as a feature if I wanted to.
- stormdennis 4y ago
  Not good!

umvi 4y ago

Can SQLite be used outside of single threaded contexts? Last I checked having multiple concurrent readers and writers is very difficult. Multiple concurrent readers only is probably ok if that's all you need.

neverartful 4y ago
It can, but as the concurrency increases you'll start to see errors indicating that database is locked.
- euroderf 4y ago
  This sounds like: so there is some point where a feedback loop tends to lock it all up, but it's fixable with faster hardware.
simonw 4y ago
In practice, most SQLite writes take less than a few ms, so concurrent writes are only likely to be a problem for you if you need to handle thousands of writes per second. I've implemented the pattern where writes sit in an in-memory queue and are applied to a dedicated write connection in series. I've not run into any problems with this so far, but I've also not tried to handle thousands of writes per second with it yet.
shrimpx 4y ago
I’ve done this with retry logic in each concurrent writer (retry if you can’t lock the file with a short timeout) and it works fine enough.
- umvi 4y ago
  If threads have to contend for db with retry loops, does concurrency actually buy you anything?
  - shrimpx 4y ago
    It definitely doesn't buy you performance. But I thought you were asking if it's possible, or how difficult it is. A use case might be if you have an existing architecture of N processes and want to collect stuff from them in a central file.

mcdonje 4y ago

>If you are going to take one thing away from this article, I hope it is that the only benchmarks that are valuable are those that run on your own platform, with your own stack, with your own data, and with your own software. And even then, you may need to add custom optimizations to get the best performance. Good advice, contextually.

bob1029 4y ago

I think it's even more complicated. The reduction in latency brought on by in-process databases, combined with modern NVMe storage, means that SQLite is a substantially faster approach than any other solution which requires a trip through the network stack. I've got services in production for several financial institutions right now that are resolving most SQL queries within 100-1000 micro seconds by simply using SQLite on reasonable hardware. How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude? Obvious caveats here being the resilience angle. We addressed this with application-level protocols and additional instances.

srcreigh 4y ago
Can you share more info about resilience, app protocols and additional instances?
infogulch 4y ago
Another angle is unbounded horizontal scalability across tenants. If your application can be divided cleanly between different customers and one customer can be served with one instance (see "How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude?") then scaling to more customers is extremely simple and linear.
abrookewood 4y ago
Agreed, but a fairer comparison would be SQLite vs PostgreSQL installed on the same machine.
- spopejoy 4y ago
  Even there you'll see the same: micros in SQLite vs millis for PostgreSQL. PostgreSQL is simply not the right choice if you want in-process speeds.
WuxiFingerHold 4y ago
> How many more users could you support if the amount of time you had to await IO for each was reduced by ~2 orders of magnitude? None - or practically not many, when using async (or sort of green threading) backend stacks. The waiting connections / users are just waiting and don't block any new connections. If the network round trip to Postgres (in my experience around 3-10 ms, but of course highly depending on your server infra) was a concern Postgres could be placed on the same server as the backend, though I would not recommend it. But this relatively small IO overhead usually is not a concern for many apps.

mritchie712 4y ago

Do clickhouse next!

yread 4y ago

I understand and partially agree with all the criticism of the article (and could add my own like why did you tweak work_mem out of all the hundreds of settings on postgres) but it's a blog post, not a scientific article - I still think it's a useful post. I might be in a similar situation at some point and it's nice to know that you haven't encountered any major gotchas and made it even 2x faster. Thanks for sharing your experience.

andy_ppp 4y ago

Why is this limited to four clients? Maybe I missed something but it would be good to see what happens with higher numbers of connections (something both of these dbs are rumoured to be bad at).

KronisLV 4y ago

You know, apart from some of the details in regards to what data types to better use etc., I think that both PostgreSQL and SQLite are excellent choices for most use cases! Of course, SQLite might be the best choice for embedded or local usage, they even have this lovely page about using it as an application file format: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html And PostgreSQL might scale better for multiple writers and situations where you need it on a separate VM/server/container that might be shared between different apps through a network connection of some sort. Though it's also curious, because you never hear a lot about MySQL or MariaDB (comparatively, in my subjective opinion) on HN or many other places for that matter. And yet, it's still a decent option, has good tooling (MySQL Workbench is great), is widely supported and backs a significant amount of production systems out there. Do people just view it as old/legacy, or is there the belief that for many use cases PostgreSQL might simply be the better option, e.g. also because of projects like PostGIS and support for more complex setups, like custom data types, more advanced scripting language for in-database processing and whatnot?

danenania 4y ago
I’m very happy with MySQL currently. I started using it because AWS Aurora supports it a bit better than postgres. I haven’t had any regrets since. It’s great when you just want simple relational storage without a lot of features or ceremony, but need a bit more than SQLite. It’s fast, predictable, and gets the job done. Postgres is great too and offers more, but sometimes that can tempt you to try to do too much in the DB layer.
tomtheelder 4y ago
I think it's more the latter: using Postgres for relatively simple workloads really isn't any more challenging than using something like MySQL, but then Postgres also supports more complex setups when you need it. So there would need to be a reason to lock yourself out of what PG offers, and I just don't think such a reason exists.

polyrand 4y ago

Great blog post. A few observations (considering the blog post skips numerous details): SQLite really shines when compared to running a database in a separate server from the app. This is when the network latency starts being noticeable. Latency between you and the app is usually constant. The latency between the app and the database is what matters. If in this case, Postgres can run in the same host as the main app, I think it's a good thing to do. But very often that's not the case. Even though not surprising, it's great to see how SQLite handles concurrency. From my experience, SQLite would be able to handle much more read concurrency. It would have been great to see more benchmarks (like 1000 concurrent users, even if the app would never see such traffic). I was surprised that SQLite didn't beat Postgres on simpler queries. Without seeing the queries, if they contain multiple joins, that could be another reason why SQLite is slower since it can only do "nested loop" JOINs. The workloads also seem mostly analytical, we'll see where DuckDB goes in this space (looking promising for now). One thing not mentioned in the blog post is "easy to maintain and update". If the app is deployed with Docker, adding Postgres means you go from worrying about: 1 docker container + 1 docker volume to: 2 docker containers (docker-compose?) + 1 docker volume + internal docker networking The fact that SQLite is so simple to operate also has some advantages. You can usually go back to an SQLite-based app 1 year later and understand everything. With other databases, you may need to review users, roles, permissions, connection string, etc. Another cool thing about SQLite is that it scales mostly linearly as you add more CPU cores. It can scale as long as you can also scale the number of processes in your app. In other databases, scaling means adding connection pools and other tools in the middle. Lastly, and related to the latency topic. The low latency in SQLite means that you can sometimes convert a big query into 5 separate, small and reusable queries without any performance hit. It's really remarkable home much cleaner your code can become with this. In any case, I love working both with Postgres and SQLite, and we need more blog posts like this one showing real-world cases.

ptrik 4y ago
Ditto on DuckDB point. This looks like OLAP workloads to me and a columnar database would work wonders. DuckDB if you going embedded, Clickhouse if you going with a server.

danaugrs 4y ago

How about PostgreSQLite? Joke aside, is anyone building something that takes a stab at fixing the issues with both?

mattxxx 4y ago

Some context: I've worked with massive PostgreSQL db's, containing millions of daily records spanning multiple years (allowing for potentially massive numbers of returned rows). I also exclusively MVP simple db architectures in SQLite (think: no massive joins or need to partition data). Best takeaways here: - SQLite kills it. It appears untuned PostgreSQL is only 2 seconds (???) faster than untuned SQLite; always try to kickoff a project with SQLite - PostgreSQL has a performance cliff when query responses leave memory. This is extra true when an index no longer fits in memory. worker_mem is critical for pushing back this performance cliff Side note: Tuning postgres can be really painful, but it's also a really dope tried-and-true db.

KingOfCoders 4y ago

From my experience SQLLite shines (using it in Go) when in-process, local, SQLlite-API and from an NVMe. It's probably not the right choice over networks and with ORMs.

Labo333 4y ago

The major drawback I encountered when building upon SQLite is concurrency of writes. I have a lot of worker processes writing new data. With SQLite, I was getting a lot of "Database locked" errors and ended up having to use pg.

cocoflunchy 4y ago

According to https://www.linode.com/pricing/ https://www.linode.com/pricing/ a shared instance with 2GB of RAM and 1CPU is $10/month. I don't know how much a principal engineer at twilio makes but I'm pretty sure this wasn't a cost effective optimization... It's also a bit weird not to talk about indexing? Are sqlite and Postgresql similar enough that the same indexing strategy should be used?

adityapatadia 4y ago

Am I the only one who is thinking why they have developed in-house system for such tracking in first place? This problem (of analysis of traffic) could easily be achieved with Segment (a twillio product) and analytics tool like Mixpanel or Amplitude.

vivzkestrel 4y ago

You are comparing the wrong databases together! Compare MySQL and PostgreSQL instead

rglullis 4y ago

Here is a comparison that I'd like to see next: how much time was spent on the following... - porting the application, making all these benchmarks and writing a blog post vs - simply getting a VM with more RAM and more CPUs. In the conclusion the author goes on to talk about the options "if I get the budget" vs "without throwing more money at the problem". I'd guess that the cost of the day of a Principal Engineer would far surpass the cost of years running a 4CPU server.

nojvek 4y ago

It seems the queries are aggregate based and they’d be served better by DuckDB instead of SQLite. DuckDB performance is astounding for doing group by, sum/count/avg with filters and sorts.

johnthescott 4y ago

is the app used by a single user or shared across many users? what question could be simpler?