Is a primary key query in a SQL database similar to a key query in a NoSQL one in terms of speed?

Question

From my understanding, we can compare SQL vs NoSQL to array vs hashmap/dict.

(Let's consider PostgreSQL vs MongoDB just for a context)

SQL is arranged in tables and searches through the rows for what you're looking for.

NoSQL is arranged in a key-value way, so if you know the key, you'll get the value "directly" without needing to search through anything.

With the above considered, when I make a query in SQL using only the primary-key as my WHERE to get one item, does it still do a row search or does it do a "direct" hit on the row?

I hope my doubt has been understood

Answer 1

Primary keys are guaranteed to be unique. Unique keys are implemented using indexes, which in all databases that I know of are b-trees.

A query on a primary key uses the b-tree to access the data. This is log(n) in complexity.

Some databases support other index structures, such as hash tables. That would generally make such a lookup more like O(1) rather than O(log n).

I don't think you are on a fruitful path trying to differentiate NOSQL from SQL databases by looking at such examples. You should look at the requirements they are trying to implement, starting with ACID properties and concepts such as delayed consistency.