Database Optimisation

Databases are mission critical elements to any solution. Connecting external systems needs to be done with a great deal of care. We have built the Database sync to ensure that your infrastructure is treated with respect and operations as light-weight as possible.

There are of course some things that can be done within your infrastructure as well. The following describes some basic database hygiene we recommend when preparing your database and infrastructure to connect to – and of course if you have any questions please reach out to our Support.

  1. Read-only user account: Except in the case of deploying a Data Warehouse Destination will not attempt to write any data into your database. It is always advisable to set the least amount of permissions required and we recommend that you provision a dedicated database user account with ‘read-only’ access. It is also advisable to set permissions only to the “Distil” views/tables described in this document. has no need, nor desire, to access any other information.  
  2. Read-Replica: We have designed the Database Sync to be lightweight and minimise calls to your database as much as possible. If you are worried about additional load being placed on your database infrastructure then setting up a read-replica is advised. 
  3. Indexing: Database performance is greatly improved by indexing tables. As the Views you create are reasonably fixed it is worth spending a bit of time checking the query execution plan to ensure you have appropriate indexes in place. 

Materialised Views (or Caching Tables)

In the documents that describe the Views we ask you to create we state that will interrogate the schema and identify any Views that match the naming conventions laid out later in this document. However there is an additional facet of this, in that will also look for tables that match the naming convention. 

If you are concerned with heavy querying of your database, for example because you have heavy analytical queries, you may wish to consider copying your Customer data into a Table that is refreshed on an hourly or even daily basis. Or if your database supports it, using a Materialized View. This will significantly reduce the load on your system during sync for those heavy analytical cases.