Implementing a functionality for processing heavy insertion

From: Shadin A <shadin__a(at)hotmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Implementing a functionality for processing heavy insertion
Date: 2014-09-02 14:44:13
Message-ID: DUB126-W25045A70BC42B9528F06ACB7C70@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello there,
I use PostgreSQL 9.1

The scenario is:

I receive heavy insertion into Table1 (about 100 rows a sec). For
each new entry, I have to check it with the previous and next ones
(check if those items are inside an area using ST_DWithin). Depending on
the result, what I need to do is: use the new entry (joining another
table) to insert/update into new table.

My Questions are:

I used to use Trigger to do the checking and insertion/updating. the header is as follows:CREATE TRIGGER ts_trigger
AFTER INSERT
ON table1
FOR EACH ROW
EXECUTE PROCEDURE test_trigger();But I don't think it's the efficient way to do it.

I'm inserted in using batches. I'd like to understand the technique but cannot find a good resources for this.
Some advise me to use temp table, but I don't think it would be useful in my case.

Browse pgsql-performance by date

  From Date Subject
Next Message Takashi Horikawa 2014-09-03 00:12:52 Re: why after increase the hash table partitions, tpmc decrease
Previous Message Xiaoyulei 2014-09-02 06:59:39 why after increase the hash table partitions, tpmc decrease