2

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.

Improve this question
3
  • You may get better answers at dba.SE.
    – Erwin Brandstetter
    Jul 23, 2012 at 14:28
  • 1
    We do about 5000 inserts per second on PostgreSQL 9.1 and it works.
    – Frank Heikens
    Jul 23, 2012 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, 2012 at 16:43

1 Answer 1

Reset to default
2

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.

Improve this answer
6
  • Without some basic configuration, it's too slow anyway.
    – Frank Heikens
    Jul 23, 2012 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, 2012 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, 2012 at 16:46
  • @A.H.: Ok I will go and read about the autovacuum settings.
    – Polly
    Jul 23, 2012 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, 2012 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.