Size increase when using create_table_like

[assaferan]

Duplicating a table using create_table_like leads to an unreasonable increase in the size of the table.
Consider the tables mf_hecke_cc and mf_hecke_cc_eis.
The latter is an exact copy of the former, created using create_table_like, but somehow takes almost double the size (480MB vs 270MB), see here.

[roed314]

The problem is in an_normalized or angles, as expected:

sage: X = db.table_sizes()
sage: X["mf_hecke_cc"]
defaultdict(<class 'int'>, {'nrows': 14417694, 'index_bytes': 3785138176, 'toast_bytes': 264470200320, 'table_bytes': 18784223232, 'total_bytes': 287039578112, 'ncounts': 0, 'counts_bytes': 8192, 'nstats': 0, 'stats_bytes': 8192})
sage: X["mf_hecke_cc_eis"]
defaultdict(<class 'int'>, {'nrows': 14417694, 'index_bytes': 3785129984, 'toast_bytes': 485611831296, 'table_bytes': 18759630848, 'total_bytes': 508156616704, 'ncounts': 0, 'counts_bytes': 16384, 'nstats': 0, 'stats_bytes': 8192})

[assaferan]

Yes, though it seems to have used the same strategy to compress them

sage: db._execute(SQL("""SELECT
....:     attname AS column_name,
....:     attstorage AS storage_strategy
....: FROM
....:     pg_attribute
....: WHERE
....:     attrelid = 'mf_hecke_cc'::regclass
....:     AND attnum > 0
....: ORDER BY
....:     attnum;""")).fetchall()
[('id', 'p'),
 ('embedding_m', 'p'),
 ('embedding_index', 'p'),
 ('conrey_index', 'p'),
 ('embedding_root_real', 'p'),
 ('embedding_root_imag', 'p'),
 ('hecke_orbit_code', 'p'),
 ('angles', 'x'),
 ('an_normalized', 'x'),
 ('label', 'x'),
 ('level', 'p'),
 ('weight', 'p'),
 ('char_orbit_index', 'p'),
 ('hecke_orbit', 'p'),
 ('dual_conrey_index', 'p'),
 ('dual_embedding_index', 'p'),
 ('dual_embedding_m', 'p')]
sage: db._execute(SQL("""SELECT
....:     attname AS column_name,
....:     attstorage AS storage_strategy
....: FROM
....:     pg_attribute
....: WHERE
....:     attrelid = 'mf_hecke_cc_eis'::regclass
....:     AND attnum > 0
....: ORDER BY
....:     attnum;""")).fetchall()
[('hecke_orbit_code', 'p'),
 ('id', 'p'),
 ('embedding_root_imag', 'p'),
 ('embedding_root_real', 'p'),
 ('conrey_index', 'p'),
 ('dual_conrey_index', 'p'),
 ('dual_embedding_index', 'p'),
 ('dual_embedding_m', 'p'),
 ('embedding_index', 'p'),
 ('embedding_m', 'p'),
 ('hecke_orbit', 'p'),
 ('level', 'p'),
 ('char_orbit_index', 'p'),
 ('weight', 'p'),
 ('an_normalized', 'x'),
 ('angles', 'x'),
 ('label', 'x')]

[assaferan]

I tried to check the bloat size of the original table -

db._execute(SQL("""
....: SELECT
....:     schemaname,
....:     tablename,
....:     ROUND((CASE WHEN actual_size = 0 THEN 0.0 ELSE (actual_size - expected_size) * 100 / actual_size END)::numeric, 2) AS bloat_percent,
....:     pg_size_pretty((actual_size - expected_size)::bigint) AS wasted_space, -- Cast to bigint
....:     pg_size_pretty(actual_size::bigint) AS actual_size, -- Cast to bigint
....:     pg_size_pretty(expected_size::bigint) AS expected_size -- Cast to bigint
....: FROM (
....:     SELECT
....:         N.nspname AS schemaname,
....:         C.relname AS tablename,
....:         pg_table_size(C.oid) AS actual_size,
....:         (
....:             SELECT sum(null_frac * avg_width) FROM pg_stats S WHERE S.schemaname = N.nspname AND S.tablename = C.relname
....:         ) AS stats_size,
....:         (
....:             -- approximate tuple header size
....:             24 + (SELECT count(*) FROM pg_stats S WHERE S.schemaname = N.nspname AND S.tablename = C.relname) * 2
....:         ) AS tuple_header_size,
....:         -- calculate expected size
....:         CEIL( (C.reltuples * ((24 + (SELECT sum(null_frac * avg_width) FROM pg_stats S WHERE S.schemaname = N.nspname AND S.tablename = C.relname) + (SELECT count(*) FROM pg_s
....: tats S WHERE S.schemaname = N.nspname AND S.tablename = C.relname) * 2)::numeric)) / (current_setting('block_size')::numeric - 20) ) * current_setting('block_size')::numeric A
....: S expected_size
....:     FROM
....:         pg_class C
....:     LEFT JOIN
....:         pg_namespace N ON N.oid = C.relnamespace
....:     WHERE
....:         C.relkind = 'r'
....:         AND N.nspname NOT IN ('pg_catalog', 'information_schema')
....:         AND C.relname = 'mf_hecke_cc'
....: ) AS bloat_estimate;
....: """)).fetchall()
[('public', 'mf_hecke_cc', 99.63, '263 GB', '264 GB', '1001 MB')]