Set the sort order of a table

Declares how a table's data files should be sorted. DuckLake sorts data on insert (unless the sort_on_insert option is disabled), during compaction with merge_adjacent_files(), and when flushing inlined data with flush_inlined_data(). Sorted files carry tighter min/max statistics, so filters on the sort columns prune files instead of scanning them – the complement to set_table_partitioning() for high-cardinality columns.

Usage

set_table_sorting(table_name, sort_by)

Arguments

table_name

The name of the table to sort.

sort_by

Character vector of sort keys. Each entry is a column name, optionally followed by ASC or DESC and by NULLS FIRST or NULLS LAST, e.g. "event_time DESC" or "id ASC NULLS LAST".

Value

Invisibly returns NULL.

Details

Runs ALTER TABLE ... SET SORTED BY (...). Only newly written files are sorted; existing files keep their layout until compaction rewrites them.

DuckLake also accepts arbitrary SQL expressions as sort keys; this wrapper deliberately accepts only column-based keys so the input can be validated. For expression keys, run the ALTER TABLE statement directly with DBI::dbExecute().

To keep insert speed and sort the files only at compaction time, disable sorting on insert with set_ducklake_option("sort_on_insert", FALSE, table_name = ...).

See also

reset_table_sorting(), set_ducklake_option(), set_table_partitioning()

Other sorting: get_table_sorting(), reset_table_sorting()

Examples

lake_dir <- tempfile("sort_lake_")
dir.create(lake_dir)
attach_ducklake("sort_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Order rows so range filters can prune files
set_table_sorting("cars", "mpg")
#> Table "cars" is now sorted by "mpg".
#> ℹ Only newly written data is sorted; existing files keep their layout until
#>   compaction.

# Compound key with explicit directions
set_table_sorting("cars", c("cyl ASC", "mpg DESC"))
#> Table "cars" is now sorted by "cyl ASC" and "mpg DESC".
#> ℹ Only newly written data is sorted; existing files keep their layout until
#>   compaction.

detach_ducklake("sort_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)