Feb 24, 2021 08:00:00

Why is SQLite so good at handling 'large numbers of small queries'?

When issuing a large number of queries in a client-server database such as MySQL or PostgreSQL , communication between the client and server can become a major bottleneck. On the other hand, SQLite , a lightweight database, is good at processing 'a large number of small queries' due to its design. SQLite explains why SQLite can handle large numbers of queries efficiently.

Many Small Queries Are Efficient In SQLite
https://sqlite.org/np1queryprob.html

According to the

page that describes how to use SQLite, 200 queries are appropriate for one web page in SQLite. Developers often point out that '200 queries per page is ridiculous'.

SQLite responded to the developer's point that ''200 queries per page' is a problem for client-server databases such as MySQL and PostgreSQL, where applications and databases run separately.' .. In a client-server database, communication between the application and the database causes a problem called the ' N + 1 problem ', where 'executing a large number of queries takes a very long time'. In general, you can use JOIN clauses to join tables or Eager loading to preload data to avoid N + 1 problems.

Unlike the client-server database that has the N + 1 problem, SQLite has a structure in which the application reads and writes files directly, so even if a large number of queries are executed, a bottleneck due to communication does not occur. The version control system '

Fossil ' that uses SQLite for the database seems to have succeeded in simplifying the code base by making heavy use of N + 1 queries. In addition, overseas news site Hacker News has pointed out that 'the nature of SQLite is very compatible with GraphQL , which has an N + 1 problem.'

The disadvantage of SQLite is that it cannot be replicated, but there are also projects that realize SQLite replication using Amazon S3 .

The reason why 'Litestream' was born to simplify 'too complicated server configuration' to the origin --GIGAZINE