Re: storage calculations

On Thu, 31 Jul 2003, Peter Eisentraut wrote:N
> pgboy(at)guthrie(dot)charm(dot)net writes:N
> > sorry, when i referred to "disk space used" i meant the actual amount
> > used by live rows. if i insert 1000 rows then delete 999 of them, the
> > disk file will be the size of the 100 row container (make that 1000, not
> > 100 - i cannot backspace here) until i do a "vacuum full" - which does a
> > table lock, which is a bad thing.
>
> The task that you originally described was that you want to monitor when
> the disk is getting full. For that task, you need to take into account
> the actual size of the data on disk, not the size after a "vacuum full"
> which you say you do not want to execute. Basing a disk full monitor on
> hypothetical sizes sounds pretty useless.
>
> > given that, i'd like to try to be able to calculate the number of
> > bytes a row uses given its schema. i've seen this kind of
> > documentation for other dbs, i just cannot seem to find it
> > in the postgresql docs.
>
> There is some information about that in the FAQ, but keep in mind that
> rows may be compressed or moved to secondary storage automatically.

well, i can admit that i am confused. my assumption is that when a row
is deleted in pg, that row is merely marked as 'gone' until a vacuum-full
is done. my further assumption is that if i continue to add rows, those
rows are not necessarily appended to the end of the physical data file,
but can be written over 'deleted' rows.

given that, a vacuum-full is the only way i know of to get an accurate
reflection of the number of bytes being used to store the data. without
the vacuum, i can tell how big a potentially sparse file is, but i don't
want to raise a warning just because the file size is getting large
(unless, of course, pg dos not overwrite deleted rows, in which case the
warning, or a vaccum-full, seems appropriate.

i think i agree with you, too, that i cannot really calculate a
hypothetical size, unless i have all fixed-sized fields. in that case,
i should be able to accurately calculate the size, yes? if not, what
are the variables i could not account for?

uh, any more info on your comment "rows may be compressed or moved
to secondary storage automatically." i'd *love* to know how to do
that.

thanks.
pgboy

On Thu, Jul 31, 2003 at 08:51:09AM -0400, pgboy(at)guthrie(dot)charm(dot)net wrote:
>
> well, i can admit that i am confused. my assumption is that when a row
> is deleted in pg, that row is merely marked as 'gone' until a vacuum-full
> is done. my further assumption is that if i continue to add rows, those
> rows are not necessarily appended to the end of the physical data file,
> but can be written over 'deleted' rows.

Your assumption is wrong. VACUUM without FULL will allow you to
write over the free space now available in your data files, subject
to the limitations of tracking as determined by your FSM settings.

VACUUM FULL actually shortens the data file. Except for cases having
to do with large tuples that won't fit in the previous page, VACUUM
FULL means that you can't fit any more data in that file, once the
VACUUM FULL is done.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

On Thu, 31 Jul 2003, Andrew Sullivan wrote:

> On Thu, Jul 31, 2003 at 08:51:09AM -0400, pgboy(at)guthrie(dot)charm(dot)net wrote:
> >
> > well, i can admit that i am confused. my assumption is that when a row
> > is deleted in pg, that row is merely marked as 'gone' until a vacuum-full
> > is done. my further assumption is that if i continue to add rows, those
> > rows are not necessarily appended to the end of the physical data file,
> > but can be written over 'deleted' rows.
>
> Your assumption is wrong. VACUUM without FULL will allow you to
> write over the free space now available in your data files, subject
> to the limitations of tracking as determined by your FSM settings.
>
> VACUUM FULL actually shortens the data file. Except for cases having
> to do with large tuples that won't fit in the previous page, VACUUM
> FULL means that you can't fit any more data in that file, once the
> VACUUM FULL is done.
>
> A
>
>

well, i was close. i didn't realize that i had to do a (non-full)
vacuum to mark deleted space as free.

but after that, i am still left with a potentially sparse file
and i don't really have a way to guess how much free space is
available until i do a full vacuum, correct? (at which time the
file size(s) will indicate the amount being used)

just started looking at fsm. thanks for the pointer there. i hope
i'm not acting too obtuse here.

thanks.
pg

On Thu, Jul 31, 2003 at 01:41:54PM -0400, pgboy(at)guthrie(dot)charm(dot)net wrote:
>
> well, i was close. i didn't realize that i had to do a (non-full)
> vacuum to mark deleted space as free.

Ooops, I think I was still unclear. VACUUM FULL actually re-arranges
the file, and returns it to the filesystem. Plain VACUUM does indeed
leave you with a file that is bigger than the actual data stored
there.

You can learn how much more data you could fit in the files using
VACUUM VERBOSE, keeping in mind that tuples may not always fit in an
already-allocated page (like when the tuples are large).

> available until i do a full vacuum, correct? (at which time the
> file size(s) will indicate the amount being used)

That's right, yes.

> just started looking at fsm. thanks for the pointer there. i hope
> i'm not acting too obtuse here.

Nope. If you don't ask, you won't learn about it.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110