mirror of
https://github.com/lancedb/lancedb.git
synced 2026-10-12 23:02:28 +00:00
Brendan Clement
6a1645116e
Creating, writing, updating, merging and deleting rows, managing tables, branches and read consistency work in the embedded library and on a deployment, and those pages now say so. The SQL forms they mention need a deployment. Where the offerings differ, the section says how: a deployment reuses an existing table without comparing schemas, a merge insert can be refused past a configured number of unindexed rows, space is reclaimed by optimize() locally and by the deployment's compaction and configured cleanup otherwise, renaming depends on the catalog, a deployment clones shallowly only, drops all tables only locally, keeps freshness state per table handle and defaults freshness to its own setting, and refuses to delete a branch main depends on. table_exists works only on a namespace connection. Guarantees and limits span both offerings, so they carry section notes rather than a label: reads, retention, index freshness and compaction differ on a deployment, and the name and job limits are a deployment's.
85 lines
2.9 KiB
Plaintext
85 lines
2.9 KiB
Plaintext
---
|
|
title: "Delete rows"
|
|
sidebarTitle: "Delete"
|
|
description: "Remove rows by predicate, and reclaim their space."
|
|
icon: trash
|
|
availability:
|
|
oss: available
|
|
enterprise: available
|
|
summary: >-
|
|
Rows are deleted in the embedded library and on a deployment. In the
|
|
embedded library you reclaim their space with `optimize()`; a deployment
|
|
compacts automatically and removes old versions as its configuration sets.
|
|
---
|
|
|
|
These examples use the table set up in
|
|
[Ingesting data](/ingestion), and the connection made there.
|
|
|
|
import {
|
|
PyDeleteOperation as DeleteOperation,
|
|
PyUpdateOptimizeCleanup as UpdateOptimizeCleanup,
|
|
TsDeleteOperation,
|
|
TsUpdateOptimizeCleanup,
|
|
RsDeleteOperation,
|
|
RsUpdateOptimizeCleanup,
|
|
} from '/snippets/tables.mdx';
|
|
|
|
<Note>
|
|
In SQL on a deployment, this is [`DELETE`](/sql/dml/delete).
|
|
</Note>
|
|
|
|
## Delete rows {#delete-rows}
|
|
|
|
Delete operations **soft delete** rows that match a given condition.
|
|
The underlying data is not immediately removed, but is marked
|
|
for deletion (in the [deletion files](https://lance.org/format/table/#deletion-files) at the Lance format level) and excluded from query results.
|
|
|
|
<CodeGroup>
|
|
<CodeBlock filename="Python" language="Python" icon="python">
|
|
{DeleteOperation}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
|
|
{TsDeleteOperation}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="Rust" language="Rust" icon="rust">
|
|
{RsDeleteOperation}
|
|
</CodeBlock>
|
|
</CodeGroup>
|
|
|
|
Expected table contents:
|
|
|
|
| id | name | login_count |
|
|
| --- | --- | --- |
|
|
| 1 | Alice | 10 |
|
|
| 2 | Bob | 20 |
|
|
|
|
|
|
<Warning>
|
|
**Deleting rows removes them from the index**
|
|
|
|
When rows are deleted, those rows are also excluded from the index segments, so indexed queries will not return them either. If ALL the rows are deleted (i.e., the table is emptied), ensure that you recreate the index after ingesting new data.
|
|
</Warning>
|
|
|
|
To permanently remove deleted rows, the table has to be compacted and its old versions cleaned up, which frees up storage space.
|
|
|
|
- In LanceDB OSS, compaction and cleanup are manual. Run `table.optimize()` regularly to free up disk space.
|
|
- In LanceDB Enterprise, compaction runs automatically, and old versions are removed only when the deployment's configuration enables cleanup, with the retention period it sets. `optimize()` is not available there; see [Cleanup](/maintenance/cleanup).
|
|
|
|
In OSS, `optimize()` removes versions older than 7 days by default. If you need to reclaim space from deleted rows more aggressively, call `table.optimize()` with a shorter retention window as follows:
|
|
|
|
<CodeGroup>
|
|
<CodeBlock filename="Python" language="Python" icon="python">
|
|
{UpdateOptimizeCleanup}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
|
|
{TsUpdateOptimizeCleanup}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="Rust" language="Rust" icon="rust">
|
|
{RsUpdateOptimizeCleanup}
|
|
</CodeBlock>
|
|
</CodeGroup>
|