4 ms·
SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your pro
by oppositelock 4y ago
SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration.
SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need multiple backends to handle more load, or the moment you need high availability, you can't do it with SQLite. SQLite has very limited DDL operations, so you also can't evolve your schema over time without downtime. Now, for streaming backups - how do you come back from node failure? You're going to incur downtime downloading your DB.
I run many SQL backed production services, and my sweet spot has become a big honking Postgres instance in RDS in AWS, with WAL streaming based replicas for instant failover in case of outage, and read replicas, also via WAL. This system has been up for four years at this point, with no downtime.
I love SQLite, and use it regularly, just not for production web services.
- SnorkelTan 4y agoSQLite has write ahead logging as well https://sqlite.org/wal.html https://sqlite.org/wal.html
- oppositelock 4y agoIt doesn't let you run an active replica from WAL.
- js2 4y ago> my sweet spot has become a big honking Postgres instance in RDS Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.) [1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price. https://aws.amazon.com/blogs/database/is-amazon-rds-for-postgresql-or-amazon-aurora-postgresql-a-better-choice-for-me/ https://aws.amazon.com/blogs/database/is-amazon-rds-for-post...
- oppositelock 4y agoPrice is the main reason. Write performance is a second one.
- js2 4y agoThe article I linked implies that Aurora offers higher write performance: > If your database workload reaches the Amazon RDS max limit of 80,000 IOPS, and it requires additional IOPS, Aurora PostgreSQL is the preferred database solution. If database workload requires less than 80,000 IOPS, you can choose either Amazon RDS for PostgreSQL or Aurora PostgreSQL based on supported features.