Datanommer

  • Reads-in messages from the bus

  • Stores them into the database

Database tables

Here is how the database schema looks like currently:

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:

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:

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
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:

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 )