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

[GENERAL] vacuum on streaming replication

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 against it as its in recovery mode. So how do update the stats and

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

[GENERAL] Vacuum and state_change

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 1; pid | backend_start |

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

[GENERAL] vacuum on table with all rows frozen

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 scan that table for any reason or does it unde

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

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

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 schemas, each with ~100-300 tables. Gen

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

[GENERAL] vacuum freeze in 96

Hi, 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. If I upgrade from 93 using pg_upgrade, that is re-using the data files, can it still do that? Or do I have to recreate the table? Thanks, Torsten

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.

[GENERAL] Vacuum Full - Questions

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 the size of the table? The table that I'm referring to is 150MB bi

R: [GENERAL] Vacuum full: alternatives?

Excellent Scott! Thank you! Francesco Da: Scott Marlowe [scott.marl...@gmail.com] Inviato: martedì 21 giugno 2016 2.06 A: Job Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Vacuum full: alternatives? On Mon, Jun 20, 2016 at 3:18 AM, Job wrote

R: R: [GENERAL] Vacuum full: alternatives?

hould be slower but free-marked space should be reused again? Thank you! Francesco Da: Jeff Janes [jeff.ja...@gmail.com] Inviato: lunedì 20 giugno 2016 17.51 A: Martín Marqués Cc: Melvin Davidson; Rakesh Kumar; Job; pgsql-general@postgresql.org Ogget

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: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 1:53 PM, Vik Fearing wrote: > On 20/06/16 17:25, Melvin Davidson wrote: > >>And you haven't read Vik's reply. :) > > > > Yes I have. Vacuum wll not lock all tables at once, only the ones it is > > currently working on, so the planner may have a slight delay, > > but it wil

Re: R: [GENERAL] Vacuum full: alternatives?

On 20/06/16 17:25, Melvin Davidson wrote: >>And you haven't read Vik's reply. :) > > Yes I have. Vacuum wll not lock all tables at once, only the ones it is > currently working on, so the planner may have a slight delay, > but it will not be gigantic. I think you should try it. > I have proposed

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: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 7:23 AM, Martín Marqués wrote: > El 20/06/16 a las 09:50, Melvin Davidson escribió: >> >> >>>but it won't let it grow too (or am I missing something). >> >> Yes, you are missing something. By partioning and {Vacuum Full only the >> table with data no longer needed}, the res

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: R: [GENERAL] Vacuum full: alternatives?

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) -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-gen

Re: R: [GENERAL] Vacuum full: alternatives?

El 20/06/16 a las 11:52, Jeff Janes escribió: > On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer > wrote: >> >> >> Am 20.06.2016 um 11:43 schrieb Job: >>> >>> Hi Andreas, >>> I would suggest run only autovacuum, and with time you will see a not more growing table. There is no need for

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 11:18 AM, Martín Marqués wrote: > El 20/06/16 a las 12:06, Melvin Davidson escribió: > > > > Martin and Vik, > > > >>...Think about a SELECT which has to scan all child tables. > > > > You are really digging for a corner case. > > If a scan has to scan all child tables, th

Re: R: [GENERAL] Vacuum full: alternatives?

El 20/06/16 a las 12:06, Melvin Davidson escribió: > > Martin and Vik, > >>...Think about a SELECT which has to scan all child tables. > > You are really digging for a corner case. > If a scan has to scan all child tables, then > A. it negates the ability to make partitions which are not used >

Re: R: [GENERAL] Vacuum full: alternatives?

2016-06-20 17:03 GMT+02:00 Scott Mead : > > > On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer < > andr...@a-kretschmer.de> wrote: > >> >> >> Am 20.06.2016 um 11:43 schrieb Job: >> >>> Hi Andreas, >>> >>> I would suggest run only autovacuum, and with time you will see a not more growing ta

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 11:03 AM, Scott Mead wrote: > > > On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer < > andr...@a-kretschmer.de> wrote: > >> >> >> Am 20.06.2016 um 11:43 schrieb Job: >> >>> Hi Andreas, >>> >>> I would suggest run only autovacuum, and with time you will see a not mo

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer wrote: > > > Am 20.06.2016 um 11:43 schrieb Job: > >> Hi Andreas, >> >> I would suggest run only autovacuum, and with time you will see a not >>> more growing table. There is no need for vacuum full. >>> >> So new record, when will be pg_bulkloa

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer wrote: > > > Am 20.06.2016 um 11:43 schrieb Job: >> >> Hi Andreas, >> >>> I would suggest run only autovacuum, and with time you will see a not >>> more growing table. There is no need for vacuum full. >> >> So new record, when will be pg_bulkloa

Re: R: [GENERAL] Vacuum full: alternatives?

2016-06-20 11:30 GMT-03:00 Vik Fearing : > On 20/06/16 16:23, Martín Marqués wrote: >> >> That's not entirely true. Think about a SELECT which has to scan all >> child tables. > > Or any SELECT on the parent at all. The planner needs to examine the > CHECK constraints on the children and can't do

Re: R: [GENERAL] Vacuum full: alternatives?

On 20.06.2016 17:30, Vik Fearing wrote: On 20/06/16 16:23, Martín Marqués wrote: El 20/06/16 a las 09:50, Melvin Davidson escribió: but it won't let it grow too (or am I missing something). Yes, you are missing something. By partioning and {Vacuum Full only the table with data no longer nee

Re: R: [GENERAL] Vacuum full: alternatives?

On 20/06/16 16:23, Martín Marqués wrote: > El 20/06/16 a las 09:50, Melvin Davidson escribió: >> >> >>> but it won't let it grow too (or am I missing something). >> >> Yes, you are missing something. By partioning and {Vacuum Full only the >> table with data no longer needed}, the rest of the data

Re: R: [GENERAL] Vacuum full: alternatives?

El 20/06/16 a las 09:50, Melvin Davidson escribió: > > >>but it won't let it grow too (or am I missing something). > > Yes, you are missing something. By partioning and {Vacuum Full only the > table with data no longer needed}, the rest of the data remains > available to the users > AND space is

Re: R: [GENERAL] Vacuum full: alternatives?

row too (or am I missing something). >> >> >> -- >> *From:* Job >> *To:* Rakesh Kumar ; " >> pgsql-general@postgresql.org" >> *Sent:* Monday, June 20, 2016 5:39 AM >> *Subject:* R: [GENERAL] Vacuum full: alternatives? >> >> Hi Rakes

Re: R: [GENERAL] Vacuum full: alternatives?

akesh Kumar ; " > pgsql-general@postgresql.org" > *Sent:* Monday, June 20, 2016 5:39 AM > *Subject:* R: [GENERAL] Vacuum full: alternatives? > > Hi Rakesh, > > if i do not free disk space, after some days disk can become full. > Everyday we

Re: R: [GENERAL] Vacuum full: alternatives?

But then autovaccum avoids this. Granted it won't give back free space to OS, but it won't let it grow too (or am I missing something). From: Job To: Rakesh Kumar ; "pgsql-general@postgresql.org" Sent: Monday, June 20, 2016 5:39 AM Subject: R: [GENERAL] Vacuu

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer wrote: > > > Am 20.06.2016 um 11:43 schrieb Job: > >> Hi Andreas, >> >> I would suggest run only autovacuum, and with time you will see a not >>> more growing table. There is no need for vacuum full. >>> >> So new record, when will be pg_bulkloa

Re: R: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:43 schrieb Job: Hi Andreas, I would suggest run only autovacuum, and with time you will see a not more growing table. There is no need for vacuum full. So new record, when will be pg_bulkloaded, will replace "marked-free" location? exactly, that's the task for vacuum

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, 2016-06-20 at 11:43 +0200, Job wrote: > Hi Andreas, > > >I would suggest run only autovacuum, and with time you will see a not > >more growing table. There is no need for vacuum full. > > So new record, when will be pg_bulkloaded, will replace "marked-free" > location? Yes, but you may

R: [GENERAL] Vacuum full: alternatives?

__ Da: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] per conto di Andreas Kretschmer [andr...@a-kretschmer.de] Inviato: lunedì 20 giugno 2016 11.37 A: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Vacuum full: alternatives? Am 20.06.2016 um 11:18 schrieb Job: >

R: [GENERAL] Vacuum full: alternatives?

[rakeshkumar46...@gmail.com] Inviato: lunedì 20 giugno 2016 11.34 A: pgsql-general@postgresql.org Oggetto: 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-ge

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 p

[GENERAL] Vacuum full: alternatives?

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 full . But the operation is very slow, some

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

[GENERAL] Vacuum full of parent without partitions possible?

Hello, 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 disk space cannot be rec

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

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

Hello All, In an attempt to offload some of the pressure off our master postgres node, We recently decided to start running reports off of our hot-standby. There is a desire for these reports to return fairly current data, so we have been monitoring the replication delay between the master -> st

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

[GENERAL] Vacuum never completed....

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 that disk IO graph kept cycling, lot

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

[GENERAL] vacuum - reclaiming disk space.

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 within the scheduled downtime. Any suggestions for reclaiming th

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

  1   2   3   4   5   6   7   8   9   >