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 )