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

[Rick Schumeyer]

As the originator of this thread, thanks to whoever suggested partitioning
the table to begin with.  At the moment I have about 350 sub tables, each
with about 230k records, for a total of over 80 million records.  The COPY
time remained constant, and spatial index searches work as expected.  Keep
in mind that the tables are partitioned by time, not by location, so a
spatial query looks at all sub tables.  

Having to create a new table for each data file is not that big of a deal.
I'm a little concerned about the effects of having so many tables.  I'll
have to ask the postgres-performance list about that.

> 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