#35793: Add support for atomic upserts
-------------------------------------+-------------------------------------
Reporter: Storm Heg | Type: New
| feature
Status: new | Component: Database
| layer (models, ORM)
Version: | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Hello,
I took over maintenance of a package called wagtail-ab-testing which
performs a raw SQL query to insert or update (UPSERT) a model atomically
([https://github.com/wagtail-nest/wagtail-ab-
testing/blob/1ba57e4707061abf26c3c5829a8a79138fcaa2a5/wagtail_ab_testing/models.py#L450-L460
link to relevant source code])
This raw sql query confused me, but as it turns out it is there because
this the Django ORM does not appear support atomic upserts without taking
a lock on the row, which hurts performance
Specifically, the (performant) raw SQL for PostgreSQL looks like this:
{{{#!sql
INSERT INTO %s (ab_test_id, version, date, hour, participants,
conversions)
VALUES (%ss, %ss, %%s, %%s, %%s, %%s)
ON CONFLICT (ab_test_id, version, date, hour)
DO UPDATE SET participants = %s.participants + %%s, conversions =
%s.conversions + %%s;
}}}
There are a few things to note here:
* There is an unique constraint on combination of `ab_test_id`, `version`,
`date`, and `hour` columns that prevents duplicate objects from being
created.
* The `participants` and `conversions` columns are updated atomically;
That is: these columns are ''incremented'' by the given values, not set
directly. This is important for atomicity. We don't want multiple
concurrent database calls to overwrite each other.
The more common way to do this would be to use the
[https://docs.djangoproject.com/en/5.1/ref/models/querysets/#update-or-
create `update_or_create`] method, but this
[https://github.com/django/django/blob/5865ff5adcf64da03d306dc32b36e87ae6927c85/django/db/models/query.py#L965-L969
internally takes a lock on the row in case of updates], which is not
acceptable because this is a hot code path with a lot of concurrent
requests. We can’t afford to take a lock on the row.
I think it would be great if Django ORM supported atomic upserts without
taking a lock on the row. I’m not sure what the api for that would look
like, suggestions are welcome.
As for database support, PostgreSQL and SQLite support this syntax since
PostgreSQL 9.5 and SQLite 3.24.0 respectively. MySQL and MariaDB do not
support this natively, but it can be emulated using `INSERT ... ON
DUPLICATE KEY UPDATE` syntax and creating a unique index on the columns.
Oracle apparently supports something similar using the vastly different
`MERGE` statement syntax. That might be a bit of a challenge.
Looking for feedback on this idea. Is this something that would be worth
supporting in Django? I’ll admit it’s a bit of a niche feature, but it’s a
very useful one in certain cases. I would love to get rid of the raw SQL
query in `wagtail-ab-testing` and have something that works across all
supported databases.
- Storm
--
Ticket URL: <https://code.djangoproject.com/ticket/35793>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/010701922f2077e1-a7a98c82-065e-4192-80cc-81f97979a5ba-000000%40eu-central-1.amazonses.com.
