21–30 of 226 posts
> I use grep on a flat file, and testing now it takes 200ms for a query across my 180k history entries 200ms is order of magnitude more than I'd prefer for interactive use
Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering.
If you deliberately aren’t implementing indexing, know at what point indexing would begin to matter. Put in place guard rails so the system doesn’t just blow up unexpectedly.
There are 100% cases where you shouldn’t use indexes. That’s not the same thing as being able to get away without an index.
On the flip side, a missing index can bring down production. We experienced that just a couple of weeks ago, where a missing index and an unexpected 1000x increase in volume at a customer brought the DB to its knees. Sure it was still serving queries but at such a low rate it was effectively useless. For queries that will be run more than once, I try make sure there's an index it can use for something fairly unique.
My lessons from that experience were twofold. First, you can't plan for sudden performance regressions ahead of time. The number of stars that have to align for that anticipatory index to actually help you are just too great. Even if you correctly guessed that a given table might be affected by one in the future, you'll never guess what fields it needs to include, and in which order, to support whatever new query or query plan change or whatever caused the problem. Second, Murphy's Law dictates that any attempt at preventing future read performance regressions by speculatively creating an index will end up causing a write performance problem instead.
Better instead to just get in the habit of periodically reviewing query plans for every query in the database. If you know the scaling characteristics of the various operations and the kinds of statistics changes are likely to cause the optimizer to choose different ones (and you should), then it's easy enough to select for better scaling characteristics. This is, incidentally, an excellent excuse for making the engineers use sprocs or a clean data access layer or something instead of a heavyweight ORM, so that you have some sensible way of getting a warning before they change queries on you. You can't effectively aim for a target that's bouncing around erratically. Even better still - and only realistically feasible if you somehow managed to win that bigger war I just proposed - set up a tool to monitor query plans and send you an alert when they change unexpectedly, so you can hear about the problem when it's still small.
If the data is small, then the index will also be small, so it doesn't really matter either way. The reason to avoid indexes is if you can't accept the time or space cost. Only the last of OP's examples is a situation where choosing an index causes a problem; the rest are examples where an index isn't necessary but does not cause problems. Several examples are so trivial that you'd be crazy to use an external databas…
I wish languages other than SQL had a concept of adding an index. Imagine a variant of Python's list or C++'s vector where you can add indicies on arbitrary fields of the contents, like a more flexible dict. Something like (pseudocode)
books = List
Earlier quoted context omitted.
Indexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.
They add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.
Of course, if the object is large scale bulk inserts, with only very occasional selects, then yes.
?subject ?predicate ?object.
I went to a talk at Dreamforce years ago where the CTO explained their database architecture and it is interesting that the core table in Saleforce is literally a collection of triples. They have a smart optimizer that builds views, materialized views, indexes, etc. in Oracle based on the queries you run.
I built a bot that would download a lot of records from an organization, build a "machine learning" model (built by a geospatial analyst and we didn't call it that these days) that automatically assigns opportunities to salespeople, and then pushes the assignments into the system. The first time I ran the big query in the test system it timed out, then I went to the bathroom and when I came back the second time it worked perfectly. When it went to production exactly the same thing happened.
CloudWatch Logs Insights, Snowflake, Grafana Loki all do minimally indexed queries with highly parallel brute force scans of tons and tons of smallish s3 objects. A lot of data, especially data like logs, the ratio of “retrieved:ingested” can be in excess of 1:100. That makes it very much worth while to not index up front but to pay the retrieval cost for that rare amount of data that is actually retrieved. The minim…
> CloudWatch Logs Insights, Snowflake, Grafana Loki And the common thing in this list of tools? They're all laggy and not a nice experience to use. Even if queries are rare, there is often an actual human sitting waiting for the results of a query. Whereas while ingesting logs, there is no human waiting. I would prefer to burn more CPU time to save some human time.
That probably isn't the end of the world, but it can make it harder for others to understand what is going on in your database (indexes can also be a form of documentation) and could make your ORM code somewhat more complex.
The worst case scenario, I suppose, would be you create an index on a table that grows large
On the flip side, a missing index can bring down production. We experienced that just a couple of weeks ago, where a missing index and an unexpected 1000x increase in volume at a customer brought the DB to its knees. Sure it was still serving queries but at such a low rate it was effectively useless. For queries that will be run more than once, I try make sure there's an index it can use for something fairly unique.
Was this because of a missing index or because the optimizer vomited on the queries without an index to provide it statistics? My gripe with RDBs is generally the query optimizer is non deterministic with respect to the query, I.e., it can make a great decision with certain statistics and flip to a terrible one with slightly different statistics even though the original query would have performed basically the same.…
Years ago I forked postgres and tried to implement the above. Initial results were promising - there were a good chunk of queries that ended up taking a different plan, and sometimes returning 100x quicker.
Alas, the postgres codebase is heindously complex, and implementing the above to be production grade would be many months work - and, due to the way postgres streams results to the client, might have actually required a change to the wire format.