5 ms·

Disk space is cheap… That's not the point

46 points by kachnuv_ocasek 15y ago

typicalrunt 15y ago
I completely agree. I've been told many times, even in university by seemingly educated professors, that disk space is not a major factor while programming. But that doesn't mean it can be dismissed either. I have seen some terrible waste of space by programmers that don't know or care about the cost of enterprise disk space (let alone performance of system/app).
yason 15y ago
Disk space is cheap but your access to it goes through quite a narrow bandwidth.
maxxxxx 15y ago
It's the same as "Don't optimize prematurely". True, but also don't be stupid.
kevinpet 15y ago
I feel like this a very forceful response to something, but I don't know what. I'm missing some context to be able to make sense of the article.
- steve-howard 15y ago
  I think she's refuting the idea that GUID primary keys are a one-size-fits-all solution for database design. Jeff Atwood discussed some of the pros and cons of this approach a few years ago [1]. The force does seem a little odd, though, and the use of all-caps words and bold/underlined text throughout the article made it harder to read and understand. The take-away, as far as I can tell, is that database design requires some analysis of what's best for one's particular data set, rather than duplicating what looks hot right now. Which is a good idea in any context if performance really matters. [1] http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html http://www.codinghorror.com/blog/2007/03/primary-keys-ids-ve...
  - famousactress 15y ago
    GUIDs are weird, but global ids are awesome. I'm not really sure what reason I'd have to use GUIDs instead of assigning ids out of a single 64bit int key space.. What am I missing? But seriously. Global-id-space is something I'll probably use on every new project I touch... presumably for the same reasons people use GUIDs. So nice to know that you can change data types, pack things into caches without type-ids, or log identifiers without extra metadata. Super handy.
    - steve-howard 15y ago
      I'm not familiar with the type of global IDs you're referring to, I'm afraid. A GUID is essentially assigning an ID out of a 128-bit integer keyspace, with the idea that collisions will be highly improbable if used correctly. Any database I've ever designed has been on a very small scale (weekend project kind of stuff), and the real scalable databases I've used were designed by people way smarter than me. Some of them used GUIDs and some didn't, and I have no idea what informed those choices.
      - PaulHoule 15y ago
        The nice thing about GUIDs is that any node in a distributed system can make one at any time and you know that a collision is impossible or almost impossible. On the other hand, they take up a lot of space, in particular, they fill RAM. They make the most used indexes in your system large and slow. Practically, the need for GUIDs can be eliminated by establishing a server that works like the "take a ticket" machine at the supermarket. Any node that makes a new object makes an API call and gets a new id.
        famousactress 15y ago
        Yes, exactly... and if the cost of getting new keys piece-meal is too expensive, the 'ticket machine' can dole them out in blocks to nodes for fast local-allocation.
        15y ago
njharman 15y ago
Am I the only one who is less convinced after reading this? 10k inserts in 5min on a laptop. That's enough for 95+% of stuff I work on. Seems like my time is better spent learning technology that has performance leap over esoteric tuning of SQL Servers. Leave that to people like the author. I'm sure there's plenty of them to handle the small % that need this level and no more.
- lukesandberg 15y ago
  The benchmarks in the article are obviously limited. For instance there was no accounting for read performance, or even moderately complex queries. Also given how fast performance was dropping off for the guids this would probably matter for any db that was doing 10k inserts over the course of any amount of time. A drop of of 30% write performance is extremely significant for even moderately sized applications. Also i don't think that the percentage of applications that need this level of performance and no more is as small as you think it is. plenty of successful businesses run critical application on SQL server (and other dbs) and this kind of degradation could be disastrous. I don't think the necessity for SQL tuning is as esoteric at all.
eridius 15y ago
What is a "sequential GUID"?
- dspillett 15y ago
  This is where you start with a UUID that is as random as usual, but subsequent UUIDs for that same field are sequential rather than each being random relative to each other. This makes them a little faster to generate (though the significance of this is likely minute compared to the cost of writing them to disk except in very specific cases) and makes index maintenance operations more likely to produce efficient results (fewer split pages, less need to defragment the indexes later, and so forth). Another possible advantage of "sequential" UUIDs comes when a number of them get transmitted in a textual form, for instance if they are product IDs and you are sending out a long HTML page of links to products. The sequential IDs will offer more redundancy for any transport level compression to chew on than the normal random ones (assuming they are indeed sufficiently random) will. This may also come into play if your database engine supports compressed storage directly, as MSSQL 2008 does for backups (std and enterprise editions) and live data files (ent edition only), though to a much lesser extent unless the compression algorithm chosen is specifically optimal for small variations in to 128-bit aligned data.
ntoshev 15y ago
The clustered index of a table determines the order in which actual records are stored. This means that with a random GUIDs column as a clustered index you insert records in random places of the table, while with the two other options you append records at the end - no surprise this is much faster. Now, the table is not just sequentially written records, they are actually organized as some b-tree variant. Still, appending to a b-tree is a faster operation than inserting in the middle. Rebuilding the index (actually the table) with a FILLFACTOR parameter makes space in each b-tree node so that new records don't cause too much reordering. This is not much of a solution though, because as the table grows the 10% unallocated space will fill up and the performance will be back where we started. To explain the benchmarks shortly: - the performance difference between ints and sequential GUIDs is because GUIDs are larger - the performance difference between sequental and random GUIDs comes from appending records to the table vs inserts at random positions in the table - FILLFACTOR helps temporarily with random inserts by making space available before the inserts happen

I completely agree. I've been told many times, even in university by seemingly educated professors, that disk space is not a major factor while programming. But that doesn't mean it can be dismissed either. I have seen some terrible waste of space by programmers that don't know or care about the cost of enterprise disk space (let alone performance of system/app).

yason 15y ago

Disk space is cheap but your access to it goes through quite a narrow bandwidth.

maxxxxx 15y ago

It's the same as "Don't optimize prematurely". True, but also don't be stupid.

kevinpet 15y ago

I feel like this a very forceful response to something, but I don't know what. I'm missing some context to be able to make sense of the article.

steve-howard 15y ago
I think she's refuting the idea that GUID primary keys are a one-size-fits-all solution for database design. Jeff Atwood discussed some of the pros and cons of this approach a few years ago [1]. The force does seem a little odd, though, and the use of all-caps words and bold/underlined text throughout the article made it harder to read and understand. The take-away, as far as I can tell, is that database design requires some analysis of what's best for one's particular data set, rather than duplicating what looks hot right now. Which is a good idea in any context if performance really matters. [1] http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html http://www.codinghorror.com/blog/2007/03/primary-keys-ids-ve...
- famousactress 15y ago
  GUIDs are weird, but global ids are awesome. I'm not really sure what reason I'd have to use GUIDs instead of assigning ids out of a single 64bit int key space.. What am I missing? But seriously. Global-id-space is something I'll probably use on every new project I touch... presumably for the same reasons people use GUIDs. So nice to know that you can change data types, pack things into caches without type-ids, or log identifiers without extra metadata. Super handy.
  - steve-howard 15y ago
    I'm not familiar with the type of global IDs you're referring to, I'm afraid. A GUID is essentially assigning an ID out of a 128-bit integer keyspace, with the idea that collisions will be highly improbable if used correctly. Any database I've ever designed has been on a very small scale (weekend project kind of stuff), and the real scalable databases I've used were designed by people way smarter than me. Some of them used GUIDs and some didn't, and I have no idea what informed those choices.
    - PaulHoule 15y ago
      The nice thing about GUIDs is that any node in a distributed system can make one at any time and you know that a collision is impossible or almost impossible. On the other hand, they take up a lot of space, in particular, they fill RAM. They make the most used indexes in your system large and slow. Practically, the need for GUIDs can be eliminated by establishing a server that works like the "take a ticket" machine at the supermarket. Any node that makes a new object makes an API call and gets a new id.
      - famousactress 15y ago
        Yes, exactly... and if the cost of getting new keys piece-meal is too expensive, the 'ticket machine' can dole them out in blocks to nodes for fast local-allocation.
        15y ago

njharman 15y ago

Am I the only one who is less convinced after reading this? 10k inserts in 5min on a laptop. That's enough for 95+% of stuff I work on. Seems like my time is better spent learning technology that has performance leap over esoteric tuning of SQL Servers. Leave that to people like the author. I'm sure there's plenty of them to handle the small % that need this level and no more.

lukesandberg 15y ago
The benchmarks in the article are obviously limited. For instance there was no accounting for read performance, or even moderately complex queries. Also given how fast performance was dropping off for the guids this would probably matter for any db that was doing 10k inserts over the course of any amount of time. A drop of of 30% write performance is extremely significant for even moderately sized applications. Also i don't think that the percentage of applications that need this level of performance and no more is as small as you think it is. plenty of successful businesses run critical application on SQL server (and other dbs) and this kind of degradation could be disastrous. I don't think the necessity for SQL tuning is as esoteric at all.

eridius 15y ago

What is a "sequential GUID"?

dspillett 15y ago
This is where you start with a UUID that is as random as usual, but subsequent UUIDs for that same field are sequential rather than each being random relative to each other. This makes them a little faster to generate (though the significance of this is likely minute compared to the cost of writing them to disk except in very specific cases) and makes index maintenance operations more likely to produce efficient results (fewer split pages, less need to defragment the indexes later, and so forth). Another possible advantage of "sequential" UUIDs comes when a number of them get transmitted in a textual form, for instance if they are product IDs and you are sending out a long HTML page of links to products. The sequential IDs will offer more redundancy for any transport level compression to chew on than the normal random ones (assuming they are indeed sufficiently random) will. This may also come into play if your database engine supports compressed storage directly, as MSSQL 2008 does for backups (std and enterprise editions) and live data files (ent edition only), though to a much lesser extent unless the compression algorithm chosen is specifically optimal for small variations in to 128-bit aligned data.

ntoshev 15y ago

The clustered index of a table determines the order in which actual records are stored. This means that with a random GUIDs column as a clustered index you insert records in random places of the table, while with the two other options you append records at the end - no surprise this is much faster. Now, the table is not just sequentially written records, they are actually organized as some b-tree variant. Still, appending to a b-tree is a faster operation than inserting in the middle. Rebuilding the index (actually the table) with a FILLFACTOR parameter makes space in each b-tree node so that new records don't cause too much reordering. This is not much of a solution though, because as the table grows the 10% unallocated space will fill up and the performance will be back where we started. To explain the benchmarks shortly: - the performance difference between ints and sequential GUIDs is because GUIDs are larger - the performance difference between sequental and random GUIDs comes from appending records to the table vs inserts at random positions in the table - FILLFACTOR helps temporarily with random inserts by making space available before the inserts happen