Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the best way to store UniProt biological sequences in PostreSQL?

Data Details

  • We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month.
  • The length of a sequence can vary from 10 to 50 billion characters
  • Less than 1% of the sequences are longer than 10 thousand characters
    • Would it improve performance to store the longer sequences separately?
  • A sequence can be of either Protein or DNA alphabet
    • The DNA alphabet has 5 characters (A, T, C, G, or -).
    • The Protein alphabet will have around 30 characters.
    • We don't mind storing the sequences of the two different alphabets in different columns or even different tables. Would that help?

Data Access Details

To answer Jeremiah Peschka's comment:

  • Protein and DNA sequences would be accessed at different times
  • Would not need to search within the sequence (that's done outside of db)
  • Would ether access single rows at a time or pull out sets of rows by IDs. We would not need to scan rows. All sequences are referenced by other tables - several biologically and chronologically meaningful hierarchies exist in the database.

Backwards Compatibility

It would be nice to be able to continue to be able to apply the following hashing function (SEGUID - SEquence Globally Unique IDentifier) to the sequences.

CREATE OR REPLACE FUNCTION gfam.get_seguid(p_sequence character varying)
  RETURNS character varying AS
$BODY$
declare
  result varchar := null;
  x integer;
begin

  select encode(gfam.digest(p_sequence, 'sha1'), 'base64')
  into   result;

  x := length(result);
  if substring(result from x for 1) = '=' then

     result := substring( result from 1 for x-1 );

  end if;

  return result;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
share|improve this question
    
What kind of data access patterns will you have? Will DNA and protein data be accessed at the same time for a sequence? Will you need to search within the sequence? Will data access largely be for single rows at a time or will you be performing scans of the data? The way that you're accessing the data is, in many ways, far more important than the data itself. –  Jeremiah Peschka Jan 4 '11 at 1:26
1  
Not to dissuade you from consulting this fledgling community, but for a bioinformatics question, biostar.stackexchange.com might have the answer you're looking for. Hope that helps! –  Gaurav Jan 4 '11 at 15:56
    
+1 for Biostar but I'm keeping this questing strictly DB. –  Aleksandr Levchuk Jan 4 '11 at 16:50
    
@jcolebrand, this is related to Blast. We have an export function that writes out the sequences to a FASTA format and that is a valid input to Blast. Then Blast can do high-throughput similarity searches against the sequences or against a larger database (but only Uniprot can be larger then Uniport). We also construct HMM from sets of sequences and use HMMER2 to search for similarity. –  Aleksandr Levchuk Aug 1 '11 at 18:03
add comment

2 Answers

up vote 5 down vote accepted

Exploring the functions at PostBio it looks like they have a couple of ways of encoding. However, given that those extensions are optimized for searching, they make multiple references to simply using the text data type.

According to the documentation:

Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

Therefore, by putting the table into its own very large tablespace on dedicated hardware should be sufficient for your performance goals. If 1 GB is too small for your data, the int_interval from ProtBio should provide excellent performance:

A sequence feature corresponds to a triplet (id, orient, ii) where id is a sequence identifier (possibly the primary key for a sequence table), orient is a boolean indicating if the feature is in the same or contrary orientation of the sequence, and ii is the int_interval representing the feature as a subsequence.

Encoding the sequence in sha1 looks to be a very painful way of making a GUID, considering the potential lengths of the sequence.

If the different sequences are unrelated, store them on different tablespaces on different disks for maximum performance.

share|improve this answer
add comment

I think 50 billion characters will likely push the limits of what you can do with PostgreSQL without splitting your records in some way. I suspect you will have to find some way to break things apart in some way. I don't know what sort of encoding postbio allows but....

Quick calculations here: 5 characters requries 3 bits to encode, but 4 bits will make searching easier since two characters can be encoded per byte. On the other hand 3 may be sufficient if you are searching for groups of 10 or more letters since you could do 10 characters per 4 bytes. So optimized for short string searches, 50 billion characters takes approx 25gb of storage, well beyond what you can do in a single column. Compression may help, but that's a huge compression scale required beyond the minimal uncompressed binary representation in order to get down to 1GB. Optimized for longer searches, we get only 20GB. so I think even if you had genetic information types, you'd have break things up. Proteins at that complexity will be even more of a challenge since the best you can hope for is 5 bit notation which means you have 6 per 32, meaning your best case for storage is 30GB per column. So unless you can get Compression may again help but that's a large compression rate required. I have seen good compression rates, but keep in mind you may be pushing it.

So my recommendation is be aware of this problem, and do some testing with real data. Be parepared to decompose your readings in some cases.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.