1

I've created a table in vinyl engine (disk), how can I convert it to memtx engine (ram)?

I have some small table that would be better if it converted to memtx for something like this:

WITH x AS ( -- big table that wont fit in memory 
  SELECT cat_id, click_count FROM bla WHERE user_id = ?
), mx AS (
  SELECT MAX(click_count) max_click FROM x
)
SELECT IFNULL(x.click_count,0)/IFNULL((SELECT max_click FROM mx),1) 
  , listings.*
FROM listings -- a small table less than 100k records which better in memory
  LEFT JOIN x 
    ON listings.cat_id = x.cat_id
ORDER BY 1 DESC
LIMIT 10

but currently listings table is a vinyl table, how to convert it in memtx? tried to find in documentation, there's no other method other than create in box.space related to engine.

If there's a way to rename old table, create a new space with proper engine, insert from old table, then drop the old table.

Kokizzu
  • 24,974
  • 37
  • 137
  • 233

1 Answers1

1

You can do dump/restore with tarantool/dump

Or you may walk the whole space and copy it row by row

Or create a replica with changed engine

Artem Rusinov
  • 176
  • 2
  • 9
  • 'Or create a replica with changed engine'. I suspect it is not obvious and some intruction is necessary. Are you about setting a trigger on _space? – Alexander Turenko Dec 22 '20 at 20:31
  • Looks like last option is partially described here https://www.tarantool.io/en/doc/latest/reference/reference_lua/box_ctl/on_schema_init/ – Denis Jun 07 '22 at 08:43