YugabyteDB Transactional Load with Non-transactional COPY
Tags: yugabytedb, distributed, postgres, sql
Published: 2024-03-19 22:41:51


By default, YugabyteDB COPY does intermediate commits every 20000 rows:
```sql
yugabyte=# show yb_default_copy_from_rows_per_transaction;

 yb_default_copy_from_rows_per_transaction
-------------------------------------------
 20000
(1 row)

yugabyte=# show yb_disable_transactional_writes;

 yb_disable_transactional_writes
---------------------------------
 on
(1 row)
```

Let's take an example with the following table:
```sql
yugabyte=# create table loaded_data ( id bigserial, data text );
CREATE TABLE
```
I set `statement_timeout` to 5 seconds to simulate a failure before the end, and load some rows:
```sql
yugabyte=# set statement_timeout=5000;
SET

yugabyte=# copy loaded_data(data) from program 'base64 -w 100 /dev/urandom | head -c $(( 1024 * 1024 * 1024 ))';
ERROR:  57014: canceling statement due to statement timeout

yugabyte=# select count(*) from loaded_data;
 count
--------
 120000
(1 row)

```
The statement has failed but, because of intermediate commits, some rows are loaded, a multiple of 20000, have been visible during the load, and have to be cleaned up manually:
```sql
yugabyte=# delete from loaded_data;
DELETE 120000
```

To be transactional, you can disable intermediate commits:
```sql
yugabyte=# set yb_default_copy_from_rows_per_transaction=0;
SET

yugabyte=# copy loaded_data(data) from program 'base64 -w 100 /dev/urandom | head -c $(( 1024 * 1024 * 1024 ))';
ERROR:  57014: canceling statement due to statement timeout

yugabyte=# select count(*) from loaded_data;
 count
-------
     0
(1 row)
```
However, this is less efficient, allocating more memory and large inserts into IntentsDB

Another solution is to keep intermediate commits but add a column that will set the visibility at the end when the table is queried though a view. It will use a user-define batch number:
```sql
yugabyte=# set app.batchid=1;
SET
yugabyte=# alter table loaded_data add batch bigint default current_setting('app.batchid')::bigint;
ALTER TABLE
```

A table will store the visible batches, and a view will filter on it:
```sql
yugabyte=# create table loaded_batches(batch bigint primary key default current_setting('app.batchid')::bigint );
CREATE TABLE

yugabyte=# create view loaded_view as select * from loaded_data where batch in (select batch from loaded_batches);
CREATE VIEW
```

I set the batch ID:
```sql
yugabyte=# select set_config('app.batchid',extract (epoch from now())::bigint::text,false);

 set_config
------------
 1710884655
(1 row)
```

When I COPY, the batch ID will be set to this value. I can use intermediate commits, or even non-transactional inserts which will bypasse the provisional records and make each row visible as it is inserted:
```sql
yugabyte=# set yb_disable_transactional_writes=on;
SET

yugabyte=# copy loaded_data(data) from program 'base64 -w 100 /dev/urandom | head -c $(( 1024 * 1024 * 1024 ))';
ERROR:  57014: canceling statement due to statement timeout

```

The rows are visible from the table but not from the view:
```sql
yugabyte=# select count(*) from loaded_data;

 count
-------
 27648
(1 row)

yugabyte=# set yb_bnl_batch_size=1024;
SET

yugabyte=# select count(*) from loaded_view;

 count
-------
     0
(1 row)
```

The execution plan is efficient with Batched Nested Loops.

In case of failure as above, the rows can be cleaned-up later:
```sql
yugabyte=# delete from loaded_data where batch not in (select batch from loaded_batches);
DELETE 27648
```

When an insert is sucessful, the batch ID is inserted at the end to make it visible:
```sql
yugabyte=# select set_config('app.batchid',extract (epoch from now())::bigint::text,false);
 set_config
------------
 1710885534
(1 row)

yugabyte=# set statement_timeout=0;
SET

yugabyte=# copy loaded_data(data) from program 'base64 -w 100 /dev/urandom | head -c $(( 1024 * 1024 * 1024 ))';
COPY 10631108

yugabyte=# insert into loaded_batches select;
INSERT 0 1
```
All new rows have been made atomically visible from the view:
```sql
yugabyte=# select count(*) from loaded_data;
  count
----------
 10631108
(1 row)

yugabyte=# select count(*) from loaded_view;
  count
----------
 10631108
(1 row)
```

This is a trade-off: faster load with non-transactional inserts, but the overhead of filtering on queries.