On 03/20/2012 09:12 PM, Jim Green wrote:
On 20 March 2012 22:08, Jim Green<student.northwestern@xxxxxxxxx> wrote:
On 20 March 2012 22:03, David Kerr<dmk@xxxxxxxxxxxxxx> wrote:
\copy on 1.2million rows should only take a minute or two, you could make
that table "unlogged"
as well to speed it up more. If you could truncate / drop / create / load /
then index the table each
time then you'll get the best throughput.
Thanks, Could you explain on the "runcate / drop / create / load /
then index the table each time then you'll get the best throughput."
part.. or point me to some docs?..
Also if I use copy, I would be tempted to go the one table route, or
else I need to parse my raw daily file, separate to individual symbol
file and copy to individual table for each symbol(this sounds like not
very efficient)..
Jim
Dave
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I think the decisions:
1) one big table
2) one big partitioned table
3) many little tables
would probably depend on how you want to read the data. Writing would be very similar.
I tried to read through the thread but didnt see how you're going to read.
I have apache logs in a database. Single table, about 18 million rows. I have an index on hittime (its a timestamp), and I can pull a few hundred records based on a time, very fast. On the other hand, a count(*) on the entire table takes a while. If you are going to hit lots and lots of records, I think the multi-table (which include partitioning) would be faster. If you can pull out records based on index, and be very selective, then one big table works fine.
On the perl side, use copy. I have code in perl that uses it (and reads from .gz as well), and its very fast. I can post some if you'd like.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
