begin;
drop table if exists audit_data;
create table audit_data ( id bigserial unique not null, payment_id text, payload text );
insert into audit_data (payment_id, payload)
select md5(i::text),
repeat(md5(i::text), 10)
from generate_series(1, 1000*1000) s(i);
commit;
select pg_size_pretty(pg_total_relation_size('audit_data'));
begin;
drop table if exists audit_data_log;
create table audit_data_log (id bigserial, storage_data json);
do $$
declare
cur cursor(id bigint)
for select * from audit_data;
pack json[];
count int;
rec record;
begin
open cur(0);
loop
fetch cur into rec;
exit when not found;
pack := array_append(pack, row_to_json(rec.*));
count := count + 1;
if count = 100 then
insert into audit_data_log(storage_data) values (json_build_array(pack));
pack = null;
count = 0;
end if;
end loop;
close cur;
insert into audit_data_log(storage_data) values (json_build_array(pack));
end
$$ language plpgsql;
commit;
select pg_size_pretty(pg_total_relation_size('audit_data_log'));
-
-
Save vadv/fb5fed58c57f69167f091530d917d749 to your computer and use it in GitHub Desktop.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment