Squeeze the hell out of the system you have

Post by aidos » Sat, Aug 12, 2023, 7:57 AM UTC

unnouinceput wrote: post #268

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…

If the tables involved in the join are of 100M+ records what I do when the joins use varchar columns to improve the performance is to use an additional integer column of the varchar one that is a CRC of it (or hash if you prefer that) and use the integer one instead in the join.

Post by donavanm » Sat, Aug 12, 2023, 8:05 AM UTC

feoren wrote: post #115

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

There are "tall" applications and "wide" applications. Almost all advice you ever read about database design and optimization is for "tall" applications. Basically, it means that your application is only doing one single thing, and everything else is in service of that. Most of the big tech companies you can think of are tall. They have only a handful of really critical, driving concepts in their data model. Facebook…

Post by aidos » Sat, Aug 12, 2023, 8:16 AM UTC

franckpachot wrote: post #254

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…

With an index on User (createdAt, id) and one on ContactMethod ( primary,ContactMethod,userId), it should be fast (check that the the execution plan starts with User). Except if lot of recent users have no phones, but that will not be better in a single table (except if columnar storage)

    select
      *
    from
      user
    where
      exists (
        select
          true
        from
          contact_method cm
        where
          cm.contact_id = contact.id
          and cm.method = 'phone'
          and cm.primary
      )
    order by
      created_at desc
    limit 10

    —- partial index is even faster
    create index primary_phone on contact_method (contact_id) where method = 'phone' and primary;

Post by aidos » Sat, Aug 12, 2023, 8:18 AM UTC

tlarkworthy wrote: post #275

ako wrote: post #273

Earlier quoted context omitted.

Why do you prefer manually doing this rather than using materialized views? Materialized views seem easier to create and maintain?

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

Post by atmosx » Sat, Aug 12, 2023, 8:32 AM UTC