George,
I am brand new to PostgreSQL and am just now getting my info together for a
possible deployment on AIX. I have however worked with several large(in my
frame of thinking) 6 million record databases on MSSQL. I did a ton of
reading, here is what I've read, what I use and am pleased with:
The best columns to sort on are integers(smallest you need- smallint then
int) and character.
Integer are good because they are precise as opposed to a float which is
not. Use a char field for text as in char(20), picking a size equal to the
largest text string you will be using. Do not use varchar(20) as your search
must first determine the size that is being used in the case of each
specific record. I do not see why there would be any advantage in converting
to hex. As far as a search goes I do not understand why that would be any
faster.
Good Luck,
Richard Vlahov
rv@dmauto.com
"George Johnson" <gjohnson@jdsc.com> wrote in message
news:NEBBJGKMGLGMDGBMOHJNOEFFCAAA.gjohnson@jdsc.com...
> Hello All,
>
> I need some advice regarding table column types and read speeds.
> I have a database of protein sequence data (strings of characters mostly
> including 20 alphabet characters) and nucleotide base sequence
> data (strings of characters mostly including a,t,g,c).
>
> When I wrote the homegrown backend to our database way back when, I cooked
> up some fun ways of storing the data for very fast access speeds (not
really
> interested in doing SQL-ish searching on the columns in place).
>
> I'm wondering what the best choice of storage is: just leave them as text
> strings, do some fancy hexidecimal conversion, etc? Or does it matter?
The
> tables for the most part contain the sequence information as well as a
> primary key.
>
> Any thoughts/suggestions would be useful.
>
> Happy Holidays (oh ... darn ... it's going to be 60's and sunny AGAIN on
the
> peninsula today [nudge to back-east bretheren] heheh)
>
> George Johnson
>