How to mass upsert on Tarantool?

Question

In MySQL I can do something like this:

-- post_id-user_id is unique pair
INSERT INTO userviews(post_id, user_id, view_count) 
VALUES(1,1,1),(2,1,1),(3,1,1),(4,1,1)
ON DUPLICATE KEY UPDATE 
view_count = view_count + 1;

Or in PostgreSQL:

INSERT INTO userviews(post_id, user_id, view_count)
VALUES(1,1,1),(2,1,1),(3,1,1),(4,1,1)
ON CONFLICT DO UPDATE SET view_count = view_count + 1;

How to do this kind of thing in Tarantool? (Currently I'm using Tarantool's library to upsert in a loop):

import "github.com/tarantool/go-tarantool"
type AX []interface{}
...
for ... {
   conn.Upsert(`userviews`, tuple, AX{AX{`+`,viewCountColumn, 1}})
}

Answer 1

I guess the problem you aim to solve is about performance. The naive variant (perform an operation, wait for a response, continue with the next operation) would give about (1 / network RTT) RPS at max: it is about 1000 RPS for 1ms RTT.

There are the following options:

1. Use async operations (go-tarantool supports them) and wait for a batch rather than for each operation. (You can even sending one batch while waiting for the previous one.)
2. Define a Lua function, which accepts a batch of requests and returns a batch of responses (if you need each response, of course).
3. (Don't know for sure, but maybe you can also use Tarantool/SQL, which is available in tarantool since version 2.)

You can also use 'eval' operation without having a function on the server side, but it requires 'execute' permission on 'universe' and so usually discouraged (except for testing and experimenting purposes).

Answer 2

Be aware that upsert() works differently than MySQL's ON DUPLICATE KEY UPDATE. Unlike MySQL, it checks for uniqueness only the primary key and ignores all other secondary unique indices. Not sure if this is your case, but if you need to replicate the exact behavior of MySQL and update the entry regardless of whether there is a duplicate on the primary key or any secondary unique index, the only decent way I found is to write a Lua function that first tries to insert the tuple and if the operation failed with error code 3 (Duplicate key exists in the unique index), it does the update.

(Don't know for sure, but maybe you can also use Tarantool/SQL, which is available in tarantool since version 2.)

Unfortunately, it does not yet support UPSERT: https://github.com/tarantool/tarantool/issues/5732