.. _datanommer: Datanommer ========== - Reads-in messages from the bus - Stores them into the database Database tables --------------- Here is how the database schema looks like currently: .. code-block:: datanommer=# \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+------------ public | alembic_version | table | datanommer public | messages | table | datanommer public | package | table | datanommer public | package_messages | table | datanommer public | user | table | datanommer public | user_messages | table | datanommer Table sizes ----------- Here is the size of each table: .. code-block:: datanommer-# SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; Table | Size | External Size ------------------+------------+--------------- messages | 706 GB | 555 GB package_messages | 10 GB | 4779 MB user_messages | 10171 MB | 4444 MB package | 22 MB | 10 MB user | 6608 kB | 2744 kB alembic_version | 8192 bytes | 0 bytes (6 rows) The 3 columns are: .. code-block:: Table – The name of the table Size – The total size that this table takes External Size – The size that related objects of this table like indices take .. code-block:: datanommer=# SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class WHERE relpages >= 8 ORDER BY relpages DESC; objectname | objecttype | #entries | size --------------------------------------------+------------+----------------+--------- pg_toast_257488909 | t | 3.4282912e+08 | 440 GB messages | r | 1.8001134e+08 | 149 GB messages_datanommer_timestamp_topic_idx | i | 1.8001134e+08 | 13 GB messages_msg_id_key | i | 1.8001134e+08 | 12 GB index_msg_topic | i | 1.8001134e+08 | 11 GB pg_toast_257488909_index | i | 3.4282912e+08 | 7350 MB messages_datanommer_timestamp_category_idx | i | 1.8001134e+08 | 7069 MB package_messages | r | 1.14010896e+08 | 5365 MB user_messages | r | 1.2142492e+08 | 5165 MB index_msg_category | i | 1.8001134e+08 | 5087 MB package_messages_pkey | i | 1.14010896e+08 | 4049 MB index_msg_timestamp | i | 1.8001134e+08 | 3867 MB messages_pkey | i | 1.8001134e+08 | 3862 MB user_messages_pkey | i | 1.2142492e+08 | 3699 MB package | r | 189348 | 11 MB package_pkey | i | 189348 | 8616 kB user | r | 92626 | 3792 kB user_pkey | i | 92626 | 2632 kB pg_proc | r | 2960 | 632 kB pg_toast_2618 | t | 249 | 456 kB pg_depend | r | 7601 | 448 kB pg_attribute | r | 2913 | 432 kB pg_depend_reference_index | i | 7601 | 344 kB pg_description | r | 4714 | 328 kB pg_depend_depender_index | i | 7601 | 280 kB pg_proc_proname_args_nsp_index | i | 2960 | 232 kB pg_collation | r | 779 | 200 kB pg_description_o_c_o_index | i | 4714 | 184 kB pg_statistic | r | 422 | 152 kB pg_attribute_relid_attnam_index | i | 2913 | 128 kB pg_operator | r | 770 | 112 kB pg_class | r | 395 | 104 kB pg_rewrite | r | 126 | 96 kB pg_proc_oid_index | i | 2960 | 88 kB pg_type | r | 406 | 80 kB pg_attribute_relid_attnum_index | i | 2913 | 80 kB sql_features | r | 716 | 64 kB (37 rows) The 4 columns are: .. code-block:: objectname – The name of the object objecttype – r for the table, i for an index, t for toast data, ... #entries – The number of entries in the object (e.g. rows) size – The size of the object (source for these queries: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database )