Re: huge price database question..

On 03/20/2012 09:35 PM, Jim Green wrote:
> On 20 March 2012 22:25, Andy Colson<andy(at)squeakycode(dot)net> wrote:
>> 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.
>
> my queries would mostly consider select for one symbol for one
> particular day or a few hours in a particular day, occasionally I
> would do select on multiple symbols for some timestamp range. you code
> sample would be appreciated, Thanks!
>
> Jim.
>
>>
>> -Andy
>>

Here is some copy/pasted parts:

my @list = glob('*.gz');
for my $fname (@list)
{
$db->do('copy access from stdin');
open my $fh, "-|", "/usr/bin/zcat $fname" or die "$fname: $!";
while (<$fh>)
{
# bunch of stuff to format sniped here
# if you have comma separated or something you might be able
# to just feed it in
$db->pg_putcopydata("$county\t$ip\t$time\t$status\t$size\t$url\t$ua\n");
}
$db->pg_endcopy;
$db->commit;
}

Do you ever plan on batch deleted a BUNCH of records?

Do you ever want to do read all of one symbol (like, select avg(high) from stocks where symbol = 'bob')?

-Andy