Sorry for the delay on this. Based on what you’ve shared, the update would take a long time, and probably not even successfully commit. If we take a look at the explain statement for the update, you’ll see that it spans every row, which I assume is what you’re trying to do since you’re looking to replace every score with a jsonb value:
explain UPDATE test SET metadata = metadata::jsonb || jsonb_build_object('score', jsonb_build_array(score, 0, 0, 0, 0, 0))::jsonb;
tree | field | description
count | |
└── update | |
│ | table | test
│ | set | metadata
│ | strategy | updater
└── render | |
└── scan | |
| table | test@primary
| spans | ALL
What you could do is something along the lines of:
UPDATE test SET metadata = metadata::jsonb || jsonb_build_object('score', jsonb_build_array(score, 0, 0, 0, 0, 0))::jsonb where WHERE pk > [last_pk] ORDER BY pk LIMIT 1000
where PK stands for Primary Key, which based on your schema that you provided, hasn’t been explicitly defined. Please note, if you create a table without defining a primary key, CockroachDB uses a unique identifier for each row, which it then uses for the primary index. Because you cannot meaningfully use this unique row identifier column to filter table data, it does not offer any performance optimization. This means you will always have improved performance by defining a primary key for a table.
As for the second ALTER TABLE statement, this is rewrites the whole table it has to write one record per row, either rewriting the col-family that column was to remove it, or tombstone-ing the whole col family if it was by itself.
As for why
SHOW QUERIES wouldn’t’ show the query, it’s most likely that it failed to commit, so the transaction was aborted.
Let me know if you have any other questions.