10 ms·

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you ha

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even they often use 64bit integer keys for a lot of entities, see Graph API or Adwords). Will wreak havoc on insert and join performance and index size. 64 bit is more than you'll ever need even for the most serious application. - Default values on NULL columns: Okay advice, but a pretty random issue - Going away from normalization is optimization that's mostly premature and regarding the drawbacks should only be done with utmost care and to resolve specific performance problems, not as a general approach. Overall, pretty mediocre advice.

vesak 10y ago
>UUID primary keys? Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.
- hinkley 10y ago
  Or your customers start merging and acquiring each other. Or you end up hosting your app for customers and want to go multitennant.
JoeAltmaier 10y ago
Its sad that UUIDs are not handled well yet, even in 2016. Its a final id solution that clearly avoids all id collision issues.
- Jweb_Guru 10y ago
  UUIDs don't avoid all id collision issues, and it's far from final. They'll prevent you from using BRIN indexes effectively, for example. Timestamp plus unique server id is an arguably much better approach; on top of everything else, timestamps are actually useful, and they fit in 8 bytes.
  - JoeAltmaier 10y ago
    Denial isn't the same as argument. The definition of a UUID pretty much means they are. Enough bits and the chances of collision are less than that of a superintelligence spontaneously evolving in your morning coffee and hacking your computer. And its past time amateurs stopped thinking they've come up with the perfect UUID (timestamp + server id! Perfect! Until I set my clock back, or repurpose the server) and just use the vetted solutions available.
    - Jweb_Guru 10y ago
      Timestamp + server id can be uniquely guaranteed per server without coordination, so yes, it works fine :P Also, modern computers have monotonic clocks available and can use things like PTP.
      - IanCal 10y ago
        Well you need to ensure that two servers never re-use the same ID so they can't come up with their own ID and there must be some form of co-ordination there. You also must ensure that you never enter two things into the database from the same server within one tick of the clock. Monotonic doesn't mean you'll never get the same number twice, just that it always changes in the same direction (in this case, the timestamp never gets lower, but nothing stops you getting the same number twice).
        Jweb_Guru 10y ago
        I'm aware. Step 1 is generally still considered under the "eventual consistency" umbrella (fork-join specifically). Step 2 doesn't really matter because all you actually need is monotonicity from the clock; locally it's really easy to guarantee uniqueness (and remember, it's fine to delay transactions as long as they commit eventually, which is one reason CAP is kind of imprecisely stated; you're only unavailable if you never respond to a request, when in practice it's really trying to convey latency requirement).
    - mercer 10y ago
olavgg 10y ago
Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html https://sqlite.org/intern-v-extern-blob.html I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.
- ryanbrunner 10y ago
  The thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file. There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.
  - naasking 10y ago
    But if you replicate your DB, now you also have to replicate your filesystem. Easier to just segregate BLOBs in their own tables and let DB replication do all the work.
    - ryanbrunner 10y ago
      It depends on why you're replicating but I would say in most cases you don't need to. If you're forking your DB (i.e. there will now be two copies that will diverge from each other) than sure, you would need to replicate your filesystem. But that's not complicated (copying directories is a solved problem). If you're talking about replication for performance reasons, then DB replication and file replication aren't necessarily going to go hand-in-hand, and the solutions are going to look fairly different (for web apps, "file replication" probably looks like a CDN).
  - olavgg 10y ago
    Actually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)
KurtMueller 10y ago
I think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.
- Someone1234 10y ago
  They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns. In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.
  - craigkerstiens 10y ago
    Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.
    - Someone1234 10y ago
      Fair enough. Still an ugly hack, and you're still over stuffing a column. It does avoid some issues with string passing which is nice, but that's just about all it solves.
      - endymi0n 10y ago
        On top, arrays in PG don't support foreign key constraints and in this way make your model weaker. We do use them too, but in well defined, pretty rare circumstances.
    - KurtMueller 10y ago
      I'm sorry, but I still must disagree with this advice as it pertains to beginners. I would rather a beginner "overnormalize" their database tables rather than have them default to an array column type. It's much easier to transition from "overnormalized" db tables to one that uses array columns when appropriate than it is to go the opposite direction.
hinkley 10y ago
If you can avoid exposing those ID's then 64 bits should do fine with no problems. But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string. You also avoid being the next developer in a long line who thinks they know what 'unique' 'random' mean but can't actually be trusted with that much responsibility (it's okay, most of us can't. I once stopped someone in the 11th hour from shipping a mutually authenticated SSL application that could only generate 256 unique AES session keys, due to a seeding bug. That was scary)
- _RPM 10y ago
  The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"
  - sshykes 10y ago
    100% agree with this method. Math calculations are never really used with IDs in my experience.
  - hinkley 10y ago
    Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests. But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.
    - bmm6o 10y ago
      It also depends on your environment. On a platform like .Net it's not impossible to get your JS serializer to serialize 64-bit ints and floats as strings while keeping 32-bit and smaller numeric. So no matter how you represent it server-side it will survive a round trip. For a language that doesn't make that kind of distinction in the type system (I'm thinking Python, but I could be wrong), you really have to toString it right when you get it from the database. The only other option is to base the serialization decision on the value, which means sometimes your ids are returned as ints and sometimes as strings. At best it's ugly, at worst it leads to bugs.
PaulHoule 10y ago
Years ago I built an image server that served up millions of tiny images (1k or so) and found mysql did a pretty good job for that, avoiding the file system overhead which would overwise be awful.
- rwmj 10y ago
  I think it very much depends on specifics. Years and years ago I stored emails as BLOBs in an Informix database for a large webmail system. That turned out to be a bad idea and we ended up migrating (very painfully) millions of emails to a file server. However I don't think it's universally true this would always be bad. It depends on the speed at which you can insert and fetch blobs from your database, the speed of your NFS server, whether your database or file server can be replicated, and the problems with data integrity you're definitely going to have when not everything is known by the database. No substitute for careful analysis of individual cases.
- exceptione 10y ago
  How did MySQL help here? It uses the filesystem too... I can imagine that you want to hold these images in RAM to bypass the filesystem. Maybe MySQl did that for you, but that sounds like a heavy middleman.
  - pmelendez 10y ago
    If you have a great amount of tiny images then you are relying in how fast is the filesystem to localize the image. Chances are that depending on the index you are using MySQL could do a better job (this depends on the storage engine but usually is one file for the table and another ones for indexes)
  - PeterisP 10y ago
    "bypassing the filesystem" in this case means not having a million of tiny files, which often hurts performance. If you'd have non-DB system for that (e.g. any game with a lot of tiny images), then you would generally want to use spritesheets, archives or some other approach to store many of these images in a single file instead of having each of them be a separate entry in the filesystem.
Practicality 10y ago
Agreed. And the reason here for using UUIDs is not accurate. UUIDs are for having multiple systems (or components) create IDs and having a reasonable expectation for uniqueness, hence the Universal Unique part. It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.
- drumdance 10y ago
  Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.
  - sshykes 10y ago
    Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.
    - davidw 10y ago
      I mostly use integers (but see my comment elsewhere). There is most definitely a german tank problem involved in them, though: https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem For most things I do, it's not a concern, but it is something to keep in mind.
      - mercer 10y ago
        In which cases would this be a German tank problem for you? I can think of apps where you want investors or something, but I can't help but wonder if they'd be savvy enough to check for that. I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invoices are both murky and stringent, so I'm pretty much limited to a <year>0000<invoice no.> format.
dragonwriter 10y ago
> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Google/Facebook scale, depending on the use case.
- kbenson 10y ago
  Depending on your configuration and needs, using a method to generate primary keys in a way unique to each system might still yield better performance. For example, in MySQL you can use auto_increment_increment and auto_increment_offset to force one server use insert using even integers and one odd, or any combination you need. It does eat bits from the key size though. Changing it later can be a pain, but it possible if you think it through. I don't doubt something like this is possible in Postgres as well.
- davidw 10y ago
  Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, where I did have a say in things, used UUID's and Postgres.
  - mercer 10y ago
    Wait, what? MySQL reuses them? Can you elaborate on that?
    - davidw 10y ago
      Here's what happens. Perhaps it has been fixed now: * New record is created, with new ID. * Record is deleted. * Database is shut down (cleanly). * Database is started again. * New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.
SonicSoul 10y ago
I found the images advice unnecessarily black and white. Of course there are cases where storing images or documents is the right move. few reasons i can think of: 1. you can leverage the same auth scheme as the rest of your data 2. audit trail 3. transactional support for image CRUD operations 4. leveraging replication features already offered by your DB 5. blobs can be searchable if the database supports full text search, super useful to search document contents. Edit: shortened some points
tomg 10y ago
LIMIT / OFFSET should be avoided from day one for a realtime app. It's not too difficult to build a future proof solution, and in my experience you run into problems rather quickly if you're building a realtime system (user generated content especially). LIMIT / OFFSET problems: - There are portability issues with some other kinds of datastores and can be a mess to keep caches consistent (removing an item from one page requires invalidating every page set after it) - It makes caching at the request / cdn level more complicated. - Any update to the data while the user is paginating results in duplicate or missing items (especially noticeable with any kind of infinite scroll). You can instead use something like ?object_id=123&page_size=50 to get the next 50 results after item with id=123 (assuming some default order, you could also pass in an order param). To keep your client code clean, you can return a pagination object with the response so you don't need the client to figure out the id of the last object and build the url.

vesak 10y ago

>UUID primary keys? Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.

hinkley 10y ago
Or your customers start merging and acquiring each other. Or you end up hosting your app for customers and want to go multitennant.

JoeAltmaier 10y ago

Its sad that UUIDs are not handled well yet, even in 2016. Its a final id solution that clearly avoids all id collision issues.

Jweb_Guru 10y ago
UUIDs don't avoid all id collision issues, and it's far from final. They'll prevent you from using BRIN indexes effectively, for example. Timestamp plus unique server id is an arguably much better approach; on top of everything else, timestamps are actually useful, and they fit in 8 bytes.
- JoeAltmaier 10y ago
  Denial isn't the same as argument. The definition of a UUID pretty much means they are. Enough bits and the chances of collision are less than that of a superintelligence spontaneously evolving in your morning coffee and hacking your computer. And its past time amateurs stopped thinking they've come up with the perfect UUID (timestamp + server id! Perfect! Until I set my clock back, or repurpose the server) and just use the vetted solutions available.
  - Jweb_Guru 10y ago
    Timestamp + server id can be uniquely guaranteed per server without coordination, so yes, it works fine :P Also, modern computers have monotonic clocks available and can use things like PTP.
    - IanCal 10y ago
      Well you need to ensure that two servers never re-use the same ID so they can't come up with their own ID and there must be some form of co-ordination there. You also must ensure that you never enter two things into the database from the same server within one tick of the clock. Monotonic doesn't mean you'll never get the same number twice, just that it always changes in the same direction (in this case, the timestamp never gets lower, but nothing stops you getting the same number twice).
      - Jweb_Guru 10y ago
        I'm aware. Step 1 is generally still considered under the "eventual consistency" umbrella (fork-join specifically). Step 2 doesn't really matter because all you actually need is monotonicity from the clock; locally it's really easy to guarantee uniqueness (and remember, it's fine to delay transactions as long as they commit eventually, which is one reason CAP is kind of imprecisely stated; you're only unavailable if you never respond to a request, when in practice it's really trying to convey latency requirement).
  - mercer 10y ago

olavgg 10y ago

Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html https://sqlite.org/intern-v-extern-blob.html I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.

ryanbrunner 10y ago
The thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file. There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.
- naasking 10y ago
  But if you replicate your DB, now you also have to replicate your filesystem. Easier to just segregate BLOBs in their own tables and let DB replication do all the work.
  - ryanbrunner 10y ago
    It depends on why you're replicating but I would say in most cases you don't need to. If you're forking your DB (i.e. there will now be two copies that will diverge from each other) than sure, you would need to replicate your filesystem. But that's not complicated (copying directories is a solved problem). If you're talking about replication for performance reasons, then DB replication and file replication aren't necessarily going to go hand-in-hand, and the solutions are going to look fairly different (for web apps, "file replication" probably looks like a CDN).
- olavgg 10y ago
  Actually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)

KurtMueller 10y ago

I think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.

Someone1234 10y ago
They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns. In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.
- craigkerstiens 10y ago
  Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.
  - Someone1234 10y ago
    Fair enough. Still an ugly hack, and you're still over stuffing a column. It does avoid some issues with string passing which is nice, but that's just about all it solves.
    - endymi0n 10y ago
      On top, arrays in PG don't support foreign key constraints and in this way make your model weaker. We do use them too, but in well defined, pretty rare circumstances.
  - KurtMueller 10y ago
    I'm sorry, but I still must disagree with this advice as it pertains to beginners. I would rather a beginner "overnormalize" their database tables rather than have them default to an array column type. It's much easier to transition from "overnormalized" db tables to one that uses array columns when appropriate than it is to go the opposite direction.

hinkley 10y ago

If you can avoid exposing those ID's then 64 bits should do fine with no problems. But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string. You also avoid being the next developer in a long line who thinks they know what 'unique' 'random' mean but can't actually be trusted with that much responsibility (it's okay, most of us can't. I once stopped someone in the 11th hour from shipping a mutually authenticated SSL application that could only generate 256 unique AES session keys, due to a seeding bug. That was scary)

_RPM 10y ago
The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"
- sshykes 10y ago
  100% agree with this method. Math calculations are never really used with IDs in my experience.
- hinkley 10y ago
  Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests. But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.
  - bmm6o 10y ago
    It also depends on your environment. On a platform like .Net it's not impossible to get your JS serializer to serialize 64-bit ints and floats as strings while keeping 32-bit and smaller numeric. So no matter how you represent it server-side it will survive a round trip. For a language that doesn't make that kind of distinction in the type system (I'm thinking Python, but I could be wrong), you really have to toString it right when you get it from the database. The only other option is to base the serialization decision on the value, which means sometimes your ids are returned as ints and sometimes as strings. At best it's ugly, at worst it leads to bugs.

PaulHoule 10y ago

Years ago I built an image server that served up millions of tiny images (1k or so) and found mysql did a pretty good job for that, avoiding the file system overhead which would overwise be awful.

rwmj 10y ago
I think it very much depends on specifics. Years and years ago I stored emails as BLOBs in an Informix database for a large webmail system. That turned out to be a bad idea and we ended up migrating (very painfully) millions of emails to a file server. However I don't think it's universally true this would always be bad. It depends on the speed at which you can insert and fetch blobs from your database, the speed of your NFS server, whether your database or file server can be replicated, and the problems with data integrity you're definitely going to have when not everything is known by the database. No substitute for careful analysis of individual cases.
exceptione 10y ago
How did MySQL help here? It uses the filesystem too... I can imagine that you want to hold these images in RAM to bypass the filesystem. Maybe MySQl did that for you, but that sounds like a heavy middleman.
- pmelendez 10y ago
  If you have a great amount of tiny images then you are relying in how fast is the filesystem to localize the image. Chances are that depending on the index you are using MySQL could do a better job (this depends on the storage engine but usually is one file for the table and another ones for indexes)
- PeterisP 10y ago
  "bypassing the filesystem" in this case means not having a million of tiny files, which often hurts performance. If you'd have non-DB system for that (e.g. any game with a lot of tiny images), then you would generally want to use spritesheets, archives or some other approach to store many of these images in a single file instead of having each of them be a separate entry in the filesystem.

Practicality 10y ago

Agreed. And the reason here for using UUIDs is not accurate. UUIDs are for having multiple systems (or components) create IDs and having a reasonable expectation for uniqueness, hence the Universal Unique part. It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.

drumdance 10y ago
Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.
- sshykes 10y ago
  Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.
  - davidw 10y ago
    I mostly use integers (but see my comment elsewhere). There is most definitely a german tank problem involved in them, though: https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem For most things I do, it's not a concern, but it is something to keep in mind.
    - mercer 10y ago
      In which cases would this be a German tank problem for you? I can think of apps where you want investors or something, but I can't help but wonder if they'd be savvy enough to check for that. I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invoices are both murky and stringent, so I'm pretty much limited to a <year>0000<invoice no.> format.

dragonwriter 10y ago

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Google/Facebook scale, depending on the use case.

kbenson 10y ago
Depending on your configuration and needs, using a method to generate primary keys in a way unique to each system might still yield better performance. For example, in MySQL you can use auto_increment_increment and auto_increment_offset to force one server use insert using even integers and one odd, or any combination you need. It does eat bits from the key size though. Changing it later can be a pain, but it possible if you think it through. I don't doubt something like this is possible in Postgres as well.
davidw 10y ago
Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, where I did have a say in things, used UUID's and Postgres.
- mercer 10y ago
  Wait, what? MySQL reuses them? Can you elaborate on that?
  - davidw 10y ago
    Here's what happens. Perhaps it has been fixed now: * New record is created, with new ID. * Record is deleted. * Database is shut down (cleanly). * Database is started again. * New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.

SonicSoul 10y ago

I found the images advice unnecessarily black and white. Of course there are cases where storing images or documents is the right move. few reasons i can think of: 1. you can leverage the same auth scheme as the rest of your data 2. audit trail 3. transactional support for image CRUD operations 4. leveraging replication features already offered by your DB 5. blobs can be searchable if the database supports full text search, super useful to search document contents. Edit: shortened some points

tomg 10y ago

LIMIT / OFFSET should be avoided from day one for a realtime app. It's not too difficult to build a future proof solution, and in my experience you run into problems rather quickly if you're building a realtime system (user generated content especially). LIMIT / OFFSET problems: - There are portability issues with some other kinds of datastores and can be a mess to keep caches consistent (removing an item from one page requires invalidating every page set after it) - It makes caching at the request / cdn level more complicated. - Any update to the data while the user is paginating results in duplicate or missing items (especially noticeable with any kind of infinite scroll). You can instead use something like ?object_id=123&page_size=50 to get the next 50 results after item with id=123 (assuming some default order, you could also pass in an order param). To keep your client code clean, you can return a pagination object with the response so you don't need the client to figure out the id of the last object and build the url.