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 log_table (
>> i
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 ZONE DEFAULT now()
>
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 log_t
On Wed, Apr 30, 2014 at 10:49 AM, Elanchezhiyan Elango 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 and
>> so wil
On Wed, Apr 30, 2014 at 10:45 AM, Elanchezhiyan Elango 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 bloated?
Che
On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango 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 deal with a rec
On Wed, Apr 30, 2014 at 9:59 AM, Elanchezhiyan Elango
wrote:
> 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 VACUUMING s
Hi:
On Wed, Apr 30, 2014 at 7:40 PM, Elanchezhiyan Elango
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 it's something I
On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango
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 to set scale fa
>
>
> 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 inde
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 wrote:
> Sergey,
> Thanks for the aggressive settings. I have li
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 abou
On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango
wrote:
> 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
Hi:
On Wed, Apr 30, 2014 at 1:59 AM, Elanchezhiyan Elango
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 minutes about 50
On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango
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 'vacuum full' fo
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 50
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 (pgsql-g
On Wed, Dec 11, 2013 at 1:08 AM, Jayadevan M
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 records and deleted them
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 pg
Bill Moran 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 lane
--
Sent via pg
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 u
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
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
bject: 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.
<...>
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 conside
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
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 |
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 da
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., it's much more easy to setup
> autovacuum then before.
Hel
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 dow
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 month
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
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 clu
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 * * *
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 bu
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 * * *
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
"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
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 m
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.
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 tabl
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 sh
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
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 (d
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 a
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
l.org
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 yo
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
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
nges between 7.3 and 8.1,
may 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 d
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
"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
neral@postgresql.org
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.postgr
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 *v
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
8.1.
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?
-
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
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 transactio
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 e
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
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.
M
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 cycl
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 conne
Joe Maldonado <[EMAIL PROTECTED]> writes:
> iostat shows that without vacuum (on SCSI Raid1), our application
> is not using much I/O on an avg (although there is bursty I/O)
> Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s
> avgrq-sz avgqu-sz
> /dev/sda 0.00 1.60 0.
Tom Lane wrote:
Joe Maldonado <[EMAIL PROTECTED]> writes:
Can concurrent updates/deletes slow down vacuum when it is progressing ? I
mean to ask if vacuum would have to redo or stall its work because of the
updates/deletes. Is it even possible that it goes into a long loop while
such updates occ
Joe Maldonado <[EMAIL PROTECTED]> writes:
> Can concurrent updates/deletes slow down vacuum when it is progressing ? I
> mean to ask if vacuum would have to redo or stall its work because of the
> updates/deletes. Is it even possible that it goes into a long loop while
> such updates occur ?
vacuu
Can concurrent updates/deletes slow down vacuum when it is progressing ? I
mean to ask if vacuum would have to redo or stall its work because of the
updates/deletes. Is it even possible that it goes into a long loop while
such updates occur ?
The reason for my question is that I'm seeing vacuuming
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 recomme
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 I
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 template
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 bro
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 o
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 wou
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 UNIC
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 w
> > 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
76 matches
Mail list logo
