Re: How to free disk space

Hi:

My database is growing fast taking too much disk space. How can I free disk
space without performing a VACCUM FULL? It locks the database for several
hours, and that is not a solution.

I guess a backup-restore would do the work but, isn't there a better way to
do this without shutting down postgres?

Thanks in advandce.

Greetings!

The database we install at our customers as part of our product includes
an event_history table. For some reason lost in the mists of time, the
most important field in that table, the description, is a varchar field
specified to be only 64 characters long. This leads me to a more
fundamental question: why specify the length of a varchar field at all?
Is there a big difference between the amount of disk space taken up by
"abc" stored in a varchar(64) field and stored in a varchar field? How
much space does an unspecified-length varchar field take up? Are there
other reasons to use varchar(64) instead of varchar?

Thank you very much!

RobR

After run VACUUM, you must run REINDEXDB to decrease indexes.

You can pg_resetxlog too, but you need restart server to do that.

2008/10/21 Ruben Blanco <rubenblan(at)gmail(dot)com>:
> Hi:
>
> My database is growing fast taking too much disk space. How can I free disk
> space without performing a VACCUM FULL? It locks the database for several
> hours, and that is not a solution.
>
> I guess a backup-restore would do the work but, isn't there a better way to
> do this without shutting down postgres?
>
> Thanks in advandce.
>
>

--
Emanuel Calvo Franco
Syscope Postgresql DBA
BaPUG Member

Well, I would guess that whoever designed the DB structure was used to
non-Postgres databases. First see

http://www.postgresql.org/docs/8.3/static/datatype-character.html

for the tip in Para. 7 on that page.

Most Data Bases DO require much more effort (i.e. don't run as fast)
if you use unlimited size data fields but Postgres is different.

If either an old version of Postgres was the target for the original
design or the designer had not read the above item then the result is
varchar(nn) being used where for a pure Postgres system a TEXT (or varchar)
field is a better design.

----- Original Message -----
From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: pgsql-general(at)postgresql(dot)org
Sent: Tuesday, October 21, 2008 8:07:31 AM GMT -05:00 US/Canada Eastern
Subject: [GENERAL] Varchar vs varchar(64)

Greetings!

The database we install at our customers as part of our product includes an event_history table. For some reason lost in the mists of time, the most important field in that table, the description, is a varchar field specified to be only 64 characters long. This leads me to a more fundamental question: why specify the length of a varchar field at all? Is there a big difference between the amount of disk space taken up by "abc" stored in a varchar(64) field and stored in a varchar field? How much space does an unspecified-length varchar field take up? Are there other reasons to use varchar(64) instead of varchar?

Thank you very much!

RobR

--
This email, and any files transmitted with it, is confidential
and intended solely for the use of the individual or entity to
whom they are addressed. If you have received this email in error,
please advise postmaster(at)mdtsoft(dot)com <mailto:postmaster(at)mdtsoft(dot)com>.

New MDT Software Headquarters (As of July 1, 2008):
3480 Preston Ridge Road
Suite 450
Alpharetta, GA 30005

Philip W. Dalrymple III <pwd(at)mdtsoft(dot)com>
MDT Software - The Change Management Company
+1 678 297 1001
Fax +1 678 297 1003

On Tue, 2008-10-21 at 11:59 +0100, Ruben Blanco wrote:
> My database is growing fast taking too much disk space.

Are you running regular vacuum?
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

On 21/10/2008 11:59, Ruben Blanco wrote:

> My database is growing fast taking too much disk space. How can I free
> disk space without performing a VACCUM FULL? It locks the database for
> several hours, and that is not a solution.

You shouldn't need to do VACUUM FULL - plain VACUUM should do the job.
Have a trawl through the archives, as this topic comes up regularly.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Rob Richardson wrote:
> The database we install at our customers as part of our
> product includes an event_history table. For some reason
> lost in the mists of time, the most important field in that
> table, the description, is a varchar field specified to be
> only 64 characters long. This leads me to a more fundamental
> question: why specify the length of a varchar field at all?
> Is there a big difference between the amount of disk space
> taken up by "abc" stored in a varchar(64) field and stored in
> a varchar field? How much space does an unspecified-length
> varchar field take up? Are there other reasons to use
> varchar(64) instead of varchar?

You can't have "varchar" without a length in parentheses,
as far as I know.

But you can use "text" which is essentially the same thing.

I can think of two reasons to use varchar(n) instead of text:
- you deliberately want to limit the amount of characters.
- you want to index the column (indexes have a maximum row size).

It is not a performance issue, however, and there is no
wasted space either.

Yours,
Laurenz Albe

On Tuesday 21 October 2008 09:00:30 postgres Emanuel CALVO FRANCO wrote:
> After run VACUUM, you must run REINDEXDB to decrease indexes.
>

This is probably overkill, as you won't need to do this for a lot of tables in
your database, and the locking issues are probably unhelpful.

> You can pg_resetxlog too, but you need restart server to do that.
>

No No No!!! You should never ever ever run pg_resetxlog on a production
machine!! I'm not sure where you got this idea, but it is a bad one to be
sure!

> 2008/10/21 Ruben Blanco <rubenblan(at)gmail(dot)com>:
> > Hi:
> >
> > My database is growing fast taking too much disk space. How can I free
> > disk space without performing a VACCUM FULL? It locks the database for
> > several hours, and that is not a solution.
> >
> > I guess a backup-restore would do the work but, isn't there a better way
> > to do this without shutting down postgres?
> >
> > Thanks in advandce.
>
> --
> Emanuel Calvo Franco
> Syscope Postgresql DBA
> BaPUG Member

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> Rob Richardson wrote:
>> Are there other reasons to use
>> varchar(64) instead of varchar?

> You can't have "varchar" without a length in parentheses,
> as far as I know.

That's what the spec says and that's what some other implementations
require, but not Postgres. We treat varchar without a length as pretty
much just an alias for text.

regards, tom lane

On Tue, Oct 21, 2008 at 10:46 AM, Robert Treat
<xzilla(at)users(dot)sourceforge(dot)net> wrote:
> On Tuesday 21 October 2008 09:00:30 postgres Emanuel CALVO FRANCO wrote:
>> After run VACUUM, you must run REINDEXDB to decrease indexes.
>>
>
> This is probably overkill, as you won't need to do this for a lot of tables in
> your database, and the locking issues are probably unhelpful.

Note, however, that if you have scheduled downtime and your db server
is fast enough, you can schedule a vacuum full / reindexdb during that
time. We had a rogue query that made a few of our largest tables very
very bloated (like 95% bloat) and we had some downtime to fix it and
it was way easier to just do the whole db with vacuum full verbose,
then reindex. I also had a chacne to set fill factors on some of the
tables that I'd wanted new fill factors on.

what's the version you're running ? you shouldn't run into that problem too
often with 8.3

just like guys said here, regular vacuum, and reindex once in a while.

On Wed, Oct 22, 2008 at 2:46 AM, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> wrote:
> what's the version you're running ? you shouldn't run into that problem too
> often with 8.3

I'm running 8.3.3 soon to be 8.3.4 or .5 if there's a bug fix due out.

There's only so much the autovacuum daemon can do when a rogue query
runs an update on a whole table several times in a row. The rogue
query has been eliminated from our app, but the damage was already
done.

--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis