PostgreSQL Discussion - to drop a 30GB database. is it slow?

Author

to drop a 30GB database. is it slow?

GÃ¡bor Farkas

2005-09-30, 3:23 am

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Alban Hertroys

2005-09-30, 7:23 am

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//

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

GÃ¡bor Farkas

2005-09-30, 7:23 am

Alban Hertroys wrote:
> GÃ¡bor Farkas wrote:
>
>
>
> 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

---------------------------(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

Douglas McNaught

2005-09-30, 9:23 am

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

---------------------------(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

James Cradock

2005-09-30, 9:23 am

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

>
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links