VACUUM FULL doesn't reduce table size

pinker wrote:

I have deleted a large number of records from my_table, which originally had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that table
contains now only 241 rows and after rewriting it in classic way: CREATE
TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB.
What went wrong? And how can I remove those blocks?

Normally this happens when you have transactions that are older than the
delete, so the deleted rows cannot be removed by vacuum because that
transaction might still see them. If you close all old transactions,
vacuum full would be able to reclaim all the space. You might have
prepared transactions also, see select * from pg_prepared_xacts;

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Query output is empty...

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5840797.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pinker wrote:

Query output is empty...

I hope you read the whole paragraph, not just the last phrase.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Mar 6, 2015 at 5:59 AM, pinker <pinker@onet.eu> wrote:

I have deleted a large number of records from my_table, which originally
had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that
table

If your remaining records were in say, block 2 and block 10000, then the
blocks in between won't be returned to the system.

You could achieve your "fix" by running cluster on the table, which will
rewrite the table in-place. There are also scripts out there that do
in-place compaction by running targeted updates and vacuum commands to get
the empty pages at the end of the files truncated off the file.

Vick Khera wrote

On Fri, Mar 6, 2015 at 5:59 AM, pinker &lt;

pinker@

&gt; wrote:

I have deleted a large number of records from my_table, which originally
had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that
table

If your remaining records were in say, block 2 and block 10000, then the
blocks in between won't be returned to the system.

Really? This is vacuum full we are talking about. How would such a thing
occur?

The OP hasn't stated his version and I wouldn't assume 9.x

I have no experience here but given recent versions rewrite the table the
vacuum verbose output shown seems unusual.

David J.

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5840897.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G Johnston <david.g.johnston@gmail.com> writes:

I have no experience here but given recent versions rewrite the table the
vacuum verbose output shown seems unusual.

"vacuum verbose output shown?" There wasn't any.

[ digs about ... ]

Oh. The version of the message that nabble sent to the postgresql lists
was missing vital parts of what got posted at nabble: compare
/messages/by-id/1425639585904-5840782.post@n5.nabble.com
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-td5840782.html

I've seen that before. I'm about ready to propose that we flat out ban
messages from nabble to the PG lists; I'm tired of them wasting our time
with this sort of BS.

(FWIW, the output shown on nabble doesn't look materially different from
what I see in HEAD. It also proves positively that the OP has uncommitted
transactions blocking cleanup of known-dead rows. But we were just
guessing at that over here.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes I have. Environment I'm working on is production snapshot, so there is no
active transactions that could block those blocks from being removed...

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841085.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Vick Khera wrote

On Fri, Mar 6, 2015 at 5:59 AM, pinker &lt;

pinker@

&gt; wrote:

I have deleted a large number of records from my_table, which originally
had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that
table

If your remaining records were in say, block 2 and block 10000, then the
blocks in between won't be returned to the system.

You could achieve your "fix" by running cluster on the table, which will
rewrite the table in-place. There are also scripts out there that do
in-place compaction by running targeted updates and vacuum commands to get
the empty pages at the end of the files truncated off the file.

I have tried many combinations of VACUUM, VACUUM FULL and CLUSTER after
first failed VACUUM FULL and nothing works...

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841086.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/09/2015 04:22 AM, pinker wrote:

Yes I have. Environment I'm working on is production snapshot, so there is no
active transactions that could block those blocks from being removed...

Well the below from your original post would say different:

VACUUM FULL VERBOSE output:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 2989662 nonremovable row versions
in 36910 pages
DETAIL: 2989421 dead row versions cannot be removed yet.
CPU 1.10s/1.82u sec elapsed 9.46 sec.
Query returned successfully with no result in 9826 ms.

So some information is in order:

What version of Postgres are you using?

What do you mean by 'production snapshot' or more to the point how did
you get the snapshot?

What does select * from pg_stat_activity show?

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841085.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I did: select pg_cancel_backend(pid) from pg_stat_activity where usename <>
'mine';

and then tried again vacuum full:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in
37580 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 1.07s/1.56u sec elapsed 3.24 sec.
Query returned successfully with no result in 6436 ms.

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit

It was flash copy snapshot

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841110.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/09/2015 07:08 AM, pinker wrote:

I did: select pg_cancel_backend(pid) from pg_stat_activity where usename <>
'mine';

What makes you think that queries from usename = 'mine' are not important?

Or to get back to the original request:

What does select * from pg_stat_activity show?

Also did the queries actually get cancelled?

and then tried again vacuum full:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in
37580 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 1.07s/1.56u sec elapsed 3.24 sec.
Query returned successfully with no result in 6436 ms.

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit

It was flash copy snapshot

So what does that mean?

In other words detail the steps you took to get the snapshot.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver-4 wrote

On 03/09/2015 07:08 AM, pinker wrote:

I did: select pg_cancel_backend(pid) from pg_stat_activity where usename
<>
'mine';

What makes you think that queries from usename = 'mine' are not important?

Because on production I don't have access to this table.

Or to get back to the original request:

What does select * from pg_stat_activity show?

mainly idle connections and queries to tables in different schemas.
query select * from pg_stat_activity where query ilike '%my_table%' shows
nothing as well.

Also did the queries actually get cancelled?
Yes, but not all.

and then tried again vacuum full:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions
in
37580 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 1.07s/1.56u sec elapsed 3.24 sec.
Query returned successfully with no result in 6436 ms.

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit

It was flash copy snapshot

So what does that mean?

In other words detail the steps you took to get the snapshot.

I would like to know as well. Sysadmin team manage it, I'll ask them, but
as far I know it's matrix feature....

--
Adrian Klaver

adrian.klaver@

--
Sent via pgsql-general mailing list (

pgsql-general@

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/09/2015 07:37 AM, pinker wrote:

Adrian Klaver-4 wrote

On 03/09/2015 07:08 AM, pinker wrote:

I did: select pg_cancel_backend(pid) from pg_stat_activity where usename
<>
'mine';

What makes you think that queries from usename = 'mine' are not important?

Because on production I don't have access to this table.

Or to get back to the original request:

What does select * from pg_stat_activity show?

mainly idle connections and queries to tables in different schemas.
query select * from pg_stat_activity where query ilike '%my_table%' shows
nothing as well.

And there is no relation between this table and the tables or functions
being queried?

Also did the queries actually get cancelled?
Yes, but not all.

So what do you see in pg_stat_activity where state ilike 'idle in
transaction%'?

and then tried again vacuum full:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions
in
37580 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 1.07s/1.56u sec elapsed 3.24 sec.
Query returned successfully with no result in 6436 ms.

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit

It was flash copy snapshot

So what does that mean?

In other words detail the steps you took to get the snapshot.

I would like to know as well. Sysadmin team manage it, I'll ask them, but
as far I know it's matrix feature....

If snapshot is what I think it means, you might want to point them at:

http://www.postgresql.org/docs/9.3/interactive/backup-file.html

--
Adrian Klaver

adrian.klaver@

--
Sent via pgsql-general mailing list (

pgsql-general@

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

select * from pg_stat_activity where state ilike 'idle in transaction%' it's
empty.

And there is no relation between this table and the tables or functions

being queried?

no...

If snapshot is what I think it means, you might want to point them at:

http://www.postgresql.org/docs/9.3/interactive/backup-file.html

hmmm, maybe it's because WAL is in another tablespace?

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841123.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

and select txid_current_snapshot() - 1694632069:1694632069:

select distinct xmin::text::bigint from sms_actual_mapper order by 1 desc
1694595273

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841128.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pinker <pinker@onet.eu> wrote:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages
DETAIL: 0 dead row versions cannot be removed yet.

So there are no longer any dead rows being left behind, right?

Why are we still discussing this? Do you have some other question?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/09/2015 08:05 AM, pinker wrote:

select * from pg_stat_activity where state ilike 'idle in transaction%' it's
empty.

And there is no relation between this table and the tables or functions

being queried?

no...

If snapshot is what I think it means, you might want to point them at:

http://www.postgresql.org/docs/9.3/interactive/backup-file.html

hmmm, maybe it's because WAL is in another tablespace?

Well without information on how the 'snapshot' is being created it is
just speculation at the moment. I would say to move forward the
snapshot process needs to be spelled out.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/09/2015 08:49 AM, Kevin Grittner wrote:

pinker <pinker@onet.eu> wrote:

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages
DETAIL: 0 dead row versions cannot be removed yet.

So there are no longer any dead rows being left behind, right?

Why are we still discussing this? Do you have some other question?

Well from the original post:

"I have deleted a large number of records from my_table, which
originally had 288 MB. Then I ran vacuum full to make the table size
smaller. After this operation size of the table remains the same,
despite of the fact that table contains now only 241 rows and after
rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM
old_table - new_table size is 24kB. "

So I think the question remains how is 241 rows = 3043947 nonremovable
row versions? And that number is an increase from the original number
which was 2989662 nonremovable row versions.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

So there are no longer any dead rows being left behind, right?

Why are we still discussing this? Do you have some other question?

There are no dead rows, but postgres still cannot reuse the space because of
3043947 nonremovable row versions ..

INFO: vacuuming "my_table"
INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in
37580 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 2.67s/1.59u sec elapsed 7.71 sec.
Query returned successfully with no result in 8319 ms.

--
View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841137.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general