"},{"ID":366352,"user_firstname":"Ann","user_lastname":"Wang","nickname":"Ann Wang","user_nicename":"awang","display_name":"Ann Wang","user_email":"awang@esri.com","user_url":"","user_registered":"2024-12-17 01:34:48","user_description":"Ann is a Product Engineer for ArcGIS Online.","user_avatar":"
"},{"ID":39221,"user_firstname":"Eddie","user_lastname":"Idolyantes","nickname":"Eddie Idolyantes","user_nicename":"eidolyantes","display_name":"Eddie Idolyantes","user_email":"eidolyantes@esri.com","user_url":"","user_registered":"2020-05-29 23:47:17","user_description":"Eddie is a Product Engineer on the ArcGIS Online team and works primarily with the Hosted Feature Service. Our ArcGIS Online team is at the heart of the ArcGIS platform and I have the passion for problem solving to fulfill the needs of the Esri user community by pushing the envelope of Hosted Feature Service capabilities provided by ArcGIS.","user_avatar":"
"}],"short_description":"Explore columnstore indexes, when to use them, best practices, and how they boost ArcGIS Online analytics.","flexible_content":[{"acf_fc_layout":"content","content":"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.<\/p>\n
With the June 2025 release<\/strong>, ArcGIS Online introduces Columnstore Indexes<\/strong>, a powerful new feature designed to accelerate analytics and make them scalable, even across millions of features.<\/p>\n 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.<\/p>\n <\/p>\n At its core, a columnstore index<\/strong> changes how data is stored and queried. Instead of organizing data row by row <\/strong>like a traditional table, it stores it column by column<\/strong>. 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.<\/p>\n A columnstore index <\/strong>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:<\/p>\n Think of it as adding a high-speed express lane to your data, ideal for large-scale analytics, without rerouting your entire traffic system.<\/p>\n"},{"acf_fc_layout":"image","image":{"ID":2929981,"id":2929981,"title":"Rowstore vs columnstore indexing","filename":"Rowstore-vs-columnstore-indexing-2.jpg","filesize":180702,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-online\/analytics\/utilizing-columnstore-indexes-in-arcgis-online\/rowstore-vs-columnstore-indexing-2","alt":"Rowstore vs Columnstore Indexing: A Visual Comparison","author":"366362","description":"","caption":"","name":"rowstore-vs-columnstore-indexing-2","status":"inherit","uploaded_to":2901972,"date":"2025-08-14 19:24:02","modified":"2025-08-14 19:24:39","menu_order":0,"mime_type":"image\/jpeg","type":"image","subtype":"jpeg","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":1318,"height":738,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2-213x200.jpg","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","medium-width":464,"medium-height":261,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","medium_large-width":768,"medium_large-height":430,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","large-width":1318,"large-height":738,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","1536x1536-width":1318,"1536x1536-height":738,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","2048x2048-width":1318,"2048x2048-height":738,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2-826x463.jpg","card_image-width":826,"card_image-height":463,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/08\/Rowstore-vs-columnstore-indexing-2.jpg","wide_image-width":1318,"wide_image-height":738}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":" Imagine you walk into a library where data is stored in books. You want to find the price of an apple.<\/p>\n This makes columnstore faster and more efficient for analytical queries because:<\/p>\n <\/p>\n Columnstore Indexes can be game-changing, but only in the right scenarios.<\/p>\n 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 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.<\/p>\n <\/p>\n Columnstore indexes significantly enhance the performance of several commonly used analytical APIs in ArcGIS Online, especially when working with large datasets.<\/p>\n Take the Similarly, the For heavier analytic operations, Even the The charts below illustrate the significant impact of indexing on performance in large-scale operations. We compared 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 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.<\/p>\n"},{"acf_fc_layout":"image","image":{"ID":2911632,"id":2911632,"title":"query and queryBin test results with various indexs","filename":"query-and-queryBin-test-results-with-various-indexs-2.jpg","filesize":156854,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-online\/analytics\/utilizing-columnstore-indexes-in-arcgis-online\/query-and-querybin-test-results-with-various-indexs-3","alt":"","author":"366362","description":"","caption":"","name":"query-and-querybin-test-results-with-various-indexs-3","status":"inherit","uploaded_to":2901972,"date":"2025-07-29 14:06:45","modified":"2025-07-29 14:06:45","menu_order":0,"mime_type":"image\/jpeg","type":"image","subtype":"jpeg","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":1171,"height":524,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2-213x200.jpg","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","medium-width":464,"medium-height":208,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","medium_large-width":768,"medium_large-height":344,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","large-width":1171,"large-height":524,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","1536x1536-width":1171,"1536x1536-height":524,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","2048x2048-width":1171,"2048x2048-height":524,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2-826x370.jpg","card_image-width":826,"card_image-height":370,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2025\/07\/query-and-queryBin-test-results-with-various-indexs-2.jpg","wide_image-width":1171,"wide_image-height":524}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":" <\/p>\n Columnstore indexes can unlock serious performance gains. They are now fully supported in the ArcGIS Online UI, allowing you to create and manage them directly without using the REST Admin API. For details on managing columnstore indexes in the ArcGIS Online UI, see the ArcGIS Online documentation on adding and managing an index<\/a>.<\/p>\n Note: When this post was originally published, columnstore indexes were only accessible through the REST Admin API. ArcGIS Online UI support was added later in the 2025 October release.<\/em><\/p>\n You can add only one columnstore index per layer<\/strong>. \u00a0Use the Add To Definition<\/strong><\/a> (addToDefinition<\/strong>) operation via the layer\u2019s REST Admin API. Here is the syntax of the JSON payload you will need:<\/p>\n What these fields mean:<\/strong><\/p>\n Restrictions:<\/strong><\/p>\nWhat Is a Columnstore Index<\/h2>\n
\n
A Simple Analogy: Finding the Price of an Apple<\/strong><\/h4>\n
\n
\n
\n
\n
When Should You Use a Columnstore Index<\/h2>\n
SUM<\/code>, AVG<\/code>) or GROUP BY<\/code> clauses are especially good candidates.<\/p>\nHow Do Columnstore Indexes Boost API Performance in ArcGIS Online<\/h2>\n
query<\/code> operation as an example. When you includeoutStatistics<\/code>, 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.<\/p>\nqueryBin<\/code> API, commonly used to group features into bins, experiences substantial performance gains, particularly when working with high-volume datasets.<\/p>\nqueryAnalytic<\/code> benefits from columnar access patterns that allow the system to scan large volumes of data far more efficiently.<\/p>\nqueryBoxplot<\/code> operation can take advantage of a columnstore index in many cases.<\/p>\nquery<\/code>and queryBin<\/code> operations across datasets ranging from 3.8 million to 46 million records, using three configurations: no index<\/strong>, rowstore index<\/strong>, and columnstore index<\/strong>.<\/p>\nqueryBin<\/code> results, where no indexing performance declines sharply with larger datasets.<\/p>\nHow to Use a Columnstore Index in ArcGIS Online<\/h2>\n
Adding a Columnstore Index Using the REST Admin API<\/h3>\n
{\r\n \"indexes\": [\r\n {\r\n \"name\": \"<index name>\",\r\n \"fields\": \"<field1,field2,field3,...>\",\r\n \"description\": \"Description of the index\",\r\n \"indexType\": \"ColumnStore\"\r\n }\r\n ]\r\n }\r\n<\/code><\/pre>\n\n
\n