Обсуждение: inserting 4800 records at a time

Hi,

We use an application that generates 4800 points for the graph of a waveform.
We capture this data and display it to the user. Now we want to save all this
information to a database. I have tried to create a record for each point,
but insertion/retrieval is slow. I thought that maybe I could save one record
per graph and save all the points as a large string, but there would be 148k
characters in the string. Then I'm still not sure what the performance would
be like. Would the use of BLOBs a better way to go here? Any ideas on what
the best approach would be for us?

Thanks,
Tom

PostgreSQL has both array and point data types.

Link to documentation:
http://www.postgresql.org/docs/8.2/interactive/datatype-geometric.html#A
EN5367

> -----Original Message-----
> From: Dann Corbit
> Sent: Wednesday, March 28, 2007 4:42 PM
> To: 'Tom Brown'; pgsql-general@postgresql.org
> Subject: RE: [GENERAL] inserting 4800 records at a time
>
> PostgreSQL has both array and point data types.

Good Evening Tom-

probably the one good reason to use Postgres is the ability to install PostGIS
http://postgis.refractions.net/docs/ch04.html#id2673328 supports these simple curve geometries
CompoundCurve
CurvePolygon
MultiCurve

Does this help ?
Martin--
--------------------------------------------------------------------------- 
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is
addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you
arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication
isstrictly prohibited.
 
--------------------------------------------------------------------------- 
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire
indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce
document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
 
----- Original Message ----- 
From: "Tom Brown" <brown@esteem.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, March 28, 2007 7:30 PM
Subject: [GENERAL] inserting 4800 records at a time


> Hi,
> 
> We use an application that generates 4800 points for the graph of a waveform. 
> We capture this data and display it to the user. Now we want to save all this 
> information to a database. I have tried to create a record for each point, 
> but insertion/retrieval is slow. I thought that maybe I could save one record 
> per graph and save all the points as a large string, but there would be 148k 
> characters in the string. Then I'm still not sure what the performance would 
> be like. Would the use of BLOBs a better way to go here? Any ideas on what 
> the best approach would be for us?
> 
> Thanks,
> Tom
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

On Wednesday 28 March 2007 16:43, Dann Corbit wrote:
> Link to documentation:
> http://www.postgresql.org/docs/8.2/interactive/datatype-geometric.html#A
> EN5367

Yep. The polygon type is what I was needing. I tested it out by saving data
from nine graphs all at once. That's about 24000 points. Postgres handled it
all in about a second.

Thanks!
Tom

>> We use an application that generates 4800 points for the graph of a waveform.
>> We capture this data and display it to the user. Now we want to save all this
>> information to a database. I have tried to create a record for each point,
>> but insertion/retrieval is slow. I thought that maybe I could save one record
>> per graph and save all the points as a large string, but there would be 148k
>> characters in the string. Then I'm still not sure what the performance would
>> be like. Would the use of BLOBs a better way to go here? Any ideas on what
>> the best approach would be for us?
>>

I strongly recommend the use of PostGIS for storing (and
managing/querying) point
geometries in PostGIS.

If you do take this approach there are several advantages, not least the
large number of
supporting applications.

For example, OGR now supports GMT (in SVN right now), so you can plot
your spatial &
timeseriesdata from the command line with data driven scripts, a
simplistic example:


LIST=`psql $DB -A -t -c "select distinct species from table;"`

for SPP in $LIST ; do
   ogr2ogr -f "GMT" -nln data.gmt data PG:dbname=db -sql "select pont,
catch from table where species = '$SPP';"

   psxy data.gmt -R -JM ... > ${SPP}.ps
done

this approach allows maps/plots to be generated automagically from the
data, as GMT is a commandline package for
plotting data & ogr2pgr can generate GMT format data from a PostGIS table...



As far as loading is concerned, are you loading as separate inserts or
using copy?

A bulk load via copy is generally much faster.



Cheers,

   Brent Wood