Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--remove all instances of `limit 1e5`
drop table if exists assets;
create table assets (
  id bigserial, 
  name text, 
  owner text);

drop table if exists users;
create table users (
    id bigserial primary key, 
    username text generated always as ('user_'||id::text) stored, 
    ref1 text, 
    ref2 text);
    
--a chain of 1 million users, each connected only to the next
--text prepended to the ref to prevent cheating by direct casts
with batch as (
insert into users (ref1, ref2)
select 'ref_'||case when n%2=1 then n::text else (n+1)::text end,
       'ref_'||case when n%2=0 then n::text else (n+1)::text end
from generate_series(1,1e6,1) g(n)
limit 1e5
returning *
)
select * from batch limit 8;

--another 1 million users, made up of 10x100k chains
with batch as (
insert into users (ref1, ref2)
select 'ref_'||case when n%2=1 then n::text else (n+1)::text end,
       'ref_'||case when n%2=0 then n::text else (n+1)::text end
from generate_series(2e6,3e6,1) g(n)
where n%1e5 not in (1,0)
limit 1e5
returning *
)
select * from batch limit 8;

--another 1 million unique users, unlinked
with batch as (
insert into users (ref1, ref2)
select 'ref_'||n::text,
       'ref_'||(n+1e6)::int::text
from generate_series(4e6,5e6,1) g(n)
limit 1e5 
returning *
)
select * from batch limit 8;

--another 1 million users, all owning the same pair of assets
with batch as (
insert into users (ref1, ref2)
select 'ref_'||6e6::text,
       'ref_'||(1+6e6)::int::text
from generate_series(1,1e6,1) g(n)
limit 1e5 
returning *
)
select * from batch limit 8;

--another 1 million users, made up of 1000x1k chains
with batch as (
insert into users (ref1, ref2)
select 'ref_'||case when n%2=1 then n::text else (n+1)::text end,
       'ref_'||case when n%2=0 then n::text else (n+1)::text end
from generate_series(7e6,8e6,1) g(n)
where n%1e3 not in (1,0)
limit 1e5
returning *
)
select * from batch limit 8;

--each user owns an asset
with batch as (
insert into assets (name,owner)
select 'asset_'||(row_number()over())::text,
       ref as owner
from (select distinct ref1 as ref from users union select ref2 from users) a
returning *
)
select * from batch limit 8;

--there are 8 orphaned assets
with batch as (
insert into assets (name,owner)
select 'asset_'||n::text,
       null as owner
from generate_series(5e6+1,5e6+8,1) g(n)
returning *
)
select * from batch limit 8;

--inspect
select * from users order by id limit 10;
select * from users tablesample bernoulli(1) limit 20;

/*
--shuffle to prevent insert order based bias
create table users2 as select * from users order by random();
truncate table users;
insert into users (ref1,ref2) 
select ref1,ref2 from users2 order by random();
drop table users2;

select * from users order by id limit 10;
select * from users tablesample bernoulli(1) limit 20;
*/

DROP TABLE

CREATE TABLE

DROP TABLE

CREATE TABLE

id	username	ref1	ref2
1	user_1	ref_1	ref_2
2	user_2	ref_3	ref_2
3	user_3	ref_3	ref_4
4	user_4	ref_5	ref_4
5	user_5	ref_5	ref_6
6	user_6	ref_7	ref_6
7	user_7	ref_7	ref_8
8	user_8	ref_9	ref_8

SELECT 8

id	username	ref1	ref2
100001	user_100001	ref_2000003	ref_2000002
100002	user_100002	ref_2000003	ref_2000004
100003	user_100003	ref_2000005	ref_2000004
100004	user_100004	ref_2000005	ref_2000006
100005	user_100005	ref_2000007	ref_2000006
100006	user_100006	ref_2000007	ref_2000008
100007	user_100007	ref_2000009	ref_2000008
100008	user_100008	ref_2000009	ref_2000010

SELECT 8

id	username	ref1	ref2
200001	user_200001	ref_4000000	ref_5000000
200002	user_200002	ref_4000001	ref_5000001
200003	user_200003	ref_4000002	ref_5000002
200004	user_200004	ref_4000003	ref_5000003
200005	user_200005	ref_4000004	ref_5000004
200006	user_200006	ref_4000005	ref_5000005
200007	user_200007	ref_4000006	ref_5000006
200008	user_200008	ref_4000007	ref_5000007

SELECT 8

id	username	ref1	ref2
300001	user_300001	ref_6000000	ref_6000001
300002	user_300002	ref_6000000	ref_6000001
300003	user_300003	ref_6000000	ref_6000001
300004	user_300004	ref_6000000	ref_6000001
300005	user_300005	ref_6000000	ref_6000001
300006	user_300006	ref_6000000	ref_6000001
300007	user_300007	ref_6000000	ref_6000001
300008	user_300008	ref_6000000	ref_6000001

SELECT 8

id	username	ref1	ref2
400001	user_400001	ref_7000003	ref_7000002
400002	user_400002	ref_7000003	ref_7000004
400003	user_400003	ref_7000005	ref_7000004
400004	user_400004	ref_7000005	ref_7000006
400005	user_400005	ref_7000007	ref_7000006
400006	user_400006	ref_7000007	ref_7000008
400007	user_400007	ref_7000009	ref_7000008
400008	user_400008	ref_7000009	ref_7000010

SELECT 8

id	name	owner
1	asset_1	ref_1
2	asset_2	ref_10
3	asset_3	ref_100
4	asset_4	ref_1000
5	asset_5	ref_10000
6	asset_6	ref_100000
7	asset_7	ref_100001
8	asset_8	ref_10001

SELECT 8

id	name	owner
500107	asset_5000001	null
500108	asset_5000002	null
500109	asset_5000003	null
500110	asset_5000004	null
500111	asset_5000005	null
500112	asset_5000006	null
500113	asset_5000007	null
500114	asset_5000008	null

SELECT 8

id	username	ref1	ref2
1	user_1	ref_1	ref_2
2	user_2	ref_3	ref_2
3	user_3	ref_3	ref_4
4	user_4	ref_5	ref_4
5	user_5	ref_5	ref_6
6	user_6	ref_7	ref_6
7	user_7	ref_7	ref_8
8	user_8	ref_9	ref_8
9	user_9	ref_9	ref_10
10	user_10	ref_11	ref_10

SELECT 10

id	username	ref1	ref2
109	user_109	ref_109	ref_110
236	user_236	ref_237	ref_236
493	user_493	ref_493	ref_494
646	user_646	ref_647	ref_646
756	user_756	ref_757	ref_756
872	user_872	ref_873	ref_872
972	user_972	ref_973	ref_972
1057	user_1057	ref_1057	ref_1058
1063	user_1063	ref_1063	ref_1064
1137	user_1137	ref_1137	ref_1138
1252	user_1252	ref_1253	ref_1252
1290	user_1290	ref_1291	ref_1290
1322	user_1322	ref_1323	ref_1322
1326	user_1326	ref_1327	ref_1326
1449	user_1449	ref_1449	ref_1450
1641	user_1641	ref_1641	ref_1642
1704	user_1704	ref_1705	ref_1704
1769	user_1769	ref_1769	ref_1770
1836	user_1836	ref_1837	ref_1836
1917	user_1917	ref_1917	ref_1918

SELECT 20