Advanced database configuration

Normal database access configuration

There are two databases that we use in the QuickCase application:

• definitions - this holds case definitions
• cases - this holds the actual cases

Access to these is configured in the store: section of the YAML config:

store:
  cases:
    db_name: cases
    host: somehost.com
    port: 5432
    password: VerySecret
    username: quickcase
    options: {}
  definitions:
    db_name: definitions
    host: somehost.com
    port: 5432
    password: AlsoSecret
    username: quickcase

The setting of all of these depend on the setup of the PostgreSQL database you are using.

The two databases cases and definitions are configured in the same way. If you are using a single database instance, then the common server config host, port, etc is simply replicated in each configuration. While the other configuration may vary.

Database options configuration

To pass options to the PostgreSQL database we use the options: section. The generically works as follows:

    options:
      option: value
      option2: value2

Examples of this are in the SSL and schema configuration.

Single database using schemas

A single database making use of schemas should not to be confused with a single server instance with multiple databases. In this case it is a single PostgreSQL database with data-store and definitions-store data stored under different PostgreSQL schemas.

The same concerns about load apply as with a single database server instance.

The main reason for doing this would be to take advantage of some of the provisioned database services such as AWS Aurora where a single database is provisioned and where you don't control the instance to be able to create a second database on the instance.

SQL uses the concept of schemas to allow a database to be separated into sections that contains different tables, indexes, access rights, etc.

In a standard database deployment we make use of the default schema. The only difference now is that we will create two named schemas.

The definition-store and data-store database definitions are already defined to be schema neutral so that they can be installed into any named schema.

The schemas need to be created in the database before the applications are deployed. When you deploy the application they will connect to the database and create the SQL objects within the named schema.

Access the database directly and run the following to create two named schemas:

CREATE SCHEMA IF NOT EXISTS cases;
CREATE SCHEMA IF NOT EXISTS definitions;

The connection string to access the data needs to be altered so that the applications use the correct named schema:

store:
  cases:
    options:
      currentSchema: cases
  definitions:
    options:
      currentSchema: definitions

Serverless

A serverless PostgreSQL database deployment is one where you do not control the instance in any way. They are able to scale quickly from their minimum configuration to the load currently needed and also scale back down when load dissipates.

This is a helpful configuration if:

1. Your database load is unpredictable during normal operations. That is it can be high or low depending on a host of environmental factors. The server will scale as required but you only ever pay for what was provisioned, not for the maximum size you deployed.
2. Your load is time based. Load occurs during daytime business hours. In this case a serverless instance would scale down at night, but still be able to scale back up if a night-time job requires load. It would scale back up when daytime load starts.
3. Adhoc load. An event generates a large load for a few hours or days. For instance, you release something new and many people access the server. Serverless allows the server to scale without you needing to know in advance when exactly the load will occur, and how much to scale up the database, and when the load abates.

When using serverless the following are not required but at worth evaluating:

1. Using schemas in a single database
2. Scaling down to zero

Serverless scaling to zero

A serverless server can be scaled down to zero database server instances. So no database server is running. The only cost incurred then is the cost of storing the data.

This allows an instance that receives very little or no traffic at certain times to be turned off, thus saving database costs.

The disadvantage is that it will take a period of time for the database to be reprovisioned if the application requires it. You control this by determining the time of no traffic load before the database is turned off. The time taken to reprovision is not within your control.

In order for this to work correctly we need to turn activity off that would wake the database unnecessarily.

data_store:
  environment:
    MANAGEMENT_HEALTH_DB_ENABLED: "false"
    SPRING_DATASOURCE_HIKARI_MINIMUMIDLE: "0"

definition_store:
  environment:
    MANAGEMENT_HEALTH_DB_ENABLED: "false"
    SPRING_DATASOURCE_HIKARI_MINIMUMIDLE: "0"

With this configuration we are turning off:

1. Database health checks - the health check pings will cause the database to be reprovisioned.
2. Recycling of database connections - the database connection pool will recreate old connections following an idle timeout. The timeout is disabled with this change.

SSL Configuration

SSL ensures that data traffic between you and the server is encrypted. While you would in most instances want this on. There are reasons why you might want to disable this e.g.:

• If you are using a sidecar proxy. The proxy does the SSL and there is no need or ability to encrypt between the pods.
• You are running locally without an SSL certificate

You can configure the SSL mode easily as follows:

store:
  cases:
    options:
      sslmode: require
  definitions:
    options:
      sslmode: require