@bbaird I don't think that insert performance has any bearing on this; after all, DW workloads are heavily skewed toward reads, not writes. Those workloads though don't benefit much from b-tree indices, and in many cases such indices are not normally maintained at all, so to have an index just to maintain integrity is often an unwarranted cost
Especially considering that DWs are typically fed from staging areas where constraints are properly maintained, and the risk of integrity violations are therefor low
@mustaccio - "Those workloads though don't benefit much from b-tree indices,"surely that's true if you're querying over entire tables - i.e. scanning all the time rather than querying for specific dates and/or other factors? Having indexes for these cases - where the data being queried doesn't make up more than 10% (or whatever the rule of thumb is for RDBMS x) of an entire table...
Also, and this is just a personal observation/opinion - give people (DBAs, devs...) enough rope and they'll hang themselves - "Oh, I'll just INSERT this record for a quick test..." - phone rings, voice on other end "Your wife has just gone into labour..." - "test" record not backed out... if it's a question of sacrificing speed for accuracy, I'd take accuracy any day - a bit like food - cheap fast take-away vs. well-prepared gourmet cuisine.
Unless you're using SQLite to run your DW - cheap, small, fast & reliable - choose any 4 - well, 3 really - "cheap" isn't included in their logo! :-)
@Vérace Note the conversation is about the likes of Synapse DW, Snowflake, and Netezza -- these are not "normal" DBMSes. Maintaining a global unique index over a massively partitioned table is very costly and as I said is not necessary, since you're not scanning a table -- you're scanning dozens/hundreds of relatively small partitions in parallel. Snowflake is also a columnar store, so it is in a sense its own index.
@mustaccio You need the index to maintain uniqueness, which maintains trust. Sure, you can throw it away because it's "expensive" to maintain, but to maintain trust you basically have to expend the same amount of effort (un-optimized, mind you) to recreate the same functionality.
And the indexes help in all manner of queries certainly EXISTS/NOT EXISTS.
Yes, specific DBs will do neat little tricks to avoid reading as much data. But in my experience, these are nowhere near as efficient as a properly normalized database with full constraints.
There are many ways to maintain trust, and b-trees do not always give you most bang for the buck, for varying definitions of "bang" and "buck". Trust is also relative
