Re: set autovacuum=off

On Thu, Feb 23, 2012 at 11:07 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> That depends on if you have triggers that are doing selects. But in
> general you are correct, analyze wont help inserts.
>
> I do have some, actually. I have a couple trigger functions like:

CREATE OR REPLACE FUNCTION locations_quiet_unique_violation()
RETURNS trigger AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM public.locations WHERE geohash = NEW.geohash)
THEN
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

that are triggered thusly:

CREATE TRIGGER locations_check_unique_violation
BEFORE INSERT
ON locations
FOR EACH ROW
EXECUTE PROCEDURE locations_quiet_unique_violation();

I left auto-vacuum enabled for those tables.

checkpoint_segments can help insert speed, what do you have that set to?
>
> 40. Checking http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverit looks like setting that as high as 256 would not necessarily be
unreasonable. What do you think?

> Also how you insert can make things faster too. (insert vs prepared vs
> COPY)
>
> I'm doing this all with INSERT. Is COPY that much faster? I don't know
anything about prepared.

> Also, if you have too many indexes on a table that can cause things to
> slow down.
>
> Yeah, got that. I removed a bunch. I'd rather not remove what's left
unless I have to.

> Your IO layer needs to be fast too. Have you watched vmstat and iostat?
>
> I don't know if I have access to vmstat and iostat. Heroku is hosting this
for me on AWS.

> Have you read up on synchronous_commit?
>
> Only a tiny bit. A couple people suggested disabling it since my database
is being hosted on AWS so I did that. It seems a bit risky but perhaps
worth it.