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!

up vote 1 down vote accepted

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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.