Обсуждение: [GENERAL] PostgreSQL not reusing free space in table ?

Hi

Running PostgreSQL 9.4, I am running in the following issue.
On a huge table, I have to remove the content (set to '') of a column that
makes for 99% of the weight of the table. Since the production can not be
stopped, I will not be able to do a VACUUM FULL nor any direct rewrite of the
table, so I tried the following trick to drop the content while reducing the
table weight. (I re-indented it for this email)

ppr=500
for i in `seq 0 60` ; do
    psql $MYDB -c "UPDATE $TABLE
        SET content = ''::bytea
        WHERE ctid = ANY(ARRAY(
            SELECT ('(' || p.i || ',' || s.i || ')')::tid
            FROM generate_series(`$i * $ppr`, `($i + 1) * $ppr`) p(i),
                 generate_series(0, 2048) AS s(i)
        ));"
    psql $MYDB -c "VACUUM $TABLE;"
done

This reduced my table from 1200MB down to 240MB, 29000 pages, while I expected
the final result to be even lower. I did a copy of the table, and it needed
only 30MB, 3800 pages, so there was still something wrong.
I did a simple (and slow) query to get the number of rows per page on both
table. The new one had slightly more rows per page, but this was in no way
enough to justify such a gap. Then I saw that after page 2080 the table had,
for the following 27000 pages, between 0 and 5 rows per page. So vacuum could
not remove the pages because there were gaps.
I figured out that, after a vacuum, updating the rows at the end of the table
should be put back at the beginning of the free space and thus after an update
of the rows of the last 27000 pages the table could be reduced to the optimal
size.
But when I raw the following, I was very surprised (NB: I know this query does
a full scan, I won't do it on a 100GB table, I promise)

VACUUM VERBOSE $TABLE;

SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
=> 29 rows
UPDATE $TABLE SET content = ''::bytea WHERE ctid > '(29000,0)';
=> 29 rows updated
SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
==> 29 rows again !

So instead of filling huge gaps (I've got for instance an 80 pages gap, from
id 2082 to id 2160), Pg put the data back at the end of the table.


I'm quite puzzled by this situation. I believed PostgreSQL would use the free
space to put the new data, so my update loop would give me a clean, «packed»
table.

What behaviour did I miss here ? How can I get PostgreSQL to use that free
space without falling back to a vacuum full ? (And without using tools like
pg_repack if possible, because this seems like not needed in that situation)


Thanks

 Pierre

On Fri, Jan 6, 2017 at 4:09 AM, Pierre Ducroquet
<pierre.ducroquet@people-doc.com> wrote:
> Hi
>
> Running PostgreSQL 9.4, I am running in the following issue.
> On a huge table, I have to remove the content (set to '') of a column that
> makes for 99% of the weight of the table. Since the production can not be
> stopped, I will not be able to do a VACUUM FULL nor any direct rewrite of the
> table, so I tried the following trick to drop the content while reducing the
> table weight. (I re-indented it for this email)
>
> ppr=500
> for i in `seq 0 60` ; do
>         psql $MYDB -c "UPDATE $TABLE
>                 SET content = ''::bytea
>                 WHERE ctid = ANY(ARRAY(
>                         SELECT ('(' || p.i || ',' || s.i || ')')::tid
>                         FROM generate_series(`$i * $ppr`, `($i + 1) * $ppr`) p(i),
>                                  generate_series(0, 2048) AS s(i)
>                 ));"
>         psql $MYDB -c "VACUUM $TABLE;"
> done
>
> This reduced my table from 1200MB down to 240MB, 29000 pages, while I expected
> the final result to be even lower. I did a copy of the table, and it needed
> only 30MB, 3800 pages, so there was still something wrong.
> I did a simple (and slow) query to get the number of rows per page on both
> table. The new one had slightly more rows per page, but this was in no way
> enough to justify such a gap. Then I saw that after page 2080 the table had,
> for the following 27000 pages, between 0 and 5 rows per page. So vacuum could
> not remove the pages because there were gaps.
> I figured out that, after a vacuum, updating the rows at the end of the table
> should be put back at the beginning of the free space and thus after an update
> of the rows of the last 27000 pages the table could be reduced to the optimal
> size.
> But when I raw the following, I was very surprised (NB: I know this query does
> a full scan, I won't do it on a 100GB table, I promise)
>
> VACUUM VERBOSE $TABLE;
>
> SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
> => 29 rows
> UPDATE $TABLE SET content = ''::bytea WHERE ctid > '(29000,0)';
> => 29 rows updated
> SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
> ==> 29 rows again !
>
> So instead of filling huge gaps (I've got for instance an 80 pages gap, from
> id 2082 to id 2160), Pg put the data back at the end of the table.
>
>
> I'm quite puzzled by this situation. I believed PostgreSQL would use the free
> space to put the new data, so my update loop would give me a clean, «packed»
> table.
>
> What behaviour did I miss here ? How can I get PostgreSQL to use that free
> space without falling back to a vacuum full ? (And without using tools like
> pg_repack if possible, because this seems like not needed in that situation)

hm.  Maybe HOT is involved?  Does your column have an index on it?

merlin

On Friday, January 6, 2017 9:00:06 AM CET Merlin Moncure wrote:
> On Fri, Jan 6, 2017 at 4:09 AM, Pierre Ducroquet
>
> <pierre.ducroquet@people-doc.com> wrote:
> > Hi
> >
> > Running PostgreSQL 9.4, I am running in the following issue.
> > On a huge table, I have to remove the content (set to '') of a column that
> > makes for 99% of the weight of the table. Since the production can not be
> > stopped, I will not be able to do a VACUUM FULL nor any direct rewrite of
> > the table, so I tried the following trick to drop the content while
> > reducing the table weight. (I re-indented it for this email)
> >
> > ppr=500
> > for i in `seq 0 60` ; do
> >
> >         psql $MYDB -c "UPDATE $TABLE
> >
> >                 SET content = ''::bytea
> >                 WHERE ctid = ANY(ARRAY(
> >
> >                         SELECT ('(' || p.i || ',' || s.i || ')')::tid
> >                         FROM generate_series(`$i * $ppr`, `($i + 1) *
> >                         $ppr`) p(i),
> >
> >                                  generate_series(0, 2048) AS s(i)
> >
> >                 ));"
> >
> >         psql $MYDB -c "VACUUM $TABLE;"
> >
> > done
> >
> > This reduced my table from 1200MB down to 240MB, 29000 pages, while I
> > expected the final result to be even lower. I did a copy of the table,
> > and it needed only 30MB, 3800 pages, so there was still something wrong.
> > I did a simple (and slow) query to get the number of rows per page on both
> > table. The new one had slightly more rows per page, but this was in no way
> > enough to justify such a gap. Then I saw that after page 2080 the table
> > had, for the following 27000 pages, between 0 and 5 rows per page. So
> > vacuum could not remove the pages because there were gaps.
> > I figured out that, after a vacuum, updating the rows at the end of the
> > table should be put back at the beginning of the free space and thus
> > after an update of the rows of the last 27000 pages the table could be
> > reduced to the optimal size.
> > But when I raw the following, I was very surprised (NB: I know this query
> > does a full scan, I won't do it on a 100GB table, I promise)
> >
> > VACUUM VERBOSE $TABLE;
> >
> > SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
> > => 29 rows
> > UPDATE $TABLE SET content = ''::bytea WHERE ctid > '(29000,0)';
> > => 29 rows updated
> > SELECT count(*) FROM $TABLE WHERE ctid > '(29000,0)';
> > ==> 29 rows again !
> >
> > So instead of filling huge gaps (I've got for instance an 80 pages gap,
> > from id 2082 to id 2160), Pg put the data back at the end of the table.
> >
> >
> > I'm quite puzzled by this situation. I believed PostgreSQL would use the
> > free space to put the new data, so my update loop would give me a clean,
> > «packed» table.
> >
> > What behaviour did I miss here ? How can I get PostgreSQL to use that free
> > space without falling back to a vacuum full ? (And without using tools
> > like
> > pg_repack if possible, because this seems like not needed in that
> > situation)
> hm.  Maybe HOT is involved?  Does your column have an index on it?
>

No, the column is a non-indexed bytea.