DEV Community

loading...

What are UNIQUE index limitations?

patarapolw profile image Pacharapol Withayasakpunt ・1 min read

MongoDB will throw an error if string indices are being too large.

For SQLite, you can indeed create a unique index on DESCRIPTION, or VARCHAR(VERY_LONG) fields, and probably not throw an error; but are you really willing to accept the consequence?

Even for non-unique indices, probably not all indices helps querying performance that much?

Now that I looked it up, I don't even understand most of the terms.

  • Bitmap index
  • Dense index
  • Sparse index
  • Reverse index
  • Primary index
  • Secondary index

Even for MongoDB and nedb, there are choices of Unique Indices, whether sparse or not.

Now, I am curious on how to design a strongly typed database, with good querying performance, especially if flat and involves minimal joins?

  • About being well-typed, maybe Nitrite is a good one?
  • Well-defined schema can potentially help with typing, but how? Definitely not SQLite, though.
    • I like to work with Node.js, so TypeScript? What about the popular Golang?
    • Does well-defined schema help with performance?

Discussion (0)

pic
Editor guide