[Feature Request] Optimized Hierarchical Path Aggregation for Large-Scale Scientific Datasets

[wjbgis]

Background

I am building a data discovery and download platform using pgstac with over 3 million Items. Unlike standard satellite imagery which is primarily searched by Spatio-Temporal filters, my dataset follows a deep business logic hierarchy common in scientific research (e.g., Product Name -> City -> Data Type -> Train/Test Sets).

The Problem

Current pgstac implementations and standard STAC API patterns make it difficult to render a "Directory Tree" UI efficiently for large-scale datasets:

Recursive Catalog Bottleneck: Implementing a hierarchical structure using nested Catalogs requires numerous recursive API calls, which is too slow for a seamless UI experience.

Aggregation Performance: Performing a SELECT DISTINCT on a path-like property (e.g., properties.path) within a 3M+ record JSONB column leads to full table scans, resulting in 5-15s latency—unacceptable for real-time navigation.

Delimiter-based Discovery Gap: There is currently no optimized way to "explore" sub-folders within a Collection without fetching all Items or hitting performance walls.

Request for Guidance

As my goal is to provide a seamless browsing experience for researchers to "drill down" through these logical folders, I would like to ask the community and maintainers:

Is there a recommended pattern within pgstac to handle such deep, non-spatial hierarchical navigation efficiently at scale?

Are there any plans to support features that would allow for "prefix-based" or "delimiter-based" discovery of unique property values (similar to a file system explorer) without fetching all Items?

I am very impressed with pgstac's performance on spatial queries, and I would appreciate your wisdom or any guidance on how to best bridge this gap for scientific data organization.

Thank you for your time and for this amazing project!

[alukach]

Hey @wjbgis, I appreciate this well written issue.

Implementing a hierarchical structure using nested Catalogs requires numerous recursive API calls, which is too slow for a seamless UI experience.

While nested STAC catalogs/collections are often requested on STAC users (I myself have made such requests), they're rarely necessary. I think the solution to your needs will be to model your data within a flat set of models. i.e. Don't try too hard to make the API match the UI, rather keep it closer to the database.

There is currently no optimized way to "explore" sub-folders within a Collection without fetching all Items or hitting performance walls.

Would it not be sufficient to place product_name, city, data_type, and training_set properties on each Item and use those to quickly navigate via filters + proper indexing?

It sounds like you know what you're talking about, so I apologize if my suggestion is obvious

[alukach]

The main issue I'm facing is that we have hundreds of heterogeneous datasets from different research teams, each with its own unique hierarchy (e.g., some use City/Year, others use Sensor/Level/Tile).

Curious to hear more about your needs. Is the idea that each heterogenous dataset would be its own Collections? And the prefix is a property of the Collection? And there are many similar Collections but with different prefix values (e.g. different years)? OR, is each dataset a Collection and within that Collection, each Item has a prefix values?

Thinking more about this, it feels like the right solution could be made with the ltree extension ¹ in postgres. Perhaps you could add an ltree column onto the Collection or Items table and use that for performant queries to traverse the tree (e.g. getting distinct descendant nodes from a level of the prefix); or alternatively you could use a separate table to track these. However, this wouldn't help with querying the records from a STAC API, in which case I think you'd also want the prefix stored in the properties column and to use that b-tree index to allow performant lookups via CQL2 (I'm assuming you're going to use stac-fastapi-pgstac). Some fancy trigger functions could keep the two values in sync.

Curious to hear if @bitner has any thoughts on this idea.

Footnotes

1. https://www.postgresql.org/docs/current/ltree.html ↩

[alukach]

To clarify our dataset:

• Each heterogeneous dataset is its own Collection.
• The hierarchy exists within the Items of that Collection.

@wjbgis Okay, this is all sounding good. I think that, given an API extension which returns the child nodes for a given collection ID & parent path, you should be able to build a decent UI experience to traverse a tree of items over a given collection.

Please do report back letting us now how this goes!

(btw, I've converted this issue to a discussion which caused a bit of chaos regarding the linear history of this discussion, sorry about that)

[wjbgis]

@alukach Thanks for your reply. I see your point about keeping the API close to the database.

The main issue I'm facing is that we have hundreds of heterogeneous datasets from different research teams, each with its own unique hierarchy (e.g., some use City/Year, others use Sensor/Level/Tile).

Mapping all those into unique flat properties would be a maintenance nightmare—we’d have to write and manage custom parsing/matching rules for every new dataset just to extract the right fields.

To keep it manageable, I’m thinking of a middle ground:

1. Store the UI tree in a separate config table (for the frontend).

2. Store a single path property on each Item.

3. Use prefix filters (e.g., path LIKE 'A/B/%') to fetch the items when a user clicks a "folder."

I'm assuming a B-tree index on that path property should hold up fine. Do you think this is a solid enough workaround for this kind of heterogeneous data?

Would love to hear your take before I go down this road.

[wjbgis]

Thinking more about this, it feels like the right solution could be made with the ltree extension 1 in postgres.

Thanks for the suggestion! I'll definitely dive into the ltree extension and see how it can work for my use case.

Curious to hear more about your needs. Is the idea that each heterogenous dataset would be its own Collections? And the prefix is a property of the Collection? And there are many similar Collections but with different prefix values (e.g. different years)? OR, is each dataset a Collection and within that Collection, each Item has a prefix values?

To clarify our dataset:

• Each heterogeneous dataset is its own Collection.

• The hierarchy exists within the Items of that Collection.

For example, in a collection called Multi-scale building height products and GF-7 building roof dataset, an Item's logical path might be Multi-scale-building-height-products/China/China_BH_2005_v2/BH2005_E100_N40_E105_N45.tif. We need the UI to let users "drill down" through those levels.

[bitner]

Please make sure to review #88 (comment) as well.

If you proceed to work on this, please sketch out your plans for any changes to the storage model before spending too much time working on things. Changes in the storage model are a big deal and are not something that can easily be added as they may cause the need for an entire data rewrite within the database which could make an update prohibitive for anyone with a very large existing STAC database.