Qualification criteria is:
- Needs to support minimum 2 databases, e.g. sqlite + something-else
- Runs on Python3.7
- Actively developed
- Has ability to generate initial DDL off specified models
- Handle one-to-many relationships
These benchmarks are not meant to be used as a direct comparison. They suffer from co-operative back-off, and is a lot simpler than common real-world scenarios.
- Insert: Single (single entry at a time)
- Insert: Batch (many batched in a transaction)
- Insert: Bulk (using bulk insert operations)
- Filter: Large (a large result set)
- Filter: Small (a limit 20 with random offset)
- Get
- Filter: dict
- Filter: tuple
- Update: Whole (update the whole object)
- Update: Partial (update only a single field of the whole object)
- Delete
model Journal:
id: autonumber primary key
timestamp: datetime → now()
level: small int(enum) → 10/20/30/40/50 (indexed)
text: varchar(255) → A selection of text (indexed)model Journal:
id: autonumber primary key
timestamp: datetime → now()
level: small int(enum) → 10/20/30/40/50 (indexed)
text: varchar(255) → A selection of text (indexed)
parent: FK to parent BigTree
child: reverse-FB to parent BigTree
knows: M2M to BigTreemodel BigTree:
id: uuid primary key
created_at: datetime → initial-now()
updated_at: datetime → always-now()
level: small int(enum) → 10/20/30/40/50 (indexed)
text: varchar(255) → A selection of text (indexed)
# Repeated 2 times with defaults, another 2 times as optional:
col_float: double
col_smallint: small integer
col_int: integer
col_bigint: big integer
col_char: char(255)
col_text: text
col_decimal: decimal(12,8)
col_json: json- Django:
https://www.djangoproject.com/
Pros:
- Provides all the essential features
- Simple, clean, API
- Great test framework
- Excellent documentation
- Migrations done right™
Cons:
- Brings whole Django along with it
- peewee:
- Pony ORM:
https://github.com/ponyorm/pony
Pros:
- Fast
- Does cacheing automatically
Cons:
- Does not support bulk insert.
- SQLAlchemy ORM:
-
Pros:
- The "de facto" ORM in the python world
- Supports just about every feature and edge case
- Documentation re DB quirks is excellent
Cons:
- Complicated, layers upon layers of leaky abstractions
- You have to manage transactions manually
- You have to write a script to get DDL SQL
- Documentation expects you to be intimate with SQLAlchemy
- Migrations are add ons
- SQLObject:
https://github.com/sqlobject/sqlobject
- Does not support 16-bit integer for
level, used 32-bit instead. - Does not support bulk insert.
- Does not support 16-bit integer for
- Tortoise ORM:
https://github.com/tortoise/tortoise-orm
- Currently the only
asyncORM as part of this suite. - Disclaimer: I'm an active contributor to this project
- Currently the only
Results for SQLite, using the SHM in-memory filesystem on Linux, to try and make the tests more CPU limited, but still do FS round-trips. Also more consistent than an SSD.
Py39:
| Test 1 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Update: Whole |
|
|
|
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
|
|
|
| Delete |
|
|
|
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
|
|
|
| Test 2 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
|
|
|
| Delete |
|
|
|
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
|
|
|
| Test 3 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
|
|
|
| Delete |
|
|
|
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
|
|
|
PyPy7.3-Py3.6: (Outdated)
| Test 1 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
| Test 2 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
| Test 3 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
PostgreSQL 14.2 on my iMac.
| Test 1 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
Tortoise ORM |
| Update: Partial |
|
|
|
|
|
|
|
Tortoise ORM |
| Delete |
|
|
|
|
|
|
|
Tortoise ORM |
| Geometric Mean |
|
|
|
|
|
|
|
Tortoise ORM |
| Test 2 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
Tortoise ORM |
| Update: Partial |
|
|
|
|
|
|
|
Tortoise ORM |
| Delete |
|
|
|
|
|
|
|
Tortoise ORM |
| Geometric Mean |
|
|
|
|
|
|
|
Tortoise ORM |
| Test 3 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
Tortoise ORM |
| Update: Partial |
|
|
|
|
|
|
|
Tortoise ORM |
| Delete |
|
|
|
|
|
|
|
Tortoise ORM |
| Geometric Mean |
|
|
|
|
|
|
|
Tortoise ORM |
MySQL 8.0.28 on my iMac.
| Test 1 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
Tortoise ORM |
| Update: Partial |
|
|
|
|
|
|
|
Tortoise ORM |
| Delete |
|
|
|
|
|
|
|
Tortoise ORM |
| Geometric Mean |
|
|
|
|
|
|
|
Tortoise ORM |
| Test 2 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
Tortoise ORM |
| Update: Partial |
|
|
|
|
|
|
|
Tortoise ORM |
| Delete |
|
|
|
|
|
|
|
Tortoise ORM |
| Geometric Mean |
|
|
|
|
|
|
|
Tortoise ORM |
| Test 3 | Django | peewee | Pony ORM | SQLAlchemy ORM | SQLObject | Tortoise ORM | Max | Best ORM |
|---|---|---|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Batch |
|
|
|
|
|
|
|
Tortoise ORM |
| Insert: Bulk |
|
|
|
|
|
|
|
Tortoise ORM |
| Filter: Large |
|
|
|
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Update: Partial |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Delete |
|
|
|
|
|
|
|
SQLAlchemy ORM |
| Geometric Mean |
|
|
|
|
|
|
|
SQLAlchemy ORM |
PyPy7.3-Py3.6: (Outdated)
| Test 1 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
| Test 2 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
| Test 3 | Django | peewee | Pony ORM | SQLAlchemy ORM | Tortoise ORM |
|---|---|---|---|---|---|
| Insert: Single |
|
|
|
|
|
| Insert: Batch |
|
|
|
|
|
| Insert: Bulk |
|
|
|
|
|
| Filter: Large |
|
|
|
|
|
| Filter: Small |
|
|
|
|
|
| Get |
|
|
|
|
|
| Filter: dict |
|
|
|
|
|
| Filter: tuple |
|
|
|
|
|
| Update: Whole |
|
|
|
|
|
| Update: Partial |
|
|
|
|
|
| Delete |
|
|
|
|
|
| Geometric Mean |
|
|
|
|
|
- Pony ORM is heavily optimised for performance.
- Django & SQLAlchemy is surprisingly similar in performance.
- Tortoise ORM is competitive.
Getis surprisingly slow for everyone.- Pony ORM, SQLAlchemy & SQLObject does merge operations for updates, so is technically always partial updates.
- Tortoise ORM performance using the
asyncpgPostgreSQL driver is really good, winning overall. - Tortoise ORM performance using the
aiomysqlMySQL driver is mediocre, the driver itself is taking the majority of CPU time. PyPy runs this driver a lot faster, which indicates that the slow paths are likely just in Python itself.
peeweeandPony ORMgets a noticeable performance improvementSQLAlchemy ORMandDjangoperforms similarilyTortoise ORMhas slow Reads and fast Create, Update & Delete operationSQLObjectfails
peeweeandTortoise ORMgets a noticeable performance improvementPony ORMis marginally fasterSQLAlchemy ORMandDjangois marginally slowerSQLObjectfails
Note that these benchmarks have since changed, so state is not exactly the same as above. This should only be used as a "guideline" of the improvement in performance since we started with the performance optimization process.
| Tortoise ORM: | v0.10.6 | v0.10.7 | v0.10.8 | v0.10.9 | v0.10.11 | v0.11.3 | v0.12.1 |
|---|---|---|---|---|---|---|---|
| Seedup (Insert & Big | & Small) |
|
|
|
|
118.2, 2.7, 14.6 | 136.9, 2.4, 13.5 |
| ===================== | ============== | ================ | ================ | ================ | ================ | ================ | ================ |
| Insert |
|
|
|
|
|
|
|
| Insert: atomic |
|
|
|
|
|
|
|
| Insert: bulk |
|
|
|
|
|
|
|
| Filter: match |
|
|
|
|
|
|
|
| Filter: contains |
|
|
|
|
|
|
|
| Filter: limit 20 |
|
|
|
|
|
|
|
| Get |
|
|
|
|
|
|
|
base.executor._field_to_db()could be replaced with a pre-computed dict lookupQueryset.resolve_filtersis doing lots of unnecessary stuff, especially for .get() method- Get operation is slow (likely slow SQL generation, could be resolved with parametrized query cacheing)
Since pypika is immutable, and our Queryset object is as well, we need tests to guarantee our immutability. Then we can aggresively cache querysets.
Also, we can make more queries use parameterised queries, cache SQL generation, and cache prepared queries.
It seems in cases where we can cache the PyPika result (and use prepared statements), PyPy performance increase is even larger than CPython.
aiosqlitepolling misalignment (sqlite specific)(20-40% speedup for retrieval, 10× — 15× speedup for insertion): omnilib/aiosqlite#12
pypikaimproved copy implementation (generic)(53% speedup for insertion): kayak/pypika#160
tortoise.models.__init__restructure (generic)(25-30% speedup for retrieval) tortoise/tortoise-orm#51
tortoise.models.__init__restructure (generic)(9-11% speedup for retrieval) tortoise/tortoise-orm#52
aiosqlitemacros (sqlite specific)(1-5% speedup for retrieval, 10-40% speedup for insertion) omnilib/aiosqlite#13
Simple prepared insert statements (generic)
(35-250% speedup for insertion) omnilib/aiosqlite#13 tortoise/tortoise-orm#54
pre-generate initial pypika query object per model (generic)
(25-50% speedup for small fetch operations) tortoise/tortoise-orm#54
pre-generate filter map, and standard select for all values per model (generic)
(15-30% speedup for small fetch operations) tortoise/tortoise-orm#64
More optimal queryset cloning (generic)
(6-15% speedup for small fetch operations) tortoise/tortoise-orm#64
pypikaimproved copy implementation (generic)(10-15% speedup for small fetch operations) kayak/pypika#205
Optimised inserts/updates & Bulk create (generic)
(5-40% speedup for small insert operations)
(350-600% speedup for bulk insert over small insert operations) tortoise/tortoise-orm#142De-lazied some metadata objects & More efficient queryset manipulation (generic)
(15-25% speedup for large fetch operations)
(5-30% speedup for small fetches) tortoise/tortoise-orm#158Parametrized delete/update (generic)
(260-280% speedup for delete operations)
(300-600% speedup for update operations) tortoise/tortoise-orm#177Lazy Relation properties (generic)
(15~140% speedup for all on Test 2 (Small & Relational)) tortoise/tortoise-orm#187
Know about default converters & native DB types (generic)
(20-25% speedup for Fetch operations) tortoise/tortoise-orm#190
Connection Pooling (MySQL & PostgreSQL)
(30-50% speedup overall) tortoise/tortoise-orm#229
Many small tweaks (generic)
(5-30% depending on driver) tortoise/tortoise-orm#241
