12.2:documentation:architecture:dev:database

Database design

For the automatic creation and modification of the database scheme, the Database scripts (Flyway) is used.

We are using generated jpa metamodel by configuration, where criteria api is used.

...
// id
if (filter.getId() != null) {
  predicates.add(builder.equal(root.get(AbstractEntity_.id), filter.getId()));
}
...

Pros and cons of the use of uuid as the primary key for the database.

  • Pros:
    • Easier data migration between the environments (e.g. cloning the pre-production to production)
    • generating the identifier on the part of the client (distributed system, offline access)
    • impossibility of tipping the url (the guid is not sequential)
  • Cons:
    • double size of the indexes compared to bigint (8B vs 16B) - for numbers, see the next chapter with testing
    • the uuid is unreadable at the first sight directly in the database - the uuid is saved in the data type binary(16). If the uuid was saved to be readable, this would imply more disadvantages:
      • uuid native data type for the databse in question (e.g. uuid in posqresql) would result in the need to maintain a set of entities according to the chosen database on the application level
      • string - the size of the column would be of char(36), which would make the indexes 4 times bigger compared to the use of data type bigint
    • some databases always save the primary key together with the data (clustered index, MySQL/InnoDB), which may lead to data fragmentation
    • illegible / unmemorable url
    • Different uuid representation in application and MSSQL database.

Listing and searching according to the string guid in PostgreSQL:

SELECT encode(i.id, 'hex')::uuid, i.* FROM idm_identity i WHERE i.id = uuid_send('5cd7ec1c-6b34-45b7-9835-e533e9374ebf'::uuid)

Generating uuid without uuid-ossp package instaled in PosgreSQL:

INSERT INTO idm_configuration(id, created, creator, name, VALUE, secured, confidential) VALUES (
    decode(md5(random()::text || clock_timestamp()::text), 'hex'),
    now(),
    '[SYSTEM]',
    'idm.sec.core.tree.defaultType',
    (SELECT id FROM idm_tree_type WHERE default_tree_type = TRUE),
    TRUE,
    FALSE);

The aim of the testing should be to verify the steady time of data record / reading in the chart with an increasing number of records and the size of the charts and indexes. Two charts in the binding 1:N (master/detail) with the primary keys, indexes for the uniqueness of the record name (master name and detail name in one master) and for the index of the relation between the masters - detail for searching all the details of the master in question were created (todo: link to git for the structure, when it appears in develop).

How to read the individual records:

  • with a number of 5000 records saved in the master chart, the insert of one record into the master chart took 9ms.
  • with a number of 1943455 records saved in the detail chart, the size of all the indexes for the detail chart was 171MB.
  • with a number of 19505030 records saved in the detail chart, the PK index size for the detail chart was 417MB.
bigint as the primary key in PostgreSQL 9.4
Number of records (master)Number of records (detail) Insert (master)Insert (detail)Load details by masterIndex Size (master)Index size (detail)PK size (detail)
5000969959ms10ms1ms0,5MB8MB2MB
1000020359711ms11ms2ms1MB18MB4MB
5000096747511ms11ms2ms4MB85MB19.5MB
100000194345511ms12ms2ms8MB171MB39MB
100000019505030 11ms12ms 2.5ms73MB1703MB417MB
binary(16) representing uuid as the primary key in PostgreSQL 9.4
Number of records (master)Number of records (detail) Insert (master)Insert (detail)Load details by masterIndex Size (master)Index size (detail)PK size (detail)
50009765211ms12ms2.5ms1MB16M4.5MB
1000019480411.2ms13,5ms3ms1.5MB31MB9MB
5000097383611.4ms13.5ms3ms5MB156MB45.5MB
100000194706211.7ms14.5ms3ms10MB313MB95.2MB
100000019380690 30ms30ms 3.5ms68MB2653MB750MB

The testing took place on localhost (16GB RAM, 2 processors, regular HDD, default database configuration, logging turned off), so the individual times are not fully representative - it is the stability that matters the most. With the increasing number of records, the disadvantage of the non-sequential uuid PK starts to come through, when there are more accesses to the disk - work with different pages (the default set-up size of one page is 8 kB - can contain approx. 200 uuid) in which the index itself is saved.

After replacement of HDD by SSD:

binary(16) representing uuid as the primary key in PostgreSQL 9.4 - SSD disk
Number of records (master)Number of records (detail) Insert (master)Insert (detail)Load details by masterIndex Size (master)Index size (detail)PK size (detail)
100000019 380 690 5ms5ms 3.5ms68MB2653MB750MB
150000028 732 1775ms5ms4ms134MB4707MB1130MB
150000028 732 1775ms5ms4ms134MB4707MB1130MB
150000028 732 1775ms5ms4ms134MB4707MB1130MB

The use of binary(16) identifiers in PostgreSQL 9.4 on the number of records tested is (~40M) stable.