On 03/20/2012 09:12 PM, Jim Green wrote:
> On 20 March 2012 22:08, Jim Green<student.northwestern@gmail.com> wrote:
>> On 20 March 2012 22:03, David Kerr<dmk@mr-paradox.net> 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@postgresql.org)
>>> 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),
andI can pull a few hundred records based on a time, very fast. On the other hand, a count(*) on the entire table
takesa while. If you are going to hit lots and lots of records, I think the multi-table (which include partitioning)
wouldbe 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
postsome if you'd like.
-Andy
