Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:

CREATE TABLE ch02.markers  
(
  ff_id SERIAL PRIMARY KEY,
  col1 boolean,
  col2 smallint,
  created_at timestamp,
  lat double precision,
  lon double precision,
  geog geography(POINT,4326)
);

CREATE INDEX idx_markers_geog ON ch02.markers USING gist(geog);

This table is having records, probably thousands but potentially tens of thousands per hour, inserted and deleted 24/7.

I am wondering if the default autovacuum settings will sufficiently manage the table?

Otherwise I presume I write a SP function that is called by cron. However, what should I use? VACUUM ANALYZE? I am wary of using any anything that locks the table (CLUSTER, REINDEX). Do these even work for an index on a geography point column?

I am not used to such active tables and I am completely new to Postgres, so I have absolutely no feel for this. Any help appreciated.

share|improve this question

migrated from stackoverflow.com Jul 24 '12 at 2:36

This question came from our site for professional and enthusiast programmers.

    
You may get better answers at dba.SE. –  Erwin Brandstetter Jul 23 '12 at 14:28
1  
We do about 5000 inserts per second on PostgreSQL 9.1 and it works. –  Frank Heikens Jul 23 '12 at 14:29
    
@Erwin Brandstetter Thanks - I didn't even realise there was such a thing. I shall direct my db questions there from now on. –  Polly Jul 23 '12 at 16:43
add comment

1 Answer 1

up vote 2 down vote accepted

Before you fiddle with cron jobs and other curiosities you can adjust the autovacuum default settings just inside PostgreSQL. This can be done globally or individually for each table.

share|improve this answer
    
Without some basic configuration, it's too slow anyway. –  Frank Heikens Jul 23 '12 at 15:23
    
@FrankHeikens: Maybe, but Polly asked specifically about the "default configuration". Of course this might no be what he wanted to ask but who knows that... –  A.H. Jul 23 '12 at 15:36
    
@FrankHeikens: You do not mention deletes which I understand are more demanding upon VACUUM. But, I take from your responses that I should be fine with autovacuum as long as I tune it properly. Is that correct? –  Polly Jul 23 '12 at 16:46
    
@A.H.: Ok I will go and read about the autovacuum settings. –  Polly Jul 23 '12 at 16:50
1  
@Polly: Yes, that is correct. Tune your database and your SQL to get the best performance for your specific situation. No default configuration can get the max performance out of any workload. And it's not just auto vacuum that needs some tuning, other settings as well. The default configuration is just good enough to get the database started. –  Frank Heikens Jul 23 '12 at 16:59
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.