This comment is really interesting to me as I'm working on getting essentially this functionality added to Django[1].

The case statement is slightly different, more like this (usually with a couple of hundred ID's):

    UPDATE TABLE place 
    SET rating = (
       CASE WHEN id = 1 THEN 25
       CASE WHEN id = 2 THEN 94
       CASE WHEN id = 3 THEN 16
    ) 
    WHERE id IN (1, 2, 3)

Members of the Django community, myself included, have seem huge performance benefits using this technique. I had an update loop go from taking 90 seconds to 3.

As a seasoned DBA you see any huge downsides with this technique? I agree that if you're updating the whole table using a CASE statement is not a great idea but if you want to update 1000 rows with differing values and you have to issue 1000 individual queries then this can take some time (including whatever overhead Django has).

1. https://github.com/django/django/pull/9606

I'd probably use a temp table for that, rather than a case.

  begin;
  select id, new_rating into temp_ratings values (1,25),(2,94),(3,16);
  update table place set rating=new_rating
    from temp_ratings
    where place.id=temp_ratings.id;
  drop table temp_ratings;
  commit;

Scales well, and almost all db engines know how to do that join. You can index the id, or use a temp table, or whatever depending on the shape and quantity of your data.

(edit -- actually finished the post)

This is really bad advice:

  1) This is not really a temporary table in the traditional
     sense; you're creating a real table and then dropping it.
     This has problems with concurrency (you can't have two
     "real" tables with the same name), among other issues.
  2) Temporary tables in Postgres suck because they bloat
     the catalogs.  Creating temporary tables regularly is
     better avoided.
  3) There's no real need to create a table of any kind.  A
     regular VALUES would do.  (Though you might have to encode
     some type information into the query.)

> (usually with a couple of hundred ID's)

> I had an update loop go from taking 90 seconds to 3.

How is the performance with prepared statements? It seems like repeatedly executing the same statement with different values is what prepared statements are made for.

The ORM likely does use prepared statements, it's that most ORMs by default makes 100 round-trip IO operations (one for each update). The code would need to be rewritten without the ORM to send all the updates as one database call. The query isn't expensive to the database, but to the application itself just the IO of the database call adds up quickly.

Unfortunately Django does not use prepared statements. There are some big challenges to work around before support can be added. In any case I'd be very, very surprised if a significant portion of the 87 seconds using a single statement saved was saved also saved by using a prepared statement.

I don't think using a prepared statement 100 times will have anywhere close to the same performance improvement as sending one concatenated bulk update or one update set case.

No reason why prepared statements and single-call batched-statements can't both be used together. Of course, if one method (batching statements) works well enough, there's no need for another (prepared statements) too.

In every ORM I've ever used, everything is a prepared statement by default, so I've only been comparing 100 separate database call updates vs 100 updates concatenated as a single database call and 100 updates as a single update case when, where all three were already implied prepared statements.

Prepared statements will still be noticeably slower in most deployments. Almost all drivers are synchronous, and each individual query is a round trip to the database. This tends to dwarf parse and planning times unless you have a sub-millisecond ping time to the database.

It can be a lot faster, but it can also be slower. With databases, the answer for almost everything is "it depends." Here, it really depends on how big the table is, how many rows you're updating, what the indexes are, how much RAM is available, etc.

My point is that, in a vacuum, you can't really say anything about what the performance difference will be. Yes, you can certainly find instances where the performance is better, but it may not be much.

My point is that the complexity of writing the statement is non-trivial, and unless you have a good reason to you shouldn't use CASE. It shouldn't be your first choice, is all I'm saying. For example, the example you provided here is not even valid SQL. It should be:

    UPDATE TABLE place 
    SET rating = (
        CASE WHEN id = 1 THEN 25
            WHEN id = 2 THEN 94
            WHEN id = 3 THEN 16
        END
    ) 
    WHERE id IN (1, 2, 3)

Keep in mind, I also said: "If performance becomes a problem on this query, you can fix it later when you can focus on just that one issue and understand the specific problem much better."

There are lots of reasons writing a query one way might be faster than another. Running in parallel might be one reason. If you had say 20 separate cases, and you compared that to 20 separate queries one after another, they'd be waiting to run till the previous one ran, and so there's no overlap when run separately. I bet 20 parallel separate queries would have been much faster. Depending on your index, how much of the whole table did the query scan to create the update set - if it had to scan most of it for each query, that could explain why a single query was better.

I agree with others that usually it's best to write queries that are simple to understand and let the optimizer pick a good query. Under higher load and with higher data, the indexes matter more of course (and amount of ram).

I can't say the above case is more likely to be better or not, it depends on the database schema.

what's the diff when running 100 updates in a single transaction? that's typically where I see a lot of benefits with multiple updates.

So I was actually wondering if I was correct after seeing some comments here. This is what my quick benchmarking says:

I made a postgres table with two columns, id and num. I filled it with 1000000 rows in sequential order (just a generate_series()), so the first row was (1,1), the second was (2,2) etc.

I ran two queries to update the first 1000 'num' columns with random values. The first was as described above:

    UPDATE test SET num = (CASE WHEN id = 1 THEN 123 CASE WHEN id = 2 THEN 356 ...) WHERE id IN ...

This took about 1.5 seconds (on my completely un-optimized postgres instance)

The second uses 1,000 individual queries in a transaction (BEGIN, COMMIT):

    BEGIN
    UPDATE test set num = 123 WHERE id = 1
    ....
    COMMIT

This is still running, so it's taken over 3 minutes so far.

I'm guessing it's because even though the statements are in a single transaction the database still issues 1,000 individual table scans for a single record, and does 1,000 individual updates. Doing 1,000 updates using a single CASE statement seems to take the same time as updating a single one with the traditional way.

Edit: Query returned successfully in 21 min 39 secs.

Did you have an index on the table? If you didn't, you had to scan the whole table on each "separate query", whereas in the single query you scanned it once.

But like someone said above, it depends on the best way, it will be different. Also when dividing into 1000 separate queries, they have to wait for the previous one to run, and then they'll need to be optimized. A more interesting comparison might be (1) create index on the id column, (2) run the 1000 updates in parallel, maybe 30 at a time?

There was an index on the PK column and the value column as well.

Edit: Oh no, there was not. My bad :/ I added an index and the query time dropped to 5 seconds or so. A lot, lot faster, but 4x as slow as the CASE version.

Parallel queries would probably perform better, but I'm looking at a more general case. Django supports a lot of backends and not all of them support parallel queries, and indeed there is no way to make parallel queries like this in Django.

If we are talking about Postgres specifics, there is some better syntax for this that lets you join on a list of Values you can construct, which I intend to add support for later.

But if using 1 case statement is generally faster than issuing N individual synchronous queries then it's a big win for applications that have to do this kind of thing:

    for instance in queryset.all():
      instance.field = some_function()
      instance.save()

While this isn't a super common pattern it does crop up from time to time and it's annoying to work around (if you even can).

thanks for the details.

4-5 seconds sort of makes sense, depending on hardware. 3 minutes made no sense.

I think he was saying that concatenating 100 updates into one database API call is faster and/or better than using a case statement like that.

Both methods will be faster than sending 100 separate update. postgre API calls.

how about:

  UPDATE place
  SET rating = new.rating
  from (values (1,25),(2,94),(3,16)) new(id,rating)
  where place.id = new.id;

“Make your queries difficult for the server and easy for you”... that’s a great quote. We’re a bit more fragile!

Yup! The simple UPDATEs could improve the chances of the query planner working optimally--think of cases where a single, complex, setwise update might avoid using some really helpful partial indexes, etc.

And would also improve the chances that somebody looking at the sql in 5 or 10 years would quickly understand it as well.

Head's up. Depending on the circumstances, multiple updates are vulnerable to a race condition known as "read skew". If a user's status changes while the multiple update transaction is executing, their status can wind up in a bad state. Here's a demonstration:

    > CREATE TABLE reward_members (id bigint, member_status text);
    > INSERT INTO reward_members (values (0, 'silver'));

    > BEGIN;
    > UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold';
    > UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze';

    -- Concurrently: > UPDATE reward_members SET member_status = 'gold' WHERE id = 0 AND member_status = 'silver';

    > UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum';
    > UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver';
    > COMMIT;

    > SELECT * FROM reward_members;

     id | member_status
    ----+---------------
      0 | gold

If the transactions executed atomically (in the instantaneous sense, not the ACID sense), the member_status of the user should be either silver_group or gold_group depending on the order the transactions executed in. Due to the race condition, the user winds up with the member_status of gold instead.

If you want to avoid this you either have to use the case statement version or use a stricter isolation level. Either repeatable read or serializable would prevent this. I've written about read skew, other related race conditions, and how to prevent them in a series of blog posts you can find here[0][1][2].

On a related note, efficiently batching disjoint updates is an interesting problem. One of my coworkers wrote about how he solved this exact problem in Redshift[3]. He found the most efficient way was to create a staging table with what old values corresponded to what new values. Then you can join the staging table with the table you are updating.

[0] http://malisper.me/postgres-transactions-arent-fully-isolate...

[1] http://malisper.me/postgres-row-level-locks/

[2] http://malisper.me/postgres-transaction-isolation-levels/

[3] https://heapanalytics.com/blog/engineering/redshift-pitfalls...

Ditto, thanks for sharing the articles.

For a trivially small set of mapping data, I like using VALUES to create a "virtual" staging table:

    UPDATE reward_members
    SET member_status = status_map.new_status
    FROM (VALUES ('gold',     'gold_group'),
                 ('bronze',   'bronze_group'),
                 ('platinum', 'platinum_group'),
                 ('silver',   'silver_group')
         ) AS status_map (old_status, new_status)
    WHERE member_status = status_map.old_status;

I find this to be more readable than the CASE statement version.

I was looking through the thread for this solution. It's great because it's much harder to mess up and is less verbose than a case, and it's built on common SQL features making it portable and fast and easy to grok (as long as both the engine and the dba knows how joins work).

As a data analyst this technique is also great for scrubbing dirty data at query time when you don't have the ability to just fix it.

Yeah, I use it in queries as well. Good point!

Thank you very much for taking the time to explain that and including all the links.

I've had issues in the past with atomic transactions in Django still appearing to have race conditions and I think this explains why. Really appreciate it.

I suggest people use "serializable" by default and relax it carefully on a as-needed basis.

I suggest read committed, and increase it to serializable only if necessary. serializable transaction isolation means your code is responsible for catching the serialization exceptions that will occur and handling them (retrying the transaction, generally). Devs just don't do that, and don't even know how to trigger the exceptions in most case. So you end up with apps that work fine during CI and staging, but start failing under load. And it might not be possible to retrofit the correct exception handling without a massive re-architecture.

That's reasonable. I usually avoid the higher isolation levels because they can cause queries to spontaneously fail. You need to be able to put in the extra work for handling the cases where they fail.

If data correctness is an absolute must, you should go with serializable.

This isn't an issue with multiple statements in a transaction. It's just an issue with using the incorrect transaction isolation level. That will cause problems with single statement transactions if you're using READ COMMITTED transaction isolation level. One of your examples already shows that you don't need multiple statements:

    S1> BEGIN;
    S1> UPDATE ints SET n = n+1;
    
    S2> BEGIN;
    S2> DELETE FROM ints WHERE n = 2;
    -- S2 blocks since the DELETE is trying to modify a row 
    -- currently being updated.
    
    S1> COMMIT;
    -- S2 unblocks.
    
    S2> COMMIT;
    
    S3> SELECT * FROM ints;
    n
    ---
    2
    3

This is working as designed and intended. This is how the READ COMMITTED transaction isolation level works. If you want higher isolation levels like REPEATABLE READ or SERIALIZABLE, then use those. If you need snapshot isolation, then use that. READ COMMITTED is the default isolation level because it's the most usable isolation level for the vast majority of situations, but having an RDBMS that handles concurrency for you doesn't mean that you don't have to understand how that concurrency model works.

The tradeoff for these higher isolation levels is that instead of getting results that you might not necessarily expect, you'll instead get deadlocks and forced transaction rollbacks, and your application will need to account for that instead and may need to resubmit queries in certain situations. This is the fundamental tradeoff between performance and isolation.

In that case, you'd either mutex lock rows you're updating or generally choose a more stricter isolation level like RepeatableReads, or SSI?

Except mutex locking the rows is also vulnerable to read skew! For example, even if you lock the rows with SELECT ... FOR UPDATE:

    > CREATE TABLE reward_members (id bigint, member_status text);
    > INSERT INTO reward_members (values (0, 'silver'));

    > BEGIN;
    > SELECT * FROM reward_members WHERE member_status = 'gold' FOR UPDATE;
    > SELECT * FROM reward_members WHERE member_status = 'bronze' FOR UPDATE;
    > SELECT * FROM reward_members WHERE member_status = 'platinum' FOR UPDATE;

    -- In concurrent transaction: > UPDATE reward_members SET member_status = 'platinum' WHERE id = 0 AND member_status = 'silver';

    > SELECT * FROM reward_members WHERE member_status = 'silver' FOR UPDATE;

    > UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold';
    > UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze';

    -- In concurrent transaction: > UPDATE reward_members SET member_status = 'gold' WHERE id = 0 AND member_status = 'platinum';

    > UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum';
    > UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver';
    > COMMIT;

    > SELECT * FROM reward_members;

     id | member_status
    ----+---------------
      0 | gold

What you would need instead are "predicate locks". Unfortunately Postgres does not expose predicate locks to the user. Predicate locks lock all rows that match some predicate and prevent any row where the predicate is false from being modified so the predicate is now true. If Postgres did have predicate locks, you could grab a predicate lock on "membership_status = 'gold'", before running the gold UPDATE. That would prevent any row from concurrently having it's membership_status to gold while the transaction is executing.

Stricter isolation levels are an option. I would only use them if data correctness is an absolute must. I try to avoid them because they abort any queries that would otherwise have a race condition. You then have to work around queries spontaneously failing.