Re: [GENERAL] Vacuuming tables with BRIN index and CLUSTER ON index

On Wed, Mar 29, 2017 at 4:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Mar 29, 2017 at 1:34 PM, Cherio wrote: > >> I have an insert/select only table (no update/delete expected) and a BRIN >> index on the timestamp column as follows >> >> CREATE TABLE

Re: [GENERAL] Vacuuming tables with BRIN index and CLUSTER ON index

On Wed, Mar 29, 2017 at 1:34 PM, Cherio wrote: > I have an insert/select only table (no update/delete expected) and a BRIN > index on the timestamp column as follows > > CREATE TABLE log_table ( > id BIGSERIAL NOT NULL, > data TEXT, > created_at TIMESTAMP WITHOUT TIME

[GENERAL] Vacuuming tables with BRIN index and CLUSTER ON index

I have an insert/select only table (no update/delete expected) and a BRIN index on the timestamp column as follows CREATE TABLE log_table ( id BIGSERIAL NOT NULL, data TEXT, created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() CONSTRAINT log_table__pk PRIMARY KEY(id) ); CREATE INDEX

Re: [GENERAL] Vacuuming strategy

On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: Why do you have a 4 minute timeout? That seems counter-productive. Oh, Is it less or more? I would not have timeouts on maintenance operations at all. To me a statement timeout is a last ditch method to

Re: [GENERAL] Vacuuming strategy

On Wed, Apr 30, 2014 at 10:45 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: Missed to answer this one: Is the 5GB for the table plus indexes, or just the table itself? No it's not including the the indices. Including indices it's actually 17GB!! Is it one particular index that is

Re: [GENERAL] Vacuuming strategy

On Wed, Apr 30, 2014 at 10:49 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: Each table has 4 indices. The updates are to the indexed columns. Here is the schema of a table: http://pastebin.com/ffu0dUjS All tables have this same schema except that some tables don't have a port column

Re: [GENERAL] Vacuuming strategy

Hi: On Wed, Apr 30, 2014 at 7:40 PM, Elanchezhiyan Elango elanela...@gmail.com wrote: Francisco, Thanks for the partitioning idea. I used to have the tables partitioned. But now that I have moved to a schema where data is split across about ~90 tables I have moved away from partitioning. But

Re: [GENERAL] Vacuuming strategy

On Wed, Apr 30, 2014 at 9:59 AM, Elanchezhiyan Elango elanela...@gmail.comwrote: Hi, I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables. Important and critical configuration is fillfactor. fillfactor will have a greater impact on

Re: [GENERAL] Vacuuming strategy

Hi: On Wed, Apr 30, 2014 at 1:59 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables. Tables1: Following is the query patterns on 4 high traffic table in my database: 1. Every 5

Re: [GENERAL] Vacuuming strategy

On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango elanela...@gmail.comwrote: Hi, I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables. Tables1: Following is the query patterns on 4 high traffic table in my database: 1. Every 5

Re: [GENERAL] Vacuuming strategy

Sergey, Thanks for the aggressive settings. I have listed some settings I am planning to try below. Please review and let me know your feedback. Francisco, Thanks for the partitioning idea. I used to have the tables partitioned. But now that I have moved to a schema where data is split across

Re: [GENERAL] Vacuuming strategy

Missed to answer this one: Is the 5GB for the table plus indexes, or just the table itself? No it's not including the the indices. Including indices it's actually 17GB!! On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: Sergey, Thanks for the aggressive

Re: [GENERAL] Vacuuming strategy

Each table has 4 indices. The updates are to the indexed columns. Here is the schema of a table: http://pastebin.com/ffu0dUjS All tables have this same schema except that some tables don't have a port column and so will have one less index What indexes exist? Are the updates to indexed

Re: [GENERAL] Vacuuming strategy

On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango elanela...@gmail.com wrote: log_autovacuum_min_duration = 0 autovacuum_vacuum_scale_factor = 0 autovacuum_vacuum_threshold = 4 autovacuum_analyze_scale_factor = 0 autovacuum_analyze_threshold = 4 I don't think it is a good idea

[GENERAL] Vacuuming strategy

Hi, I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables. Tables1: Following is the query patterns on 4 high traffic table in my database: 1. Every 5 minutes about 5 rows in the table are updated. And for a given clock hour the same

Re: [GENERAL] Vacuuming strategy

On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango elanela...@gmail.com wrote: [...] With the above query pattern with intensive updates and deletes, I need to do some aggressive vacuuming. Current strategy:I am running with default autovacuum settings (postgres 9.1.9) and I tried doing a

[GENERAL] vacuuming - doubt

Hi, Another theory question - PostgreSQL documentation says that - There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space I created a table, inserted 1000 records and deleted them. The size after a vacuum and a vacuum full are given - select

Re: [GENERAL] vacuuming - doubt

On Wed, Dec 11, 2013 at 1:08 AM, Jayadevan M maymala.jayade...@gmail.com wrote: Hi, Another theory question - PostgreSQL documentation says that - There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space I created a table, inserted 1000

Re: [GENERAL] vacuuming - doubt

Scott Marlowe-2 wrote 30 second vacuum lesson: Thank you. -- View this message in context: http://postgresql.1045698.n5.nabble.com/vacuuming-doubt-tp5782828p5783057.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list

[GENERAL] Vacuuming and reindexing tables that I don't own

We have some processes that have proved themselves to cause ridiculous amounts of table/index bloat above and beyond what autovac can manage. The good news is that this is very predictable in the application and cause by background processes that can afford to take a little extra time to clean

Re: [GENERAL] Vacuuming and reindexing tables that I don't own

Bill Moran wmo...@potentialtech.com writes: We have some processes that have proved themselves to cause ridiculous amounts of table/index bloat above and beyond what autovac can manage. Sure you can't fix that with some table-specific autovacuum settings? regards, tom

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 08:38:09PM +0200, Scott Marlowe wrote: OK. Assuming that the 50G is mostly dead space, there are a few possibilities that could be biting you here, but the most likely one is that your Free Space Map settings aren't high enough to include all the rows that have been

[GENERAL] Vacuuming on heavily changed databases

Hello, I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day - cca 620 000 inserts - 0 updates - cca 620 000 deletes The table is vacuumed daily, but somehow after several months I got to size

Re: [GENERAL] Vacuuming on heavily changed databases

Hello, I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day - cca 620 000 inserts - 0 updates - cca 620 000 deletes The table is vacuumed daily, but somehow after several months I got

Re: [GENERAL] Vacuuming on heavily changed databases

Apart from reinterating what someone else: you're not vacuuming anywhere near often often. Normal vacuum takes no locks. On Mon, May 19, 2008 at 04:50:18PM +0200, Bohdan Linda wrote: 3) There were suggestions (in archives) doing dump and then restore on dropped database, but still requires

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 04:59:42PM +0200, Harald Armin Massa wrote: do not vacuum DAILY. set up autovacuum to run AT LEAST every minute. autovacuum will flag the deleted rows as to be reusable by next insert. Make sure to use 8.3.latest, it's much more easy to setup autovacuum then before.

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 04:59:42PM +0200, Harald Armin Massa wrote: On Mon, May 19, 2008 at 04:50:18PM +0200, Bohdan Linda wrote: I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 06:21:18PM +0100, Sam Mason wrote: for t in foo bar baz do ( while echo VACUUM $t; false ; do true ; done | psql ) done oops, that false shouldn't be there! like like this: for t in foo bar baz do ( while echo VACUUM $t; ; do true ; done | psql )

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 8:50 AM, Bohdan Linda [EMAIL PROTECTED] wrote: Hello, I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day - cca 620 000 inserts - 0 updates - cca 620 000

Re: [GENERAL] Vacuuming on heavily changed databases

Hello I have some similar situation like Yours,we're using at the moment PG 8.2.0. At the moment we do manually vacuum (one or more times to minimize 'dead' data/tuples),and if necessary we do 'full' vacuum. On heavy-updated PG,one surely must think of this procedures because they are

Re: [GENERAL] Vacuuming on heavily changed databases

: Re: [GENERAL] Vacuuming on heavily changed databases Hello I have some similar situation like Yours,we're using at the moment PG 8.2.0. ...

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 5:44 PM, Dragan Zubac [EMAIL PROTECTED] wrote: Hello I have some similar situation like Yours,we're using at the moment PG 8.2.0. As Gregory above mentioned, update NOW to 8.2.7. It only takes minutes to do. At the moment we do manually vacuum (one or more times to

[GENERAL] Vacuuming Questions

We have two PostgreSQL servers (8.2) running in a cluster. We have autovacuum switched on on both servers and also we are running the following as a cron job; Server 1: 30 0,2,4,6,8,10,12,14,16,18,20,22 * * * /usr/bin/vacuumdb --all --analyze Server 2: 30 1,3,5,7,9,11,13,15,17,19,21,23 * * *

Re: [GENERAL] Vacuuming Questions

On Wed, 23 Apr 2008 16:27:33 +0100 John Gardner [EMAIL PROTECTED] wrote: We have two PostgreSQL servers (8.2) running in a cluster. Could you be a bit more specific about what you mean by: in a cluster? Now, we're not seeing any problems with performance and we're not seeing any bloat but I

Re: [GENERAL] Vacuuming Questions

On Wed, Apr 23, 2008 at 9:27 AM, John Gardner [EMAIL PROTECTED] wrote: We have two PostgreSQL servers (8.2) running in a cluster. We have autovacuum switched on on both servers and also we are running the following as a cron job; Server 1: 30 0,2,4,6,8,10,12,14,16,18,20,22 * * *

Re: [GENERAL] Vacuuming Questions

Joshua D. Drake wrote: On Wed, 23 Apr 2008 16:27:33 +0100 John Gardner [EMAIL PROTECTED] wrote: We have two PostgreSQL servers (8.2) running in a cluster. Could you be a bit more specific about what you mean by: in a cluster? Well, we're using middleware technology to load balance and

Re: [GENERAL] Vacuuming

On 5/8/07, Tom Lane [EMAIL PROTECTED] wrote: I forgot to mention that any other operation that examines every table row will fix all the hint bits as well. In particular a CREATE INDEX would do that --- I might be missing something, but I think CREATE INDEX work on SnapshotAny and hence

Re: [GENERAL] Vacuuming

Pavan Deolasee [EMAIL PROTECTED] writes: On 5/8/07, Tom Lane [EMAIL PROTECTED] wrote: I forgot to mention that any other operation that examines every table row will fix all the hint bits as well. In particular a CREATE INDEX would do that --- I might be missing something, but I think

Re: [GENERAL] Vacuuming

On 5/8/07, Tom Lane [EMAIL PROTECTED] wrote: SnapshotAny is a no-op, but HeapTupleSatisfiesVacuum isn't. Oh yes. My apologies for forgetting IndexBuildHeapScan() Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com

[GENERAL] Vacuuming

Is there any point to vacuuming a table if it has been bulk-populated by data after a truncate? I.e. If I do this: TRUNCATE TABLE vehicles; INSERT INTO vehicles (SELECT DISTINCT ON (dealer_id,vehicle_address) * FROM vehicles_temp_load WHERE (dealer_id,vehicle_address) is not null); Is there

Re: [GENERAL] Vacuuming

Paul Lambert [EMAIL PROTECTED] writes: Is there any point to vacuuming a table if it has been bulk-populated by data after a truncate? I.e. If I do this: TRUNCATE TABLE vehicles; INSERT INTO vehicles (SELECT DISTINCT ON (dealer_id,vehicle_address) * FROM vehicles_temp_load WHERE

Re: [GENERAL] Vacuuming

Tom Lane wrote: The only thing a vacuum would do for you there is set the commit hint bits on the newly-inserted rows. Which might be worth doing if you want to get the table into a totally clean state, but it's probably a bit excessive. SELECTs on the table will set the hint bits anyway as

Re: [GENERAL] Vacuuming

Paul Lambert [EMAIL PROTECTED] writes: Tom Lane wrote: What you *do* want to do in this situation is an ANALYZE. Should the ANALYZE be done before or after indexes are built? Or is that irrelevant? For ordinary indexes it doesn't matter. If you have any expression indexes then you should

Re: [GENERAL] Vacuuming

I wrote: Is there any point in vacuuming? The only thing a vacuum would do for you there is set the commit hint bits on the newly-inserted rows. Which might be worth doing if you want to get the table into a totally clean state, but it's probably a bit excessive. SELECTs on the table will

Re: [GENERAL] Vacuuming

Tom Lane wrote: I forgot to mention that any other operation that examines every table row will fix all the hint bits as well. In particular a CREATE INDEX would do that --- so if you are planning to create some indexes then there's certainly no point in a VACUUM just after a table load.

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Subject: Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes On Jun 15, 2006, at 1:16 PM, Florian G. Pflug wrote: Nitin Verma wrote: Were these bugs fixed by 7.3.2, if not what version should I look for? http://archives.postgresql.org/pgsql-admin/2001-06/msg5.php http

Re: [GENERAL] VACUUMing sometimes increasing database size /

Nitin Verma [EMAIL PROTECTED] writes: Will 7.3.2 Dump made up of copies using pg_dump import without any migration to 8.0+? What I need isn't a once process and will go as a automated script, in a way that user will not even get to know (if he isn't reading that logs) Database version

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Douglas McNaught wrote: Nitin Verma [EMAIL PROTECTED] writes: Will 7.3.2 Dump made up of copies using pg_dump import without any migration to 8.0+? What I need isn't a once process and will go as a automated script, in a way that user will not even get to know (if he isn't reading that logs)

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

be release by release? -Original Message- From: Florian G. Pflug [mailto:[EMAIL PROTECTED] Sent: Friday, June 16, 2006 4:48 PM To: Douglas McNaught Cc: Nitin Verma; Jim Nasby; pgsql-general@postgresql.org Subject: Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes Douglas

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Nitin Verma wrote: if your application depends on things that changed between 7.3 and 8.1. Postgres tends to become more strict with every release, so there are things you got away with in 7.3 which now cause an error message. Do we have change lists where I can see all the changes between

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Thanx so much which would really help -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, June 16, 2006 6:29 PM To: Nitin Verma Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes Nitin Verma wrote

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Subject: Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes Nitin Verma wrote: if your application depends on things that changed between 7.3 and 8.1. Postgres tends to become more strict with every release, so there are things you got away with in 7.3 which now cause

Re: [GENERAL] VACUUMing sometimes increasing database size /

In response to Nitin Verma [EMAIL PROTECTED]: $ ls -al pgsqldb/pg_xlog total 32816 drwx--2 nitinverma root 4096 Jun 16 19:53 . drwx--6 nitinverma root 4096 Jun 16 19:33 .. -rw---1 nitinverma root 16777216 Jun 16 20:08 0001 -rw---

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes crashing it

Is there a workaround for any of these? -Original Message- From: Nitin Verma Sent: Thursday, June 15, 2006 11:03 PM To: pgsql-general@postgresql.org Subject: VACUUMing sometimes increasing database size / sometimes crashing it Were these bugs fixed by 7.3.2, if not what version should

[GENERAL] VACUUMing sometimes increasing database size / sometimes crashing it

Were these bugs fixed by 7.3.2, if not what version should I look for? http://archives.postgresql.org/pgsql-admin/2001-06/msg5.php http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php It would be gr8 if can myself look into bug list next time before asking questions, any URL?

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

Nitin Verma wrote: Were these bugs fixed by 7.3.2, if not what version should I look for? http://archives.postgresql.org/pgsql-admin/2001-06/msg5.php http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php Ahm... 7.3.2 is *very* outdated. The current version of postgresql is

Re: [GENERAL] VACUUMing sometimes increasing database size / sometimes

On Jun 15, 2006, at 1:16 PM, Florian G. Pflug wrote: Nitin Verma wrote: Were these bugs fixed by 7.3.2, if not what version should I look for? http://archives.postgresql.org/pgsql-admin/2001-06/msg5.php http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php Ahm... 7.3.2 is

Re: [GENERAL] Vacuuming of indexes on tables.

elein [EMAIL PROTECTED] writes: Much later in the day, a vacuum analyze of the db showed that all of the indexes for that table required significant vacuuming, although the table did not. What do you mean by that exactly? If it's just that the index pages emptied by one VACUUM aren't

Re: [GENERAL] Vacuuming of indexes on tables.

On Tue, Apr 18, 2006 at 09:50:04AM -0400, Tom Lane wrote: elein [EMAIL PROTECTED] writes: Much later in the day, a vacuum analyze of the db showed that all of the indexes for that table required significant vacuuming, although the table did not. What do you mean by that exactly? If

Re: [GENERAL] Vacuuming of indexes on tables.

elein [EMAIL PROTECTED] writes: The order of events seems to be vacuum indexes and then vacuum the table. Wouldn't we get more bang if we vacuumed the table and then the indexes? No, the problem is that we can't recycle removed index pages until we are certain there are not any transactions

[GENERAL] Vacuuming of indexes on tables.

Running version 8.1.2, 1-2G RAM. Configuration set up to use available RAM. Running autovacuum. I have a table with 850 rows. The table gets only inserts (constantly). Once a day a range of the rows is deleted and an explicit vacuum analyze of the table is done. The table has 4 indexes.

[GENERAL] vacuuming strangeness

Hello all, I am currently experiencing some strange behaviour when vacuuming an active table. This table is constantly being updated by one process which gets a new connection every time it updates the table. There is a second process which is selecting from this table, also aquiring a new

Re: [GENERAL] vacuuming strangeness

Joe Maldonado [EMAIL PROTECTED] writes: The VACUUM process does not seem to be able to exit, instead it seems to be stuck in some strange loop for some time. Is this something to be expected? That trace looks perfectly normal. Try increasing vacuum_mem if you want fewer tuple-removal cycles.

[GENERAL] VACUUMING questions...

Hello all, I have a few somewhat simple questions Does the postmaster vacuum it's internal (pg_*) tables? if not what is the best way to vacuum them without having to vacuum the entire db? and how often is this recommended to be done? Thanks, -Joe ---(end of

Re: [GENERAL] VACUUMING questions...

Joe Maldonado wrote: Hello all, I have a few somewhat simple questions Does the postmaster vacuum it's internal (pg_*) tables? if not what is the best way to vacuum them without having to vacuum the entire db? and how often is this recommended to be done? No, and I'd vacuum full

Re: [GENERAL] VACUUMING questions...

Richard Huxton wrote: Joe Maldonado wrote: Hello all, I have a few somewhat simple questions Does the postmaster vacuum it's internal (pg_*) tables? if not what is the best way to vacuum them without having to vacuum the entire db? and how often is this recommended to be done? No, and

Re: [GENERAL] VACUUMING questions...

Matthew T. O'Connor wrote: Richard Huxton wrote: Joe Maldonado wrote: Hello all, I have a few somewhat simple questions Does the postmaster vacuum it's internal (pg_*) tables? if not what is the best way to vacuum them without having to vacuum the entire db? and how often is this

Re: [GENERAL] Vacuuming and already vacuumed table?

On Thu, 2003-08-28 at 16:53, Stephen Frost wrote: Would it make sense for vacuum to keep a list of 'last vacuumed' or similar so that it doesn't vacuum a table which hasn't changed since the last time it was vacuum'd? Seems to me that would be a pretty simple optimization which would

Re: [GENERAL] Vacuuming and already vacuumed table?

In the last exciting episode, [EMAIL PROTECTED] (Stephen Frost) wrote: Would it make sense for vacuum to keep a list of 'last vacuumed' or similar so that it doesn't vacuum a table which hasn't changed since the last time it was vacuum'd? Seems to me that would be a pretty simple

[GENERAL] Vacuuming unicode database

My Postgres databases used to have default (SQL_ASCII) encoding. I could store any 8-bit character in it regardless of actual charset, because all clients also used default encoding and no charset conversion was done. Now we started a new project with Qt3 and it's Postgres driver defaults to

[GENERAL] VACUUMing in general

Is any work being done to streamline/redesign the way PostgreSQL handles updates and/or the way VACUUM works so that it doesn't lock the entire table while it does it's job? It seems that tables that need to be VACUUM'ed the most would typically have fewer acceptable "windows" in which to

RE: [GENERAL] vacuuming

Every night I dump my database, and vacuum it afterwards. Why would you dump every night? That sounds quite painful :( Why should I want a backup when I never need it anyway???;-)) regards Rob