Why is tuple_percent so low?

From: Sam Saffron <sam(dot)saffron(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Why is tuple_percent so low?
Date: 2018-02-27 05:03:36
Message-ID: CAAtdryOZ7EQaKfgPDXz5RCOXr3f_JMGOUYwWjLnSEe3GvUK+Jg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am trying to refactor a table on disk so it consumes less space:

Original is:

create table post_timings(
topic_id int not null,
post_number int not null,
user_id int not null,
msecs int not null
)

Target is:

create table post_timings(
post_id int not null,
user_id int not null,
dsecs smallint not null
)

Before I have:

select * from pgstattuple('post_timings2');

table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
5146427392 | 116221695 | 4648867800 | 90.33 |
0 | 0 | 0 | 15082484 | 0.29

After I have:

table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
5142036480 | 116122544 | 3948166496 | 76.78 |
0 | 0 | 0 | 15069224 | 0.29

What I find striking is that the table size on disk remains almost
unchanged despite tuples taking 6 less bytes per tuple.

All the "missing space" is in overhead that is missing from
pgstattuple, in particular tuple percent moves from 90 to 76.7

I was wondering:

1. Where is all my missing space, is this in page alignment stuff and
per-page overhead?

2. Is there any other schemes I can look at for storing this data to
have a more efficient yet easily queryable / updateable table.

Keep in mind these tables get huge and in many of our cases will span
10-20GB just to store this information.

Sam

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Łukasz Jarych 2018-02-27 06:25:30 Re: Creating complex track changes database - challenge!
Previous Message Konstantin Izmailov 2018-02-27 04:03:51 Re: is libpq and openssl 1.1.* compatible?