Utilizing Columnstore Indexes in ArcGIS Online

Have you ever waited several seconds, or even minutes, for a complex query in ArcGIS Online to return results? As your datasets grow, so does the time it takes to analyze them. Slow queries can frustrate users and hinder decision-making, particularly in dashboards and analytics-intensive workflows.

With the June 2025 release, ArcGIS Online introduces Columnstore Indexes, a powerful new feature designed to accelerate analytics and make them scalable, even across millions of features.

In this post, we’ll explain what a columnstore index is, when to use one, how it can supercharge your ArcGIS Online queries, and best practices for getting the most out of it.

What Is a Columnstore Index

At its core, a columnstore index changes how data is stored and queried. Instead of organizing data row by row like a traditional table, it stores it column by column. This columnar format enables the system to scan, filter, and aggregate data more efficiently, particularly in large datasets where you’re only interested in a few fields at a time.

A columnstore index takes this a step further. It adds a column-based index alongside your existing feature layer, without changing how the data is stored at the base. That means:

• Your layer remains fully writable. You can continue to insert, update, or delete features.
• You gain access to a highly compressed columnstore format, optimized for analytics.
• You don’t lose the benefits of your existing row-based storage, and you’re simply layering on an analytical superpower.

Think of it as adding a high-speed express lane to your data, ideal for large-scale analytics, without rerouting your entire traffic system.

A Simple Analogy: Finding the Price of an Apple

Imagine you walk into a library where data is stored in books. You want to find the price of an apple.

• In a rowstore index, each book contains full rows: Apple, 2025-01-01, 10 units, $1.00, etc. To find the price, you need to read every row, even if you only care about one column.
• In a columnstore index, data is neatly organized by column. There’s a whole shelf just for “Price”, another for “Product”, and so on. To find the price of an apple, you just:
  1. Go to the “Product” shelf and find all rows labeled “Apple”.
  2. Then check the “Price” shelf at the same position.

This makes columnstore faster and more efficient for analytical queries because:

• It reads only the columns you need.
• It skips irrelevant chunks of data using min/max values.
• It works in batches, not one row at a time.

When Should You Use a Columnstore Index

Columnstore Indexes can be game-changing, but only in the right scenarios.

They shine when you’re working with large, mostly read-heavy feature layers. If your data is primarily used for reporting, dashboards, or statistical summaries, a columnstore index can significantly improve performance. Queries that involve aggregations (like SUM, AVG) or GROUP BY clauses are especially good candidates.

Another great use case? When you’re trying to reduce storage size. Columnstore indexes compress data very efficiently, which can cut down not just on disk usage but also on I/O during queries, meaning faster results with less strain on your system.

How Do Columnstore Indexes Boost API Performance in ArcGIS Online

Columnstore indexes significantly enhance the performance of several commonly used analytical APIs in ArcGIS Online, especially when working with large datasets.

Take the query operation as an example. When you includeoutStatistics, a columnstore index can drastically reduce the time it takes to compute statistics. The larger your dataset, the more noticeable the improvement becomes, turning slow, multi-second queries into near-instant responses.

Similarly, the queryBin API, commonly used to group features into bins, experiences substantial performance gains, particularly when working with high-volume datasets.

For heavier analytic operations, queryAnalytic benefits from columnar access patterns that allow the system to scan large volumes of data far more efficiently.

Even the queryBoxplot operation can take advantage of a columnstore index in many cases.

The charts below illustrate the significant impact of indexing on performance in large-scale operations. We compared queryand queryBin operations across datasets ranging from 3.8 million to 46 million records, using three configurations: no index, rowstore index, and columnstore index.

The difference is striking. For both operations, the columnstore index consistently delivers the fastest execution times, even as data volumes grow. In contrast, rowstore indexing and no indexing result in noticeably slower response times, especially evident in the queryBin results, where no indexing performance declines sharply with larger datasets.

These results confirm that columnstore indexes are a powerful optimization for analytics workloads, dramatically reducing execution times in operations that involve grouping, binning, and summarizing on massive datasets.

How to Use a Columnstore Index in ArcGIS Online

Columnstore indexes can unlock serious performance gains, but if you’re looking for them in the ArcGIS Online UI, you won’t find them. Columnstore indexes are currently only accessible via the REST Admin API.

Adding a Columnstore Index

You can add only one columnstore index per layer.  Use the Add To Definition (addToDefinition) operation via the layer’s REST Admin API. Here is the syntax of the JSON payload you will need:

    {
      "indexes": [
        {
          "name": "<index name>",
          "fields": "<field1,field2,field3,...>",
          "description": "Description of the index",
          "indexType": "ColumnStore"
        }
      ]
    }

What these fields mean:

• name: Optional. If you skip this, the system will autogenerate a name for the index.
• fields: Required. These are the fields to include in your columnstore index—typically ones used in filters, aggregations, or groupings.
• description: Required. Describe what the index is for (e.g., “Column store index used for improving aggregation”).
• indexType: Required. Must be “ColumnStore” to indicate a columnstore index.

Restrictions:

• Spatial and LOB (e.g., geometry, attachments) columns are not supported. Refer to Microsoft documentation for a full list of unsupported types.

• Adding unsupported fields triggers an error:
  The statement failed. Column '<columnName>' has a data type that cannot participate in a columnstore index.

• Attempting to add a second columnstore index results:
  Invalid definition for System.Collections.Generic.List[ESRI.ArcGIS.SDS.FieldIndex]

Dropping a Columnstore Index

If you need to remove a columnstore index, for example, to rebuild or change the fields, use Delete From Definition (deleteFromDefinition) operation:

    {
      "indexes": [
        {
          "name": "<index name>"
        }
      ]
    }

If the index name was autogenerated, you can find it under the layer’s JSON properties under the indexes section.

Updating or Rebuilding an Index

You can’t update or rebuild an existing columnstore index. If you want to make changes:

1. Delete the current columnstore index.

2. Re-add it with the updated fields or description.

Best Practices for Working with Columnstore Indexes

Columnstore indexes can do wonders for performance, but like any tool, they work best when used correctly. Here’s how to make the most of them in your ArcGIS Online layers:

• Start with big data

  Columnstore indexes are built for scale. If your layer has millions of features, you’ll likely see dramatic speed-ups in analytics and dashboards. For smaller datasets? The benefits may be less noticeable.

• Index the right fields

  Not all fields belong in a columnstore index. Focus on the ones that matter most for performance, fields used in filters (WHERE clauses), groupings (GROUP BY), or aggregations (SUM, AVG, etc). These are where columnstore indexes shine.

  Skip fields like OBJECTID and GLOBALID. These unique identifiers don’t benefit from compression or fast scanning, and including them just bloats the index without boosting performance.

• Clean up old rowstore indexes

  Still have leftover rowstore indexes that aren’t in use? Consider removing them. They take up space and can slow things down. Let the columnstore index do the heavy lifting for aggregations and scans.

• Go easy on deletes

  Bulk deletes can fragment your index over time. If you do lots of deletions, plan for periodic reorganizing or rebuilding of your columnstore indexes to keep performance sharp.

• Keep rowstore indexes for pinpoint lookups

  Need to grab a single record quickly, say for a boxplot or feature by ID? A traditional rowstore index still has its place. Use both index types side-by-side when needed.

Conclusion

Columnstore Indexes are a practical way to accelerate performance and reduce storage in ArcGIS Online, especially for analytics-heavy workloads. With the right schema design and index maintenance, columnstore indexes allow you to:

• Query millions of records in seconds
• Optimize storage without sacrificing update capability
• Enable real-time dashboards and reports

Next Step

Try adding a columnstore index to one of your largest layers via the REST Admin API. Test performance before and after; it’s one of the most impactful changes you can make for data at scale.