Thread: 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