Horizon Database Size / Are there very large indexes (yet)?

Question

Could someone with a running Horizon instance please check current index sizes in the Horizon DB? I was told it currently needs around 300GB disk space but curious about index sizes to determine (current) RAM requirements.

To check the index sizes simple run below query after connecting to the DB (\c horizon):

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

Thanks!

Answer 1

As of September 18th 2018 the largest tables and indexes are:

            table_name            | row_estimate |   index    |   table    
----------------------------------+--------------+------------+------------
 history_operation_participants   |  1.69259e+08 | 17 GB      | 9062 MB
 history_transactions             |   3.4308e+07 | 14 GB      | 70 GB
 history_effects                  |  8.95771e+07 | 12 GB      | 17 GB
 history_operations               |  1.29739e+08 | 11 GB      | 62 GB
 history_ledgers                  |  1.40887e+07 | 8667 MB    | 15 GB
 history_transaction_participants |  7.63354e+07 | 7675 MB    | 4053 MB