pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDisk space usage
3+ messages / 3 participants
[nested] [flat]
* Disk space usage
@ 2006-11-29 19:57 Joe Bloggs <polonium210@googlemail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Bloggs @ 2006-11-29 19:57 UTC (permalink / raw)
To: pgsql-admin
I created a table in 8.1.5 on Linux with three columns;
date, bigint and integer.
Then I populated the table with more than 2 million rows.
I looked at the size of the file that contained the table and divided this
by the number of rows which gave an average of just over 60 bytes per row.
This seems to be quite a large overhead as I would guess that the date field
takes around 4 bytes, the bigint 8 bytes and the integer 4 bytes. I would
have hoped to have had an average size of between 20 and 30 bytes per row.
Is this normal and is there any way of improving this as I'm hoping to use
have around 80 million rows in a table without it taking up too much disk
space and too much memory to cache it?
Regards
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Disk space usage
@ 2006-11-29 20:46 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Joe Bloggs <polonium210@googlemail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2006-11-29 20:46 UTC (permalink / raw)
To: Joe Bloggs <polonium210@googlemail.com>; +Cc: pgsql-admin
On Wed, 2006-11-29 at 13:57, Joe Bloggs wrote:
> I created a table in 8.1.5 on Linux with three columns;
>
> date, bigint and integer.
>
> Then I populated the table with more than 2 million rows.
>
> I looked at the size of the file that contained the table and divided
> this by the number of rows which gave an average of just over 60 bytes
> per row.
>
> This seems to be quite a large overhead as I would guess that the date
> field takes around 4 bytes, the bigint 8 bytes and the integer 4
> bytes. I would have hoped to have had an average size of between 20
> and 30 bytes per row. Is this normal and is there any way of improving
> this as I'm hoping to use have around 80 million rows in a table
> without it taking up too much disk space and too much memory to cache
> it?
Had you done any updates to the tables? If so, had you run a vacuum
full?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Disk space usage
@ 2006-11-29 21:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-11-29 21:25 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Joe Bloggs <polonium210@googlemail.com>; pgsql-admin
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Wed, 2006-11-29 at 13:57, Joe Bloggs wrote:
>> I created a table in 8.1.5 on Linux with three columns;
>> date, bigint and integer.
>>
>> I looked at the size of the file that contained the table and divided
>> this by the number of rows which gave an average of just over 60 bytes
>> per row.
>>
>> This seems to be quite a large overhead as I would guess that the date
>> field takes around 4 bytes, the bigint 8 bytes and the integer 4
>> bytes. I would have hoped to have had an average size of between 20
>> and 30 bytes per row.
> Had you done any updates to the tables?
I think it's about dead on, if he's using a machine with MAXALIGN 8:
Row's item pointer 4 bytes
Row header 32 bytes (including alignment padding)
date column 4 bytes
alignment padding 4 bytes
bigint column 8 bytes
integer column 4 bytes
alignment padding 4 bytes
--------
60 bytes
The choice of column order is unfortunate --- if he'd put the bigint
column either first or last, there wouldn't be the alignment penalties.
Hopefully we'll be able to get the row header back down to 24 bytes in
8.3, but it's never going to be the 4-or-so-bytes that the OP is
imagining.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-11-29 21:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-29 19:57 Disk space usage Joe Bloggs <polonium210@googlemail.com>
2006-11-29 20:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-11-29 21:25 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox