pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Liberman <mliberman@goldpocket.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Feedback on auto-pruning approach
Date: Tue, 14 Feb 2006 22:33:40 -0500
Message-ID: <25689.1139974420@sss.pgh.pa.us> (raw)
In-Reply-To: <9D938282F8C6EE43B748B910386DE93E0138B3F5@srvgpimail1.GPI.local>
References: <9D938282F8C6EE43B748B910386DE93E0138B3F5@srvgpimail1.GPI.local>

"Mark Liberman" <mliberman@goldpocket.com> writes:
> where c.oid = a.attrelid
>   and c.relnamespace = n.oid
>   and c.relkind in ('i','r')
>   and a.atttypid not in (26,27,28,29)
> group by c.relname) by_table;

> A few notes:

> 1) I have used 32 bytes for the row tuple header overhead and 4 bytes =
> for index tuple overhead
> 2) The attribute types 26,27,28,29 are oid,tid,xid,cid - which, I =
> believe are already counted in the row overhead

You should not do it that way, because those are perfectly valid
datatypes for user columns.  Instead of the type test, check for
attnum > 0.  The "system columns" that represent row overhead items
have attnum < 0.  You might want to consider ignoring columns where
attisdropped, too, though this is a bit of a judgment call since a
dropped column might still be eating storage space.

Another thing you could do is left-join to pg_stats and use ANALYZE's
estimate of average column width where available, instead of hardwired
guesses.

Another important point is that this calculation is ignoring TOAST
space ... do you have any columns wide enough to get toasted?

> 1) I have found the 32 bytes overhead mentioned in a few places, but =
> have not seen any specific reference to the byte overhead of an index =
> header row.  Does know the best number to use here for an assumption?

12 bytes (8-byte header + 4-byte line pointer).

> 3) Has anyone solved this entire problem in another fashion (e.g. =
> auto-pruning - only delete what's necessary to stay within limits).

Have you looked at contrib/pgstattuple?

			regards, tom lane



view thread (9+ messages)  latest in thread

Message-ID: <25689.1139974420@sss.pgh.pa.us>
Permalink:  ../25689.1139974420@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25689.1139974420@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mliberman@goldpocket.com
  Subject: Re: Feedback on auto-pruning approach
  In-Reply-To: <25689.1139974420@sss.pgh.pa.us>
  References: <9D938282F8C6EE43B748B910386DE93E0138B3F5@srvgpimail1.GPI.local> <25689.1139974420@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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