Skip site navigation (1) Skip section navigation (2)

Re: Table size does not include toast size

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Bernd Helmle <mailings(at)oopsware(dot)de>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Table size does not include toast size
Date: 2010-01-19 03:20:33
Message-ID: 4B552501.2040604@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Bernd Helmle wrote:
> These are two new functions pg_table_size() and pg_indexes_size(). 
> This patch also changes pg_total_relation_size() to be a shorthand for 
> pg_table_size() + pg_indexes_size().

Attached is a test program to exercise these new functions.  I 
thoroughly abuse generate_series and arrays to create a table with a few 
megabytes of both regular and TOAST-ed text, and with two indexes on 
it.  Here's the results from a sample run (it's random data so each run 
will be a bit different):

pg_relation_size       | 11,755,520
pages_size             | 11,755,520
toast_and_fsm          | 22,159,360
pg_table_size          | 33,914,880
pg_indexes_size        |    524,288
pkey                   |    262,144
i                      |    262,144
pg_total_relation_size | 34,439,168
computed_total         | 34,439,168

This seems to work as expected.  You can see that pg_relation_size gives 
a really misleading value for this table, whereas the new pg_table_size 
does what DBAs were asking for here.  Having pg_indexes_size around is 
handy too.  I looked over the code a bit, everything in the patch looks 
clean too.

The only question I'm left with after browsing the patch and staring at 
the above results is whether it makes sense to expose a pg_toast_size 
function.  That would make the set available here capable of handling 
almost every situation somebody might want to know about, making this 
area completely done as I see it.  In addition to being a useful 
shorthand on its own, that would then allow you to indirectly compute 
just the FSM size, which seems like an interesting number to know as 
feedback on what VACUUM is up to.  It's easy enough to add, too:  the 
calculate_toast_table_size code needed is already in the patch, just 
have to add another external function to expose it.

I don't think there's any useful case for further exposing the two 
component parts of the toast size.  If you're enough of a hacker to know 
what to do with those, you can certainly break them down yourself.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com  www.2ndQuadrant.com


Attachment: test-size.sql
Description: text/x-sql (1.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-01-19 03:30:33
Subject: Re: Table size does not include toast size
Previous:From: Tom LaneDate: 2010-01-19 01:37:17
Subject: Re: plpgsql: open for execute - add USING clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group