@Rocky expand on offset vs keyset pagination in primary key table and the efficiency

In StarRocks Primary Key (PK) tables, the choice between Offset and Keyset pagination significantly impacts performance, especially as datasets grow. 1. Offset Pagination (

LIMIT offset, count

) This is the traditional method using

LIMIT

and

OFFSET

clauses. * How it works: The engine scans through all rows from the beginning of the result set, discards the number of rows specified by

OFFSET

, and returns the next

count

rows. * Efficiency: * Low Efficiency for Deep Paging: As the offset increases (e.g.,

LIMIT 1000000, 10

), the performance degrades linearly. StarRocks must still read and process those 1 million rows before discarding them. * Resource Heavy: In a distributed system, this requires gathering and sorting large amounts of data across multiple BEs (Backends) only to throw most of it away. * Best Use Case: Small datasets or "shallow" paging (e.g., only the first few pages of a UI). 2. Keyset Pagination (Seek Method) This method uses a

WHERE

filter on a unique, ordered column (typically the Primary Key) instead of an offset. * How it works: You "seek" to the next page by filtering for values greater than the last record of the previous page. _ Example:

SELECT _ FROM table WHERE pk_id > {last_id_from_prev_page} ORDER BY pk_id ASC LIMIT 10;

* Efficiency: * Constant Performance: Performance remains stable regardless of how deep you are in the dataset. * Index Utilization: StarRocks Primary Key tables keep the PK index in memory (or preloaded). By using the PK in the

WHERE

clause, the engine can use the prefix index and zone maps to jump directly to the relevant data pages, skipping massive amounts of I/O. * Reduced Materialization: Only the 10 rows actually needed are fully materialized and sent over the network. * Best Use Case: Large datasets, deep paging, or high-concurrency pagination APIs. Why Primary Key Tables excel at Keyset Pagination Primary Key tables in StarRocks use a specialized storage engine that enhances this pattern: 1. Memory-Resident PK Index: Since PK indexes are often in memory, finding the starting point for a "seek" is nearly instantaneous. 2. Delete+Insert Strategy: Unlike Merge-On-Read tables, PK tables use a DelVector to track deletes. This means the scan layer only reads the latest version of data, making the "seek" and subsequent "limit" scan much faster (3–10x improvement over Unique Key models). 3. Table Clustering: If your pagination column is also part of the Sort Key (which the PK is by default), StarRocks uses the short-key (prefix) index to perform millisecond-level range seeks. Summary Comparison Feature | Offset Pagination | Keyset Pagination SQL Syntax |

LIMIT 1000, 10

|

WHERE id > 1000 ... LIMIT 10

Scaling | Degrades as offset grows | Constant performance I/O Impact | Reads and discards all skipped rows | Reads only the target rows Consistency | Risk of missing/duplicate rows if data changes | Stable and consistent Recommendation | Use for small/simple offsets | Best practice for deep paging large- <../../sql-reference/sql-statements/table_bucket_part_index/SELECT.md|SELECT> • <../../table_design/table_types/primary_key_table.md|Primary Key table> • <../../knowledge/usage/best-practice.md|Experience sharing> References • table_design/table_types/primary_key_table.md • best_practices/table_clustering.md • best_practices/primarykey_table.md • knowledge/usage/best-practice.md

@Rocky It seems changing lake_compaction_interval_ms_on_success was the key. Isn't it basically the equivalent of reducing update_compaction_per_tablet_min_interval_seconds in shared nothing cluster from 120s to lower? For my shared nothing cluster I set it to 20s instead. Changing lake_compaction_interval_ms_on_success to 2000ms seemed to help a lot. This just makes compaction more aggressive and cost more CPU right? Does this mean 5x S3 round trips in the shared data cluster for compaction? Or just 5x more compaction on partitions but same number of s3 round trips

Yes, your understanding is correct. These two parameters are conceptually equivalent in their respective architectures, though they operate at different granularities and are managed by different components. 1. Are they equivalent? Yes. Both parameters serve as a "cooldown" timer to prevent a single data unit from being compacted too frequently, which protects the system from excessive resource consumption. *

update_compaction_per_tablet_min_interval_seconds

(Shared-Nothing): Managed by the BE. It works at the tablet level. In Shared-Nothing, because data is distributed across local disks, the BEs independently decide when a tablet needs compaction based on this interval. *

lake_compaction_interval_ms_on_success

(Shared-Data): Managed by the FE. It works at the partition level. In a Shared-Data (Lakehouse) cluster, the FE acts as the central scheduler for compaction tasks across the entire cluster. 2. Efficiency and Resource Impact Reducing the interval from 10s to 2s (or 120s to 20s) makes the system more "sensitive" to new data. * CPU Usage: You are correct that this increases CPU cost. Compaction for Primary Key tables is CPU-intensive because it involves reading rowsets, checking the Primary Key index (often in memory), resolving delete vectors, and encoding new segments. By compacting more frequently, you are likely performing more "small" compactions, which increases the cumulative CPU cycles spent on background tasks. * Write Amplification: This is the primary trade-off. More frequent compaction usually leads to higher write amplification—meaning a specific row might be rewritten to storage multiple times as it is merged into increasingly larger rowsets. 3. S3 Round Trips vs. Data Volume Regarding your question about "5x S3 round trips": * API Calls (Metadata): Yes, it results in more S3 API calls. Every compaction task requires S3

LIST

,

GET

(for metadata/index), and

PUT

(for new segments/metadata) calls. If you trigger compaction 5x more often, you will see a corresponding increase in these S3 API operations. * Data Throughput: It is not necessarily 5x the data volume transferred, but it is 5x more compaction events. If you have a constant stream of small writes, a 2s interval will merge those small files into slightly larger ones almost immediately. With a 10s interval, those same small files would have sat there longer and been merged in a single, larger batch later. * The Cost: In S3, the "PUT" and "LIST" requests have a cost. While the data transfer itself might not quintuple, the overhead of managing many small compaction jobs (metadata updates, S3 request counts) will increase. Summary By lowering the interval to 2000ms, you have prioritized Query Latency and Data Freshness (by keeping the "Compaction Score" or number of versions low) at the expense of CPU overhead and S3 API costs. This is generally the right move if you are seeing "Too many versions" errors or if your PK table queries are slowing down due to high Rowset counts. • <../../administration/management/FE_configuration.md|FE Configuration> • <../../administration/management/compaction.md|Compaction for Shared-data Clusters> • <../../best_practices/primarykey_table.md|Primary Key table Best Practices> References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • administration/management/monitoring/metrics.md