You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by ri...@apache.org on 2026/10/04 10:32:50 UTC

(asterixdb) branch master updated: [ASTERIXDB-3885][STO] Compress secondary btree indexes

This is an automated email from the ASF dual-hosted git repository.

asf-gitbox-commits pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git


The following commit(s) were added to refs/heads/master by this push:
     new 620fbdabd6 [ASTERIXDB-3885][STO] Compress secondary btree indexes
620fbdabd6 is described below

commit 620fbdabd6b62a2f38f43acca071db0e018f2aeb
Author: Ritik Raj <ra...@gmail.com>
AuthorDate: Thu Aug 27 11:58:42 2026 +0530

    [ASTERIXDB-3885][STO] Compress secondary btree indexes
    
    - user model changes: yes
    - storage format changes: no
    - interface changes: no
    
    Details: A secondary BTREE or ARRAY index was never compressed. It now
    takes a scheme of its own, as a dataset does:
    
        CREATE INDEX i ON ds(f)
          WITH {"storage-block-compression": {"scheme": "none"}};
    
    and the configured default where it names none. The scheme is resolved
    when the index is created and recorded in its metadata, so a later
    change of the default leaves the index alone. An index recorded without
    one is uncompressed, as every existing secondary index is.
    
    The cost, measured by SecondaryIndexCompressionBenchmark (added here),
    is decompression CPU paid once per page load, not per access: +3 us per
    page at 6% saved to +18 us at 39% saved. A cached working set pays
    nothing, since buffer cache pages are held uncompressed.
    
    Dataset statistics now count a compressed index's pages as read through
    the buffer cache, not by its size on disk, which understated them to the
    optimizer.
    
    Generated-by: Claude Opus 5 <no...@anthropic.com>
    Generated-by: Claude Opus 5.5 (1M context) <no...@anthropic.com>
    Change-Id: I894eed2fbe262c5d53a077740cf804bda1b30230
    Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21625
    Tested-by: Ritik Raj <ri...@couchbase.com>
    Reviewed-by: Murtadha Hubail <mh...@apache.org>
---
 .../asterix/app/translator/QueryTranslator.java    |  10 +-
 .../metadata/results/basic/meta05/meta05.1.adm     |   2 +-
 .../metadata/results/basic/meta16/meta16.1.adm     |   2 +-
 .../metadata/results/custord/custord_index.adm     |   6 +-
 .../secondary-index-invalid-scheme.1.ddl.sqlpp     |  27 ++
 .../secondary-index-scheme.1.ddl.sqlpp             |  37 ++
 .../secondary-index-scheme.2.update.sqlpp          |  26 ++
 .../secondary-index-scheme.3.query.sqlpp           |  23 +
 .../secondary-index-scheme.4.query.sqlpp           |  26 ++
 .../secondary-index-unsupported-field.1.ddl.sqlpp  |  27 ++
 .../secondary-index-unsupported-type.1.ddl.sqlpp   |  27 ++
 .../secondary-index-scheme.3.adm                   |   5 +
 .../secondary-index-scheme.4.adm                   |   1 +
 .../create_dataset_with_filter_on_meta.1.adm       |   2 +-
 .../ddl/index-cast-null/index-cast-null.015.adm    |   2 +-
 .../create_dataset_with_filter_on_meta.1.adm       |   2 +-
 .../ddl/index-cast-null/index-cast-null.015.adm    |   2 +-
 .../src/test/resources/runtimets/sqlpp_queries.xml |  26 ++
 .../common/statement/CreateIndexStatement.java     |  15 +-
 .../common/util/DatasetDeclParametersUtil.java     |  22 +
 .../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj    |  16 +-
 .../ArrayBTreeResourceFactoryProvider.java         |   6 +-
 .../declared/BTreeResourceFactoryProvider.java     |  10 +-
 .../apache/asterix/metadata/entities/Index.java    |  19 +
 .../IndexTupleTranslator.java                      |  42 +-
 .../DatasetStreamStatsOperatorDescriptor.java      |   9 +
 .../common/SecondaryIndexCompressionBenchmark.java | 477 +++++++++++++++++++++
 27 files changed, 834 insertions(+), 35 deletions(-)

diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
index ff94f0531c..e112bc36b9 100644
--- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
+++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
@@ -254,6 +254,7 @@ import org.apache.asterix.om.types.hierachy.ATypeHierarchy;
 import org.apache.asterix.om.utils.RecordUtil;
 import org.apache.asterix.om.vector.VectorIndexParameters;
 import org.apache.asterix.optimizer.rules.visitor.FunctionCardinalityInferenceVisitor;
+import org.apache.asterix.runtime.compression.CompressionManager;
 import org.apache.asterix.runtime.fulltext.AbstractFullTextFilterDescriptor;
 import org.apache.asterix.runtime.fulltext.FullTextConfigDescriptor;
 import org.apache.asterix.runtime.fulltext.StopwordsFullTextFilterDescriptor;
@@ -2003,8 +2004,13 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
                 }
             }
 
+            // resolved now rather than when the index is built, so the catalog records what the index was built
+            // with even if the configured default changes later, as for a dataset
+            String compressionScheme = indexType == IndexType.BTREE || indexType == IndexType.ARRAY ? metadataProvider
+                    .getCompressionManager().getDdlOrDefaultCompressionScheme(stmtCreateIndex.getCompressionScheme())
+                    : CompressionManager.NONE;
             Index newIndex = new Index(databaseName, dataverseName, datasetName, indexName, indexType, indexDetails,
-                    stmtCreateIndex.isEnforced(), false, MetadataUtil.PENDING_ADD_OP, creator);
+                    stmtCreateIndex.isEnforced(), false, MetadataUtil.PENDING_ADD_OP, creator, compressionScheme);
 
             bActiveTxn = false; // doCreateIndexImpl() takes over the current transaction
             EntityDetails entityDetails = EntityDetails.newIndex(databaseName, dataverseName, datasetName, indexName);
@@ -5927,7 +5933,7 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
             MetadataManager.INSTANCE.addIndex(mdTxnCtx,
                     new Index(databaseName, dataverseName, datasetName, indexName, index.getIndexType(),
                             index.getIndexDetails(), index.isEnforced(), index.isPrimaryIndex(),
-                            MetadataUtil.PENDING_DROP_OP, index.getCreator()));
+                            MetadataUtil.PENDING_DROP_OP, index.getCreator(), index.getCompressionScheme()));
         }
     }
 
diff --git a/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta05/meta05.1.adm b/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta05/meta05.1.adm
index 5f1d0714c4..e75d36cb0b 100644
--- a/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta05/meta05.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta05/meta05.1.adm
@@ -1,2 +1,2 @@
-{ "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "idx1", "IndexStructure": "BTREE", "SearchKey": [ [ "name" ] ], "IsPrimary": false, "Timestamp": "Tue Jun 21 15:54:27 PDT 2016", "PendingOp": 0, "ExcludeUnknownKey": false }
+{ "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "idx1", "IndexStructure": "BTREE", "SearchKey": [ [ "name" ] ], "IsPrimary": false, "Timestamp": "Tue Jun 21 15:54:27 PDT 2016", "PendingOp": 0, "ExcludeUnknownKey": false, "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
 { "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "t1", "IndexStructure": "BTREE", "SearchKey": [ [ "id" ] ], "IsPrimary": true, "Timestamp": "Tue Jun 21 15:54:27 PDT 2016", "PendingOp": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta16/meta16.1.adm b/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta16/meta16.1.adm
index 300715d151..0b55f68931 100644
--- a/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta16/meta16.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/metadata/results/basic/meta16/meta16.1.adm
@@ -1,2 +1,2 @@
-{ "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "idx1", "IndexStructure": "BTREE", "SearchKey": [ [ "name" ] ], "IsPrimary": false, "Timestamp": "Tue Jun 21 15:54:28 PDT 2016", "PendingOp": 0, "SearchKeyType": [ "string" ], "IsEnforced": true, "ExcludeUnknownKey": false }
+{ "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "idx1", "IndexStructure": "BTREE", "SearchKey": [ [ "name" ] ], "IsPrimary": false, "Timestamp": "Tue Jun 21 15:54:28 PDT 2016", "PendingOp": 0, "SearchKeyType": [ "string" ], "IsEnforced": true, "ExcludeUnknownKey": false, "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
 { "DataverseName": "testdv", "DatasetName": "t1", "IndexName": "t1", "IndexStructure": "BTREE", "SearchKey": [ [ "id" ] ], "IsPrimary": true, "Timestamp": "Tue Jun 21 15:54:28 PDT 2016", "PendingOp": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/metadata/results/custord/custord_index.adm b/asterixdb/asterix-app/src/test/resources/metadata/results/custord/custord_index.adm
index 06f6bd5954..be791a7910 100644
--- a/asterixdb/asterix-app/src/test/resources/metadata/results/custord/custord_index.adm
+++ b/asterixdb/asterix-app/src/test/resources/metadata/results/custord/custord_index.adm
@@ -1,5 +1,5 @@
 { "DataverseName": "custord", "DatasetName": "Customers", "IndexName": "Customers", "IndexStructure": "BTREE", "SearchKey": [ "cid", "name" ], "IsPrimary": true, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012" }
-{ "DataverseName": "custord", "DatasetName": "Customers", "IndexName": "custName", "IndexStructure": "BTREE", "SearchKey": [ "name", "cid" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012" }
+{ "DataverseName": "custord", "DatasetName": "Customers", "IndexName": "custName", "IndexStructure": "BTREE", "SearchKey": [ "name", "cid" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
 { "DataverseName": "custord", "DatasetName": "Orders", "IndexName": "Orders", "IndexStructure": "BTREE", "SearchKey": [ "oid" ], "IsPrimary": true, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012" }
-{ "DataverseName": "custord", "DatasetName": "Orders", "IndexName": "ordClerkTotal", "IndexStructure": "BTREE", "SearchKey": [ "clerk", "total" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012" }
-{ "DataverseName": "custord", "DatasetName": "Orders", "IndexName": "ordCustId", "IndexStructure": "BTREE", "SearchKey": [ "cid" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012" }
+{ "DataverseName": "custord", "DatasetName": "Orders", "IndexName": "ordClerkTotal", "IndexStructure": "BTREE", "SearchKey": [ "clerk", "total" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
+{ "DataverseName": "custord", "DatasetName": "Orders", "IndexName": "ordCustId", "IndexStructure": "BTREE", "SearchKey": [ "cid" ], "IsPrimary": false, "Timestamp": "Thu Sep 13 14:20:57 PDT 2012", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-invalid-scheme/secondary-index-invalid-scheme.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-invalid-scheme/secondary-index-invalid-scheme.1.ddl.sqlpp
new file mode 100644
index 0000000000..cbc48b0ee4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-invalid-scheme/secondary-index-invalid-scheme.1.ddl.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE T AS { id: bigint };
+CREATE DATASET DS(T) PRIMARY KEY id;
+
+CREATE INDEX idx ON DS(name: string) WITH {"storage-block-compression": {"scheme": "zip"}};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.1.ddl.sqlpp
new file mode 100644
index 0000000000..11d608f2d9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.1.ddl.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : A BTREE or ARRAY index takes its own compression scheme, independent of its dataset's,
+ *                and the configured default when it names none
+ * Expected Res : Success
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE T AS { id: bigint, name: string };
+
+CREATE DATASET DS(T) PRIMARY KEY id WITH {"storage-block-compression": {"scheme": "none"}};
+
+CREATE INDEX idx_default ON DS(name);
+CREATE INDEX idx_none ON DS(name) WITH {"storage-block-compression": {"scheme": "none"}};
+CREATE INDEX idx_snappy ON DS(age: bigint) WITH {"storage-block-compression": {"scheme": "snappy"}};
+CREATE INDEX idx_array ON DS(UNNEST tags: string) WITH {"storage-block-compression": {"scheme": "snappy"}}
+    EXCLUDE UNKNOWN KEY;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.2.update.sqlpp
new file mode 100644
index 0000000000..4f9ad5284e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.2.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+INSERT INTO DS ([
+  { "id": 1, "name": "a", "age": 10, "tags": ["x", "y"] },
+  { "id": 2, "name": "b", "age": 20, "tags": ["y"] },
+  { "id": 3, "name": "c", "age": 30, "tags": ["z"] }
+]);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.3.query.sqlpp
new file mode 100644
index 0000000000..4acf4c343c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.3.query.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+SELECT i.IndexName, i.BlockLevelStorageCompression
+FROM Metadata.`Index` i
+WHERE i.DataverseName = "test" AND i.DatasetName = "DS" AND i.IndexStructure != "SAMPLE"
+ORDER BY i.IndexName;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.4.query.sqlpp
new file mode 100644
index 0000000000..cc3d90953d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-scheme/secondary-index-scheme.4.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE [
+  (SELECT VALUE d.id FROM DS d WHERE d.name = "b"),
+  (SELECT VALUE d.id FROM DS d WHERE d.age >= 20 ORDER BY d.id),
+  (SELECT VALUE d.id FROM DS d WHERE SOME t IN d.tags SATISFIES t = "y" ORDER BY d.id)
+];
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-field/secondary-index-unsupported-field.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-field/secondary-index-unsupported-field.1.ddl.sqlpp
new file mode 100644
index 0000000000..20346e9747
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-field/secondary-index-unsupported-field.1.ddl.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE T AS { id: bigint };
+CREATE DATASET DS(T) PRIMARY KEY id;
+
+CREATE INDEX idx ON DS(name: string) WITH {"merge-policy": {"name": "prefix"}};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-type/secondary-index-unsupported-type.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-type/secondary-index-unsupported-type.1.ddl.sqlpp
new file mode 100644
index 0000000000..8ebbdf08a1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/compression/secondary-index-unsupported-type/secondary-index-unsupported-type.1.ddl.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE T AS { id: bigint };
+CREATE DATASET DS(T) PRIMARY KEY id;
+
+CREATE INDEX idx ON DS(name: string) TYPE KEYWORD WITH {"storage-block-compression": {"scheme": "snappy"}};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.3.adm
new file mode 100644
index 0000000000..fc7b7dd3a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.3.adm
@@ -0,0 +1,5 @@
+{ "IndexName": "DS" }
+{ "IndexName": "idx_array", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
+{ "IndexName": "idx_default", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
+{ "IndexName": "idx_none" }
+{ "IndexName": "idx_snappy", "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.4.adm
new file mode 100644
index 0000000000..0e9476c8b3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/compression/secondary-index-scheme/secondary-index-scheme.4.adm
@@ -0,0 +1 @@
+[ [ 2 ], [ 2, 3 ], [ 1, 2 ] ]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
index e8c9dfa902..d206d11faf 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
@@ -1,3 +1,3 @@
 { "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "KVStore", "IndexStructure": "BTREE", "SearchKey": [ [ "key" ] ], "IsPrimary": true, "Timestamp": "Mon Sep 07 18:14:11 PDT 2020", "PendingOp": 0, "SearchKeySourceIndicator": [ 1 ] }
 { "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_location", "IndexStructure": "RTREE", "SearchKey": [ [ "location" ] ], "IsPrimary": false, "Timestamp": "Tue Aug 03 21:39:47 AST 2021", "PendingOp": 0 }
-{ "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_rating", "IndexStructure": "BTREE", "SearchKey": [ [ "area_code" ] ], "IsPrimary": false, "Timestamp": "Tue Aug 03 21:39:47 AST 2021", "PendingOp": 0, "ExcludeUnknownKey": false }
+{ "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_rating", "IndexStructure": "BTREE", "SearchKey": [ [ "area_code" ] ], "IsPrimary": false, "Timestamp": "Tue Aug 03 21:39:47 AST 2021", "PendingOp": 0, "ExcludeUnknownKey": false, "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm
index 6080d5555a..45ce8ea3ff 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm
@@ -1 +1 @@
-{ "DataverseName": "test", "DatasetName": "ds2", "IndexName": "ds2_o_idx2", "IndexStructure": "BTREE", "SearchKey": [ [ "o_s_f" ], [ "o_d_f" ] ], "IsPrimary": false, "Timestamp": "Sun Oct 31 17:55:11 PDT 2021", "PendingOp": 0, "SearchKeyType": [ "double", "string" ], "ExcludeUnknownKey": false, "Cast": { "Default": null } }
\ No newline at end of file
+{ "DataverseName": "test", "DatasetName": "ds2", "IndexName": "ds2_o_idx2", "IndexStructure": "BTREE", "SearchKey": [ [ "o_s_f" ], [ "o_d_f" ] ], "IsPrimary": false, "Timestamp": "Sun Oct 31 17:55:11 PDT 2021", "PendingOp": 0, "SearchKeyType": [ "double", "string" ], "ExcludeUnknownKey": false, "Cast": { "Default": null }, "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
index 1f0dd69f16..7bc5b382f8 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/create_dataset_with_filter_on_meta/create_dataset_with_filter_on_meta.1.adm
@@ -1,3 +1,3 @@
 { "DatabaseName": "Default", "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "KVStore", "IndexStructure": "BTREE", "SearchKey": [ [ "key" ] ], "IsPrimary": true, "Timestamp": "Mon Oct 14 07:37:28 UTC 2024", "PendingOp": 0, "SearchKeySourceIndicator": [ 1 ], "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" } }
 { "DatabaseName": "Default", "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_location", "IndexStructure": "RTREE", "SearchKey": [ [ "location" ] ], "IsPrimary": false, "Timestamp": "Sat Sep 30 23:12:54 PDT 2023", "PendingOp": 0, "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" } }
-{ "DatabaseName": "Default", "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_rating", "IndexStructure": "BTREE", "SearchKey": [ [ "area_code" ] ], "IsPrimary": false, "Timestamp": "Sat Sep 30 23:12:54 PDT 2023", "PendingOp": 0, "ExcludeUnknownKey": false, "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" } }
\ No newline at end of file
+{ "DatabaseName": "Default", "DataverseName": "KeyVerse", "DatasetName": "KVStore", "IndexName": "s_rating", "IndexStructure": "BTREE", "SearchKey": [ [ "area_code" ] ], "IsPrimary": false, "Timestamp": "Sat Sep 30 23:12:54 PDT 2023", "PendingOp": 0, "ExcludeUnknownKey": false, "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" }, "BlockLevelStorageCompression": { "IndexCompressionScheme": "snappy" } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/index-cast-null/index-cast-null.015.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/index-cast-null/index-cast-null.015.adm
index 797b6a6649..2f44505c82 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/index-cast-null/index-cast-null.015.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cloud/ddl/index-cast-null/index-cast-null.015.adm
@@ -1 +1 @@
-{ "DatabaseName": "Default", "DataverseName": "test", "DatasetName": "ds2", "IndexName": "ds2_o_idx2", "IndexStructure": "BTREE", "SearchKey": [ [ "o_s_f" ], [ "o_d_f" ] ], "IsPrimary": false, "Timestamp": "Mon Oct 14 07:37:29 UTC 2024", "PendingOp": 0, "SearchKeyType": [ "double", "string" ], "ExcludeUnknownKey": false, "Cast": { "Default": null }, "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" } }
\ No newline at end of file
+{ "DatabaseName": "Default", "DataverseName": "test", "DatasetName": "ds2", "IndexName": "ds2_o_idx2", "IndexStructure": "BTREE", "SearchKey": [ [ "o_s_f" ], [ "o_d_f" ] ], "IsPrimary": false, "Timestamp": "Mon Oct 14 07:37:29 UTC 2024", "PendingOp": 0, "SearchKeyType": [ "double", "string" ], "ExcludeUnknownKey": false, "Cast": { "Default": null }, "Creator": { "Name": "@sys", "Uuid": "97c793f3-bcbf-4595-8bf0-e9d6a5953523" }, "BlockLevelStorageCompression": { "IndexCompressionScheme": " [...]
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
index 2f1e920356..0861b52ecc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -16136,6 +16136,32 @@
         <output-dir compare="Text">scheme-snappy</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="compression">
+      <compilation-unit name="secondary-index-invalid-scheme">
+        <output-dir compare="Text">secondary-index-invalid-scheme</output-dir>
+        <expected-error>ASX1096: Unknown compression scheme zip. Supported schemes are [snappy,none]</expected-error>
+        <source-location>false</source-location>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="compression">
+      <compilation-unit name="secondary-index-scheme">
+        <output-dir compare="Text">secondary-index-scheme</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="compression">
+      <compilation-unit name="secondary-index-unsupported-field">
+        <output-dir compare="Text">secondary-index-unsupported-field</output-dir>
+        <expected-error>ASX1059: Field(s) [merge-policy] unsupported in the with clause</expected-error>
+        <source-location>false</source-location>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="compression">
+      <compilation-unit name="secondary-index-unsupported-type">
+        <output-dir compare="Text">secondary-index-unsupported-type</output-dir>
+        <expected-error>WITH clause is only supported for BTREE, ARRAY and VTREE indexes</expected-error>
+        <source-location>false</source-location>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="ddl-with-clause">
     <test-case FilePath="ddl-with-clause">
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
index 192f1d14ea..e3dcee2a51 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
@@ -33,6 +33,7 @@ import org.apache.asterix.lang.common.base.Statement;
 import org.apache.asterix.lang.common.expression.IndexedTypeExpression;
 import org.apache.asterix.lang.common.expression.RecordConstructor;
 import org.apache.asterix.lang.common.struct.Identifier;
+import org.apache.asterix.lang.common.util.DatasetDeclParametersUtil;
 import org.apache.asterix.lang.common.util.VectorIndexDeclUtil;
 import org.apache.asterix.lang.common.visitor.base.ILangVisitor;
 import org.apache.asterix.om.vector.VectorIndexParameters;
@@ -60,6 +61,7 @@ public class CreateIndexStatement extends AbstractStatement {
 
     private final List<CreateIndexStatement.IndexedElement> includeElements;
     private final VectorIndexParameters vectorParameters;
+    private final String compressionScheme;
 
     public CreateIndexStatement(Namespace namespace, Identifier datasetName, Identifier indexName, IndexType indexType,
             List<IndexedElement> indexedElements, boolean enforced, int gramLength, String fullTextConfigName,
@@ -79,10 +81,12 @@ public class CreateIndexStatement extends AbstractStatement {
         this.castDefaultNull = OptionalBoolean.ofNullable(castDefaultNull);
         this.castConfig = castConfig == null ? Collections.emptyMap() : castConfig;
         this.includeElements = includeElements;
-        // The WITH clause is vector-index-only (the grammar rejects it on any other index type), and a
-        // vector index always has one, so this is non-null exactly for TYPE VTREE.
+        // A vector index always has a WITH clause, so this is non-null exactly for TYPE VTREE. On a BTREE or ARRAY
+        // index the clause carries the compression scheme only; the grammar rejects it on any other type.
         this.vectorParameters =
                 indexType == IndexType.VTREE ? VectorIndexDeclUtil.validateAndGetParameters(withObjectRecord) : null;
+        this.compressionScheme = indexType == IndexType.VTREE ? null
+                : DatasetDeclParametersUtil.validateAndGetIndexCompressionScheme(withObjectRecord);
     }
 
     public String getFullTextConfigName() {
@@ -149,9 +153,14 @@ public class CreateIndexStatement extends AbstractStatement {
         return castConfig;
     }
 
+    /** The compression scheme the statement names, or null where it names none. */
+    public String getCompressionScheme() {
+        return compressionScheme;
+    }
+
     /**
      * The validated {@code WITH} parameters of a {@code TYPE VTREE} index, never {@code null} for that index
-     * type; {@code null} for every other type, none of which accepts a {@code WITH} clause.
+     * type; {@code null} for every other type.
      */
     public VectorIndexParameters getVectorParameters() {
         return vectorParameters;
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/DatasetDeclParametersUtil.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/DatasetDeclParametersUtil.java
index 95b53a875b..d24a284ee5 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/DatasetDeclParametersUtil.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/DatasetDeclParametersUtil.java
@@ -78,6 +78,7 @@ public class DatasetDeclParametersUtil {
      * ***********************************************
      */
     private static final ARecordType WITH_OBJECT_TYPE = getWithObjectType();
+    private static final ARecordType INDEX_WITH_OBJECT_TYPE = getIndexWithObjectType();
     static final AdmObjectNode EMPTY_WITH_OBJECT = new AdmObjectNode();
 
     private DatasetDeclParametersUtil() {
@@ -100,6 +101,27 @@ public class DatasetDeclParametersUtil {
         }
     }
 
+    /**
+     * @return the compression scheme a secondary index's WITH clause names, or null where it names none. The scheme
+     *         is not checked against the registered ones here, which is left to the DDL, as for a dataset.
+     */
+    public static String validateAndGetIndexCompressionScheme(RecordConstructor withRecord)
+            throws CompilationException {
+        if (withRecord == null) {
+            return null;
+        }
+        AdmObjectNode node = ExpressionUtils.toNode(withRecord);
+        new ConfigurationTypeValidator().validateType(INDEX_WITH_OBJECT_TYPE, node);
+        AdmObjectNode storageBlockCompression = (AdmObjectNode) node.get(STORAGE_BLOCK_COMPRESSION_PARAMETER_NAME);
+        return storageBlockCompression == null ? null
+                : storageBlockCompression.getOptionalString(STORAGE_BLOCK_COMPRESSION_SCHEME_PARAMETER_NAME);
+    }
+
+    private static ARecordType getIndexWithObjectType() {
+        return new ARecordType("withObject", new String[] { STORAGE_BLOCK_COMPRESSION_PARAMETER_NAME },
+                new IAType[] { AUnionType.createUnknownableType(getStorageBlockCompressionType()) }, false);
+    }
+
     private static ARecordType getWithObjectType() {
         final String[] withNames = { MERGE_POLICY_PARAMETER_NAME, STORAGE_BLOCK_COMPRESSION_PARAMETER_NAME,
                 NODE_GROUP_NAME, DATASET_FORMAT_PARAMETER_NAME };
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index a02c542a3b..a50bf114cd 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -1804,13 +1804,6 @@ CreateIndexStatement IndexSpecification(Token startStmtToken) throws ParseExcept
           "INCLUDE clause is only supported for vector indexes (TYPE VTREE)");
       }
 
-      // Validate: the WITH clause is only for vector indexes. The non-vector statement built below does not
-      // read withRecord, so accepting it here would silently discard it (e.g. a typo'd option on a BTREE).
-      if (withRecord != null) {
-        throw new SqlppParseException(getSourceLocation(startStmtToken),
-          "WITH clause is only supported for vector indexes (TYPE VTREE)");
-      }
-
       // Handle regular (non-vector) index creation
       IndexType indexType;
       int gramLength;
@@ -1823,10 +1816,17 @@ CreateIndexStatement IndexSpecification(Token startStmtToken) throws ParseExcept
         gramLength = -1;
         fullTextConfigName = null;
       }
+
+      // Only the index types that are block-compressed take a WITH clause, which carries nothing but the compression
+      // scheme; any other type would silently discard it.
+      if (withRecord != null && indexType != IndexType.BTREE && indexType != IndexType.ARRAY) {
+        throw new SqlppParseException(getSourceLocation(startStmtToken),
+          "WITH clause is only supported for BTREE, ARRAY and VTREE indexes");
+      }
       try{
           CreateIndexStatement stmt = new CreateIndexStatement(nameComponents.getLeft(), nameComponents.getRight(),
           new Identifier(indexName), indexType, indexedElementList, enforced, gramLength, fullTextConfigName, ifNotExists,
-              excludeUnknown, castDefaultNull, castConfig, Collections.emptyList(), null);
+              excludeUnknown, castDefaultNull, castConfig, Collections.emptyList(), withRecord);
           return addSourceLocation(stmt, startStmtToken);
       }   catch (CompilationException e) {
           throw new SqlppParseException(getSourceLocation(startStmtToken), e.getMessage());
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/ArrayBTreeResourceFactoryProvider.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/ArrayBTreeResourceFactoryProvider.java
index d59aa0eaa6..a5c0464833 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/ArrayBTreeResourceFactoryProvider.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/ArrayBTreeResourceFactoryProvider.java
@@ -50,7 +50,6 @@ import org.apache.hyracks.storage.am.lsm.common.api.ILSMOperationTrackerFactory;
 import org.apache.hyracks.storage.am.lsm.common.api.ILSMPageWriteCallbackFactory;
 import org.apache.hyracks.storage.common.IResourceFactory;
 import org.apache.hyracks.storage.common.IStorageManager;
-import org.apache.hyracks.storage.common.compression.NoOpCompressorDecompressorFactory;
 
 public class ArrayBTreeResourceFactoryProvider implements IResourceFactoryProvider {
 
@@ -87,13 +86,12 @@ public class ArrayBTreeResourceFactoryProvider implements IResourceFactoryProvid
                 AsterixVirtualBufferCacheProvider vbcProvider =
                         new AsterixVirtualBufferCacheProvider(dataset.getDatasetId());
 
-                final ICompressorDecompressorFactory compDecompFactory;
                 if (index.isPrimaryIndex()) {
                     throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE,
                             "Array indexes cannot be " + "primary indexes.");
-                } else {
-                    compDecompFactory = NoOpCompressorDecompressorFactory.INSTANCE;
                 }
+                final ICompressorDecompressorFactory compDecompFactory =
+                        mdProvider.getCompressionManager().getFactory(index.getCompressionScheme());
 
                 return new LSMBTreeLocalResourceFactory(storageManager, typeTraits, cmpFactories, filterTypeTraits,
                         filterCmpFactories, filterFields, opTrackerFactory, ioOpCallbackFactory,
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
index 27f6dea7cc..a791c632a4 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
@@ -57,7 +57,6 @@ import org.apache.hyracks.storage.am.lsm.common.api.ILSMOperationTrackerFactory;
 import org.apache.hyracks.storage.am.lsm.common.api.ILSMPageWriteCallbackFactory;
 import org.apache.hyracks.storage.common.IResourceFactory;
 import org.apache.hyracks.storage.common.IStorageManager;
-import org.apache.hyracks.storage.common.compression.NoOpCompressorDecompressorFactory;
 
 public class BTreeResourceFactoryProvider implements IResourceFactoryProvider {
 
@@ -95,13 +94,8 @@ public class BTreeResourceFactoryProvider implements IResourceFactoryProvider {
                 AsterixVirtualBufferCacheProvider vbcProvider =
                         new AsterixVirtualBufferCacheProvider(dataset.getDatasetId());
 
-                final ICompressorDecompressorFactory compDecompFactory;
-                if (index.isPrimaryIndex()) {
-                    //Compress only primary index
-                    compDecompFactory = mdProvider.getCompressionManager().getFactory(dataset.getCompressionScheme());
-                } else {
-                    compDecompFactory = NoOpCompressorDecompressorFactory.INSTANCE;
-                }
+                final ICompressorDecompressorFactory compDecompFactory = mdProvider.getCompressionManager().getFactory(
+                        index.isPrimaryIndex() ? dataset.getCompressionScheme() : index.getCompressionScheme());
 
                 boolean isSecondaryNoIncrementalMaintenance = index.getIndexType() == DatasetConfig.IndexType.SAMPLE;
 
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
index 151c2173e0..f743114f38 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
@@ -42,6 +42,7 @@ import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.om.utils.NonTaggedFormatUtil;
 import org.apache.asterix.om.utils.ProjectionFiltrationTypeUtil;
 import org.apache.asterix.om.vector.VectorIndexParameters;
+import org.apache.asterix.runtime.compression.CompressionManager;
 import org.apache.commons.lang3.tuple.MutablePair;
 import org.apache.commons.lang3.tuple.Pair;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
@@ -70,10 +71,23 @@ public class Index implements IMetadataEntity<Index>, Comparable<Index> {
     // Type of pending operations with respect to atomic DDL operation
     private int pendingOp;
     private final Creator creator;
+    private final String compressionScheme;
 
     public Index(String databaseName, DataverseName dataverseName, String datasetName, String indexName,
             IndexType indexType, IIndexDetails indexDetails, boolean isEnforced, boolean isPrimaryIndex, int pendingOp,
             Creator creator) {
+        this(databaseName, dataverseName, datasetName, indexName, indexType, indexDetails, isEnforced, isPrimaryIndex,
+                pendingOp, creator, CompressionManager.NONE);
+    }
+
+    /**
+     * @param compressionScheme
+     *            the block compression scheme of a secondary index. A primary index is compressed with its
+     *            dataset's scheme and ignores this.
+     */
+    public Index(String databaseName, DataverseName dataverseName, String datasetName, String indexName,
+            IndexType indexType, IIndexDetails indexDetails, boolean isEnforced, boolean isPrimaryIndex, int pendingOp,
+            Creator creator, String compressionScheme) {
         boolean categoryOk = (indexType == null && indexDetails == null) || (IndexCategory
                 .of(Objects.requireNonNull(indexType)) == ((AbstractIndexDetails) Objects.requireNonNull(indexDetails))
                         .getIndexCategory());
@@ -90,6 +104,7 @@ public class Index implements IMetadataEntity<Index>, Comparable<Index> {
         this.isEnforced = isEnforced;
         this.pendingOp = pendingOp;
         this.creator = creator;
+        this.compressionScheme = Objects.requireNonNull(compressionScheme);
     }
 
     @Deprecated
@@ -164,6 +179,10 @@ public class Index implements IMetadataEntity<Index>, Comparable<Index> {
         return creator;
     }
 
+    public String getCompressionScheme() {
+        return compressionScheme;
+    }
+
     public boolean isPrimaryKeyIndex() {
         // a primary key index has no key field names
         return indexType == IndexType.BTREE && ((ValueIndexDetails) indexDetails).keyFieldNames.isEmpty();
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
index 3d856ac215..bf0e5b72b0 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
@@ -42,6 +42,7 @@ import org.apache.asterix.common.transactions.TxnId;
 import org.apache.asterix.formats.nontagged.SerializerDeserializerProvider;
 import org.apache.asterix.metadata.MetadataNode;
 import org.apache.asterix.metadata.bootstrap.IndexEntity;
+import org.apache.asterix.metadata.bootstrap.MetadataRecordTypes;
 import org.apache.asterix.metadata.declared.MetadataManagerUtil;
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Datatype;
@@ -62,6 +63,7 @@ import org.apache.asterix.om.base.ARecord;
 import org.apache.asterix.om.base.AString;
 import org.apache.asterix.om.base.IACursor;
 import org.apache.asterix.om.base.IAObject;
+import org.apache.asterix.om.pointables.base.DefaultOpenFieldType;
 import org.apache.asterix.om.types.AOrderedListType;
 import org.apache.asterix.om.types.ARecordType;
 import org.apache.asterix.om.types.ATypeTag;
@@ -69,6 +71,7 @@ import org.apache.asterix.om.types.BuiltinType;
 import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.om.utils.RecordUtil;
 import org.apache.asterix.om.vector.VectorIndexParameters;
+import org.apache.asterix.runtime.compression.CompressionManager;
 import org.apache.commons.lang3.tuple.Pair;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Triple;
@@ -98,6 +101,7 @@ public class IndexTupleTranslator extends AbstractTupleTranslator<Index> {
     public static final String INDEX_SEARCHKEY_ELEMENTS_FIELD_NAME = "SearchKeyElements";
     public static final String COMPLEXSEARCHKEY_UNNEST_FIELD_NAME = "UnnestList";
     public static final String COMPLEXSEARCHKEY_PROJECT_FIELD_NAME = "ProjectList";
+    public static final String INDEX_COMPRESSION_SCHEME_FIELD_NAME = "IndexCompressionScheme";
     public static final String SAMPLE_SEED = "SampleSeed";
     public static final String SAMPLE_CARDINALITY_TARGET = "SampleCardinalityTarget";
     public static final String SOURCE_CARDINALITY = "SourceCardinality";
@@ -597,7 +601,20 @@ public class IndexTupleTranslator extends AbstractTupleTranslator<Index> {
         Creator creator = Creator.createOrDefault(indexRecord);
 
         return new Index(databaseName, dataverseName, datasetName, indexName, indexType, indexDetails, isEnforcingKeys,
-                isPrimaryIndex, pendingOp, creator);
+                isPrimaryIndex, pendingOp, creator, getCompressionScheme(indexRecord));
+    }
+
+    /** Absent means uncompressed, which is what every secondary index created before this field existed is. */
+    private static String getCompressionScheme(ARecord indexRecord) {
+        ARecordType indexRecordType = indexRecord.getType();
+        int compressionPos = indexRecordType
+                .getFieldIndex(MetadataRecordTypes.DATASET_ARECORD_BLOCK_LEVEL_STORAGE_COMPRESSION_FIELD_NAME);
+        if (compressionPos < 0) {
+            return CompressionManager.NONE;
+        }
+        ARecord compressionRecord = (ARecord) indexRecord.getValueByPos(compressionPos);
+        int schemePos = compressionRecord.getType().getFieldIndex(INDEX_COMPRESSION_SCHEME_FIELD_NAME);
+        return ((AString) compressionRecord.getValueByPos(schemePos)).getStringValue();
     }
 
     @Override
@@ -757,6 +774,29 @@ public class IndexTupleTranslator extends AbstractTupleTranslator<Index> {
         writeCast(index);
         writeSampleDetails(index);
         writeIndexCreator(index);
+        writeBlockLevelStorageCompression(index);
+    }
+
+    private void writeBlockLevelStorageCompression(Index index) throws HyracksDataException {
+        if (index.isPrimaryIndex() || CompressionManager.NONE.equals(index.getCompressionScheme())) {
+            return;
+        }
+        RecordBuilder compressionObject = new RecordBuilder();
+        compressionObject.reset(DefaultOpenFieldType.NESTED_OPEN_RECORD_TYPE);
+        fieldName.reset();
+        aString.setValue(INDEX_COMPRESSION_SCHEME_FIELD_NAME);
+        stringSerde.serialize(aString, fieldName.getDataOutput());
+        fieldValue.reset();
+        aString.setValue(index.getCompressionScheme());
+        stringSerde.serialize(aString, fieldValue.getDataOutput());
+        compressionObject.addField(fieldName, fieldValue);
+
+        fieldName.reset();
+        aString.setValue(MetadataRecordTypes.DATASET_ARECORD_BLOCK_LEVEL_STORAGE_COMPRESSION_FIELD_NAME);
+        stringSerde.serialize(aString, fieldName.getDataOutput());
+        fieldValue.reset();
+        compressionObject.write(fieldValue.getDataOutput(), true);
+        recordBuilder.addField(fieldName, fieldValue);
     }
 
     private void writeComplexSearchKeys(Index.ArrayIndexDetails indexDetails) throws HyracksDataException {
diff --git a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/operators/DatasetStreamStatsOperatorDescriptor.java b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/operators/DatasetStreamStatsOperatorDescriptor.java
index 699fd3bf31..7cb9383823 100644
--- a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/operators/DatasetStreamStatsOperatorDescriptor.java
+++ b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/operators/DatasetStreamStatsOperatorDescriptor.java
@@ -44,6 +44,7 @@ import org.apache.hyracks.dataflow.std.base.AbstractSingleActivityOperatorDescri
 import org.apache.hyracks.dataflow.std.base.AbstractUnaryInputUnaryOutputOperatorNodePushable;
 import org.apache.hyracks.storage.am.common.api.IIndexDataflowHelper;
 import org.apache.hyracks.storage.am.common.dataflow.IIndexDataflowHelperFactory;
+import org.apache.hyracks.storage.am.common.impls.AbstractTreeIndex;
 import org.apache.hyracks.storage.am.lsm.common.api.AbstractLSMWithBloomFilterDiskComponent;
 import org.apache.hyracks.storage.am.lsm.common.api.ILSMDiskComponent;
 import org.apache.hyracks.storage.am.lsm.common.api.ILSMIndex;
@@ -161,6 +162,14 @@ public final class DatasetStreamStatsOperatorDescriptor extends AbstractSingleAc
                             long numPages = 0;
                             synchronized (indexInstance.getOperationTracker()) {
                                 for (ILSMDiskComponent component : indexInstance.getDiskComponents()) {
+                                    // the optimizer costs an index by its pages as read through the buffer cache,
+                                    // which a compressed file's on-disk size understates
+                                    if (component.getIndex() instanceof AbstractTreeIndex treeIndex
+                                            && treeIndex.getFileReference().isCompressed()) {
+                                        numPages += indexInstance.getBufferCache()
+                                                .getNumPagesOfFile(treeIndex.getFileId());
+                                        continue;
+                                    }
                                     long componentSize = component.getComponentSize();
                                     if (component instanceof AbstractLSMWithBloomFilterDiskComponent) {
                                         componentSize -= ((AbstractLSMWithBloomFilterDiskComponent) component)
diff --git a/hyracks-fullstack/hyracks/hyracks-tests/hyracks-storage-common-test/src/test/java/org/apache/hyracks/storage/common/SecondaryIndexCompressionBenchmark.java b/hyracks-fullstack/hyracks/hyracks-tests/hyracks-storage-common-test/src/test/java/org/apache/hyracks/storage/common/SecondaryIndexCompressionBenchmark.java
new file mode 100644
index 0000000000..c09eff68d8
--- /dev/null
+++ b/hyracks-fullstack/hyracks/hyracks-tests/hyracks-storage-common-test/src/test/java/org/apache/hyracks/storage/common/SecondaryIndexCompressionBenchmark.java
@@ -0,0 +1,477 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.hyracks.storage.common;
+
+import java.nio.ByteBuffer;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import java.util.Random;
+import java.util.concurrent.TimeUnit;
+
+import org.apache.hyracks.api.compression.ICompressorDecompressor;
+import org.apache.hyracks.api.context.IHyracksTaskContext;
+import org.apache.hyracks.api.exceptions.HyracksDataException;
+import org.apache.hyracks.api.io.FileReference;
+import org.apache.hyracks.api.io.IIOManager;
+import org.apache.hyracks.storage.common.buffercache.HaltOnFailureCallback;
+import org.apache.hyracks.storage.common.buffercache.IBufferCache;
+import org.apache.hyracks.storage.common.buffercache.ICachedPage;
+import org.apache.hyracks.storage.common.buffercache.IFIFOPageWriter;
+import org.apache.hyracks.storage.common.buffercache.NoOpPageWriteCallback;
+import org.apache.hyracks.storage.common.buffercache.context.write.DefaultBufferCacheWriteContext;
+import org.apache.hyracks.storage.common.compression.SnappyCompressorDecompressorFactory;
+import org.apache.hyracks.storage.common.compression.file.CompressedFileReference;
+import org.apache.hyracks.storage.common.compression.file.ICompressedPageWriter;
+import org.apache.hyracks.storage.common.file.BufferedFileHandle;
+import org.apache.hyracks.test.support.TestStorageManagerComponentHolder;
+import org.apache.hyracks.test.support.TestUtils;
+import org.junit.AfterClass;
+import org.junit.Test;
+
+/**
+ * Measures what block compression costs on the <em>read</em> path of an index, to decide whether
+ * secondary indexes should be compressed like the primary already is.
+ * <p>
+ * The class name deliberately ends in {@code Benchmark}, not {@code Test}, so surefire's
+ * {@code **}{@code /*Test.java} include pattern never picks it up in CI. Run it explicitly:
+ *
+ * <pre>
+ * mvn -o -pl hyracks-fullstack/hyracks/hyracks-tests/hyracks-storage-common-test test \
+ *     -Dtest=SecondaryIndexCompressionBenchmark
+ * </pre>
+ *
+ * <h2>Two regimes, because they answer opposite halves of the question</h2>
+ * In both, the buffer cache is sized to a small fraction of the file, so nearly every pin misses and
+ * has to reach the file. What differs is where the bytes come from:
+ * <ol>
+ * <li><b>Warm OS page cache</b> ({@link #reportRandomPinCost}, {@link #reportSequentialScanCost}) —
+ * the file was just written, so no physical device read happens on either side. This isolates the
+ * decompression CPU and grants compression none of its benefit, making it the <b>worst case for
+ * compression</b>: an overhead that is acceptable here is acceptable anywhere.</li>
+ * <li><b>Cold device</b> ({@link #reportColdDeviceCost}) — the page cache is evicted first, so a pin
+ * is a real device read. This is the regime that matters for an index larger than memory, and the
+ * one where reading fewer bytes can pay for the decompression. Without it the benchmark would only
+ * ever report costs and never the corresponding benefit.</li>
+ * </ol>
+ * Both report <b>microseconds per page access</b>, not just totals, since per-access latency is what
+ * a secondary-index probe actually pays.
+ * <p>
+ * Page content is synthesised to span a range of compressibility rather than to a single guess,
+ * because the achieved ratio drives both the space win and the decompression cost. The measured
+ * ratios on real data — 36% for a bigint secondary index, 52% for a string one — fall inside the
+ * swept range, so the relevant row can be read off directly.
+ */
+public class SecondaryIndexCompressionBenchmark {
+
+    private static final int PAGE_SIZE = 128 * 1024;
+    /** 2048 pages = 64 MiB of logical pages, well beyond the cache below. */
+    private static final int FILE_PAGES = 2048;
+    /** 64 pages = 2 MiB, so a random pin misses ~97% of the time. */
+    private static final int CACHE_PAGES = 64;
+    private static final int MAX_OPEN_FILES = 8;
+    private static final int RANDOM_PROBES = 20_000;
+    private static final int REPEATS = 7;
+    private static final int WARMUP_REPEATS = 2;
+    private static final int DECOMPRESS_ITERATIONS = 2_000;
+
+    /**
+     * Fraction of each page filled with incompressible bytes. Sweeping this sweeps the achieved
+     * compression ratio, which is the axis that actually matters.
+     */
+    private static final double[] INCOMPRESSIBLE_FRACTIONS = { 0.0, 0.10, 0.20, 0.30, 0.50, 0.70, 0.85 };
+
+    /** 32768 pages = 1 GiB logical: big enough that a cold pin is a real device read. */
+    private static final int COLD_FILE_PAGES = 32 * 1024;
+    private static final int COLD_PROBES = 4_000;
+    private static final int COLD_PROBE_SEED = 99;
+    /**
+     * Calibrated by running the warm sweep and reading the achieved ratio off it: 0.20 lands near the
+     * 36% saving measured on a real bigint secondary index. An earlier guess of 0.70 achieved only
+     * 12%, which understated both the cost and the benefit.
+     */
+    private static final double COLD_INCOMPRESSIBLE_FRACTION = 0.20;
+    /** GiB of memory pressure applied to evict the page cache; the machine has 32 GiB. */
+    private static final int BALLAST_GIB = 22;
+    private static final long BALLAST_TIMEOUT_SECONDS = 300;
+
+    private static final List<String> createdFiles = new ArrayList<>();
+
+    private final IHyracksTaskContext ctx = TestUtils.create(PAGE_SIZE);
+
+    private static final class Measurement {
+        private final long dataBytes;
+        private final long lafBytes;
+        private final double medianMillis;
+
+        private Measurement(long dataBytes, long lafBytes, double medianMillis) {
+            this.dataBytes = dataBytes;
+            this.lafBytes = lafBytes;
+            this.medianMillis = medianMillis;
+        }
+
+        private long totalBytes() {
+            return dataBytes + lafBytes;
+        }
+
+        private double microsPerAccess(int accesses) {
+            return medianMillis * 1000.0 / accesses;
+        }
+    }
+
+    /**
+     * Attributes the read-path gap. The warm regimes show snappy costing +2 to +22 us per page access,
+     * and there are three candidate sources: decompression CPU, the extra buffer-cache pin of the
+     * look-aside file that {@code CompressedBufferedFileHandle} needs on every read to locate a page,
+     * and the staging copy through the compressed buffer.
+     * <p>
+     * This measures decompression alone -- same page content, same codec, no buffer cache, no file, no
+     * LAF. Whatever the warm delta shows beyond this number is not CPU.
+     */
+    @Test
+    public void reportDecompressionCpuOnly() throws Exception {
+        ICompressorDecompressor compDecomp = new SnappyCompressorDecompressorFactory().createInstance();
+        StringBuilder out = new StringBuilder();
+        out.append("\n=== decompression CPU in isolation (no buffer cache, no file, no LAF) ===\n");
+        out.append(String.format("%-14s %13s %8s %14s%n", "incompressible", "compressed", "saved", "decompress us"));
+
+        ByteBuffer uBuffer = ByteBuffer.allocate(PAGE_SIZE);
+        ByteBuffer cBuffer = ByteBuffer.allocate(compDecomp.computeCompressedBufferSize(PAGE_SIZE));
+        ByteBuffer outBuffer = ByteBuffer.allocate(PAGE_SIZE);
+        for (double incompressible : INCOMPRESSIBLE_FRACTIONS) {
+            fillLikeIndexLeaf(uBuffer, 1, incompressible);
+            uBuffer.position(0).limit(PAGE_SIZE);
+            cBuffer.clear();
+            compDecomp.compress(uBuffer, cBuffer);
+            int compressedSize = cBuffer.limit();
+
+            double[] millis = new double[REPEATS];
+            for (int repeat = 0; repeat < REPEATS; repeat++) {
+                long start = System.nanoTime();
+                for (int i = 0; i < DECOMPRESS_ITERATIONS; i++) {
+                    cBuffer.position(0).limit(compressedSize);
+                    outBuffer.clear();
+                    compDecomp.uncompress(cBuffer, outBuffer);
+                }
+                millis[repeat] = (System.nanoTime() - start) / 1_000_000.0;
+            }
+            double us = medianAfterWarmup(millis) * 1000.0 / DECOMPRESS_ITERATIONS;
+            out.append(String.format("%13.0f%% %13d %7.1f%% %14.2f%n", incompressible * 100, compressedSize,
+                    (1.0 - (double) compressedSize / PAGE_SIZE) * 100, us));
+        }
+        System.out.println(out); // NOSONAR
+    }
+
+    @Test
+    public void reportRandomPinCost() throws Exception {
+        report("random pin (" + RANDOM_PROBES + " scattered pins)", false);
+    }
+
+    @Test
+    public void reportSequentialScanCost() throws Exception {
+        report("sequential scan (" + FILE_PAGES + " pages in order)", true);
+    }
+
+    private void report(String label, boolean sequential) throws Exception {
+        int accesses = sequential ? FILE_PAGES : RANDOM_PROBES;
+        StringBuilder out = new StringBuilder();
+        out.append("\n=== secondary index compression, ").append(label).append(" (OS page cache WARM) ===\n");
+        out.append(String.format("%-14s %13s %13s %7s %10s %10s %10s %10s%n", "incompressible", "none bytes",
+                "snappy bytes", "saved", "none us", "snappy us", "delta us", "overhead"));
+
+        for (double incompressible : INCOMPRESSIBLE_FRACTIONS) {
+            Measurement plain = measure(false, incompressible, sequential);
+            Measurement snappy = measure(true, incompressible, sequential);
+
+            double saved = 1.0 - (double) snappy.totalBytes() / plain.totalBytes();
+            double plainUs = plain.microsPerAccess(accesses);
+            double snappyUs = snappy.microsPerAccess(accesses);
+            out.append(String.format("%13.0f%% %13d %13d %6.1f%% %10.2f %10.2f %10.2f %9.1f%%%n", incompressible * 100,
+                    plain.totalBytes(), snappy.totalBytes(), saved * 100, plainUs, snappyUs, snappyUs - plainUs,
+                    (snappyUs / plainUs - 1.0) * 100));
+        }
+        // A benchmark's whole output is its result, so print it rather than log it.
+        System.out.println(out); // NOSONAR
+    }
+
+    /**
+     * The case the warm sweep cannot reach: the index does not fit in memory, so a pin costs a real
+     * device read. Here compression trades decompression CPU for fewer bytes off the device, which is
+     * the trade that decides whether secondary indexes should be compressed.
+     * <p>
+     * A file larger than RAM is not reachable on this class of machine (32 GiB RAM against 33 GiB of
+     * free disk), and {@code purge} needs root. So instead the file stays moderate and the OS page
+     * cache is evicted by putting the machine under memory pressure — see
+     * {@link #evictOsPageCache()}. Each side is then read once cold and once warm; the cold/warm gap
+     * is the evidence that eviction actually happened, and is printed so a run where it did not is
+     * self-evident rather than silently reported as a cold number.
+     */
+    @Test
+    public void reportColdDeviceCost() throws Exception {
+        if (!canEvictOsPageCache()) {
+            System.out.println("\n=== cold-device: SKIPPED, no python3 to apply memory pressure ===\n"); // NOSONAR
+            return;
+        }
+        StringBuilder out = new StringBuilder();
+        out.append("\n=== secondary index compression, cold device (OS page cache EVICTED) ===\n");
+        out.append(String.format("file %d MiB logical, buffer cache %d MiB%n",
+                (long) COLD_FILE_PAGES * PAGE_SIZE / (1024 * 1024), (long) CACHE_PAGES * PAGE_SIZE / (1024 * 1024)));
+        out.append(String.format("%-12s %-8s %13s %7s %10s %10s %10s %12s%n", "pattern", "scheme", "bytes", "saved",
+                "cold us", "warm us", "cold/warm", "cold MB/s"));
+
+        // Random probes are latency-bound and sequential scans are bandwidth-bound, and compression
+        // only helps the second. Reporting one without the other would answer half the question: an
+        // ordinary secondary index is probed, but the sample index is fully scanned by the CBO.
+        for (boolean sequential : new boolean[] { false, true }) {
+            int accesses = sequential ? COLD_FILE_PAGES : COLD_PROBES;
+            long plainBytes = -1;
+            for (boolean compressed : new boolean[] { false, true }) {
+                ColdMeasurement m = measureCold(compressed, sequential);
+                if (!compressed) {
+                    plainBytes = m.totalBytes;
+                }
+                double coldUs = m.coldMillis * 1000.0 / accesses;
+                double warmUs = m.warmMillis * 1000.0 / accesses;
+                double coldMbPerSec = (double) accesses * PAGE_SIZE / (1024 * 1024) / (m.coldMillis / 1000.0);
+                double saved = 1.0 - (double) m.totalBytes / plainBytes;
+                out.append(String.format("%-12s %-8s %13d %6.1f%% %10.2f %10.2f %10.1fx %12.1f%n",
+                        sequential ? "sequential" : "random", compressed ? "snappy" : "none", m.totalBytes, saved * 100,
+                        coldUs, warmUs, coldUs / warmUs, coldMbPerSec));
+            }
+        }
+        System.out.println(out); // NOSONAR
+    }
+
+    private static final class ColdMeasurement {
+        private final long totalBytes;
+        private final double coldMillis;
+        private final double warmMillis;
+
+        private ColdMeasurement(long totalBytes, double coldMillis, double warmMillis) {
+            this.totalBytes = totalBytes;
+            this.coldMillis = coldMillis;
+            this.warmMillis = warmMillis;
+        }
+    }
+
+    private ColdMeasurement measureCold(boolean compressed, boolean sequential) throws Exception {
+        TestStorageManagerComponentHolder.init(PAGE_SIZE, CACHE_PAGES, MAX_OPEN_FILES);
+        IIOManager ioManager = TestStorageManagerComponentHolder.getIOManager();
+        IBufferCache bufferCache =
+                TestStorageManagerComponentHolder.getBufferCache(ctx.getJobletContext().getServiceContext());
+        FileReference fileRef = newFileReference(ioManager, compressed, COLD_INCOMPRESSIBLE_FRACTION, sequential);
+        try {
+            int fileId = bufferCache.createFile(fileRef);
+            writePages(bufferCache, fileId, COLD_INCOMPRESSIBLE_FRACTION, COLD_FILE_PAGES);
+            long totalBytes = ioManager.getSize(fileRef)
+                    + (compressed ? ioManager.getSize(((CompressedFileReference) fileRef).getLAFFileReference()) : 0L);
+
+            evictOsPageCache();
+
+            bufferCache.openFile(fileId);
+            // Identical access sequence in both passes, so cold and warm differ only in where the
+            // bytes came from.
+            long start = System.nanoTime();
+            coldAccess(bufferCache, fileId, sequential);
+            double coldMillis = (System.nanoTime() - start) / 1_000_000.0;
+
+            start = System.nanoTime();
+            coldAccess(bufferCache, fileId, sequential);
+            double warmMillis = (System.nanoTime() - start) / 1_000_000.0;
+
+            bufferCache.closeFile(fileId);
+            bufferCache.deleteFile(fileId);
+            return new ColdMeasurement(totalBytes, coldMillis, warmMillis);
+        } finally {
+            bufferCache.close();
+        }
+    }
+
+    private static void coldAccess(IBufferCache bufferCache, int fileId, boolean sequential)
+            throws HyracksDataException {
+        if (sequential) {
+            scanPages(bufferCache, fileId, COLD_FILE_PAGES);
+        } else {
+            pinRandomPages(bufferCache, fileId, COLD_PROBE_SEED, COLD_PROBES, COLD_FILE_PAGES);
+        }
+    }
+
+    private static boolean canEvictOsPageCache() {
+        try {
+            return new ProcessBuilder("python3", "-c", "pass").start().waitFor() == 0;
+        } catch (Exception e) {
+            return false;
+        }
+    }
+
+    /**
+     * Evicts file-backed pages by allocating and touching most of RAM in a short-lived subprocess.
+     * Clean file pages are the first thing the OS gives up under this pressure, so the benchmark file
+     * leaves the page cache. A subprocess is used because the surefire {@code argLine} in the parent
+     * pom pins this JVM to {@code -Xmx2048m}, and it cannot be overridden from the command line.
+     */
+    private static void evictOsPageCache() throws Exception {
+        // The ballast must be INCOMPRESSIBLE. macOS compresses anonymous memory before it gives up
+        // file-backed pages, and zero-filled ballast compresses to nearly nothing -- the page cache
+        // would survive and every "cold" number would silently be a warm one. So every page is filled
+        // from a random block: the compressor works per page, so identical random pages are still
+        // individually incompressible, which lets one 16 MiB urandom block seed all of it cheaply.
+        String script = "import os\n" + "block = os.urandom(1 << 24)\n" + "held = []\n" + "for _ in range("
+                + BALLAST_GIB + "):\n" + "    held.append(bytearray(block * 64))\n" + "del held\n";
+        Process p = new ProcessBuilder("python3", "-c", script).redirectErrorStream(true).start();
+        if (!p.waitFor(BALLAST_TIMEOUT_SECONDS, TimeUnit.SECONDS)) {
+            p.destroyForcibly();
+        }
+    }
+
+    private Measurement measure(boolean compressed, double incompressibleFraction, boolean sequential)
+            throws Exception {
+        TestStorageManagerComponentHolder.init(PAGE_SIZE, CACHE_PAGES, MAX_OPEN_FILES);
+        IIOManager ioManager = TestStorageManagerComponentHolder.getIOManager();
+        IBufferCache bufferCache =
+                TestStorageManagerComponentHolder.getBufferCache(ctx.getJobletContext().getServiceContext());
+        FileReference fileRef = newFileReference(ioManager, compressed, incompressibleFraction, sequential);
+        try {
+            int fileId = bufferCache.createFile(fileRef);
+            writePages(bufferCache, fileId, incompressibleFraction, FILE_PAGES);
+
+            long dataBytes = ioManager.getSize(fileRef);
+            long lafBytes =
+                    compressed ? ioManager.getSize(((CompressedFileReference) fileRef).getLAFFileReference()) : 0L;
+
+            bufferCache.openFile(fileId);
+            double[] millis = new double[REPEATS];
+            for (int repeat = 0; repeat < REPEATS; repeat++) {
+                long start = System.nanoTime();
+                if (sequential) {
+                    scanPages(bufferCache, fileId, FILE_PAGES);
+                } else {
+                    pinRandomPages(bufferCache, fileId, repeat, RANDOM_PROBES, FILE_PAGES);
+                }
+                millis[repeat] = (System.nanoTime() - start) / 1_000_000.0;
+            }
+            bufferCache.closeFile(fileId);
+            bufferCache.deleteFile(fileId);
+            return new Measurement(dataBytes, lafBytes, medianAfterWarmup(millis));
+        } finally {
+            bufferCache.close();
+        }
+    }
+
+    private void writePages(IBufferCache bufferCache, int fileId, double incompressibleFraction, int filePages)
+            throws HyracksDataException {
+        bufferCache.openFile(fileId);
+        ICompressedPageWriter compressedPageWriter = bufferCache.getCompressedPageWriter(fileId);
+        IFIFOPageWriter pageWriter = bufferCache.createFIFOWriter(NoOpPageWriteCallback.INSTANCE,
+                HaltOnFailureCallback.INSTANCE, DefaultBufferCacheWriteContext.INSTANCE);
+        for (int pageId = 0; pageId < filePages; pageId++) {
+            long dpid = BufferedFileHandle.getDiskPageId(fileId, pageId);
+            ICachedPage page = bufferCache.confiscatePage(dpid);
+            compressedPageWriter.prepareWrite(page);
+            fillLikeIndexLeaf(page.getBuffer(), pageId, incompressibleFraction);
+            pageWriter.write(page);
+        }
+        compressedPageWriter.endWriting();
+        bufferCache.closeFile(fileId);
+    }
+
+    /**
+     * Writes something shaped like a btree leaf of a secondary index: a run of ascending keys,
+     * which compress well because their high-order bytes repeat, followed by a block of
+     * incompressible bytes standing in for high-entropy primary keys. The split between the two is
+     * what {@code incompressibleFraction} controls.
+     * <p>
+     * Content is seeded from the page id alone, so the compressed and uncompressed runs of a given
+     * configuration see byte-identical input.
+     */
+    private static void fillLikeIndexLeaf(ByteBuffer buf, int pageId, double incompressibleFraction) {
+        Random rnd = new Random(pageId);
+        int incompressibleBytes = (int) (PAGE_SIZE * incompressibleFraction);
+        int structuredBytes = PAGE_SIZE - incompressibleBytes;
+
+        buf.position(0);
+        long key = (long) pageId * 100_000L;
+        int written = 0;
+        while (written + Long.BYTES <= structuredBytes) {
+            buf.putLong(key);
+            key += 3;
+            written += Long.BYTES;
+        }
+        byte[] noise = new byte[PAGE_SIZE - written];
+        rnd.nextBytes(noise);
+        buf.put(noise);
+        buf.position(0);
+    }
+
+    private static void pinRandomPages(IBufferCache bufferCache, int fileId, int seed, int probes, int filePages)
+            throws HyracksDataException {
+        Random rnd = new Random(seed);
+        for (int probe = 0; probe < probes; probe++) {
+            long dpid = BufferedFileHandle.getDiskPageId(fileId, rnd.nextInt(filePages));
+            ICachedPage page = bufferCache.pin(dpid);
+            bufferCache.unpin(page);
+        }
+    }
+
+    private static void scanPages(IBufferCache bufferCache, int fileId, int filePages) throws HyracksDataException {
+        for (int pageId = 0; pageId < filePages; pageId++) {
+            ICachedPage page = bufferCache.pin(BufferedFileHandle.getDiskPageId(fileId, pageId));
+            bufferCache.unpin(page);
+        }
+    }
+
+    /** Drops the first repeats as JIT warm-up, then takes the median so a single stall cannot skew it. */
+    private static double medianAfterWarmup(double[] millis) {
+        double[] measured = Arrays.copyOfRange(millis, WARMUP_REPEATS, millis.length);
+        Arrays.sort(measured);
+        int mid = measured.length / 2;
+        return measured.length % 2 == 1 ? measured[mid] : (measured[mid - 1] + measured[mid]) / 2;
+    }
+
+    private static FileReference newFileReference(IIOManager ioManager, boolean compressed,
+            double incompressibleFraction, boolean sequential) throws HyracksDataException {
+        String name = String.format("sicb-%s-%s-%02d", sequential ? "scan" : "rand", compressed ? "snappy" : "none",
+                (int) (incompressibleFraction * 100));
+        FileReference fileRef = ioManager.resolve(name);
+        // A run that died before its cleanup would otherwise leave a file that fails createFile with
+        // HYR0082 forever, so start from a clean slate rather than depending on the previous run.
+        deleteIfExists(ioManager, fileRef);
+        createdFiles.add(name);
+        if (!compressed) {
+            return fileRef;
+        }
+        ICompressorDecompressor compDecomp = new SnappyCompressorDecompressorFactory().createInstance();
+        createdFiles.add(name + ".dic");
+        CompressedFileReference cFileRef = new CompressedFileReference(fileRef.getDeviceHandle(), compDecomp,
+                fileRef.getRelativePath(), fileRef.getRelativePath() + ".dic");
+        deleteIfExists(ioManager, cFileRef.getLAFFileReference());
+        return cFileRef;
+    }
+
+    private static void deleteIfExists(IIOManager ioManager, FileReference fileRef) throws HyracksDataException {
+        if (ioManager.exists(fileRef)) {
+            ioManager.delete(fileRef);
+        }
+    }
+
+    @AfterClass
+    public static void cleanup() {
+        createdFiles.clear();
+    }
+}