<story><title>Squeeze the hell out of the system you have</title><url>https://blog.danslimmon.com/2023/08/11/squeeze-the-hell-out-of-the-system-you-have/#like-2777</url></story><parent_chain><item><author>yashap</author><text>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:&lt;p&gt;SELECT …&lt;p&gt;FROM User&lt;p&gt;JOIN ContactMethod on ContactMethod.userId = User.id&lt;p&gt;WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’&lt;p&gt;ORDER BY User.createdAt DESC&lt;p&gt;LIMIT 10&lt;p&gt;If there are a very large number of users, and a very large number of phone number primary contacts, you cannot make this query fast&amp;#x2F;efficient (on most RDBMSes). You CAN make this query fast&amp;#x2F;efficient by denormalizing, ensuring the user creation date and primary contact method are on the same table, and then creating a compound index.  But if they’re in separate tables, and you have to join, you can’t make it efficient, because you can’t create cross-table compound indeces.&lt;p&gt;This pattern of join, filter by something in table A, sort by something in table B, and query out one page of data, is something that comes up a lot. It’s why ppl thing joins are generally expensive, but it’s more like they’re expensive in specific cases.</text></item><item><author>crazygringo</author><text>That &lt;i&gt;is&lt;/i&gt; a hot take... ;)&lt;p&gt;But joins should never impact performance in a large way if they&amp;#x27;re on the same server and properly indexed. &amp;quot;It&amp;#x27;s truly amazing how much faster everything is when you eliminate joins&amp;quot; is just not true if you&amp;#x27;re using joins correctly. Sadly, many developers simply never bother to learn.&lt;p&gt;On the other hand, having to write a piece of data to 20 different spots instead of 1 &lt;i&gt;is&lt;/i&gt; going to be &lt;i&gt;dramatically&lt;/i&gt; slower performance-wise, not to mention make your queries &lt;i&gt;tremendously&lt;/i&gt; more complex and bug-prone, when you remember to update a value in 18 spots but forget about 2 of them.&lt;p&gt;You mention cheap storage as an advantage for denormalizing, but storage is the &lt;i&gt;least&lt;/i&gt; problem here. It&amp;#x27;s a vastly larger surface area for bugs, and terrible write performance (that can easily chew up read performance).</text></item><item><author>endisneigh</author><text>The bit on the database performance issues leads me to my hottest, flamiest take for new projects:&lt;p&gt;- Design your application&amp;#x27;s hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It&amp;#x27;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.&lt;p&gt;On this note, I have mixed feelings about Amazon&amp;#x27;s DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there&amp;#x27;s something you can take from this even with a RDBMS.&lt;p&gt;In fact, I&amp;#x27;d go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal.&lt;p&gt;- Use something more akin to UUIDs to prevent hot partitions. They&amp;#x27;re not a silver bullet and have their own downsides, but you&amp;#x27;ll already be used to the consistently &amp;quot;OK&amp;quot; performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually.&lt;p&gt;&amp;#x2F;hottakes&lt;p&gt;my sun level take would be also to just index all columns. but that&amp;#x27;ll have to wait for another day.</text></item></parent_chain><comment><author>zepolen</author><text>For 10 million users + telephones, this takes 1ms.&lt;p&gt;&lt;pre&gt;&lt;code&gt;    create table users (
        id serial primary key not null,
        created_at timestamp not null default now()
    );

    create table users_telephones (
        user_id int references users(id) not null,
        is_primary boolean not null default true,
        telephone varchar not null
    );

    insert into users
    select i, NOW() + (random() * (interval &amp;#x27;90 days&amp;#x27;)) + &amp;#x27;30 days&amp;#x27; from generate_series(1, 10000000) i;
    insert into users_telephones select id, true, random() :: text from users limit 10000000; -- all users have a primary telephone
    insert into users_telephones select id, false, random() :: text from users limit 200000; -- some users have a non primary telephone
    create index on users(created_at);
    create index on users_telephones(user_id);
    create index on users_telephones(user_id, is_primary) where is_primary;

    select count(*) from users;
    count   
    ----------
    10000000
    (1 row)

    Time: 160.911 ms


    select count(*) from users_telephones;
    count   
    ----------
    10200000
    (1 row)

    Time: 176.361 ms


    select
        *
    from
        users u
        join users_telephones ut on u.id = ut.user_id
    where
        ut.is_primary
    order by
        created_at
    limit
        10;

    id    |         created_at         | user_id | is_primary |     telephone      
    ---------+----------------------------+---------+------------+--------------------
    9017755 | 2023-09-11 11:45:37.65744  | 9017755 | t          | 0.7182410419408853
    6061687 | 2023-09-11 11:45:39.271054 | 6061687 | t          | 0.3608686654204689
    9823470 | 2023-09-11 11:45:39.284201 | 9823470 | t          | 0.3026398665522869
    2622527 | 2023-09-11 11:45:39.919549 | 2622527 | t          | 0.1929579716250771
    7585920 | 2023-09-11 11:45:40.256742 | 7585920 | t          | 0.3830236472843005
    5077138 | 2023-09-11 11:45:41.076164 | 5077138 | t          | 0.9058939392225689
    1496883 | 2023-09-11 11:45:42.459194 | 1496883 | t          | 0.1519510558344308
    9234364 | 2023-09-11 11:45:42.965896 | 9234364 | t          | 0.8254433522266105
    6988331 | 2023-09-11 11:45:43.130548 | 6988331 | t          | 0.9577098184736457
    7916398 | 2023-09-11 11:45:43.559425 | 7916398 | t          | 0.9681218675498862
    (10 rows)

    Time: 0.973 ms&lt;/code&gt;&lt;/pre&gt;</text></comment>
<story><title>Squeeze the hell out of the system you have</title><url>https://blog.danslimmon.com/2023/08/11/squeeze-the-hell-out-of-the-system-you-have/#like-2777</url></story><parent_chain><item><author>yashap</author><text>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:&lt;p&gt;SELECT …&lt;p&gt;FROM User&lt;p&gt;JOIN ContactMethod on ContactMethod.userId = User.id&lt;p&gt;WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’&lt;p&gt;ORDER BY User.createdAt DESC&lt;p&gt;LIMIT 10&lt;p&gt;If there are a very large number of users, and a very large number of phone number primary contacts, you cannot make this query fast&amp;#x2F;efficient (on most RDBMSes). You CAN make this query fast&amp;#x2F;efficient by denormalizing, ensuring the user creation date and primary contact method are on the same table, and then creating a compound index.  But if they’re in separate tables, and you have to join, you can’t make it efficient, because you can’t create cross-table compound indeces.&lt;p&gt;This pattern of join, filter by something in table A, sort by something in table B, and query out one page of data, is something that comes up a lot. It’s why ppl thing joins are generally expensive, but it’s more like they’re expensive in specific cases.</text></item><item><author>crazygringo</author><text>That &lt;i&gt;is&lt;/i&gt; a hot take... ;)&lt;p&gt;But joins should never impact performance in a large way if they&amp;#x27;re on the same server and properly indexed. &amp;quot;It&amp;#x27;s truly amazing how much faster everything is when you eliminate joins&amp;quot; is just not true if you&amp;#x27;re using joins correctly. Sadly, many developers simply never bother to learn.&lt;p&gt;On the other hand, having to write a piece of data to 20 different spots instead of 1 &lt;i&gt;is&lt;/i&gt; going to be &lt;i&gt;dramatically&lt;/i&gt; slower performance-wise, not to mention make your queries &lt;i&gt;tremendously&lt;/i&gt; more complex and bug-prone, when you remember to update a value in 18 spots but forget about 2 of them.&lt;p&gt;You mention cheap storage as an advantage for denormalizing, but storage is the &lt;i&gt;least&lt;/i&gt; problem here. It&amp;#x27;s a vastly larger surface area for bugs, and terrible write performance (that can easily chew up read performance).</text></item><item><author>endisneigh</author><text>The bit on the database performance issues leads me to my hottest, flamiest take for new projects:&lt;p&gt;- Design your application&amp;#x27;s hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It&amp;#x27;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.&lt;p&gt;On this note, I have mixed feelings about Amazon&amp;#x27;s DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there&amp;#x27;s something you can take from this even with a RDBMS.&lt;p&gt;In fact, I&amp;#x27;d go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal.&lt;p&gt;- Use something more akin to UUIDs to prevent hot partitions. They&amp;#x27;re not a silver bullet and have their own downsides, but you&amp;#x27;ll already be used to the consistently &amp;quot;OK&amp;quot; performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually.&lt;p&gt;&amp;#x2F;hottakes&lt;p&gt;my sun level take would be also to just index all columns. but that&amp;#x27;ll have to wait for another day.</text></item></parent_chain><comment><author>franckpachot</author><text>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)</text></comment>