Search to be implemented for currently developing a project which is being developed using Spring Boot and Postgres.

The search table has at least 8 columns, all the columns are used in the search filtering. For this, how many indices need to be created? What type of index needs to be created?

There will be at least 1M records in the table. Is Postgres enough with better indices? or need to choose any other NoSQL like Elastic Search?

Please share your thoughts and experience on this.


enter image description here

please refer to the above screen capture for my search mock design. All the combinations of each column would be used for filtering. If so 8 power 8 would be 40320. So do I need to create 40320 indices? FYI: I have few more searches same like this. Since I have many columns, so I guess there would be more indices to be created which is not the right way to do. in this case, Elastic Search is better to choose or Postgres itself serves the purpose

  • Don't ask the same question in several forums. – Laurenz Albe Mar 8 at 9:34
  • With a small table like that, performance isn't the major criteria I would say. You should first check if the built-in full text search supports all the features you need. – a_horse_with_no_name Mar 8 at 10:04
  • Under most circumstances these days a few million records is not large for any good DB if well indexed. Beyond that we'd need a lot more details before being able to recommend an indexing strategy or a particular database. – David Spillett Mar 8 at 10:42
  • @ThomsonIgnesious You are still missing some important information that we requested, such as "examples of specific queries you need to support". For example if your searches are always equality based, e.g. WHERE PID = 123 AND TID = 56 then the type of index you need could be completely different than if you need to do wildcard matching such as WHERE Roles LIKE 'RoleA%'. The more examples, the better please. – J.D. Mar 9 at 13:32
  • @J.D. mostly it will be full-text using LIKE – Thomson Ignesious Mar 10 at 4:57

When you need 1M records out of 5M, no index will be of use, better scan the full table.

  • Even if the data request is so large that a table scan is inevitable (because the post-index-search lookups would be more expensive than the scan) then wide indexes may help considerably at the expense of storage space. Also indexes may help if the data was requested with an ORDER BY to avoid spooling to disk in order to sort. – David Spillett Mar 8 at 10:46
  • So you agree that the chances an index may be of use, are very slim? – Gerard H. Pille Mar 8 at 11:21
  • No, just that single-column non-clustered indexes are unlikely to be terribly useful, especially if there is no simple ordering use for them. We need much more detail than the question gives to say anything at all concrete, but I suspect there would be an indexing strategy that would help. – David Spillett Mar 8 at 11:56
  • 1
    @DavidSpillett - I thought that PostgreSQL didn't have clustered indexes? – Vérace Mar 8 at 18:18
  • 1
    @Vérace - it doesn't as such, but it does support wide indexes which would convey the relevant benefits here (removing the need for further table lookups in some cases). Though be careful with columns that are INCLUDEd rather than being part of the indexed values as postgres has some subtleties there (see use-the-index-luke.com/blog/2019-04/…). – David Spillett Mar 9 at 11:31

NoSQL and ElasticSearch are neither more performant database systems than any other modern relational database system, such as PostgreSQL, when talking about amount of data. NoSQL is an option of choice when you have a very dynamic schema or lack thereof, and ElasticSearch is useful for tokenizing large bodies of text to make them more easily searchable, which may very well be relevant to your use-case.

Unfortunately your post is a little unclear to be able to provide a concrete answer, as we'd need more details such as your Table's schema, some example search queries you anticipate will be ran, and some example data you plan to store in the table and would be returned by those queries.

As others have pointed out, if your table is only 1 million records, 5 records, or even 100 million records, proper structuring and indexing will provide you with performance on PostgreSQL, no problem. But without the details I mentioned above, we can't recommend what kind of structuring and indexing you'll need, most optimally. For example, a table with only 3 columns has at least 11 possible different combinations of index definitions that can be created (since field order in the definition matters). But it's unrealistic and possibly would affect performance negatively to store 11 indexes on a table of 3 columns, rather it's important to define your indexes based on the queries being executed against the table. The indexes definitions depend on the queries that are ran more so than the columns that exist in the table themselves.

  • 1
    thanks, I am building the search functionalities now, I will get the search query, with analyze details for better index – Thomson Ignesious Mar 8 at 12:37
  • All my doubt is. I have 8 columns in the table. how many indexes do I need to create? An index for each column is alone enough or compound indices required for the combination of columns for a number of possibilities? – Thomson Ignesious Mar 8 at 12:39
  • 1
    @ThomsonIgnesious: that depends on how you want to search. If you always want to search in all 8 columns at the same time, a single index on the concatenated values would be enough. If you want to search in each column individually you need 8 different indexes. – a_horse_with_no_name Mar 8 at 13:02
  • @ThomsonIgnesious This is why my answer requests the specific search queries you expect to run. It doesn't matter how many columns you have, rather it matters how those columns are used in your queries. The answer to "how many indexes are needed for an 8 column table" could literally be anywhere between 0 and ~1,000+ (though 1,000 is unrealistic). Unfortunately it's just not enough information to solve the problem with a meaningful answer. – J.D. Mar 8 at 13:08
  • 1
    @J.D. I have updated the description – Thomson Ignesious Mar 9 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.