Delete data

This article describes delete scenarios, including purge, dropping extents and retention based deletes.

Delete data from a table is supported in several ways. Use the following information to help you choose which deletion method is best for your use case.

Use case	Considerations	Method
Delete all data from a table.		Use the `.clear table data` command
Routinely delete old data.	Use if you need an automated deletion solution.	Use a retention policy
Bulk delete specific data by extents.	Only use if you’re an expert user.	Use the `.drop extents` command
Delete records based on their content.	- Storage artifacts that contain the deleted records aren’t necessarily deleted. - Deleted records can’t be recovered (regardless of any retention or recoverability settings). - Use if you need a quick way to delete records.	Use soft delete
Delete records based on their content.	- Storage artifacts that contain the deleted records are deleted. - Deleted records can’t be recovered (regardless of any retention or recoverability settings). - Requires significant system resources and time to complete.	Use purge

Use case	Considerations	Method
Delete all data from a table.		Use the `.clear table data` command
Routinely delete old data.	Use if you need an automated deletion solution.	Use a retention policy
Bulk delete specific data by extents.	Only use if you’re an expert user.	Use the `.drop extents` command
Delete records based on their content.	- Storage artifacts that contain the deleted records aren’t necessarily deleted. - Deleted records can’t be recovered (regardless of any retention or recoverability settings). - Use if you need a quick way to delete records.	Use soft delete

The following sections describe the different deletion methods.

Delete all data in a table

To delete all data in a table, use the .clear table data command. This command is the most efficient way to remove all data from a table.

Syntax:

.clear table <TableName> data

Delete data using a retention policy

Automatically delete data based on a retention policy. You can set the retention policy at the database or table level. There’s no guarantee as to when the deletion occurs, but it will not be deleted before the retention period. This is an efficient and convenient way to remove old data.

Consider a database or table that is set for 90 days of retention. If only 60 days of data are needed, delete the older data as follows:

.alter-merge database <DatabaseName> policy retention softdelete = 60d

.alter-merge table <TableName> policy retention softdelete = 60d

Delete data by dropping extents

Extent (data shard) is the internal structure where data is stored. Each extent can hold up to millions of records. Extents can be deleted individually or as a group using drop extent(s) commands.

Examples

You can delete all rows in a table or just a specific extent.

Delete all rows in a table:
```
.drop extents from TestTable
```

Delete a specific extent:

.drop extent e9fac0d2-b6d5-4ce3-bdb4-dea052d13b42

Delete individual rows

Both purge and soft delete can be used for deleting individual rows. Soft delete doesn’t necessarily delete the storage artifacts that contain records to delete, and purge does delete all such storage artifacts.

Both methods prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible.

Soft delete

With soft delete, data isn’t necessarily deleted from storage artifacts. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

Purge

With purge, extents that have one or more records to be deleted, are replaced with new extents in which those records don’t exist. This deletion process isn’t immediate, requires significant system resources, and can take a whole day to complete.

Soft delete can be used for deleting individual rows. Data isn’t necessarily deleted from storage artifacts. Soft delete prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

Feedback

Was this page helpful?

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.