pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtable size is bigger than expected
3+ messages / 3 participants
[nested] [flat]
* table size is bigger than expected
@ 2011-08-04 18:56 Jian Shi <jshi@unitrends.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jian Shi @ 2011-08-04 18:56 UTC (permalink / raw)
To: pgsql-performance
Hey,
I'm a new user of PostgreSQL. I found one of my tables is taking unexpectedly large space:
select pg_size_pretty(pg_relation_size('archive_files'));
pg_size_pretty
----------------
1113 MB
The structure of this table is like:
Column | Type | Modifiers
------------+-------------------+-----------
archive_id | integer | not null
dir_no | integer | not null
fname | character varying | not null
type | smallint | not null
size | bigint | not null
mod_date | integer | not null
blocks | bigint | not null
blk_offset | bigint | not null
the field "fname" stores file names without any directory names. In our case, each record is expected to take around 300 bytes.
However, this table contains 934829 records, which means each record takes about 1.2KB.
I did vaccum, reindex, the size is still the same. Is there anything else that I can do?
Thanks!
John
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: table size is bigger than expected
@ 2011-08-04 20:17 Justin Pitts <justinpitts@gmail.com>
parent: Jian Shi <jshi@unitrends.com>
1 sibling, 0 replies; 3+ messages in thread
From: Justin Pitts @ 2011-08-04 20:17 UTC (permalink / raw)
To: Jian Shi <jshi@unitrends.com>; +Cc: pgsql-performance
On Thu, Aug 4, 2011 at 2:56 PM, Jian Shi <jshi@unitrends.com> wrote:
> Hey,
>
> I’m a new user of PostgreSQL. I found one of my tables is taking
> unexpectedly large space:
>
> select
> pg_size_pretty(pg_relation_size('archive_files'));
>
> pg_size_pretty
>
> ----------------
>
> 1113 MB
>
>
> the field “fname” stores file names without any directory names. In our
> case, each record is expected to take around 300 bytes.
>
> However, this table contains 934829 records, which means each record takes
> about 1.2KB.
>
what does this query yield?
select pg_size_pretty(sum(length(fname))) from archive_files;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: table size is bigger than expected
@ 2011-08-04 23:47 Steve Crawford <scrawford@pinpointresearch.com>
parent: Jian Shi <jshi@unitrends.com>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2011-08-04 23:47 UTC (permalink / raw)
To: Jian Shi <jshi@unitrends.com>; +Cc: pgsql-performance
On 08/04/2011 11:56 AM, Jian Shi wrote:
>
> Hey,
>
> I'm a new user of PostgreSQL. I found one of my tables is taking
> unexpectedly large space:...
>
> I did vaccum, reindex, the size is still the same. Is there anything
> else that I can do?
>
>
Did you try CLUSTER? A basic vacuum only identifies space as reusable,
it doesn't actually shrink on-disk size.
If you have workloads that update or delete a small number of tuples per
transaction, the autovacuum process should keep things reasonably under
control. But if you run transactions that do bulk updates or deletes,
you may need to intervene. The CLUSTER statement will completely rewrite
and reindex your table (and will physically reorder the table based on
the selected index). Note: CLUSTER requires an exclusive lock on the table.
Cheers,
Steve
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-08-04 23:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-04 18:56 table size is bigger than expected Jian Shi <jshi@unitrends.com>
2011-08-04 20:17 ` Justin Pitts <justinpitts@gmail.com>
2011-08-04 23:47 ` Steve Crawford <scrawford@pinpointresearch.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