Hello. Is there a reason why PostgreSQL uses NUMERIC(20,0) instead of BIGINT for it's history_uint? BIGINT is always 8 bytes on disk, whereas NUMERIC is "two bytes for each group of four decimal digits, plus three to eight bytes overhead," per the documentation[1].
I realize that BIGINT is signed, but per docs from PostgreSQL, NUMERIC is closer to a VARCHAR than a normal binary integer. Assuming the average zabbix user has has an average most of their counters over 1M, then using BIGINT would be a savings. Even in my small dev environment, there's a tiny savings. People storing large quantities of values where history_uint.value is more than 1M, the savings would be more pronounced.
Incidentally, it looks like vm.memory.size[available] is populating bogus values:
[1] http://www.postgresql.org/docs/curre...UMERIC-DECIMAL
I realize that BIGINT is signed, but per docs from PostgreSQL, NUMERIC is closer to a VARCHAR than a normal binary integer. Assuming the average zabbix user has has an average most of their counters over 1M, then using BIGINT would be a savings. Even in my small dev environment, there's a tiny savings. People storing large quantities of values where history_uint.value is more than 1M, the savings would be more pronounced.
Code:
zabbix=# CREATE TABLE history_uint2 AS SELECT * FROM history_uint;
SELECT 5918325
zabbix=# ALTER TABLE history_uint2 ALTER COLUMN value SET DATA TYPE BIGINT;
ERROR: bigint out of range
zabbix=# DELETE FROM history_uint2 WHERE value > 9223372036854775807; -- This value is (2 ^ 63 - 1)
DELETE 17906
zabbix=# ALTER TABLE history_uint2 ALTER COLUMN value SET DATA TYPE BIGINT;
ALTER TABLE
zabbix=# \dt+ history_uint*
List of relations
Schema | Name | Type | Owner | Size | Description
----------+-------------------+-------+----------+---------+-------------
zabbix20 | history_uint | table | zabbix20 | 363 MB |
zabbix20 | history_uint2 | table | pgsql | 339 MB |
Incidentally, it looks like vm.memory.size[available] is populating bogus values:
Code:
zabbix=# SELECT COUNT(*), i.key_ FROM history_uint AS h, items AS i WHERE i.itemid = h.itemid AND h.value >= pow(2,63) GROUP BY i.key_; count | key_
-------+---------------------------
17931 | vm.memory.size[available]
(1 row)
zabbix=# SELECT i.itemid, i.key_, h.value, pow(2,64) - h.value AS delta_from_2_pow_64 FROM history_uint AS h, items AS i WHERE i.itemid = h.itemid AND h.value > 9223372036854775808 LIMIT 10;
itemid | key_ | value | delta_from_2_pow_64
-----------------+---------------------------+----------------------+---------------------
100100000023975 | vm.memory.size[available] | 18446744071572967424 | 2136584192
100100000023867 | vm.memory.size[available] | 18446744072915468288 | 794083328
100100000024119 | vm.memory.size[available] | 18446744072050368512 | 1659183104
100100000023975 | vm.memory.size[available] | 18446744071603822592 | 2105729024
100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
100100000024119 | vm.memory.size[available] | 18446744072045858816 | 1663692800
100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
100100000024119 | vm.memory.size[available] | 18446744072044445696 | 1665105920
100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
100100000023975 | vm.memory.size[available] | 18446744071566131200 | 2143420416
(10 rows)
[1] http://www.postgresql.org/docs/curre...UMERIC-DECIMAL
Comment