Re: [GENERAL] VACUUM FULL doesn't reduce table size
On 03/11/2015 08:52 AM, pinker wrote: Adrian Klaver-4 wrote Also per Kevin Grittner and Tom Lane there is a Nabble issue at work where the list here is not seeing all the information. Example: It's because I edited some post. Then is visible only on nabble because edited version isn't sent back to the mailing list. Per previous suggestions I would post to this(pgsql-general) list directly. Hidden information is not to going to be well received here and will impede an answer to your issue, just saying. Adrian Klaver-4 wrote Also per Kevin Grittner we are looking at a moving target, so some sort of information about current state would be helpful. I'm doing pg_dump from snapshot - on production that isn't possible. Today at night my colleague will run vacuum full verbose on this table in production environment, so we will see if this problem occurs only on snapshot or was replicated from production. Alright. -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841434.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
Adrian Klaver-4 wrote > Also per Kevin Grittner and Tom Lane there > is a Nabble issue at work where the list here is not seeing all the > information. Example: It's because I edited some post. Then is visible only on nabble because edited version isn't sent back to the mailing list. Adrian Klaver-4 wrote > Also per Kevin Grittner we are looking at a moving target, so some sort > of information about current state would be helpful. I'm doing pg_dump from snapshot - on production that isn't possible. Today at night my colleague will run vacuum full verbose on this table in production environment, so we will see if this problem occurs only on snapshot or was replicated from production. -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841434.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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
On 03/09/2015 09:19 AM, Joshua D. Drake wrote: On 03/09/2015 08:57 AM, Adrian Klaver wrote: On 03/09/2015 08:49 AM, Kevin Grittner wrote: pinker 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. TGL has answered this before: http://www.postgresql.org/message-id/14512.1282137...@sss.pgh.pa.us There are a number of things that can cause this but they are all about making sure that all versions of the tuple are completely and utterly of no use before vacuum will remove them. And that is what this thread is trying to determine. As others and I suspect, there is good reason to believe there is some sort of data corruption at work. This awaits a clearer understanding of what 'It was flash copy snapshot' means. Also per Kevin Grittner and Tom Lane there is a Nabble issue at work where the list here is not seeing all the information. Example: On this list I saw: "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" on Nabble I see: "> In other words detail the steps you took to get the snapshot. First, they call pg_start_backup, then flash copy is done for blocks, that were changed. Flash copy is made on another filesystem. If this matters - xmaxes for all rows are 0. " Also per Kevin Grittner we are looking at a moving target, so some sort of information about current state would be helpful. JD -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
Joshua D. Drake wrote: > On 03/09/2015 08:57 AM, Adrian Klaver wrote: >> On 03/09/2015 08:49 AM, Kevin Grittner wrote: >>> pinker wrote: 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." Initially the OP was reporting this, too: DETAIL: 2989421 dead row versions cannot be removed yet. Now that number is zero. >> 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. > > TGL has answered this before: > > http://www.postgresql.org/message-id/14512.1282137...@sss.pgh.pa.us No, that thread was about the same thing that this thread *started* with, which was that there were a large number of dead row versions which could not be removed yet. On *this* thread that was corrected by terminating long-running transactions. That number is now *zero*. AFAICS we have not seen the results of "SELECT count(*)" or any other information to show that there is still any problem after that was done. Maybe there is, but it has not yet been demonstrated. pinker: You can probably get to a solution to this much faster if you do two things: (1) Send emails directly to the pgsql-general@postgresql.org list, rather than going through nabble. Tom showed at least one case where nabble failed to pass along useful information to the list, and I have no idea how much other useful information it has not passed along. (2) Try to send enough information about the current state of the problem to allow diagnosis. It is best if you can create a reproducible test case (where you demonstrate the problem starting from an empty database, creating all the objects and loading all the data needed to show the problem). https://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- 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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
On 03/09/2015 08:57 AM, Adrian Klaver wrote: On 03/09/2015 08:49 AM, Kevin Grittner wrote: pinker 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. TGL has answered this before: http://www.postgresql.org/message-id/14512.1282137...@sss.pgh.pa.us There are a number of things that can cause this but they are all about making sure that all versions of the tuple are completely and utterly of no use before vacuum will remove them. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] VACUUM FULL doesn't reduce table size
On Mon, 9 Mar 2015 09:05:07 -0700 (MST) pinker wrote: > > > 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. Given your weird description of the "snapshot" I wouldn't be surprised if that instance of PostgreSQL had subtle corruption. pg_dump that database, re-init it and reload the dump. Then recreate the situation and see if the rows are sill nonremovable. I bet you $5.34 that everything works fine after that, which would indicate that the folks who made the snapshot didn't do it correctly. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] VACUUM FULL doesn't reduce table size
> 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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
On 03/09/2015 08:49 AM, Kevin Grittner wrote: pinker 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.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
pinker 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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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.kla...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
Vick Khera wrote > On Fri, Mar 6, 2015 at 5:59 AM, pinker < > 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 >> > > If your remaining records were in say, block 2 and block 1, 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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
David G Johnston 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 http://www.postgresql.org/message-id/1425639585904-5840782.p...@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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
Vick Khera wrote > On Fri, Mar 6, 2015 at 5:59 AM, pinker < > 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 >> > > If your remaining records were in say, block 2 and block 1, 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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
On Fri, Mar 6, 2015 at 5:59 AM, 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 > If your remaining records were in say, block 2 and block 1, 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.
Re: [GENERAL] VACUUM FULL doesn't reduce table size
pinker wrote: > Query output is empty... I hope you read the whole paragraph, not just the last phrase. -- Álvaro Herrerahttp://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
Re: [GENERAL] VACUUM FULL doesn't reduce table size
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
Re: [GENERAL] 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 Herrerahttp://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
[GENERAL] VACUUM FULL doesn't reduce table size
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? -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782.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