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.
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.