Anymap Is Flexible, But Not Always the Fastest

Question

A table needs to store an optional, variable set of fields per row — different vendors supply different fields, and the set of fields can grow over time. Anymap, a widened flat table (one column per possible field), and a keyed/parted table are all options. Is anymap the best choice for query performance?

Solution

Not necessarily. Anymap's strength is flexibility, not raw query speed. If the set of fields is small and stable enough to model directly, a widened flat table (one column per field) or a keyed table sorted on its key with the parted attribute will usually be faster to query, because they keep all of select's normal column optimisations:

t: ([] keyCol:`v1`v2`v1`v3; time:09:00 09:01 09:02 09:03; field:`bid`ask`size`bid; val:1 2 3 4f)
t: `keyCol`time xasc t         / sort by the key, then time
update `p#keyCol from `t       / apply the parted attribute

Explanation

Anymap's appeal is schema flexibility as the number of possible fields grows. A widened flat table adds a column — and storage cost — for every new field, even when most rows leave most of those columns null, whereas anymap and a keyed (field-per-row) table cope with a growing field set without changing the schema. That flexibility costs something on the query side: nested anymap data can't benefit from the column-wise optimisations that ordinary typed columns get, so a flatter design that fits the data is usually faster.

What anymap does offer is bounded memory use when reading: entries are mapped and paged in on demand, rather than the whole column being deserialised onto the heap — unlike storing each row as serialised bytes (-8!) and deserialising every one on read (-9!), where memory grows with every row returned.

The practical takeaway: choose anymap when the schema is genuinely unpredictable and flexibility matters most; choose a flat or keyed design when the field set is small and stable enough to model directly. If query speed matters, benchmark the options on your own data.