As a Product Owner I want that a partial index is created to messagelog database so that non-archived messages will be fetched faster

[hanhaka]

Affected components: messagelog Affected documentation: dm-ml_x-road_message_log._data_model Estimated delivery: Q1/2017 External reference: https://jira.csc.fi/browse/PVAYLADEV-618

Problem PostgreSQL database has table named 'logrecord' that is used for storing all the messages handled by a Security Server. At the time when log archiver works, a lot of queries are made to find out messages need for archiving (= non-archieved messages). By using the partial index feature fetching the data from 'logrecord' table is remarkable faster.

Partial index is created using the following command: sudo -u postgres psql -c "create index ix_not_archived_logrecord on logrecord(id) where discriminator = 't' and archived = false" messagelog

This partial index has to be created automatically by the system when version 6.9.0 is installed. Note that both fresh install and upgrade install must create the partial index.

Note! Backup/restore functionality must be able to restore or create partial index even in a case when backup is restored from the older database that has no this partial index existing.

Acceptance criteria

• New partial index is created both during the fresh installation and upgrade installation of Security Server
• Backup/restore creates index automatically if needed
• Performance boost due to partial index is measured in AWS environment to see how much performance is increased
• Documentation is updated

[hanhaka]

Commited to XM/develop (finnish-6.9.0 pull request). Will be available with 6.9 release.