pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How much space do database objects take up in data files
6+ messages / 5 participants
[nested] [flat]

* How much space do database objects take up in data files
@ 2008-02-13 18:43  Peter Kovacs <maxottovonstirlitz@gmail.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Peter Kovacs @ 2008-02-13 18:43 UTC (permalink / raw)
  To: pgsql-admin

Hi,

How can I find out how much space is taken up by database objects in data files?

Thanks
Peter



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How much space do database objects take up in data files
@ 2008-02-13 19:37  paul socha <pawel.socha@gmail.com>
  parent: Peter Kovacs <maxottovonstirlitz@gmail.com>
  1 sibling, 0 replies; 6+ messages in thread

From: paul socha @ 2008-02-13 19:37 UTC (permalink / raw)
  To: Peter Kovacs <maxottovonstirlitz@gmail.com>; +Cc: pgsql-admin

Hi

Im not sure ;]
but ...

http://www.postgresql.org/docs/8.3/static/functions-admin.html
and table 9-54

merlin=# select tablename, pg_size_pretty(pg_relation_size 
(tablename)) from pg_catalog.pg_tables where tableowner='merlin';;
  tablename | pg_size_pretty
-----------+----------------
  foo       | 8192 bytes
  ip        | 8192 bytes
(2 rows)



On 2008-02-13, at 19:43, Peter Kovacs wrote:

> Hi,
>
> How can I find out how much space is taken up by database objects  
> in data files?
>
> Thanks
> Peter


Pawel Socha
pawel.socha@gmail.com

perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`- 
{ a%%s%%$_%ee'




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How much space do database objects take up in data files
@ 2008-02-13 20:13  Carol Walter <walterc@indiana.edu>
  parent: Peter Kovacs <maxottovonstirlitz@gmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Carol Walter @ 2008-02-13 20:13 UTC (permalink / raw)
  To: pgsql-admin

Hi,

I've struggled with this one too.  You can get the size of databases  
with SELECT pg_database_size('database name');  You can get the size  
of tables with SELECT pg_relation_size('table name');

Carol
On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:

> Hi,
>
> How can I find out how much space is taken up by database objects  
> in data files?
>
> Thanks
> Peter
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How much space do database objects take up in data files
@ 2008-02-13 20:46  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Carol Walter <walterc@indiana.edu>
  0 siblings, 2 replies; 6+ messages in thread

From: Jeff Frost @ 2008-02-13 20:46 UTC (permalink / raw)
  To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin

On Wed, 13 Feb 2008, Carol Walter wrote:

> I've struggled with this one too.  You can get the size of databases with 
> SELECT pg_database_size('database name');  You can get the size of tables 
> with SELECT pg_relation_size('table name');
>
> Carol
> On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:
>
>> Hi,
>> 
>> How can I find out how much space is taken up by database objects in data 
>> files?

Give this query a try.  It's a variation of one posted on this list some time 
ago.  Unfortunately, I'm not sure who to credit for the original post.  This 
one takes into account index and toast size and sorts descending by totalsize.

SELECT nspname, relname,
     pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
FROM
(SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
         COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
                   FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
         CASE WHEN reltoastrelid=0 THEN 0
              ELSE pg_relation_size(reltoastrelid)
         END AS toastsize,
         CASE WHEN reltoastrelid=0 THEN 0
              ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
                                     WHERE ct.oid = cl.reltoastrelid))
         END AS toastindexsize
  FROM pg_class cl, pg_namespace ns
  WHERE cl.relnamespace = ns.oid
  AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
  AND  cl.relname IN
    (SELECT table_name FROM information_schema.tables
     WHERE table_type = 'BASE TABLE')) ss
     ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;



-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How much space do database objects take up in data files
@ 2008-02-13 21:05  Peter Kovacs <peter.kovacs.1.0rc@gmail.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Peter Kovacs @ 2008-02-13 21:05 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin

Howooow!!! Great!!!

Thanks a lot.
Peter

On Feb 13, 2008 9:46 PM, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Wed, 13 Feb 2008, Carol Walter wrote:
>
> > I've struggled with this one too.  You can get the size of databases with
> > SELECT pg_database_size('database name');  You can get the size of tables
> > with SELECT pg_relation_size('table name');
> >
> > Carol
> > On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:
> >
> >> Hi,
> >>
> >> How can I find out how much space is taken up by database objects in data
> >> files?
>
> Give this query a try.  It's a variation of one posted on this list some time
> ago.  Unfortunately, I'm not sure who to credit for the original post.  This
> one takes into account index and toast size and sorts descending by totalsize.
>
> SELECT nspname, relname,
>      pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
> FROM
> (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
>          COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
>                    FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
>          CASE WHEN reltoastrelid=0 THEN 0
>               ELSE pg_relation_size(reltoastrelid)
>          END AS toastsize,
>          CASE WHEN reltoastrelid=0 THEN 0
>               ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
>                                      WHERE ct.oid = cl.reltoastrelid))
>          END AS toastindexsize
>   FROM pg_class cl, pg_namespace ns
>   WHERE cl.relnamespace = ns.oid
>   AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
>   AND  cl.relname IN
>     (SELECT table_name FROM information_schema.tables
>      WHERE table_type = 'BASE TABLE')) ss
>      ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
>
>
>
> --
> Jeff Frost, Owner       <jeff@frostconsultingllc.com>
> Frost Consulting, LLC   http://www.frostconsultingllc.com/
> Phone: 650-780-7908     FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How much space do database objects take up in data files
@ 2008-02-13 23:15  Peter Kovacs <maxottovonstirlitz@gmail.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Peter Kovacs @ 2008-02-13 23:15 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin

Thanks, appears to work great!!
Peter

On Feb 13, 2008 9:46 PM, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Wed, 13 Feb 2008, Carol Walter wrote:
>
> > I've struggled with this one too.  You can get the size of databases with
> > SELECT pg_database_size('database name');  You can get the size of tables
> > with SELECT pg_relation_size('table name');
> >
> > Carol
> > On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:
> >
> >> Hi,
> >>
> >> How can I find out how much space is taken up by database objects in data
> >> files?
>
> Give this query a try.  It's a variation of one posted on this list some time
> ago.  Unfortunately, I'm not sure who to credit for the original post.  This
> one takes into account index and toast size and sorts descending by totalsize.
>
> SELECT nspname, relname,
>      pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
> FROM
> (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
>          COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
>                    FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
>          CASE WHEN reltoastrelid=0 THEN 0
>               ELSE pg_relation_size(reltoastrelid)
>          END AS toastsize,
>          CASE WHEN reltoastrelid=0 THEN 0
>               ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
>                                      WHERE ct.oid = cl.reltoastrelid))
>          END AS toastindexsize
>   FROM pg_class cl, pg_namespace ns
>   WHERE cl.relnamespace = ns.oid
>   AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
>   AND  cl.relname IN
>     (SELECT table_name FROM information_schema.tables
>      WHERE table_type = 'BASE TABLE')) ss
>      ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
>
>
>
> --
> Jeff Frost, Owner       <jeff@frostconsultingllc.com>
> Frost Consulting, LLC   http://www.frostconsultingllc.com/
> Phone: 650-780-7908     FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2008-02-13 23:15 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-13 18:43 How much space do database objects take up in data files Peter Kovacs <maxottovonstirlitz@gmail.com>
2008-02-13 19:37 ` paul socha <pawel.socha@gmail.com>
2008-02-13 20:13 ` Carol Walter <walterc@indiana.edu>
2008-02-13 20:46   ` Jeff Frost <jeff@frostconsultingllc.com>
2008-02-13 21:05     ` Peter Kovacs <peter.kovacs.1.0rc@gmail.com>
2008-02-13 23:15     ` Peter Kovacs <maxottovonstirlitz@gmail.com>

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