[postgis-users] COPY too slow when geometry column is indexed

[Markus Schaber]

Hi, David,

David Bitner wrote:
> I have not implemented it yet, but I am having the same issues with a
> dataset that I add several thousand records to a day.  I have been
> looking into partitioning my data by month or quarter akin to:
> http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
> A bit more difficult to keep adding the partitions, but It shouldn't
> be too difficult to just add a script on a cron that adds a new table
> each quarter.

If you insert the data via INSERT and not via COPY, you can use a view
with ON INSERT DO INSTEAD rules that automagically creates needed
tables, and redirects the data into the appropriate partitions.

Maybe it is possible using a BEFORE INSERT trigger on a table, too.
(possibly on the base table if you inherit the partitioned tables, so
you get a really transparent partitioning).

Markus