Squeeze the hell out of the system you have

Post by yashap » Mon, Aug 14, 2023, 12:16 AM UTC

Turskarama wrote: post #256

yashap wrote: post #249

Earlier quoted context omitted.

Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…

In my experience with SQL, a query like that should return in under a second even if you have 100k or more users. There are some other tricks you can use if you're clever/lucky as well. If you're just using integer IDs (which is reasonable if your system isn't distributed) then you could order by userid on you ContactMethod table and still get the same speed as you would with no join.

Post by yashap » Mon, Aug 14, 2023, 12:25 AM UTC

sgarland wrote: post #355

yashap wrote: post #348

Earlier quoted context omitted.

First off, ty for running all these benchmarks, above and beyond! FWIW, I don’t think joins are bad, I’m 100% for normalized DB schemas with joins. But I’ve done tonnes of performance work over the past ~10 years, and run into a bunch of real world cases where, when caches are cold (which does happen frequently with large datasets and limited budgets), queries similar to the above (join two tables, read a page of dat…

Created a new table that contains `user_id, created_at, phone_primary, phone_secondary`. Inserted all 10,200,000 rows. Notably (I'll come back to this) due to the generation of the rows, the primary key (`user_id`) is an unsorted integer - this was _not_ done with a serial or identity. postgres=# CREATE INDEX sec_phone_created_at ON hn_phone_new (phone_secondary, created_at) WHERE phone_secondary IS NOT NULL; I reset…

Post by throwdbaaway » Mon, Aug 14, 2023, 3:39 AM UTC

yashap wrote: post #359

yashap wrote: post #249

Earlier quoted context omitted.

Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…

Lots of replies to this one! I created a little benchmark that you can easily run yourself, as long as you have Docker installed. It shows how, for cases like the one I described above, the only way to have consistently fast queries (i.e. even with a cold cache) is to denormalize, so you can create the ideal compound index. The normalize/join version takes 15x longer, which can be the difference between 1s and 15s qu…

Post by tlarkworthy » Mon, Aug 14, 2023, 6:42 AM UTC

gen220 wrote: post #357

tlarkworthy wrote: post #275

Earlier quoted context omitted.

Because they are o(n) complexity to refresh so either you settle for eventual complexity or have expensive writes. By forwarding just the index data to the right table you maintain an consistent idiomatic index at 0(1) write cost

Have you considered implementing this with database triggers instead of in your application logic? Requires a bit of brainpower to set up a system around it, but it makes your application logic dramatically simpler. (You don't have to remember to update `foo.bar` every time you write a function that touches `moo.bar`, and if you run migrations in SQL, the updates will also cascade naturally). It's really high up on m…

Post by sgarland » Mon, Aug 14, 2023, 11:33 AM UTC

yashap wrote: post #363

sgarland wrote: post #355

Earlier quoted context omitted.

Created a new table that contains `user_id, created_at, phone_primary, phone_secondary`. Inserted all 10,200,000 rows. Notably (I'll come back to this) due to the generation of the rows, the primary key (`user_id`) is an unsorted integer - this was _not_ done with a serial or identity. postgres=# CREATE INDEX sec_phone_created_at ON hn_phone_new (phone_secondary, created_at) WHERE phone_secondary IS NOT NULL; I reset…

Yeah, I believe which order is best (sortKey/filterKey or filterKey/sortKey) really depends on the specific data/queries, best to try both and pick the best one - looks like sortKey/filterKey in this case :) But I think this does show how, for specific data/queries, sometimes you do have to denormalize, so that you can create the ideal compound index, for specific problem queries. Should still go with normalized sche…