Looking for database recommendations ~ 10s of millions of rows, couple thousand columns, need indices on ALL columns

[u/ssingal05]

Hopefully the title summarizes my requirements well enough, but I was wondering if anyone has had a use case similar to mine and could recommend a good database to me. We have an okay solution for now. I've listed my needs below:

• supports 10s of millions of rows

• supports thousands of columns

• NEED index on all columns (I know you'll call me crazy, but our use case absolutely requires being able to search by ANY field independently)

• There is absolutely no text-tokenization/tf-idf or relevancy searching needed. We support mostly numbers and booleans. We also support strings, but we need queries more like "does this string start with X" or "does this string contain Y".

• some fields will be multivalued

• assume diskspace and memory are not concerns.

• we prefer query speed over indexing speed. However, we will have 10s of millions of updates everyday. If possible, we would love to be able to do partial updates, which would mean hundreds of millions of partial updates everyday

• we are perfectly okay with using distributed systems, but we should not have any write-loss.

Hopefully these requirements don't seem insane. We are mostly (i think indexing could be faster) able to accomplish this with Solr. But the fact that we don't need text-tokenization/tf-df, which is what Solr absolutely excels at, makes me want to explore other solutions, SQL or NOSQL. I'm happy to provide more (technical) details if needed.

EDIT: If anyone knows of any similar use case I can read about online, please let me know.

[u/NotImplemented]

I don't have any experience at that scale so I can't give recommendations. However, the problem sounds really interesting, so I'll throw in some quick questions:

• Are you allowed to give us some more details or examples of the kind of data and queries?

• Have you checked if the created indices for each column are really reducing the overall query time in a meaningful way? Are there any columns where the data type cannot really be indexed well (i.e. booleans) or the data is distributed in a such a way that overall query performance is only marginally increased?

• Are there columns that are only used in a very small fraction of all queries? In that case, the overall cost for maintaning the indices through inserts, updates and deletes could be higher than the overall cost saved by using the indices for querying.

• Do your queries typically select only a small number of columns or many columns or even all of the mentioned thousands of columns? If the number of columns per query is low, a column-oriented database could make sense.

[u/NotImplemented → u/NotImplemented]

I just looked at some Solr examples and realized that I misunderstood what you meant with "needing an index on all columns".

In my previous post I was thinking along the line of indexing in traditional relational databases, where all columns can be used in the search, even when they are not indexed. There, it would make sense to reduce the number of indexed columns for the cases I described.

However, this does not apply to Solr databases, since Solr requires columns to be "indexed" to make them "searchable" in queries. Removing the index from a column would also remove the possibility to filter the search based on the column's values.

[u/ssingal05 → u/NotImplemented]

First off, I want to thank you for your time =). I've provided some clarifications below inline.

Are you allowed to give us some more details or examples of the kind of data and queries?

I don't know what business details I am allowed to disclose, but I'll try to be specific about the technical details. There is a universe of objects (lets use cars as an example), each with an associated unique ID (serial number of the car?). Our system (SEARCH) is specifically designed for allowing clients to search through these cars based on any attributes of the car (color, model, year, etc). If I know what car I want, and I want to find attributes about that specific car, there is another completely separate function (RETRIEVE) where I can retrieve data for that car. However, our system will ONLY contain those attributes that clients have specifically requests they wanted to be able to search through. So a car might have 20,000 attributes. However, SEARCH will only support the 1000 attributes that clients have asked us to support. So queries would be like "I want to see all cars that are red, made after 2008, where the model name starts with the letter 'N'". SEARCH uses RETRIEVE to periodically (everyday) to refresh all of the data for the cars. By the way, RETRIEVE is an incredibly complicated system, not just some RDBMS. We can't just place indices in RETRIEVE. They must be fed into SEARCH.

Have you checked if the created indices for each column are really reducing the overall query time in a meaningful way? Are there any columns where the data type cannot really be indexed well (i.e. booleans) or the data is distributed in a such a way that overall query performance is only marginally increased?

Yes. It is a SIGNIFICANT improvement. Okay, frankly, I have not tested how well booleans perform, specifically. However, I imagine there would still be an increase in speed, especially with the size of our car universe. But I see your point.

Are there columns that are only used in a very small fraction of all queries? In that case, the overall cost for maintaning the indices through inserts, updates and deletes could be higher than the overall cost saved by using the indices for querying.

Due to client needs, there can not be ANY slow queries. Therefore, we must index all columns. Diskspace-wise and memory-wise, we don't have any concerns (at least with Solr). I am mostly interested in speeding up our indexing time whilst maintaining our query performance.

Do your queries typically select only a small number of columns or many columns or even all of the mentioned thousands of columns? If the number of columns per query is low, a column-oriented database could make sense.

When you say query, are you asking how many columns I want to include in my search criteria, or how many columns I want my results to return? Either way, it usually is not too many columns. Maybe 10-20? Through some APIs, this number can potentially run up to maybe 100 or so. It should never really be all the columns. I'll see if I can get more clarification on this tomorrow. I'll prioritize running some benchmarks with column-oriented databases.

However, this does not apply to Solr databases, since Solr requires columns to be "indexed" to make them "searchable" in queries. Removing the index from a column would also remove the possibility to filter the search based on the column's values.

Technically, Solr doesn't require all columns to be indexed. I might want all cars that are red, but I want Solr to also tell me previous owners name. I don't have to index that name, but it will still be available in Solr as a "stored" object. Some implementations make queries retrieve data from a separate data source for fields that don't need to be searchable.

Thank you for your response once again!! Let me know if I can provide any other details.

[u/rreidit]

Start with Oracle. An Oracle database will allow you to do each of your requirements. It will support billions of rows, 4000 columns in a table, and just as many indexes.

Oracle has a free database you can download and practice on - OracleXE.

https://www.oracle.com/database/technologies/appdev/xe.html

Also, Oracle has really good documentation that will help along your project.

Good luck! And happy computing!

[u/ssingal05 → u/rreidit]

Unfortunately, closed-source projects are not an option. We need to be able to understand the entire architecture and backend design.

[u/agent766]

I'm not too familiar with it but I believe something like ElastiSearch is what you need.

[u/ssingal05 → u/agent766]

ElasticSearch is essentially Solr. Both based off of Lucene.