Sorted Tables
Altertable catalog tables can be configured with a sort order. When a sort order is defined, data is physically sorted by the specified columns whenever it is written out as data files — during INSERT, file compaction, and inlined data flushing.
Sorting data before writing improves the effectiveness of min/max statistics at query time, which lets the query engine skip data files whose value ranges do not overlap with a query's filter predicates.
Run the statements below through Altertable's SQL engine against an Altertable catalog you own.
Set a sort order
Set the sort order for a table:
ALTER TABLE my_catalog.main.sensor_readings SET SORTED BY (recorded_at ASC);
Multiple sort keys are supported:
ALTER TABLE my_catalog.main.sensor_readings SET SORTED BY (recorded_at ASC, sensor_id DESC);
ASC and DESC control the sort direction. NULLS FIRST and NULLS LAST are also supported to control null ordering:
ALTER TABLE my_catalog.main.sensor_readings SET SORTED BY (recorded_at ASC NULLS LAST);
Expression-based sort keys
Arbitrary expressions are supported in SET SORTED BY, not just column references. This includes function calls, casts, and catalog macros.
Sort by the hour extracted from a timestamp:
ALTER TABLE my_catalog.main.sensor_readings SET SORTED BY (date_trunc('hour', recorded_at) ASC);
Sort by a macro:
CREATE MACRO reading_day(t) AS date_trunc('day', t);ALTER TABLE my_catalog.main.sensor_readings SET SORTED BY (reading_day(recorded_at) ASC);
Expressions are validated when SET SORTED BY runs — an error is returned if any referenced columns or functions cannot be resolved.
Remove a sort order
Remove the sort order from a table:
ALTER TABLE my_catalog.main.sensor_readings RESET SORTED BY;
After resetting, subsequent compactions and flushes write data without sorting.
When to sort
Sort when queries repeatedly filter or range-scan on the same columns — especially timestamps, status codes, or IDs used in WHERE and ORDER BY clauses. Sorting trades write-time cost for tighter file statistics and fewer files scanned at read time.
Skip sorting when write throughput is the bottleneck, filters are unpredictable, or tables stay small enough that full scans are cheap. You can add, change, or remove a sort order later; compaction and flush apply the current order going forward.
Learn more
- Altertable Catalogs: Create and manage managed catalogs
- Partitioning: Split writes by partition key for file-level pruning
- SQL Engine: DuckDB dialect and federated queries