Re: [GENERAL] vacuum on streaming replication

Thanks On 31 July 2017 at 18:11, Chris Travers wrote: > > > On Mon, Jul 31, 2017 at 10:08 AM, Michael Paquier < > michael.paqu...@gmail.com> wrote: > >> On Mon, Jul 31, 2017 at 7:28 AM, Andreas Kretschmer >> wrote: >> > The standby is read only, vacuum runs on the master and replicated to >> th

Re: [GENERAL] vacuum on streaming replication

On Mon, Jul 31, 2017 at 10:08 AM, Michael Paquier wrote: > On Mon, Jul 31, 2017 at 7:28 AM, Andreas Kretschmer > wrote: > > The standby is read only, vacuum runs on the master and replicated to > the standby. Analyse as well. > > Please note as well that if hot_standby_feedback is enabled, the >

Re: [GENERAL] vacuum on streaming replication

On Mon, Jul 31, 2017 at 7:28 AM, Andreas Kretschmer wrote: > The standby is read only, vacuum runs on the master and replicated to the > standby. Analyse as well. Please note as well that if hot_standby_feedback is enabled, the cleanup done by VACUUM on the primary is influenced as well so as tu

Re: [GENERAL] vacuum on streaming replication

On 31 July 2017 04:15:33 GMT+02:00, Alex Samad wrote: >Hi > >setup a cluster, with streaming replication and hot stand by > >the idea is to use the stand by to do queries whilst the primary is >doing >inserts. > >But I noticed the stats on the stand by server don't update, nor can I >run >vacuum a

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver wrote: > > On 06/09/2017 02:26 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 02:01 PM, armand pirvu wrote: > On Jun 9, 2017, at 3:52 PM, Adrian Klaver > wrote: > > On 06/09/2017

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 02:26 PM, armand pirvu wrote: On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: On 06/09/2017 02:01 PM, armand pirvu wrote: On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: On 06/09/2017 01:31 PM, armand pirvu wrote: By temporary tables I mean just regular table not tab

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: > > On 06/09/2017 02:01 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 01:31 PM, armand pirvu wrote: >>> > > > >> By temporary tables I mean just regular table not tables created by "

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 02:01 PM, armand pirvu wrote: On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: On 06/09/2017 01:31 PM, armand pirvu wrote: By temporary tables I mean just regular table not tables created by "create temporary table" . I should have been more precise. We call them tempora

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: > > On 06/09/2017 01:31 PM, armand pirvu wrote: > >>> >>> Are these large tables? > > >> I would say yes >> select count(*) from csischema.tf_purchased_badge; >> 9380749 >> select count(*) from csischema.tf_purchases_person; >> 19902172 >>

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 01:31 PM, armand pirvu wrote: Are these large tables? I would say yes select count(*) from csischema.tf_purchased_badge; 9380749 select count(*) from csischema.tf_purchases_person; 19902172 select count(*) from csischema.tf_demographic_response_person; 80868561 selec

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:23 AM, Adrian Klaver wrote: > > On 06/09/2017 09:13 AM, armand pirvu wrote: >>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver >> > wrote: >>> >>> On 06/09/2017 08:45 AM, armand pirvu wrote: Hi Had a couple of processes blocking th

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 09:13 AM, armand pirvu wrote: On Jun 9, 2017, at 11:01 AM, Adrian Klaver > wrote: On 06/09/2017 08:45 AM, armand pirvu wrote: Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running t

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:01 AM, Adrian Klaver wrote: > > On 06/09/2017 08:45 AM, armand pirvu wrote: >> Hi >> Had a couple of processes blocking the vacuum so I terminated them using >> select pg_terminate_backend(pid); >> Running the following >> select distinct pid, backend_start, query_start,

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 08:45 AM, armand pirvu wrote: Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running the following select distinct pid, backend_start, query_start, state_change, state, query from pg_catalog.pg_stat_activity order by

Re: [GENERAL] vacuum on table with all rows frozen

2017-04-01 18:34 GMT+02:00 Adrian Klaver : > On 04/01/2017 09:09 AM, Tom DalPozzo wrote: > >> Hi, >> let's suppose I have a table which after beign populated with only >> INSERTs, doesn't receive no more writing queries (neither insert or >> update or delete). Only reading queries. >> Once all tab

Re: [GENERAL] vacuum on table with all rows frozen

On 04/01/2017 09:09 AM, Tom DalPozzo wrote: Hi, let's suppose I have a table which after beign populated with only INSERTs, doesn't receive no more writing queries (neither insert or update or delete). Only reading queries. Once all table rows get frozen by (auto)vacuum, will a next (auto)vacuum

Re: [GENERAL] vacuum of empty table slows down as database table count grows

Well, unfortunately i am not seeing much difference. I shaved off maybe a second of worst case run. I guess i should just split the db into smaller ones, since tmpstats are now per-db. Are there any other things i could try? 2017-01-05 8:18 GMT+01:00 marcin kowalski : > Thanks, i'll redo the ben

Re: [GENERAL] vacuum of empty table slows down as database table count grows

Thanks, i'll redo the benchmarks and report back how things look now. 2017-01-04 20:33 GMT+01:00 Pavel Stehule : > >>> > >>> > This is irrelevant of amount of data restored, i am seeing the same >>> behavior with just schema restore, as well as with schema+data restores. >>> > >>> > If anyone is

Re: [GENERAL] vacuum of empty table slows down as database table count grows

> > >> > >> > This is irrelevant of amount of data restored, i am seeing the same >> behavior with just schema restore, as well as with schema+data restores. >> > >> > If anyone is interested i may upload the schema data + my benchmarking >> script with collected whisper data from my test run (i've

Re: [GENERAL] vacuum of empty table slows down as database table count grows

2017-01-04 20:22 GMT+01:00 Jerry Sievers : > marcin kowalski writes: > > > I am experiencing an odd issue, i've noticed it on 9.3 , but i can > reproduce it on 9.6. > > > > Basically, i have a database with a lot of schemas, but not that much > data. Each schema is maybe 2-4 GB in size, and often

Re: [GENERAL] vacuum of empty table slows down as database table count grows

marcin kowalski writes: > I am experiencing an odd issue, i've noticed it on 9.3 , but i can reproduce > it on 9.6. > > Basically, i have a database with a lot of schemas, but not that much data. > Each schema is maybe 2-4 GB in size, and often much less than that. > > The database has ~300-500

Re: [GENERAL] vacuum freeze in 96

On Wed, Dec 14, 2016 at 5:59 AM, Michael Paquier wrote: > On Wed, Dec 14, 2016 at 5:00 AM, Torsten Förtsch > wrote: > > one of the major enhancements in 96 is skipping completely frozen pages > in > > vacuum freeze. I assume that requires a special bit on the page. > > The freeze map uses an add

Re: [GENERAL] vacuum freeze in 96

On Wed, Dec 14, 2016 at 5:00 AM, Torsten Förtsch wrote: > one of the major enhancements in 96 is skipping completely frozen pages in > vacuum freeze. I assume that requires a special bit on the page. The freeze map uses an additional bit in the vm, and pg_upgrade would take care of the conversion

Re: [GENERAL] Vacuum Full - Questions

Hi Patrick, I believe Vacuum full rebuilds the indexes automatically by default, as a new copy of the table is created. Because the indexes are new, no stats are available to the optimiser to make an informed decision about whether to utilise it or not, so it doesn't. Once the analyze is perform

Re: [GENERAL] Vacuum Full - Questions

On Thu, Sep 1, 2016 at 10:32 AM, Patrick B wrote: > > > 2016-09-01 11:53 GMT+12:00 Venkata B Nagothi : > >> >> On Thu, Sep 1, 2016 at 8:41 AM, Patrick B >> wrote: >> >>> Hi guys, >>> >>> A dev has ran a VACUUM FULL command into our test database running >>> PostgreSQL 9.5 (I know... goddamn)

Re: [GENERAL] Vacuum Full - Questions

2016-09-01 11:53 GMT+12:00 Venkata B Nagothi : > > On Thu, Sep 1, 2016 at 8:41 AM, Patrick B > wrote: > >> Hi guys, >> >> A dev has ran a VACUUM FULL command into our test database running >> PostgreSQL 9.5 (I know... goddamn)... >> >> ... after the Vacuum Full, some queries start using SEQ s

Re: [GENERAL] Vacuum Full - Questions

On Thu, Sep 1, 2016 at 8:41 AM, Patrick B wrote: > Hi guys, > > A dev has ran a VACUUM FULL command into our test database running > PostgreSQL 9.5 (I know... goddamn)... > > ... after the Vacuum Full, some queries start using SEQ scans instead of > indexes... > > Does that happen because of

Re: [GENERAL] Vacuum Full - Questions

Yes that very well could happen because the size of the table changed, as well as stats being more accurate now. Just because you have a seq scan doesn't mean the planer is making a bad choice.

Re: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 3:18 AM, Job wrote: > > Hello, > > we have a table with an heavy traffic of pg_bulkload and delete of records. > The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back. > > We have important problems on size and the only way to gain free space is > is

Re: [GENERAL] Vacuum full: alternatives?

On 6/20/2016 8:51 AM, David G. Johnston wrote: incorrect. in fact, an update is performed identically to an INSERT + DELETE(old) Except for heap-only-tuple optimization, right? We cannot build a HOT chain if the user requests a delete separately since their is no longer an associ

Re: [GENERAL] Vacuum full: alternatives?

On Monday, June 20, 2016, John R Pierce wrote: > On 6/20/2016 8:03 AM, Scott Mead wrote: > >> >> I believe that free space is only available to UPDATE, not INSERT. >> > > incorrect. in fact, an update is performed identically to an INSERT + > DELETE(old) > > Except for heap-only-tuple optimizat

Re: [GENERAL] Vacuum full: alternatives?

On 06/20/2016 03:18 AM, Job wrote: > Hello, > > we have a table with an heavy traffic of pg_bulkload and delete of records. > The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back. > > We have important problems on size and the only way to gain free space is > issueing a v

Re: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:18 schrieb Job: Hello, we have a table with an heavy traffic of pg_bulkload and delete of records. The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back. We have important problems on size and the only way to gain free space is issueing a vacuum ful

Re: [GENERAL] Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining constant at 4GB. From: Job To: "pgsql-general@postgresql.org" Sent: Monday, June 20, 2016 5:18 AM Subject: [GENERAL] Vacuum full: alternatives? Hello,  we have a table with an heavy traffic of pg_bulkload

Re: [GENERAL] Vacuum full of parent without partitions possible?

On Tue, May 3, 2016 at 3:22 PM, Tom Lane wrote: > Jan Keirse writes: > > I have a table that used to contain all data. > > because it grew too big I added a partition trigger a long time ago and > > since than all new data was added to small partitions. By now all data in > > the original parent

Re: [GENERAL] Vacuum full of parent without partitions possible?

I am thinking that you are not using all child tables all time ,so it may not be the best solution but if you don't want to lock your active processes, alternatively you can try to disconnect parent-child (which is not actively in use ) relation using no inherintence then rename old child table .

Re: [GENERAL] Vacuum full of parent without partitions possible?

Jan Keirse writes: > I have a table that used to contain all data. > because it grew too big I added a partition trigger a long time ago and > since than all new data was added to small partitions. By now all data in > the original parent table has become obsolete and was deleted, however the > di

Re: [GENERAL] Vacuum of large tables causing replication delays to hot standby

9.2.12 Sent from my iPhone > On Apr 30, 2016, at 12:37 PM, Tom Lane wrote: > > Jeff Mcdowell writes: >> 95% of the time, the delay is only microseconds. But we have discovered that >> whenever the master does an auto vacuum of a large table, the transaction >> replay delay can climb is high

Re: [GENERAL] Vacuum of large tables causing replication delays to hot standby

Tom Lane wrote: > Jeff Mcdowell writes: > > 95% of the time, the delay is only microseconds. But we have discovered > > that whenever the master does an auto vacuum of a large table, the > > transaction replay delay can climb is high as 1 hour. These delays don�t > > seem to correlate with any

Re: [GENERAL] Vacuum of large tables causing replication delays to hot standby

Jeff Mcdowell writes: > 95% of the time, the delay is only microseconds. But we have discovered that > whenever the master does an auto vacuum of a large table, the transaction > replay delay can climb is high as 1 hour. These delays don’t seem to > correlate with any particular queries that ar

Re: [GENERAL] Vacuum never completed....

btree. On 2016-04-19 06:53, Jeff Janes wrote: On Apr 19, 2016 6:37 AM, wrote: We had a server approach wraparound yesterday on a 9.3 box. I restarted in single user mode and only one table was above autovacuum_freeze_max_age. The dataset was ~750G. We left vacuum running for 21hrs and it sti

Re: [GENERAL] Vacuum never completed....

On Apr 19, 2016 6:37 AM, wrote: > > We had a server approach wraparound yesterday on a 9.3 box. I restarted in single user mode and only one table was above autovacuum_freeze_max_age. The dataset was ~750G. We left vacuum running for 21hrs and it still did not complete. What was interesting is tha

Re: [GENERAL] vacuum - reclaiming disk space.

Autovacuum will eventually free your extra pages regarding index bloat but it takes multiple runs. You could also use reindex instead of vacuum full since you are only interested in the index. For the table there may be other options but they depend on your pattern of writes. On Thu, Mar 17, 201

Re: [GENERAL] vacuum - reclaiming disk space.

On Thu, Mar 17, 2016 at 10:57 AM, bricklen wrote: > On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell > wrote: > >> I have a large table with numerous indexes which has approximately >> doubled in size after adding a column - every row was rewritten and 50% of >> the tuples are dead. I'd like to

Re: [GENERAL] vacuum - reclaiming disk space.

On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell wrote: > I have a large table with numerous indexes which has approximately doubled > in size after adding a column - every row was rewritten and 50% of the > tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot > seem to finish

Re: [GENERAL] vacuum - reclaiming disk space.

Just to throw some extreme ideas out there, you could stand up a postgres on some other server, pg_dump your current database and use that dump to build up your second postgres. Use that new postgres when your system goes live again after downtime. Restoring from a dump means your database would no

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 bac

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 Kl

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 ver

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 discuss

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 l

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 nonr

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

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?

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: h

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

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

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/intera

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

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. >

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

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.

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

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, desp

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 Postgr

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 mis

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, desp

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 re

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@post

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

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

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, Feb 3, 2015 at 3:12 PM, Bill Moran wrote: > On Tue, 3 Feb 2015 14:48:17 -0500 > Adam Hooper wrote: > >> It's doable for us to VACUUM FULL and add a notice to our website >> saying, "you can't upload files for the next two hours." Maybe that's >> a better idea? > > It's really going to dep

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, 3 Feb 2015 14:48:17 -0500 Adam Hooper wrote: > On Tue, Feb 3, 2015 at 2:29 PM, Bill Moran wrote: > > On Tue, 3 Feb 2015 14:17:03 -0500 > > Adam Hooper wrote: > > > > My recommendation here would be to use Slony to replicate the data to a > > new server, then switch to the new server onc

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, Feb 3, 2015 at 2:29 PM, Bill Moran wrote: > On Tue, 3 Feb 2015 14:17:03 -0500 > Adam Hooper wrote: > > My recommendation here would be to use Slony to replicate the data to a > new server, then switch to the new server once the data has synchornized. Looks exciting. But then I notice: "S

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, 3 Feb 2015 14:17:03 -0500 Adam Hooper wrote: > On Tue, Feb 3, 2015 at 12:58 PM, Bill Moran wrote: > > On Tue, 3 Feb 2015 10:53:11 -0500 > > Adam Hooper wrote: > > > >> This plan won't work: Step 2 will be too slow because pg_largeobject > >> still takes 266GB. We tested `VACUUM FULL pg_

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, Feb 3, 2015 at 12:58 PM, Bill Moran wrote: > On Tue, 3 Feb 2015 10:53:11 -0500 > Adam Hooper wrote: > >> This plan won't work: Step 2 will be too slow because pg_largeobject >> still takes 266GB. We tested `VACUUM FULL pg_largeobject` on our >> staging database: it took two hours, during

Re: [GENERAL] VACUUM FULL pg_largeobject without (much) downtime?

On Tue, 3 Feb 2015 10:53:11 -0500 Adam Hooper wrote: > Hi list, > > We run a website. We once stored all sorts of files in pg_largeobject, > which grew to 266GB. This is on an m1.large on Amazon EC2 on a single, > magnetic, non-provisioned-IO volume. In that context, 266GB is a lot. > > We've s

Re: [GENERAL] vacuum full post 9.0 - reindex needed?

Hi, 2015-01-28 21:39 GMT+01:00 Anand Kumar, Karthik < karthik.anandku...@classmates.com>: > I haven’t been able to find clear information online about what happens > with existing indexes on a postgres post-9.0 database (we specifically use > 9.3) after a vacuum full. > > There is a lot of info

Re: [GENERAL] vacuum vs pg_repack vs pg_reorg

Hi Mr Kevin, Thanks for the suggestion, VACUUM analyze is a much simpler solution compared to rest. Thanks once more. Regards Ramachandran S -- View this message in context: http://postgresql.nabble.com/vacuum-vs-pg-repack-vs-pg-reorg-tp5832261p5832499.html Sent from the PostgreSQL - genera

Re: [GENERAL] vacuum vs pg_repack vs pg_reorg

sramay wrote: > I have a database of size 1.5 TB. The attachments are stored in bytea. > The attachment table is consuming maximum space. The database version is > 9.1.x and Streaming Replication is set. Now, I have to removed old records > to make way for new records without increasing SAN

Re: [GENERAL] Vacuum freeze

On Thu, Dec 4, 2014 at 9:02 AM, Mike Blackwell wrote: > check_postgres.pl (--action=autovac_freeze) recently complained that we > needed to run VACUUM FREEZE. Doing so generated a boatload of WAL files - > perhaps on the order of the of the database itself. > > Is VACUUM FREEZE something that is

Re: [GENERAL] Vacuum freeze

On Thu, Dec 4, 2014 at 12:02 PM, Mike Blackwell wrote: > check_postgres.pl (--action=autovac_freeze) recently complained that we > needed to run VACUUM FREEZE. Doing so generated a boatload of WAL files - > perhaps on the order of the of the database itself. > > Is VACUUM FREEZE something that is

Re: [GENERAL] Vacuum freeze

I don't think autovacuum can predict you will not update your table(s) anymore, so there's no way to know to run FREEZE on it. On Thu, Dec 4, 2014 at 12:02 PM, Mike Blackwell wrote: > check_postgres.pl (--action=autovac_freeze) recently complained that we > needed to run VACUUM FREEZE. Doing so

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

On Wed, Jan 15, 2014 at 05:37:27PM -0800, Lists wrote: > it's clearing out the cruft that results from creating temp tables, > loading a bunch of data, then dropping the table, either explicitly > or when the connection is terminated. This causes PG disk usage to > climb without causing any change

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

On 1/15/2014 5:37 PM, Lists wrote: it's clearing out the cruft that results from creating temp tables, loading a bunch of data, then dropping the table, there shoudl be zero cruft. when the table is dropped, it should pooferate. -- john r pierce 37N 122W

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

Lists writes: > The process(es) creating the temp tables are not persistent, so the > issue isn't trying to clean up bloat from a long running process, it's > clearing out the cruft that results from creating temp tables, loading a > bunch of data, then dropping the table, either explicitly or

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

On 01/15/2014 04:24 PM, Tom Lane wrote: Lists writes: Our app makes extensive use of temp tables, and this causes a significant amount of bloat that can often only be cleared with a manual vacuum process. We're looking for a better way that doesn't involve locking, we found pg_repack and pg_reo

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

On 1/15/2014 4:09 PM, Lists wrote: Our app makes extensive use of temp tables, and this causes a significant amount of bloat that can often only be cleared with a manual vacuum process. whats the persistence of these temporary tables?by design, they are meant for relatively short lifespan

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

On Wed, Jan 15, 2014 at 04:09:28PM -0800, Lists wrote: > Our app makes extensive use of temp tables, and this causes a > significant amount of bloat that can often only be cleared with a Note what Tom Lane said, but why do you have bloat that can only be cleared by vacuum? Why not drop them or wh

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

Lists writes: > Our app makes extensive use of temp tables, and this causes a > significant amount of bloat that can often only be cleared with a manual > vacuum process. We're looking for a better way that doesn't involve > locking, we found pg_repack and pg_reorg and were wondering if anybod

Re: [GENERAL] VACUUM FULL freezes

On 05/22/2013 08:49 AM, RDNikeAir wrote: > I have a database that is on a RAID5 machine that is almost out of memory > (277GB of 330GB used). I have deleted some data and run the VACUUM FULL > command, but after a few hours gave me the error message "Server closed the > connection unexpectedly. Th

Re: [GENERAL] VACUUM FULL freezes

RDNikeAir wrote: > Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last > message i have in messages.* is from a few days ago. So there are no recent > entries that i can look at. Well, it is clear then that the first thing you need to do is fix your logging. -- Álvaro H

Re: [GENERAL] VACUUM FULL freezes

Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last message i have in messages.* is from a few days ago. So there are no recent entries that i can look at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477p5756484.ht

Re: [GENERAL] VACUUM FULL freezes

On Wed, May 22, 2013 at 8:49 AM, RDNikeAir wrote: > I have a database that is on a RAID5 machine that is almost out of memory > (277GB of 330GB used). I have deleted some data and run the VACUUM FULL > command, but after a few hours gave me the error message "Server closed the > connection unexpe

Re: [GENERAL] Vacuum problem

connection open takes time. I can almost 100% reduce it when my database size is 30 MB only with 1.5 bloating. > Date: Tue, 14 May 2013 10:54:04 -0600 > Subject: Re: [GENERAL] Vacuum problem > From: scott.marl...@gmail.com > To: msq...@live.com > CC: pgsql-general@postgresql.org >

Re: [GENERAL] Vacuum problem

Not saying you HAVE bloating there, saying you MIGHT. -- 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 problem

I am doing regular insertion/deletion on the same tables .. and running vacuum in an hour... I suspect there is bloating in my tables.. but how does bloating in pgcatalog is happening is not clear...> Normally vacuum full is NOT required on a regular basis. However, if > you did something like

Re: [GENERAL] Vacuum problem

On Tue, May 14, 2013 at 7:27 AM, S H wrote: >> I wonder if you've got a bloated pg catalog then. Certainly sounds >> like it's a possibility. >> So other than vacuuming when you recreate this, is the server working >> hard? What is vacuum vacuuming when this happens (pg_stat_activity >> should sho

Re: [GENERAL] Vacuum problem

> I wonder if you've got a bloated pg catalog then. Certainly sounds > like it's a possibility. > So other than vacuuming when you recreate this, is the server working > hard? What is vacuum vacuuming when this happens (pg_stat_activity > should show that) Does vacuum full is required to avoid bloa

Re: [GENERAL] Vacuum problem

On 5/13/2013 7:10 PM, S H wrote: My disk is utilized by many other components, thus do we have minimum recommendation my postgres to have sufficient speed. Current perfomance of my disk is around 1-5MB/sec. Is it sufficient? how are you measuring this? thats painfully slow by today's standa

  1   2   3   4   5   6   7   >