Background
Fragment table includes dataspace_id and anchor_id columns. Anchor table also has dataspace_id column. Since an Anchor belongs to only one Dataspace, and a Fragment belongs to only one Anchor, the dataspace_id column in Fragment is redundant (since the fragment's dataspace_id is a duplicate of the anchor's dataspace_id).
It is at best redundant to have the same data duplicated in multiple database tables, and at worst could lead to inconsistencies.
This task is to remove dataspace_id column from the Fragment table.
In addition to wasting space on duplicate data, the Fragment table also includes an index for dataspace_id, a foreign key constraint, and dataspace_id is included in the uniqueness constraint for Fragment. Removing all these may improve write performance (albeit a small improvement).
There are no negative performance impacts expected, as the only functionality relying on dataspace_id is query-across-anchors (relevant SQL queries can be changed to use an inexpensive table JOIN on Anchor table).
Implementation Proposal
It is proposed to remove the dataspace_id column from the Fragment table in the Liquibase changelog. This also involves removing associated indexes and constraints.
Proposed Liquibase changes
databaseChangeLog:
- changeSet:
id: 19-1
author: cps
changes:
- dropIndex:
indexName: FKI_FRAGMENT_DATASPACE_ID_FK
tableName: fragment
rollback:
- createIndex:
columns:
- column:
name: dataspace_id
indexName: FKI_FRAGMENT_DATASPACE_ID_FK
tableName: fragment
- changeSet:
id: 19-2
author: cps
changes:
- dropUniqueConstraint:
constraintName: fragment_dataspace_id_anchor_id_xpath_key
tableName: fragment
- addUniqueConstraint:
columnNames: anchor_id, xpath
constraintName: fragment_anchor_id_xpath_key
tableName: fragment
rollback:
- dropUniqueConstraint:
constraintName: fragment_anchor_id_xpath_key
tableName: fragment
- addUniqueConstraint:
columnNames: dataspace_id, anchor_id, xpath
constraintName: fragment_dataspace_id_anchor_id_xpath_key
tableName: fragment
- changeSet:
id: 19-3
author: cps
changes:
- dropForeignKeyConstraint:
baseTableName: fragment
constraintName: fragment_dataspace_id_fkey
rollback:
- addForeignKeyConstraint:
baseColumnNames: dataspace_id
baseTableName: fragment
constraintName: fragment_dataspace_id_fkey
deferrable: false
initiallyDeferred: false
onDelete: NO ACTION
onUpdate: NO ACTION
referencedColumnNames: id
referencedTableName: dataspace
validate: true
- changeSet:
id: 19-4
author: cps
changes:
- dropColumn:
columnName: dataspace_id
tableName: fragment
rollback:
- addColumn:
tableName: fragment
columns:
- column:
name: dataspace_id
type: INTEGER
- sqlFile:
path: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
- addNotNullConstraint:
tableName: fragment
columnName: dataspace_id
columnDataType: INTEGER
As part of the rollback procedure, the removed column must be re-populated with data. The following SQL is used to do this:
UPDATE
fragment AS f
SET
dataspace_id = a.dataspace_id
FROM
anchor AS a
WHERE
f.anchor_id = a.id;
Results
Table description before update, after update, and after rollback
Fragment table before update, after update, and after rollback
| Before update | ||||||
| id | xpath | attributes | anchor_id | parent_id | dataspace_id | schema_node_id |
| 1 | /cps-ran-schema | {} | 1 | null | 1 | null |
| 2 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="11"] | {"idNearRTRIC": "11"} | 1 | 1 | 1 | null |
| 137 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549} | 1 | 136 | 1 | null |
| 139 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360} | 1 | 138 | 1 | null |
| 141 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361} | 1 | 140 | 1 | null |
| 144 | /dmi-registry | {} | 2 | null | 2 | null |
| 145 | /dmi-registry/cm-handles[@id='336a9e2779b64a1cac04fee3208a1c76'] | {"id": "336a9e2779b64a1cac04fee3208a1c76", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""} | 2 | 144 | 2 | null |
| After update | ||||||
| id | xpath | attributes | anchor_id | parent_id | schema_node_id | |
| 1 | /cps-ran-schema | {} | 1 | null | null | |
| 2 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="11"] | {"idNearRTRIC": "11"} | 1 | 1 | null | |
| 137 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549} | 1 | 136 | null | |
| 139 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360} | 1 | 138 | null | |
| 141 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361} | 1 | 140 | null | |
| 144 | /dmi-registry | {} | 2 | null | null | |
| 145 | /dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092'] | {"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""} | 2 | 144 | null | |
| After rollback | ||||||
| id | xpath | attributes | anchor_id | parent_id | schema_node_id | dataspace_id |
| 1 | /cps-ran-schema | {} | 1 | null | null | 1 |
| 2 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="11"] | {"idNearRTRIC": "11"} | 1 | 1 | null | 1 |
| 137 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549} | 1 | 136 | null | 1 |
| 139 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360} | 1 | 138 | null | 1 |
| 141 | /cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes | {"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361} | 1 | 140 | null | 1 |
| 144 | /dmi-registry | {} | 2 | null | null | 2 |
| 145 | /dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092'] | {"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""} | 2 | 144 | null | 2 |