PostgreSQL Limits Question/Help

Question

Not a complete database noob, but I've done a ton of research on this and I still can't figure this out. I've got a PostgreSQL database tied to a CMS. It currently has 470 columns in one of the table (long story), and I'm trying to figure out how close we are to maxing things out.

If I run

SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;

I get back

If I run

SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   table_name t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

Then I get back

Can anyone help make sense of this, or give me some other things to check?

Answer 1

It depends on the data types of the columns and the actual values being stored. If every column is of a toastable type, in my hands a table of 470 columns will start to throw errors if more than 448 of those columns are not null and large. This is relatively easy to demonstrate with the \gexec feature of psql.

select 'create table wide ('||string_agg('col'||x::text||' text',',')||')' from generate_series(1,470) f(x) \gexec

select 'insert into wide values ('||string_agg('md5(random()::text)',',')||')' from generate_series(1,449) f(x) \gexec

yielding:

ERROR:  row is too big: size 8176, maximum size 8160