Project

General

Profile

Bug #14014

A lot of space is wasted on table nodecompliance and nodeconfigurations due to pretty jsonification

Added by Nicolas CHARLES over 1 year ago. Updated over 1 year ago.

Status:
Released
Priority:
N/A
Category:
Performance and scalability
Target version:
Severity:
User visibility:
Effort required:
Priority:
0

Description

About 30% of the table size if use by spaces, even when accounting the compression within postgres
Creating the nodecompliance3 table, with the script
insert into nodecompliance3 (nodeid, runtimestamp, endoflife, runanalysis, summary, details) select nodeid, runtimestamp, endoflife, runanalysis, summary, regexp_replace(regexp_replace(regexp_replace(regexp_replace(details, '\s+"', '"', 'ig'), '\s+{', '{', 'ig'), '\s+}', '}', 'ig'), '\s+\]', ']', 'ig') from nodecompliance;

result in following disk usage, after vacuum full of both tables


  oid  | table_schema |      table_name      | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes |  total  | index  |   toast    | table  
-------+--------------+----------------------+--------------+-------------+-------------+-------------+-------------+---------+--------+------------+--------
 16497 | public       | nodecompliance       |          350 |     1089536 |       98304 |      761856 |      229376 | 1064 kB | 96 kB  | 744 kB     | 224 kB
 25435 | public       | nodecompliance3      |          350 |      794624 |       98304 |      483328 |      212992 | 776 kB  | 96 kB  | 472 kB     | 208 kB

Also available in: Atom PDF