By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Settings for autovacuum for batch uploading of data?

P: n/a
Hello Forum,

I would appreciate it if you could recommend settings to use auto-vacuum in
my version 7.4 database. I am uploading several thousands records in the
database at a rate of ~1 second per record (the data is uploading from a
different server). I have noticed that the performance of the uploading is
improved if I vacuum the database every so often so I decided to take
advantage of auto-vacuuming to maintain the best performance. Since I am
uploading several thousands records, I set autovacuum to 50 minutes with -s
3000 in the hope that a great deal of data would be committed before a
regular vacuum would take place and that vacuuming would interfere the least
possible with the uploading.

Thanks in advance for your recommendations.
Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
More important than sleep value is the vacuum threshold. If for example
you are expecting to import 5000 rows of data, you can tell
pg_autovacuum to vacuum after every 5000 changes with like

pg_autovacuum -v 5000 -V 0

Matthew
On Wed, 2004-05-19 at 17:09, Carlos wrote:
Hello Forum,

I would appreciate it if you could recommend settings to use
auto-vacuum in my version 7.4 database. I am uploading several
thousands records in the database at a rate of ~1 second per record
(the data is uploading from a different server). I have noticed that
the performance of the uploading is improved if I vacuum the database
every so often so I decided to take advantage of auto-vacuuming to
maintain the best performance. Since I am uploading several thousands
records, I set autovacuum to 50 minutes with –s 3000 in the hope that
a great deal of data would be committed before a regular vacuum would
take place and that vacuuming would interfere the least possible with
the uploading.



Thanks in advance for your recommendations.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

P: n/a
Thank you for your reply Matthew. Do you know if I can stop auto-vacuum,
and re-start it with different parameters as the uploading continues? I do
not know if my killing the auto-vacuum process would terminate my
application also.

I want to start auto-vacuum with different parameters to tune the vacuuming
to my application. I would like to vacuum often as the data is being
uploaded (inserts mainly) because I want to maintain a high speed of upload

Thanks again.

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Matthew T. O'Connor
Sent: Thursday, May 20, 2004 12:54 AM
To: Carlos
Cc: 'p************@postgresql.org'
Subject: Re: [GENERAL] Settings for autovacuum for batch uploading of data?

More important than sleep value is the vacuum threshold. If for example
you are expecting to import 5000 rows of data, you can tell
pg_autovacuum to vacuum after every 5000 changes with like

pg_autovacuum -v 5000 -V 0

Matthew
On Wed, 2004-05-19 at 17:09, Carlos wrote:
Hello Forum,

I would appreciate it if you could recommend settings to use
auto-vacuum in my version 7.4 database. I am uploading several
thousands records in the database at a rate of ~1 second per record
(the data is uploading from a different server). I have noticed that
the performance of the uploading is improved if I vacuum the database
every so often so I decided to take advantage of auto-vacuuming to
maintain the best performance. Since I am uploading several thousands
records, I set autovacuum to 50 minutes with -s 3000 in the hope that
a great deal of data would be committed before a regular vacuum would
take place and that vacuuming would interfere the least possible with
the uploading.

Thanks in advance for your recommendations.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
Feel free to kill pg_autovacuum and try different parameters as much as
you like, killing pg_autovacuum won't hurt anything.

You should note that the current implementation doesn't keep any history
of information, so when you restart pg_autovacuum, it sets it's update
counts to 0.

Matthew

On Thu, 2004-05-20 at 10:12, Carlos Oliva wrote:
Thank you for your reply Matthew. Do you know if I can stop auto-vacuum,
and re-start it with different parameters as the uploading continues? I do
not know if my killing the auto-vacuum process would terminate my
application also.

I want to start auto-vacuum with different parameters to tune the vacuuming
to my application. I would like to vacuum often as the data is being
uploaded (inserts mainly) because I want to maintain a high speed of upload

Thanks again.

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Matthew T. O'Connor
Sent: Thursday, May 20, 2004 12:54 AM
To: Carlos
Cc: 'p************@postgresql.org'
Subject: Re: [GENERAL] Settings for autovacuum for batch uploading of data?

More important than sleep value is the vacuum threshold. If for example
you are expecting to import 5000 rows of data, you can tell
pg_autovacuum to vacuum after every 5000 changes with like

pg_autovacuum -v 5000 -V 0

Matthew
On Wed, 2004-05-19 at 17:09, Carlos wrote:
Hello Forum,

I would appreciate it if you could recommend settings to use
auto-vacuum in my version 7.4 database. I am uploading several
thousands records in the database at a rate of ~1 second per record
(the data is uploading from a different server). I have noticed that
the performance of the uploading is improved if I vacuum the database
every so often so I decided to take advantage of auto-vacuuming to
maintain the best performance. Since I am uploading several thousands
records, I set autovacuum to 50 minutes with -s 3000 in the hope that
a great deal of data would be committed before a regular vacuum would
take place and that vacuuming would interfere the least possible with
the uploading.

Thanks in advance for your recommendations.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.