fhir developer days 2015. study on db implementations for fhir server
TRANSCRIPT
Nortal NHS in Lithuania - study on DB implementations for FHIR server Igor Bossenko, Jan Jasinski Nortal 19.11.2015
• Start: 2013-12 • LIVE: 2015-09
LT NHS project
Develop standard and platform for
health information exchange.
Centralize health-related classifiers and
registries.
Gather medical information from
Healthcare Providers
Provide access to health related information for: • Patients; • Health-care providers;
• Covernment.
Architecture FHIR DSTU1 REST + Documents Conformance: yes Profiles: no Security: OAuth + VPN Server: Java + OSGI Integration: Fuse ESB Database: Oracle 11
#1 Table per resource or datatype 4
#1 Query examples 5
Select * from Condition where Encounter_ID = 123;
Select * from Human_Name_Part where Name_Part_Code = ‘family’ and value = ‘XXX’;
#1 Table per resource or datatype 6
• Pros Classical RDBS solution Easy to understand for non-FHIR people Suitable with the most BI tools Good response time Ability to optimize searches Work on every database
• Cons DB structure too ugly Too static, does not support new resources or profiles Any resource extension need additional programming Searches are hardcoded, no ability to add new search parameters dynamically
#2 Resource store + custom index 7
#2 Query examples 8
select * from idx_string t1 inner join idx.idx_Reference ref1 on
ref1.resource_type='Encounter' and ref1.ref_resource_type='Patient’ and
ref1.search_param='patient' and ref1.ref_rid = t1.rid
where t1.search_param='family' and t1.text_value=’XXX' and t1.resource_type='Patient’
select key from idx_string s where s.resource_type='Patient' and s.text_value like '%XXX%'
#2 Resource store + custom index 9
• Pros Works on every database Flexible Easy to understand Easy to develop
• Cons Requires additional space for custom index tables and DB indexes for custom index tables Does not cover searches on data outside from index (custom searches in JSON/XML content) Not scalable
#3 Non-SQL + partitioning + JSON/XML index 10
#3 PostgreSQL JSONB query examples 11
select id from fhir.patient where content @> '{"name":[{"use":"official", "family":[”XXX"]}]}'
select c.id from practitioner p inner join condition c on c.content -> 'asserter' ->> 'reference' = p.id where p.content ->> 'name' ~ '"family":[ \[]*”XXX"*[ \]]'
#3 Non-SQL + partitioning + JSON/XML index 12
• Pros Very flexible Good response time
• Cons Implementation depends on database
#4 BigData 13
• Apache HBase • Apache Phoenix • Custom indexes on JSON content for simple queries • Work In Progress on complex queries
#4 BigData 14
• Pros Very scalable Index-based search is very quick
• Cons Environment installation is more complex Too new (for me) Currently we don’t have solution for complex queries (WIP)
Test data 15
Resource Rows
Practitioner 23 187
Patient 1 625 084
Encounter 11 125 528
Condition 17 375 336
DiagnosticReport 31 719 078
Response times (avg) 16
Test #1 Table per resource
#2 Custom index
#3 JSON index + partitioning
#4 BigData
Get patient record by resource Id 20 ms 100 ms 20 ms 20 ms
Find patients by exact name (=‘FAMILY’) 35 ms 35 ms 120 ms 30 ms
Find patients by name part (like '%FAMILY%') 150 ms 34000 ms 70 ms WIP
Find patients by exact system and identifier 35 ms 50 ms 115 ms 30 ms
Find all patients born in 1961 550 ms 803 ms 267 ms WIP
Find all encounters where patient name contain some word 1000 ms 3000 ms 1000 ms WIP
Find encounters of practitioner X since Y 350 ms ~60000 ms 233 ms WIP
Evaluation criteria 17
• Flexibility – possibility to add new resources and extend existing ones
• Response time – the amount of time taken to respond to a request
• Scalability – capability of a system to handle a growing amount of data
• Independent – ability to work on different databases
Estimation of Search implementations 18
Features #1 Table per Resource
#2 Custom index #3 Non-SQL + JSON/XML index
#4 BigData
Response time (simple queries)
Response time (complex queries) WIP
Flexibility / extensibility
Scalability
Platform independent Yes Yes No No
Easy to develop WIP