Out of Memory Error when using regexp_extract in an UPDATE statement

[CuriousTim]

I have a directory of ~5600 tab-delimited files all of the format

CONTIG	START	END	COUNT
chr1	100	200	1
chr1	202	300	8

The CONTIG, START, and END columns are identical in all files (different numbers than shown here); only the COUNT column is different across files. I read the files into a database like so

CREATE TABLE counts AS
SELECT * FROM read_csv('dir/*.tsv',
    delim = '\t',
    auto_detect = false,
    filename = true,
    header = true,
    columns = {
        'CONTIG': 'VARCHAR',
        'START': 'INTEGER',
        'END': 'INTEGER'',
        'COUNT': 'INTEGER'
    })

and I get a table of ~2.1B rows.

Then I tried to generate an ID from the filename column that is automatically created. So I ran ALTER followed by an UPDATE.

ALTER TABLE counts ADD COLUMN sample_id VARCHAR;
UPDATE counts SET sample_id = regexp_extract(filename, '/(.+)\.tsv$', 1);

I ran all of this using the R API and the first time I tried, my R session crashed because it ran out of memory (using a 26GB RAM machine). Then I tried setting a memory limit with SET memory_limit = '16GB'; and got an error after about a minute of running.

Error: Out of Memory Error: failed to allocate data of size 2.0 MiB (14.9 GiB/14.9 GiB used)

Is it expected for this kind of UPDATE statement to use so much memory?