Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and performance did not change. Of course I did a VACUUM ANALYZE. How comes?

(I see that the index sizes won't change if I only index single columns.)

This is the table I was coming from (I've added sizes + padding):

                               Table "public.treenode"
    Column     |           Type           | Size |          Modifiers
---------------+--------------------------+------+-------------------------------
 id            | bigint                   | 8    | not null default nextval( ...
 user_id       | integer                  | 4+4  | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 project_id    | integer                  | 4    | not null
 location      | real3d                   | 36   | not null
 editor_id     | integer                  | 4+4  |
 parent_id     | bigint                   | 8    |
 radius        | real                     | 4    | not null default 0
 confidence    | smallint                 | 2    | not null default 5
 skeleton_id   | integer                  | 4    | not null

With real3d being defined as

CREATE TYPE real3d AS (
  x real,
  y real,
  z real);

I changed this layout to the following:

                                Table "public.treenode_new"
    Column     |           Type           | Size |            Modifiers
---------------+--------------------------+------+--------------------------------
 id            | bigint                   | 8    | not null default nextval(' ...
 project_id    | integer                  | 4    | not null
 location_x    | real                     | 4    | not null
 location_y    | real                     | 4    | not null
 location_z    | real                     | 4    | not null
 editor_id     | integer                  | 4    | not null
 user_id       | integer                  | 4    | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 skeleton_id   | integer                  | 4    | not null
 radius        | real                     | 4    | not null default 0
 confidence    | real                     | 4+4  | not null default 5
 parent_id     | bigint                   | 8    |

If I am not mistaken, I should save 66 bytes per row (138 is one original row, 72 is a new row). This however is not happening: With 7604913 in these tables, the original table had a size of 1020 MB. The new table has a size of 1159 MB. I used pg_size_pretty(pg_relation_size('<tablename>')) to measure the sizes. So what am I missing?

One note: all but the last four columns are inherited from another table (of which I of course had to change the layout, too).

Update: After running VACUUM FULL as suggested by Erwin Brandstetter, the new table needs only 734 MB.

share|improve this question
    
The last two rows in your original design should actually read 2+2 and 4+4. Each tuple is padded to the next MAXALIGN at the end. Plus, remember that actual NULL columns are not stored at all. Just an entry in the null bitmask - which may also change the padding on following values. –  Erwin Brandstetter Jul 31 at 20:01
    
And the size for real3d is actually 33+3 (compare to fiddle for your last Q). The padding between tuples does not show in pg_column_size(t). All-in-all your old rows with all values NOT NULL (no null bitmask) occupied 128 bytes on disk (not 138) and the new one 72 as you wrote. That's 56 bytes per row. And would amount to saving ~ 406 MB (SELECT pg_size_pretty(56.0 * 7604913)). Did you change anything else, or are there NULL values involved? –  Erwin Brandstetter Jul 31 at 20:34
    
Thanks for the clarification. And no, I didn't change anything else. The parent_id column contains many null values (433761). –  tomka Jul 31 at 20:38
    
OK, NULL values change the calculation. I leave it at that. –  Erwin Brandstetter Jul 31 at 20:39

1 Answer 1

up vote 7 down vote accepted

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

share|improve this answer
    
Indeed! Thanks again Erwin, for yet another useful insight. The new table now has only a size of 734MB. –  tomka Jul 31 at 19:47
    
@tomka: The size of the old table (1020 MB) is after VACUUM FULL, too? –  Erwin Brandstetter Jul 31 at 19:49
    
Yes, it is still 1020 MB. –  tomka Jul 31 at 19:53
    
@tomka: Nice. 286 MB saved and the rest should be a bit faster, too. –  Erwin Brandstetter Jul 31 at 19:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.