After careful consideration, we decided to end support for Amazon FinSpace, effective October 7, 2026. Amazon FinSpace will no longer accept new customers beginning October 7, 2025. As an existing customer with an Amazon FinSpace environment created before October 7, 2025, you can continue to use the service as normal. After October 7, 2026, you will no longer be able to use Amazon FinSpace. For more information, see [Amazon FinSpace end of support](https://docs.aws.amazon.com/finspace/latest/userguide/amazon-finspace-end-of-support.html). # Database maintenance Amazon FinSpace Managed kdb allows you to perform schema changes to your database like adding a new column, updating a column type, and renaming columns, etc. You can perform the database maintenance operations by creating a general purpose cluster with a writable dataview. A writable dataview allows you to make updates to your kdb database locally on a cluster. To avoid caching the whole kdb database on a cluster, you can enable on-demand caching for your dataview segments. The dataview will only load the filesystem metadata of your database files for the segments with on-demand caching and loads the actual file content as they are accessed by a database maintenance operation. You can implement a database maintenance script and run it as an initialization script. An initialization script can run for multiple hours without being interrupted, which is required for long-running database maintenance tasks. When database maintenance script is running, monitor the cluster logs for progress and any errors. After the database maintenance script completes, connect to the cluster to verify the updated kdb database and commit changes by using the `commit_kx_database` q API. The API creates a changeset and returns the changeset id, which you can use to monitor the changeset status through either the FinSpace API or console. You can also automate verification and commit steps in your database maintenance script itself. For more information, see the following sections. **Topics** + [ # Setting up for database maintenance ](dbmaint-writable-database-dataviews.md) + [ # Performing database maintenance ](dbmaint-long-running-dbmaint.md) # Setting up for database maintenance Setting up for database maintenance To perform the database maintenance operations, you need a writeable shallow copy of a database . A writable shallow copy of a kdb database only loads the metadata of your database files to make them visible on the file system and loads the actual file content as they are accessed. To optimise time and memory utilization, it is recommended not to load file content initially, as not all files may be necessary for a database maintenance operation. For instance, in the case of renaming a table, no files are read or updated directly. To create a writeable shallow copy of database, you can create dataviews with read write property set as true and enable on-demand caching in the configuration. A dataview performs minimal loading of files on the file system as needed by a database maintenance operation when on-demand caching is enabled. Reading an existing database file for the first time is slower as compared to accessing the files that have been previously read or newly written. This is because files are loaded onto the file system as they are accessed in case of on-demand caching. ## Creating writeable dataviews by using console Before you proceed, complete the following prerequisites: + Create a kdb environment. For more information, see [Creating a kdb environment](using-kdb-environment.md#create-kdb-environment). + Create a kdb database. For more information, see [Creating a kdb database](using-kdb-db.md#create-kdb-db). + Create a new changeset. For more information, see [Creating a new changeset](using-kdb-db.md#kdb-db-changesets). + Create a kdb volume. Make sure this volume is not used by any other resource. For more information, see [Creating a Managed kdb volume](create-volumes.md). **To create a writeable dataview** 1. Sign in to the AWS Management Console and open the Amazon FinSpace console at [https://console.aws.amazon.com/finspace](https://console.aws.amazon.com/finspace/landing). 1. In the left pane, under **Managed kdb Insights**, choose **Kdb environments**. 1. From the kdb environments table, choose the name of the environment. 1. On the environment details page, choose the **Databases** tab. 1. From the list of databases, choose a database name. 1. On database details page, choose the **Dataviews** tab. 1. Choose **Create dataview**. 1. On the **Create dataview** page, enter a unique name for the dataview. 1. (Optional) Enter a description for your dataview. 1. Choose the availability zone that you want to associate with the dataview. Currently, you can only choose single availability zone. 1. Under **Changeset update settings**, do the following. 1. Choose **Static** mode of update. **Note** The **Read Write** option is only available for **Static** update mode as you cannot perform automatic updates on a writeable dataview. 1. Select the **Changeset ID** for the changeset you created to indicate which version of data you want. 1. Choose **Read Write** as **True** to make this dataview as writeable. You cannot change this later. 1. Add **Segment configuration**. **Note** The **Segment configuration** is required if **Read Write** is **True**. You can only add one segment for a writeable dataview. The **Database path** is disabled and defaults to **\$1\$1** when **Read Write** is **True** as you cannot have partial writeable dataviews on cache. 1. Choose a volume for caching. Use an exclusive volume for writable dataviews, it should not be in use by any other dataviews. 1. For **On demand caching**, choose **True** to enable on demand caching on the selected database path when a particular file or a column of a database is accessed. When you enable on demand caching, files will only be copied to the dataview when they are accessed by code for reading or writing. When you disable on demand caching, everything is cached. The default value is **False**. 1. Choose **Create dataview**. The database details page opens and the table under **Dataviews** lists the newly created database along with its status. ## Creating writeable dataviews by using FinSpace API operations Before you proceed, complete the following prerequisites: + Create a kdb environment by using the [CreateKxEnvironment](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxEnvironment.html) API operation. + Create a kdb database by using the [CreateKxDatabase](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxDatabase.html) API operation. + Create a new changeset by using the [CreateKxChangeset](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxChangeset.html) API operation. + Create a kdb volume by using the [CreateKxVolume](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxVolume.html) API operation. Make sure this volume is unique for this dataview and is not used by any other resource. To create a dataview with writable shallow copy of a database, create a dataview with the volume that has writable segments by using the [CreateKxDataview](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxDataview.html) API operation. You can make dataview as writeable by setting the `readWrite` parameter as true. You can only use this parameter for a static update mode. The `onDemand` parameter allows you to enable or disable on-demand caching on the selected dbPaths. Sample `CreateKxDataview` API request ``` { "autoUpdate": false, "availabilityZoneId": "use1-az1", "clientToken": "65117136-4421-4371-0f1a-ce012823126", "changesetId": "latest_changesetId", "readWrite": true, "segmentConfigurations": [{ "volumeName": "test_vol", "dbPaths": [ "/*" ], "onDemand": true } ], "azMode": "SINGLE", "dataviewName": "test_dv" } ``` Following are some of the considerations for the above request. + The `autoUpdate` must be `false` for if `readWrite` is `true` on the dataviews. + You need exclusive volume for creating a writable dataview. The volume mentioned in the `segmentConfiguration` should not be used by any other dataview. + The `dbPath` must be set as "/\$1" for writable dataview. + Only a single `segmentConfiguration` is allowed when `readWrite` is true. The `dbPaths` on the segment should be set as "\$1\$1" . + A dataview with `readWrite` set as `true` is not allowed to be updated. + You cannot update the `readWrite` property later. + A dataview can only have a single segment if `onDemand` is `true` on a segment. # Performing database maintenance After you create a writeable dataview, you create a scaling group general purpose cluster to run a long-running database maintenance script. For this, you use the cluster `initializationScript` attribute. The database maintenance script could run for multiple hours without being terminated. When database maintenance script is running, monitor the cluster logs for progress and any errors from the database maintenance script. After the database maintenance script completes, connect to the cluster to verify the updated kdb database and commit changes to the underlying kdb database by using the `commit_kx_database` q API. You can also automate these steps in your database maintenance script itself. ## Steps to perform database maintenance using a scaling group general purpose cluster 1. Create a general purpose cluster in the scaling group with the previously created data view and provide database maintenance script using `initializationScript` in the [CreateKxCluster](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxCluster.html) API operation. After you create the cluster, wait till the status changes to `Running`. During this time, you can monitor the logs from the cluster for progress and any errors from the database maintenance script. 1. Call the [GetKxConnectionString](https://docs.aws.amazon.com/finspace/latest/management-api/API_GetKxConnectionString.html) API to get a `signedConnectionString` for the cluster. 1. Connect to the cluster and verify the kdb database state by running q commands. 1. Call the `commit_kx_database` q API with the database name to apply the changes to the source kdb database. 1. Call the [GetKxChangset](https://docs.aws.amazon.com/finspace/latest/management-api/API_GetKxChangeset.html) API operation to check the status of the commit database changeset. After the kdb database is successfully updated, you can load the updated kdb database on an existing HDB cluster by calling the [UpdateKxClusterDatabases](https://docs.aws.amazon.com/finspace/latest/management-api/API_UpdateKxClusterDatabases.html) API operation or on a new HDB cluster by calling the [CreateKxCluster](https://docs.aws.amazon.com/finspace/latest/management-api/API_CreateKxCluster.html) API operation. ## Steps to perform database maintenance using `dbmaint.q` This is section shows how you can perform database maintenance on a partitioned database by using a `dbmaint.q` script. The following example explains how you can load the `dbmaint.q` script on a general purpose cluster that runs on a scaling group, add a new column to a table, and finally commit the database to create a changeset. 1. Load the [https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.q](https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.q) script by running the following command. This script contains utility functions for maintenance of partitioned database tables in kdb\$1. ``` q) \l /opt/kx/app/code/dbmaint/dbmaint.q ``` 1. Load a database. ``` q) \l /opt/kx/app/db/welcomedb ``` 1. Inspect the table schema in your database. ``` q) meta example c | t f a ------| ----- date | d sym | s p time | p number| j ``` 1. Change to the database parent directory. ``` q) \cd /opt/kx/app/db ``` 1. Add a new column using the `addcol` function from the `dbmaint.q` script. ``` addcol[`:welcomedb;`example;`price;0h]; ``` 1. Inspect the updated table schema with the newly added column. ``` q)con "meta example" c | t f a ------| ----- date | d sym | s p time | p number| j price | h ``` 1. Commit the database changes by calling the `.aws.commit_kx_changeset` q API. The API creates a changeset and returns the id, which you can use to monitor the changeset status through the FinSpace API or console. ``` q) .aws.commit_kx_database["welcomedb"] id | "UscXQcZ2htijCQlr1xNaIA" status| "PENDING" ``` **Note** The recommended way to perform a long-running database maintenance is to implement a database maintenance script and execute it as cluster initialization script. An initialization script can run for multiple hours without being interrupted which is required for long-running database maintenance tasks. When database maintenance script is running, monitor the cluster logs for progress and any errors. After the database maintenance script completes, connect to the cluster to verify the updated kdb database and commit changes to the underlying kdb database by using the commit\$1kx\$1database q API. You can also automate verification and commit steps in your database maintenance script itself.