Thread: to drop a 30GB database. is it slow?

hi,

we have a database, which was not vacuumed for a looooong time.

right now it's size is 30GB. it only contains a simple table with 90rows.

it seems that it's so big because it was not vacuumed for a long time.

is this a reasonable assumption?

now we'd like to somehow 'compact' him.

it seems that a normal vacuum process does not recover the disk space.

there seems to be a "full" vacuum process, which can also recover the
'lost' space, but it blocks the whole postgresql db, so other processes
cannot read/write to it.

is this correct?

so, we're thinking about dropping the whole db, and recreate it (because
it only stores session data, so if they're lost, it's not THAT bad),
because this will be much faster.

am i correct to assume that if we drop it, postgresql recovers that 30GB
of disk space?

and, how long this step (the db-drop) usually take? is it's "speed"
comparable to a normal file-delete operation?

i'm only afraid that maybe if we issue the drop-db command, it will take
for example 30minutes...

thanks,
gabor

p.s: and all those questions like 'why didnt you vacuum it before' ...
it wasn't us. we took over this project just recently.

Gábor Farkas wrote:
> i'm only afraid that maybe if we issue the drop-db command, it will take
> for example 30minutes...

Wouldn't it be more effective to create a new table by selecting your
session table and switch their names? You can drop the troublesome table
afterwards, without influencing the availability of your database any
further.

This should minimize your downtime, I think - unless people have even
speedier solutions, of course.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//

Alban Hertroys wrote:
> Gábor Farkas wrote:
>
>> i'm only afraid that maybe if we issue the drop-db command, it will
>> take for example 30minutes...
>
>
> Wouldn't it be more effective to create a new table by selecting your
> session table and switch their names? You can drop the troublesome table
> afterwards, without influencing the availability of your database any
> further.
>
> This should minimize your downtime, I think - unless people have even
> speedier solutions, of course.
>

thanks, but what my fear is:

as i understand, this little db eats up 30GB of space (the real content
should be like 10MB), because it was not vacuumed for a long time.

but a normal vacuum does not recover disk space, it still keeps it.

we need to do a different vacuum that recovers the disk space, but for
that time the db will not respond.

so, what if simply dropping the table does not recover the disk-space?

thanks,
gabo

Gábor Farkas <gabor@nekomancer.net> writes:

> so, what if simply dropping the table does not recover the disk-space?

It will.  Each table is stored in its own set of disk files, and when
the table is dropped those files are simply deleted.

-Doug

On Sep 30, 2005, at 8:21 AM, Douglas McNaught wrote:

>> so, what if simply dropping the table does not recover the disk-space?
>
> It will.  Each table is stored in its own set of disk files, and when
> the table is dropped those files are simply deleted.

This is normally true. Vacuuming the database the table (or index) was
dropped from may free up disk space too.

Jim

-----
James Cradock, jcradock@me3.com

me3 Technology Consultants, LLC
24 Preble Street, 2nd Floor
Portland, ME 04101

207-772-3217 (office)
207-838-8678 (mobile)

www.me3.com