We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred different research projects, and each project needs its own version of the data, with certain columns of the data encrypted to a project-specific value, as well as other restrictions (like only providing certain rows and/or columns that a project has permission to us).

Here is a simplified example of some of the data:


persion_id   spell_id  hospital_id   admission_dt   discharge_dt   
1234         9023      12            2012-09-04     2012-09-05
1234         1111      12            2014-01-01     2014-01-01
9876         5432      8             1999-04-27     2000-02-29


spell_id  hospital_id  diag_num diag_code
9023      12           1        A12
9023      12           2        C45

These two tables link on spell_id and hospital_id. For each project that uses this data, we need to provide a version of the data with person_id, spell_id, and hospital_id encrypted to new values (this restriction limits unauthorised combination of data from different projects). Also, a given project might only get data from certain years or for certain people (however many projects get the entire dataset).

How can we provide these multiple versions of the data efficiently, in terms of performance and storage? Ideally this would mean not copying all of the data for each project, and having only a limited performance hit for each project's use of the data (30% slower than querying the original tables would be fine, but not 300% slower).

Options we have considered:

  • Making a copy of the data for each project. This results in no performance hit, but when each project has potentially billions of rows, it takes up a lot of storage space with mostly duplicated data.
  • A view of the original table that encrypts on the fly with a scalar function. Uses the least storage space, but performance is not acceptable, particularly when the encrypted columns are often used for joins (as above).
  • A view of the original table that joins to an additional table with precomputed encrypted values. This has been our preferred option, but it turns out to also perform quite poorly. Queries against these views are many times slower than against the original table. We have worked on optimising indexing, but it hasn't solved this.

Example of the third option:

Additional table:


person_id   person_id_e
1234        1A30495810293D0293CB2093A3FED2B


create view hospital_spell_encrypted as (
    select person_id_e,
      from hospital_spell
      join person_id_encrypted on 
         hospital_spell.person_id = person_id_encrypted.person_id
      join spell_id_encrypted on 
         hospital_spell.spell_id = spell_id_encrypted.spell_id
      join hospital_id_encrypted on 
         hospital_spell.hospital_id = hospital_id_encrypted.hospital_id

Does anyone have any thoughts on how we can get better performance? So far, just copying the data for each project looks like the only workable solution.

We are using DB2 9.7 LUW, running on an IBM Blue C Supercomputer with multiple parallel servers.

  • When you say that person_id, spell_id and hospital_id have to be "encrypted to new values", what exactly counts as encrypted? Does there actually need to be some degree of cryptographic strength (i.e. a SHA-1 hash, etc), or would any function that creates a deterministic different key for each project be acceptable? – Tersosauros Mar 27 '16 at 4:32
  • @Tersosauros, anything that creates a deterministic different key, that is not reversible to get the original value, would be fine. – dan1111 Mar 29 '16 at 11:27
  • Interesting that you say "that is not reversible". Surly (in the realm of a supercomputer) nothing is entirely irreversible/reverse engineerable? Even (very slow) cryptographic techniques can be brute-forced. I think the problem you are going to have is that most efficient ways to get different values for keys will be easily-reversible. And most strong techniques will be slow and inefficient. For example, a bitwise function like XOR would be very fast, but easily reversed – Tersosauros Apr 1 '16 at 2:24
  • @Tersosauros, sorry for the imprecision in my response; we are just looking for something that is not easily reversible. – dan1111 Apr 1 '16 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.