toast.sql

create table app1 (
    id uuid primary key,
    doc jsonb compression lz4 not null,
    created_at timestamptz not null default current_timestamp,
    updated_at timestamptz
);

create table app2 (
    id uuid primary key,
    doc jsonb compression lz4 not null,
    created_at timestamptz not null default current_timestamp,
    updated_at timestamptz
);

alter table app2 alter column doc set storage main;
alter table app2 set (toast_tuple_target = 8000);


create or replace function gen_uuid(x integer)
returns uuid
language sql immutable strict parallel safe
return to_char(x, 'FM00000000-0000-0000-0000-000000000000')::uuid;

vacuum full app1;
vacuum full app2;

SELECT
    relname AS main_table,
    'pg_toast.pg_toast_' || reltoastrelid AS toast_table_name
FROM pg_class
WHERE relname = 'app1';


SELECT
    relname AS table_name,
    reltoastrelid::regclass AS toast_table_name
FROM pg_class
WHERE relname = 'app2';

pg_toast.pg_toast_41379 -- app1
pg_toast.pg_toast_41387 -- app2


select count(*) from pg_toast.pg_toast_41379; -- app1
select count(*) from pg_toast.pg_toast_41387; -- app2

select id, pg_column_size(doc) from app1 limit 100;
select id, pg_column_size(doc) from app2 limit 100;

explain (analyze, buffers, verbose)
select id, doc from app1
where doc['3']['status'] = '"active"'::jsonb
-- limit 100000;


explain (analyze, buffers, verbose)
select id, doc from app2
where doc['3']['status'] = '"active"'::jsonb
-- limit 100000;

DO $$
DECLARE
    app jsonb;
BEGIN
    FOR i IN 1..1000000 BY 1 LOOP
        select doc into app from app1 where id = gen_uuid(i);
    END LOOP;
END $$;


DO $$
DECLARE
    app jsonb;
BEGIN
    FOR i IN 1..1000000 BY 1 LOOP
        select doc into app from app2 where id = gen_uuid(i);
    END LOOP;
END $$;

insert into app2 select * from app1;

insert into app1 (id, doc, created_at)
select
    gen_uuid(x),
    jsonb_build_object(
        '1',
        jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ), '2',
    jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ), '3', jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ), '5', jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ), '7', jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    ), '9', jsonb_build_object(
        'id', gen_uuid(x),
        'status', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
        'created_at', (now() - interval '1 day' * random() * 365),
        'created_by', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'email', (format('user_%s@test.com', to_char(x % 1000, 'FM0000'))),
            'name', (format('User %s', to_char(x % 1000, 'FM0000')))
        ),
        'application_id', x,
        'organization', jsonb_build_object(
            'id', gen_uuid(x % 1000),
            'code', x % 1000,
            'short_name', format('Organization %s', x % 1000)
        ),
        'comment', format('Comment number #%s', x),
        'amounts', jsonb_build_array(
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            ),
            jsonb_build_object(
                'amount', (ceil(random() * 100000000)),
                'currency', ((array['USD', 'EUR', 'RUB'])[ceil(random() * 3)]),
                'period', jsonb_build_object('y', ceil(random() * 10), 'm', ceil(random() * 10), 'w', ceil(random() * 10), 'd', ceil(random() * 10))
            )
        ),
        'departments', jsonb_build_array(
            jsonb_build_object(
                'id', gen_uuid((x % 25)),
                'code', format('dep_%s', (x % 25)),
                'name', format('Department %s', (x % 25)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000)),
                        'email', (format('user_%s@test.com', (x % 1000))),
                        'name', (format('User %s', (x % 1000)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 10)),
                        'email', (format('user_%s@test.com', (x % 1000 + 10))),
                        'name', (format('User %s', (x % 1000 + 10)))
                    )
                )
            ),
            jsonb_build_object(
                'id', gen_uuid((x % 25 + 10)),
                'code', format('dep_%s', (x % 25 + 10)),
                'name', format('Department %s', (x % 25 + 10)),
                'users', jsonb_build_array(
                    jsonb_build_object(
                        'id', gen_uuid((x % 1000 + 20)),
                        'email', (format('user_%s@test.com', (x % 1000 + 20))),
                        'name', (format('User %s', (x % 1000 + 20)))
                    ),
                    jsonb_build_object(
                        'id', gen_uuid((x % 25 + 30)),
                        'email', (format('user_%s@test.com', (x % 25 + 30))),
                        'name', (format('User %s', (x % 25 + 30)))
                    )
                )
            )
        ),
        'journal', jsonb_build_array(
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            ),
            jsonb_build_object(
                'event', ((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)]),
                'datetime', (now() - interval '1 day' * random() * 365),
                'user_id', uuid_generate_v4()
            )
        )
    )
    ),
    (now() - interval '1 day' * random() * 365)
from
    generate_series(1, 1000000) as seq(x);