It looks like what they're describing is implementing database joins in the application: 2 network round trips, first one for ids from a table and the second to get values from another table with foreign keys corresponding to the ids.

Isn‘t implementing joins in the application what microservices are all about?

No, microservices are all about senior management ability to map applications to people to yell at.

You're getting confused with NoSQL ;)

I have supported a microservices environment where each service had its own database, and indeed "foreign keys" were mostly application-level concerns. A real pain, and I don't think any of us would have chosen that approach in hindsight.

I hope there are better ways to design microservices.

IDK if postgres has this problem but mssql does. If you do "Select * from table where ID in (blah)"

it ends up with some pretty terrible performance.

It's often a lot faster to do something like

Insert into #tempTable ids

select from table JOIN #tempTable t on t.id = table.id

I can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!

At least in our case, it comes down to expectations.

For the `IN` query the DB doesn't know how many elements are in the list. In MSSQL, it would default to assuming "well, probably a short list" which blows out performance when that's not the case.

When you first insert into the temp table, the DB and reasonably say "Oh, this table has n elements" and switch the query plan accordingly.

In addition, you can throw an index on the temp table which can also improve performance. Assuming the table you are querying against is indexed on ID, when you have another table with IDs that are indexed it doesn't have to assume random access as it pulls out each id. (Effectively, it just has to navigate the tree nodes in order rather than needing to do a full look into the tree).

I read it as "`where` skipped at code time because the table is never going to be too large".

What's wrong is replacing a single query with a join with multiple round trips implementing part of the join logic in application code because you are using an RDBMS as if it were a fairly dumb repository of dusconnected tables.

  SELECT id FROM table1

Is fine, if what your app wants is the ids from table1. Its not good if it is just a prelude to:

  SELECT * FROM table2
  WHERE table1_id in (...ids from previous query...)

instead of just doing:

  SELECT table2.*
  FROM table1 
  INNER JOIN table2 ON (
    table2.table1_id == table1.id
  )

Most of the query optimisers would automatically convert subqueries into join.

No query optimizer will automatically convert to separate queries connected by application logic that the optimizer can’t see shuttling data between them into a single join.

Not if the dev has removed the context of which table the id values come from.

Can you explain bit further. If the subquery do not have context where the value comes from, I would think query will error out?

What you're saying is that:

  SELECT * FROM table2
  WHERE table1_id in (SELECT ids FROM table1 WHERE ...)

will be optimized to a join by the query planner (which may or may not be true, depending on how confident you are in the stability of the implementation details of your RDBMS's query planner). But in most circumstances, there is no subquery, it's more like:

  SELECT * FROM table2
  WHERE table1_id in (452345, 529872, 120395, ...)

Where the list of IDs are fetched from an API call to some microservice, or from a poorly used/designed ORM library.

What’s really bad is where they (or the ORM) generate a seperate select statement for each ID in a loop.

And this doesn’t even get into bizarre edge cases, like how Postgres treats foo = ANY(ARRAY[‘bar’, ‘baz’]) differently than foo IN (VALUES((‘bar’), (‘baz’))). PROTIP: the latter is often MUCH faster. I’ve learned this twice this week.

Lots of application developers don’t really understand how sql works. So instead of learning to do a join (or do a sub query or anything like that) they just issue multiple queries from their application backend.

The posters up thread are talking about novice Python / JavaScript / whatever code which issues queries in sequence: first a query to get the IDs. Then once that query comes back to the application, the IDs are passed back to the database for a single query of a different table.

The query optimizer can’t help here because it doesn’t know what the IDs are used for. It just sees a standalone query for a bunch of IDs. Then later, an unrelated query which happens to use those same IDs to query a different table.

Wow, it is hard to believe anyone would use the first option.