The purpose of this benchmark is to measure a real web server backend handling a CRUD workload in the context of a hypermedia application (Datastar) and answer the question: Which backend can achieve the highest throughput, measured in requests per second (RPS) under different workloads?
uvicorn is used as web server, with two different web frameworks + database combinations:
- Litestar + SQLite (APSW)
- FastAPI + Postgres (psycopg3)
Litestar is a lesser known, but very capable ASGI framework which started out as a modified version of Starlette, the internal ASGI framework used by FastAPI. FastAPI is really a framework built on top of Starlette, bundling various other packages such as Pydantic for data validation.
Although FastAPI contains the word "Fast", its opinionated bundling of packages means it is less flexible, for example if you do not want to use Pydantic. In that case, you might be better off using Starlette directly.
Litestar is less opinionated, and therefore allows you to pick and choose which packages you want to bundle in your application.
MsgSpec provides many of the same validation capabilities as Pydantic while serializing an order of magnitude faster. Incidentally, Pydantic models also consume a lot of memory. By using MsgSpec, memory usage may go down by as much as 10x compared to FastAPI.
For hypermedia applications, the server spends a significant portion of its time querying the database and rendering HTML templates. The database is thus a significant factor in the performance of the application.
Postgres and SQLite have a fundamentally different architecture. The former is a standalone process which one talks to using a protocol, whereas the latter is a library embedded directly inside the user's application. SQLite with its lightweight nature should perform better compared to Postgres which carries additional overhead (processes, protocol, serialization).
This benchmark is not entirely scientific: we are also using different web frameworks (Litestar vs FastAPI). This is because Postgres + FastAPI are both very popular, and we consider this combination to be the "mainstream baseline".
All things considered, Litestar + SQLite is expected to outperform FastAPI + Postgres by a significant margin (2x or more).
Two identical applications are built on both backends, using the same schema and seeded dataset. We use the wrk tool to subject both web servers to a sustained load of requests, to find out what the limit is.
The measurement covers a duration of 60 seconds, using 4 threads to issue HTTP requests, and allowing up to 1000 concurrent connections:
wrk -t4 -c1k -d60s -s common.lua --latency http://127.0.0.1:8000
The dataset is seeded with the following parameters:
4+ GB dataset
SEED_CHANNEL_COUNT=4096
SEED_USER_COUNT=8192
SEED_MSG_COUNT=36000000
40+ GB dataset
SEED_CHANNEL_COUNT=262144
SEED_USER_COUNT=524288
SEED_MSG_COUNT=360000000
The type of workload, dataset size and database settings can all significantly affect performance. Therefore, we measure a variety of workloads and conditions.
The schema for a simple chat application is used to represent a typical CRUD workload:
- GET /: To serve a read request, the server must issue 3 separate DB queries touching all 4 tables. The queries perform several joins and require at least 500 completely (pseudo-)random pages from the database. This data is then used to render a ~190 kB HTML template (using Jinja), which must then be Brotli-compressed and returned to the client.
- POST /chat/message-send (80% probability): Inserts a message into the
messagestable with a content size between 0 and 127 bytes. - POST /chat/channel-open (10% probability): Inserts an entry in the
channelsandchannel_membershipstable, if it does not exist already. - POST /chat/nickname-set (10% probability): Updates a nickname in the
userstable.
The size of the dataset has a very big impact on performance. If the dataset does not fit in main memory, then the system must fetch data from disk which carries much higher latencies.
- Small: 4 GB (dataset fits in RAM)
- Large: 40 GB (dataset exceeds RAM)
Note: This benchmark was run on a machine with 16GB of RAM.
In each scenario, the wrk client will issue a different ratio of GET and POST requests to force different read/write mixtures on the database.
- 100/0 read/write: Read-only
- 95/5 read/write: Read-mostly
- 50/50 read/write: Mixed read/write
"skew" as a property describes the distribution of client requests over logical database resources i.e. rows. A higher skew means queries are targeting fewer, hotter rows. Lower skew means access patterns approach a uniform distribution.
To configure skew, an "alpha" parameter is fed into an inverse CDF function, which approximates a Zipfian distribution, a pattern that occurs very frequently in real-world datasets. Without skew, a benchmark can hardly be considered realistic.
- Low skew: alpha = 0.1
- High skew: alpha = 0.9
Database isolation levels provide different ACID guarantees to the application. For example, with the default isolation level of read committed in Postgres, it is possible to read the same value twice within a transaction, and read a different value each time (non-repeatable reads). Also it is possible to observe rows in a table which did not exist before, even though no new transaction was started (phantom rows). The isolation level can affect correctness and introduce subtle concurrency issues, but many databases carry performance penalties for higher isolation levels.
For SQLite, isolation level is always strong by default because it uses a single writer. There is a read_committed pragma setting, however this relies on the now-deprecated "shared chache" mode which SQLite recommends to disable in compile-time options.
- Weak Isolation: Postgres
default_transaction_isolation='read committed', SQLite always strong isolation (single writer) - Strong Isolation: Postgres
default_transaction_isolation='serializable', SQLite always strong isolation (single writer)
- 2 warmup runs before the real benchmark
- boost clock disabled
- fixed CPU clock frequency
- CPU governor performance
- 12 uvicorn workers (1 worker per CPU thread)
Block device readahead is enabled by default on most systems and can affect performance significantly when the dataset exceeds main memory. Each database uses a readahead setting which was found to work best from testing:
- For SQLite: block device readahead disabled
- For Postgres: block device readahead 256 sectors (default)
Results are displayed earlier on this page.
Overall, the hypothesis is correct: Litestar + SQLite does perform better by a significant margin.
For weak isolation levels at the 4 GB dataset, the lead difference is a comfortable 2x. Then for strong isolation level, SQLite's lead improves further to ~3-5x. Since isolation level for SQLite is always strong, its results are identical while Postgres must do a bunch of extra locking to provide the same ACID guarantees.
Another explanation for SQLite's lead in the small dataset, besides all the points mentioned earlier, is that SQLite is used with the mmap_size setting set to 4 GB. This means that, the entire database file can be memory-mapped and accessed via a direct pointer dereference.
For the large large dataset, results are more interesting. Because now, the data does not fit in memory, so access might require disk IO. This means higher latency and also limited IOPS. Overall this results in a substantial reduction in throughput for both databases.
As expected, the higher skew (= 0.9) concentrates access into fewer rows, which results in the data being more likely available in memory. Therefore, both databases benefit from it. The lower skew (= 0.1) represents a more uniform / random access pattern and therefore approaches the worst case; requiring reads from disk.
It should be said that, these result are hiding something. Namely, Postgres can actually handle ~80 queries per second on the 40 GB dataset with 0.1 skew (random access). However, this requires lower the number of concurrent HTTP requests in wrk, which in this benchmark allows up to 1000 concurrent HTTP requests "in flight". By default, any request that takes longer than 2 seconds to complete is registered as an immediate failure. My guess for what is happening is that, Postgres simply cannot keep up with the demanded load, and the queries are up piling up1 while waiting on disk. The many concurrent requests then start timing out and printing as failures. With a higher isolation level, the locking makes it even worse and throughput collapses to effectively zero.
Another important thing is that SQLite requires some special care to work well on large datasets. Because SQLite is just a C library, it can be embedded and used from just about anywhere. By default, SQLite will execute queries synchronously inside the calling thread. This does not matter much if the data was already pulled into memory, because then access is cheap and fast. However if the data must be fetched from disk, then the calling thread will end up blocked on disk IO, halting execution until IO completes. This is especially bad in say, the event loop of a web server. Therefore, SQLite in this benchmark uses up to 48 background threads to execute the queries "asynchronously".
Further notes:
- Disabling auto-escaping in jinja can increase RPS by ~20%, however this is unacceptable from an XSS security standpoint.
- Channel and message count parameters must be chosen carefully or the number of messages actually rendered will not hit the
LIMIT 500, causing benchmarks to measure a different amount of work.
Footnotes
-
Increasing
max_connections, connection pool size and several hours of tuning did not improve this. ↩