Manage Indexes in Zen Project
Managing PostgreSQL indexes in Aidbox zen-project has following advantages:
- sharing between many environments
- generate indexes for desired search parameters automatically, with no SQL
In this guide we will:
- setup Aidbox locally
- create index with SQL in zen
- create index without SQL in zen
- synchronize these indexes via Aidbox RPC API
├── docker-compose.yaml
├── .env
├── .gitignore
├── pgdata
├── README.md
├── zen-package.edn
├── zen-packages
└── zrc
├── config.edn
└── main.edn
Next we will edit files in /zrc directory.
We will create index for Practitioner.index search parameter with SQL, define new search parameter Patient.brthdt and create auto-index for that.
First we need to create repositories. Each repository will represent one resourceType, e.g.
patient-repository and practitioner-repository.Edit main.edn:
main.edn
1
{ns main
2
import #{aidbox.index.v1
3
aidbox
4
config
5
aidbox.repository.v1}
6
7
practitioner-repository
8
{:zen/tags #{aidbox.repository.v1/repository}
9
:resourceType "Practitioner"
10
:indexes #{practitioner-identifier-gin-index}
11
:extra-parameter-sources :all
12
:search-parameters #{}}
13
14
patient-repository
15
{:zen/tags #{aidbox.repository.v1/repository}
16
:resourceType "Patient"
17
:indexes #{patient-birthdate-auto-index}
18
:extra-parameter-sources :all
19
:search-parameters #{patient-birthdate-parameter}}
20
21
repositories
22
{:zen/tags #{aidbox/service}
23
:engine aidbox.repository.v1/engine
24
:load-default true
25
:repositories #{patient-repository practitioner-repository}}
26
27
box
28
{:zen/tags #{aidbox/system}
29
:config config/base-config
30
:services {:repositories repositories
31
:admin-user-seed config/admin-user-seed
32
:root-client-seed config/root-client-seed}}}
All repositories must be referenced into one schema
repositories tagged with aidbox/service.Each repository contains references for indexes (can be empty -
#{}) and search-parameters (also can be empty).Now let's define search parameter
patient-birthdate-parameter and indexes patient-birthdate-auto-index, practitioner-identifier-gin-index.main.edn
1
{ns main
2
import #{aidbox.index.v1
3
aidbox
4
config
5
aidbox.repository.v1}
6
7
practitioner-identifier-gin-index
8
{:zen/tags #{aidbox.index.v1/index}
9
:table "practitioner"
10
:type :gin
11
:expression "(\"practitioner\".resource)"}
12
13
patient-birthdate-parameter
14
{:zen/tags #{aidbox.search-parameter.v1/search-parameter}
15
:name "brthdt"
16
:type :date
17
:resource {:resourceType "Entity" :id "Patient"}
18
:expression [["birthDate"]]}
19
20
patient-birthdate-auto-index
21
{:zen/tags #{aidbox.index.v1/auto-index}
22
:for patient-birthdate-parameter}
23
24
practitioner-repository
25
{:zen/tags #{aidbox.repository.v1/repository}
26
:resourceType "Practitioner"
27
:indexes #{practitioner-identifier-gin-index}
28
:extra-parameter-sources :all
29
:search-parameters #{}}
30
31
patient-repository
32
{:zen/tags #{aidbox.repository.v1/repository}
33
:resourceType "Patient"
34
:indexes #{patient-birthdate-auto-index}
35
:extra-parameter-sources :all
36
:search-parameters #{patient-birthdate-parameter}}
37
38
repositories
39
{:zen/tags #{aidbox/service}
40
:engine aidbox.repository.v1/engine
41
:load-default true
42
:repositories #{patient-repository practitioner-repository}}
43
44
box
45
{:zen/tags #{aidbox/system}
46
:config config/base-config
47
:services {:repositories repositories
48
:admin-user-seed config/admin-user-seed
49
:root-client-seed config/root-client-seed}}}
Auto-index means that Aidbox will create index (or indexes!) for that particular search parameter. Each auto-index must reference search-parameter in
:for.Practitioner-identifier-gin-index is the ordinary index because it is tagged with aidbox.index.v1/index and it must contain an SQL expression.Now we can test it.
Start Aidbox:
docker compose up --force-recreate
Test if new
Patient.brthdt search parameter work:GET /fhir/Patient?brthdt=2000-01-01&_explain=1
Test that no indexes are created:
select * from pg_indexes where indexname ilike 'aidbox_mng%'
Request:
POST /rpc
Content-Type: text/yaml
Accept: text/yaml
method: aidbox.index.v1/sync-indexes
Response:
result:
params:
indexes-to-drop: []
indexes-to-create:
- indexexpr: >-
(knife_extract_min_timestamptz("patient".resource,
'[["birthDate"]]'))
indexname: aidbox_mng_idx_patient_brthdt_param_knife_date_min_tstz
indextype: btree
tablename: '"patient"'
- indexexpr: >-
(knife_extract_max_timestamptz("patient".resource,
'[["birthDate"]]'))
indexname: aidbox_mng_idx_patient_brthdt_param_knife_date_max_tstz
indextype: btree
tablename: '"patient"'
- indexexpr: ("practitioner".resource)
indexname: aidbox_mng_idx_main_practitioner_identifier_gin_index
indextype: gin
tablename: '"practitioner"'
status: in-progress
definition: aidbox.index/sync-indexes-workflow
id: >-
6c702283-a723-4ef4-a6c1-90f16ebef8aa
resourceType: AidboxWorkflow
meta:
lastUpdated: '2023-06-06T13:19:18.420982Z'
createdAt: '2023-06-06T13:19:18.420982Z'
versionId: '4916'
Index synchronization may take some time. You can check the status of workflow with UI Aidbox console or with
awf.workflow/status rpc method:Request:
POST /rpc
content-type: text/yaml
accept: text/yaml
method: awf.workflow/status
params:
id: 8dede5e7-08e4-4f2d-9296-3d4e554629f0
Response:
result:
resource:
params:
indexes-to-drop: []
indexes-to-create:
- indexexpr: >-
(knife_extract_min_timestamptz("patient".resource,
'[["birthDate"]]'))
indexname: aidbox_mng_idx_patient_brthdt_param_knife_date_min_tstz
indextype: btree
tablename: '"patient"'
- indexexpr: >-
(knife_extract_max_timestamptz("patient".resource,
'[["birthDate"]]'))
indexname: aidbox_mng_idx_patient_brthdt_param_knife_date_max_tstz
indextype: btree
tablename: '"patient"'
- indexexpr: ("practitioner".resource)
indexname: aidbox_mng_idx_main_practitioner_identifier_gin_index
indextype: gin
tablename: '"practitioner"'
result:
message: All indexes are synced
created-indexes:
- aidbox_mng_idx_patient_brthdt_param_knife_date_min_tstz
- aidbox_mng_idx_patient_brthdt_param_knife_date_max_tstz
- aidbox_mng_idx_main_practitioner_identifier_gin_index
dropped-indexes: []
created-indexes-count: 3
dropped-indexes-count: 0
status: done
outcome: succeeded
definition: aidbox.index/sync-indexes-workflow
id: >-
6c702283-a723-4ef4-a6c1-90f16ebef8aa
resourceType: AidboxWorkflow
meta:
lastUpdated: '2023-06-06T13:19:18.753250Z'
createdAt: '2023-06-06T13:19:18.420982Z'
versionId: '4975'
After workflow is complete, you can see 3 managed indexes are created.
select * from pg_indexes where indexname ilike 'aidbox_mng%'
Last modified 18d ago