Thread: truncating or full vacuuming

truncating or full vacuuming

From
Viktor Bojović
Date:
Hi,

while creating an index on billion records table i have canceled creation because index took me ~70GB of space.
When I looked into disk space i saw that space is still occupied , and the index doesn't exist.
I have started full vacuuming but it still runs after three days, so i wanted to ask if there is some other way to free that space.
Would it be better if i truncate table and insert all records again (costs ~1.5 day) or is there some other solution?

Thanx in advance
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me

Re: truncating or full vacuuming

From
Tom Lane
Date:
=?UTF-8?Q?Viktor_Bojovi=C4=87?= <viktor.bojovic@gmail.com> writes:
> while creating an index on billion records table i have canceled creation
> because index took me ~70GB of space.
> When I looked into disk space i saw that space is still occupied , and the
> index doesn't exist.

hmm ... was your version of "cancel" spelled "kill -9" or something like
that?  If so it's unsurprising that temp files would have been left
behind.  Look in the pgsql_tmp subdirectory.  It's also possible that
the partially-filled index files are still there but aren't linked to
by any live pg_class row.  Check for files that don't match any entry in
the pg_class.relfilenode column.  In both cases you'd have to remove any
such files by hand --- VACUUM is not going to fix this for you.

            regards, tom lane

Re: truncating or full vacuuming

From
Viktor Bojović
Date:


On Wed, Oct 20, 2010 at 4:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Viktor Bojović <viktor.bojovic@gmail.com> writes:
> while creating an index on billion records table i have canceled creation
> because index took me ~70GB of space.
> When I looked into disk space i saw that space is still occupied , and the
> index doesn't exist.

hmm ... was your version of "cancel" spelled "kill -9" or something like
that?  If so it's unsurprising that temp files would have been left
behind.  Look in the pgsql_tmp subdirectory.  It's also possible that
the partially-filled index files are still there but aren't linked to
by any live pg_class row.  Check for files that don't match any entry in
the pg_class.relfilenode column.  In both cases you'd have to remove any
such files by hand --- VACUUM is not going to fix this for you.

                       regards, tom lane

i have used Ctrl+C while i was in console.
I entered into that directory you have mentioned, but i have found no files inside.

postgres@zohar:/srv/postgresql/base$ du -h --max-depth=1
4.2M ./1
4.2M ./11510
4.3M ./11511
1.1G ./1044080708
0 ./pgsql_tmp
453G ./1051277744
454G .





--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me