P
P
Pavel Tolmachev2020-02-10 22:25:17
PostgreSQL
Pavel Tolmachev, 2020-02-10 22:25:17

Where is part of the table size "lost"?

Hello.
(PostgreSQL 10, xubuntu 16.04, x86)
Situation: created a table with one column of text type, default storage method is extended.
I uploaded big data there (999 ^ 999 converted to text), a toast table was created.
I check the size of all layers of the main table - 46 MB.
I check the size of all layers of the linked TOAST-table - 3907 MB.
I check the full size of the table - 3996 MB.
The math tells us that 46 + 3907 != 3996 MB.
(I experimented with the method of storing the external column (compression is not allowed) - there is also a "lost" piece.)
Tell me, please, where was the piece from the 43 MB table "lost"? Where to look for it? Or were these megabytes "compressed" due to the extended storage method?
---
Find the files that match the main table (tb_test) and its corresponding toast table:
5e423508493d8205917956.png
Find the size of these files:
5e423524428a1289596884.png

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2020-02-11
@ptolmachev

\dt+ shows pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_total_relation_size is pg_table_size + table index size.
All of them are calculated here: https://github.com/postgres/postgres/blob/REL_11_S... In
total, you did not calculate the size of indexes on toast. He is usually alone

file layers (main, fsm, vm)

This is relation forks

M
mayton2019, 2020-02-12
@mayton2019

Just 3 comments.
It is not clear why the author should consider 43 megabytes against the background of 3GB. It's 1%.
It is not clear why the author prints the physical sizes of files. After all, they store headers and all sorts
of other service data structures that do not apply to the size of the table.
And it is not clear why the author deduced the length of the files in a size that is rounded in human-readable? There are still not taken into account kilobytes. Well, if the bill went to a penny, then it is necessary to print pennies.
Sorry, but there are a lot of inaccuracies and indirections in the question itself. It just can't be like that.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question