If only developers learned more about the DB engines they use and their capabilities.
I've seen so many bone headed decisions (EAV "schemas" being a recurring one) because it was easier to program around the DB than learn how to use it proper. It's not because a few classes insulate you from the bone headed SQL that it's a good idea.
EAV isn't all bad, there are use-cases for it. If you have a highly dynamic data (user configurable) then EAV might be a good way to STORE that data.
Is it ugly, it sure is. Moving read out to a cache layer, and search out a purpose built system, the DB becomes a persistence engine. You have solved the other issue you don't mention with EAV, and thats performance.
Edit (my ability to post should be forbidden till I have had coffee, cleaned up for clarity)
Most EAV implementations I've seen had under 10 distinct "schema-less" virtual tables (sorry about lack of terminology) with perhaps 40-60 distinct "columns" and the different variations of a record types usually had consistent attributes.
Basically the whole thing could've been replaced by 5-10 tables. Maybe taking advantage of Postgres OORDBMS capabilities for the common columns.
Queries tended to group complex CTEs and multiple self-joins. You know it's an anti-pattern when devs start complaining about postgres join performance and you see they got two tables being joined 15 times...
I've made a product which uses Postgres' unique capabilities (It's the only one with transactions covering DDL). Side-effect: Not supporting all DBMS is a no-no when you need funding/convincing anyone. Hence the lowest-common-denominator race for most software. Mine is doing well, thank you ;)
Great post. I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed. While user centricity is a great value, if you worry about screens needed rather than data needed, you cause trouble down the road. I've found that a data-first mental model is much more scalable and supportable. Many times the details of the databases get abstracted away, and performance suffers.
It helps to have a broad view so you can plan and design accordingly. Back end developers should understand how people use the system, even if they're not going to own the designs. Similarly, to be great programmers, front end developers need an understanding of what goes on underneath the hood, even if it's not their day job. (I understand it's all part of the learning curve) The extreme of this is Joel's [0] exhortation that every programmer should learn C. It's a different context, but the same principle.
> I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed
This sentence seems contradictory. If you start with user design, you then know how the data will be accessed which allows you to store it with purpose. Obviously this shouldn't be at the expense of proper database design, but how your app functions defines how you should store your data. While not directly screen-by-screen design, one example I always see is that people default to creating an Address table. Many apps never store more than one address per user. If it's one-to-one, why force a join? (I think this is more of an overlap of both of our views though, and it popped into my head because Address tables are a pet-peeve)
>> how your app functions defines how you should store your data
I think this is the fallacy the parent you are replying to is attacking. Your data should stand apart from your application. Applications change over time. Many databases also serve multiple applications (web, mobile, api, stats, etc.). Your data store should make sense on its own without being tied to an application's design. If you build your database according to your application, then making changes to the application can be difficult or require refactoring the database to match the new application specs. If you instead build your database to make sense standalone, it's up to each application to use it appropriately.
> Many databases also serve multiple applications... Your data store should make sense on its own without being tied to an application's design.
While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. I've never experienced a case where our application changes dramatically enough that manipulating your database is anything serious enough to write home about. If you previously only had a shipping address and now you need a billing address as well, it's a pretty straightforward migration. Incremental change isn't hard.
Companies that become Medco are few and far between. Their database should default modeling one-to-one relationships as one-to-many just so someone might be able to use it more generically in the future. But that's because it's a realistic use-case. My point was that there is a balance to be had between stand-alone and real-life usage, and you shouldn't default to full generic just to make it stand-alone.
While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future.
Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-)
What I have seen frequently enough is myopic data designs hurting flexibility later. For example - assuming 2 level customer relationships (Corporate parent and individual store) with things like regions appearing at tags, rather than flexible hierarchies. The assumptions behind this then gets built into the code base, and fixing it requires more than just a database update. (And even if you fix the database, you are missing the historical hierarchies)
> The assumptions behind this then gets built into the code base
This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a feature that requires a database refactor shouldn't impact the internal logic of another feature (unless it's intentional).
I'm not encouraging willy-nilly design. I'm not saying "stick everything on one row". Just don't design a one-to-one as a one-to-many just because it might theoretically change. However, you should still have the foresight to put yourself in a position where that change is easy. People seem to think that "refactoring" is a dirty word. I'm reasonably confident none of us have worked on an application that has never been refactored. Plan on those potential refactors, not convince yourself that "this is how proper design works". I find THAT is what inevitably leads to the painful refactors.
> And even if you fix the database, you are missing the historical hierarchies
I'm not following this one. Are you referring to an audit trail?
Nice reply, first time I've wished to upvote more than once. The context you provide is something I don't think of enough when I am presenting my view. It could be misinterpreted that I'm advocating designing databases that are designed in some horribly archaic structure that is difficult for applications to work with.
All I mean is that a database should make sense 100% on its own. Your database schema should represent what makes sense for your data, not what your application wants to see. I've never seen a case where designing the database first results in a poorly optimized application codebase. If your database-first design results in terrible access patterns for applications, then your database just wasn't properly designed in the first place. Your data should have a sensible structure on its own without catering specifically to application specs. If your structure really is sensible, no application should have a difficult time manipulating the data within it.
> And even if you fix the database, you are missing the historical hierarchies
I'm not following this one. Are you referring to an audit trail?
No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in)
I hear you on avoiding over-generalizing. That creates problems too.
> data will be missing that wasn't collected properly the first time
Maybe I'm nitpicking but even if you had that field in the database, it's still up to the application to collect it (unless it's something like a timestamp, but that's just a dumb mistake regardless of how you design your database)
Data warehousing is something I really hate to even look at in general. The implementations are typically optimized not for query performance; instead, they are "designed" to allow people who don't even know what a database is to execute queries.
The idea that an entire database should be designed and normalized purely to allow people with no knowledge of basic database mechanics to work with it shocks me. Instead of designing disgusting database structures, send your marketing analytics people to courses where they can learn the basics. I had one job in particular where I spent more than a year dealing with this crap, and it was the most frustrating thing I've ever had to deal with. Never again.
Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have.
I think this isn't a question of ints being a mistake and UUIDs being better, it's about using the most appropriate type based on requirements.
I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them.
I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.
For processing data types without quality natural unique keys, being able to generate a unique key beforehand is very useful. I've written code both ways: inserting to get an auto-incremented integer, and generating a UUID, and the UUID approach was much easier to write and maintain.
I'm going to make a bold claim: no datatype has a quality natural unique key.
I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol...
Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.
Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space.
Really the article needs to bifurcate into
1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%.
2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.
Actually, uuid4 as a pkey will cause pathological insertion performance on a b-tree since you are randomly inserting across the tree (having to constantly split pages) rather than appending to a far extent.
If you want to use uuids, uuid1 may be a better bet.
I had the same thought... And not just about point 3. All of these seems complex points with no absolute answer, as not every database really needs to scale up to several terabytes... Sometimes the reported "advices" might be useful, sometimes they might just unnecessarily complicate things...
a feature of UUID PKs is to allow merging of groups of records. Say there were 1000 records from an old backup that need to get into the database because they were left out/forgotten for some reason. Doing an insert will just work. If both tables started with integer keys, you've got a mess trying to move/renumber the keys of the new records. Drop them you say and let the db make new ones? Sure but now you've just suggested throwing away the primary key! Seems like a poor feature for a PK and impossible to do if existing records reference it.
I think he is talking about a web application. So providing a image tag with the URL, as a src attribute, to the image hosted on s3 seems like a better solution than embedding the image in the HTML response (and blocking the page rendering for a longer time).
IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.
Only as a general rule: I have found that write once read mostly tables (history tables) are great candidates for denormalizing. State tables (read & write) usually benefit from 3rd form normalization.
Right, which I never understood until the first time I was tasked with building a data warehousing and reporting application. My naive 3NF schema approach led to joins that crippled report performance, and really messy schemes to deal with underlying changes in the application's schema over time.
Agreed, I should have made that clear. Anything log-like (events stores, history, "checkins", etc. is excepted from this). Analytics in particular is a case where large collections are the norm, even with low user counts. I contend that it's exceedingly rare for a beginner to be in the position of having these kinds of considerations though.
I think his example was rather poor as I can think of at least 3 use cases off of the top of my head where having separate categories would be a good idea, and having varchar arrays would be a giant PITA.
That said I'm not sure he was trying to make the point that upfront normalisation is bad in itself, just that doing too much "what if maybe someday we might want to separate this" kind of normalisation is a waste of time.
Granted. In my experience wildly changing requirements are more common than unexpected need to scale though. "Design for the most likely needs" is my general and unspecific advice. YMMV.
But what if you need to partition and possibly scale out part of your database? At that point a normalized data model becomes a huge road block. A balanced approach to normalization often allows you to more adaptive to requirements. If possible, it should be avoided for tables that can grow large.
If you're in this position, you have scale informing product requirements already and can denormalize up front. But this is uncommon for read/write transactional workloads and an especially uncommon scenario for a beginner to find themself in.
What they really MEAN is don't store images in your general purpose database, in particular as long strings.
There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc).
Additionally their "solution" is bizarre, they've jumped from using a database to a paid service by a third party, which is itself backed by a database. That seems very "apples & oranges" to me, I mean it would obviously work, but is a big jump from in-house development using a general purpose database.
To give one specific example have they not heard about Oracle Multimedia? That's exactly what it is designed to offer.
> There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc).
It also keeps them coherent, if you store images on a filesystem but metadata in a database, since they don't share transactional contexts you will eventually end up in an inconsistent state ("dead" files without metadata, or live metadata missing the corresponding image data).
I've stored images both ways and both have their issues. To declare one is superior over others in all circumstances as the OP did is short sighted. The one thing I would recommend is once you pick one, stick to it for consistency's sake.
And I think when you do encounter a performance issue with LIMIT ... OFFSET, you have to ask yourself "Is a standard CRUD interface pattern really the best way to work with such a large data set?"
Problem is actually any ORDER BY operation on a big table, but often it can be fixed with a bit of index magic... on small enough DB (and that's like 99% of web projects) you don't really need to care about this, ever...
For majority of projects limit offset is fine. Some of the linked advice on alternatives is way more horrible like Cursors WTF? this will not scale well.
These specifically apply to Postgres, and don't necessarily generalise to all databases.
One example: counter-example to #4. Oracle database (since 11g) has a "fast add column" feature - which allows adding a non-NULL column with a default value to an arbitrarily-large existing table, without a "rewrite" of the table. (Behind the scenes, the default value is stored as metadata - and the default value is then read from the metadata for preexisting rows, rather than updating each and every row in the table.)
I recommend reading the article about pagination linked in the article. I came away from that thinking the LIMIT ... OFFSET approach is the best one to use in most circumstances. It's certainly a good starting point - you can monitor how well it suits your application over time.
Consistency of results may be an issue, but any scheme you use is either going to show inconsistency when you move between pages, or it's going to lie to you. That's the reality of concurrent modification.
- 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.
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.
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.
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.
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).
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).
Could you elaborate on that? I use UUID's at the moment, trusting that they'll be unique, but in part because I don't properly understand them, I feel a little anxiety about the chance of a collision. I sort of assumed they already used timestamps as a basis already, but apparently they do not. Is it just a matter of it being highly improbably of generating the same UUID twice? I'm assuming yes on gut feeling, but I highly distrust that body part these days.
(Edit: I realize I could go look this up myself, but 1) I like explaining things so perhaps you do too, and 2) I'm hoping perhaps at the very least you can point me to a good resource to properly understand this topic)
If you write a sufficiently long series of random digits, it becomes increasingly likely that your number is the only use of that number anywhere in the universe. That's the crux of it.
I'm on a train now, but I think there's also a timestamp element to it somewhere, making it less likely that a restart of the random number generator will cause you to have a collision.
In my experience this is only true if the UUID is generated by the database system in a controlled manner. It is not uncommon to have a unique identifier be used as a key in a database that comes from an external source.
Consider an environment where you have a database that stores data for a monitoring system. So you decide to use the machine ID (a UUID) as a key. In your design you fail to take into account sys admins that do not clone a VM the correct way, so you wind up with two different machines with the same supposedly unique identifier.
Sure, if you have a unique constraint on the key, you will find out about it pretty quickly, but it is just one example.
Version 1 UUIDs are server ID (MAC address) plus timestamp. Your overall proposal is already included in "UUIDs." They don't fit into 8 bytes, though, so if you want to squeeze them down you'd have to come up with your own scheme.
If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....
Granted re: UUIDs. There are definitely tradeoffs here (time precision / inserts per second [in practice you'll just end up throttling or massaging the timestamp if you do have insufficient granularity] vs. number of supported partitions vs. ability to efficiently route a path to the key vs. fast reservation ability vs. key size, etc., etc.). I was referring to the notion of a global 128-bit pseudorandom number as the final solution to everything... things are rarely that straightforward.
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.
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.
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.
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.
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)
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.
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.
If you get to the point of sniffing, then a slightly better option might be to suffix all your foreign keys with something like 'key' or 'ID'. Or use slugs for everything.
Or you could initialize all your tables with the first primary key being 2^32 and only lose one quarter billionth of your legal keyspace.
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.
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.
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.
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)
"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.
Typically, filesystems are designed and tuned around the assumption that most of your data is stored in relatively large files. For instance, a typical ext4 filesystem uses 256 bytes per inode and rounds each data block up to a multiple of 4096 bytes.
So if you want to store lots and lots of tiny objects, it's very inefficient to store them each in a separate file. At that point, you could design your own compound data format, making sure it efficiently supports all the different operations you might want to do... or you could just use a database which has already solved the problem for you.
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.
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.
Yes. Any time you let a non-associated record be displayed by a user session that is not linked to that record and should not have access to it, that's not a problem of easily guessable keys, that's a problem of not securing data by checking access rights.
password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type.
This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Well lets check
/shippinglabel/345
/shippinglabel/346
/shippinglabel/347 oh look delivered today, sitting on back porch step, and the address is right there
Another fun one is online financial documents with sequential accounts.
Yes, of course that's what needs to happen. And that's what I do when I'm the one doing the implementation.
But a junior dev just out of code school doesn't necessarily think of this. So when I ask one to build the basic scaffold and db schema I say "make sure you use UUID," then later I show them how security holes like this can manifest.
I've seen this security hole so many times in other sites that I feel like it's a good first principle to limit "guess-ability" in the schema wherever possible.
I have faced this issue in my web application. My solution is to use a UUID wherever the ID will be exposed to the user (only one place in my application) and use an integer ID everywhere else. Although this does mean 2 IDs need to be generated and stored. The other solution is to never allow access to users without a log in.
You can avoid the need for two IDs if you HMAC the URL. There's a single private key on the server used to generate and verify the MAC on subsequent requests.
> IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security.
IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of how relying on obscurity for security can be a pernicious mistake, especially for a novice developer.
> 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?"
You're misidentifying the actual security problem. Using URLs in this manner requires cryptographically secure random numbers, or my preferred method is to HMAC the URL to sign it's protected parameters. I actually wrote a small library for .NET called Clavis to demonstrate this idea [1]. The MAC acts as the cryptographically secure identifier needed to make the URL unguessable.
You can also use ZooKeeper to increment the id for distributed systems. If we're talking about a lot of inserts per second, you can increment by a thousand.
For example system-A gets id range for 1-1000, system-B gets id range for 1001-2000 and so on.
> 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.
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.
You can also do the same in certain versions SQL Server. Change the ID field from auto-increment enabled to disabled. Add and delete a few fields. Then re-enable auto increment. Now, normally the server should start new IDs at the last position. Normally being the operative word.
Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is that the database is much more comprehensible, e.g. foreign keys are legible without having to join back to the primary table. The relational model for data is pretty cool and breaks down when e.g. UNION doesn't work if 2 rows differ only in an arbitrary integer primary key.
The problem is that a lot of people end up screwing that up and picking something that doesn't work well as a primary key. Also, just using an integer is often a great way to get started without thinking about things so much. That works for a lot of web apps.
The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.
Unique and never changes can be two things concatenated, not one thing.
If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application.
Given the above, some important things concatenated and hashed works. Can always add an application ID or importer ID or process ID or a timestamp of high enough resolution.
Obviously needs are different if you're trying to create a bank user database vs deduplicating sampled engineering data.
So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.
"Hash of the data defining the entity identified" is how version 3 and 5 UUIDs work (plus a namespace). If you really want a weak hash use version 3 - it uses MD5.
Concatenating helps you with uniqueness, but not with whether things change (if anything that's part of a composite key changes, it can no longer reliably be part of a key). And it's the changing part that's not realistic with natural keys.
Once you get into application ids / timestamps / whatever you're no longer really using a natural key in my mind, you're just making your own algorithm for a surrogate key.
> So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.
That sounds very similar to UUID version 1 except that you replaced MAC address with some other identifier and added a hashing step.
> So something that's unique is NOW() (assuming low enough sample rate LOL)
We can all invent algorithms for nice unique values when we include the caveat "apart from the edge cases". It's those edge cases that bugger everything up.
> Unique and never changes can be two things concatenated,
No, for a good primary key, the "unique" parts can be due to concatenation, but the whole key (and thus all the elements) needs to be unchanging. [0]
[0] Modern databases can actually deal with changeable PKs, though with a potentially serious performance hit, but in many use cases where data travels outside the database for some kind of interaction where the results need to get reentered into the database, this is still a problem, since you can't (in any general way) cascade updates to things (which may not even be online systems, e.g., paper records feeding human processes) outside of the DB.
The problem is usually with the assumptions about natural fields being unique, or not change over time. One day you realize they can actually change, or they are not unique. Changing the data model at that time to support a different primary key would be very difficult.
Okay so you run a school and want to make a students database. What do you use a a PK?
1) Social security #? Fails when you have international student.
2) Last Name, First Name, Middle Name? Fails when you have a repeat name.
3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys duplicated in the classes table to do the join.
Simple Integer primary keys "suck" in that you are adding bogus data to your database that has no value.. but man, they sure solve a LOT of problems. You need to think fairly hard before you get rid of them. In some cases you totally can. But making your default datamodel include an Integer PK solves a LOT of problems.
I have actually done this for a classroom management app I made mostly for fun when I taught high school. At first I wanted to use the district issued ID number as the primary key for each student. Except some students came and started classes and needed attendance records before the district got around to issuing them their official numbers. So I fell back to auto-increment int as the primary key, with their "district_id" nullable, default null, but unique constrained as just another field equivalent to their first_name.
> Am I the only one who thinks that primary keys should be derived from the actual data?
Where a valid natural key exists, it absolutely should be used. Surrogate keys should only be used where there isn't a natural attribute (or composite of such attributes) that corresponds to the unique identity of a tracked entity.
Including timestamp may be appropriate for log data, where otherwise identical records differing by time should be considered different entities. Otherwise it loses the property asked for upthread that our primary key detects/eliminates duplication.
Version 3 and 5 UUIDs are derived from the data. They can be a good fit some places, but you have to be sure the only (pre-hash) collisions you'll have are the collisions you want.
With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers.
UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.
Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.
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.
A special case where UUID keys are extremely helpful is when you have to convert your data from one system to another - customer "105" can be different in different databases, but customer "7C823BE3-CB5B-4E34-BE8D-4B5A71945D3F" is truly unique. This takes dealing with foreign keys from "nightmare" to "non-issue".
Indeed. I recently built a todo app inspired by a particular paper-based methodology (FVP) to scratch a personal itch, and it was a web app that uses localStorage. It was much easier to use UUID keys for the todos than to store/retrieve the max ID every time the app was opened.
That's basically the 'hello world' of web apps example where UUID's make sense already.
If you have a lot of data to synchronize, you probably still want some kind of 'version vector' thing, which in the simple case with a centralized server, is an integer 'change counter' (I forget the exact term for it) that will let you fetch only the updated/new items. You'd still use a UUID for your ID though.
wouldn't filtering by date field be good enough, generally?
Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.
UUIDs absolutely have their place - both the problems of distributed ID generation (mostly in remote clients / apps) and avoiding information leakage (-> German Tank Problem) are very real world examples where using them is a sensible thing.
However, my point was that using them as a default choice has a lot of drawbacks. All over our data model, we've got like 60-70 entities with serial IDs and about 2 with UUID columns.
Also, don't underestimate how fast PostgreSQL can deal you out from a sequence via nextval(), should you really go down that route. But yes, if the database is more than a millisecond away, UUIDs definitely have their time and place.
> UUID primary keys aren't needed for big keyspaces
Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion.
The main problem I have seen with UUIDs from a performance standpoint has to do with indexing and ordering.
To start with ordering, it is inherently a NP-complete problem. UUID can add a good bit of over head here since it's less computationally expensive to order an integer than it is to order something like a unique identifier.
This is also why UUIDs tend to increase fragmentation with indexes, since indexing can be so closely tied to ordering.
SQL Server has made some improvements here with the newsequentialid() function, so maybe it's less of a problem moving forward.
To contradict myself for a second, perhaps the move to more distributed systems and service models over the long term increases the need for the move from INTs to UUIDs. Better to architect for the future now and save yourself some pain down the road. It will be interesting to see how that plays out.
To be fair, most of my experience with UUID/GUID is with SQL Server, and in that case it's stored as a 16-byte binary.
Character string format: '6F9619FF-8B86-D011-B42D-00C04FC964FF'
Binary format: 0xff19966f868b11d0b42d00c04fc964ff
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
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.
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.
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.
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).
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 :-)
For sure - but you should ask yourself if you need to routinely deal with that blob, or just process it on the way in? If your upload functionality generates thumbnails and resizes images properly, you can isolate file handling to that section of the code and the rest of your app can just deal with nice, simple URLs.
So if you do this on a large site, what % of your DB load is going to be serving images?
If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge.
IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.
What if 100 people hit the page at the same time for an uncached image?
I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...
Varnish will actually hold those 100 connections open while it dispatches 1 single request to the backend. When that request returns (assuming the cache control headers allow it), it will serve that one response to all 100 requestors. It's a very powerfull tool for anything that's even slightly cachable.
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.
"Use UUIDs instead of integer PKs" is 95% of the time a HORRIBLE idea. As a rule of thumb, if you are a beginner, you should NEVER use a UUID instead of an integer PK.
If you know the definition and the ins-and-outs of 'clustered index', 'index fragmentation' and 'page split' then feel free to use a UUID if you see fit-- otherwise, please don't.
If you are a beginner and for some reason have to have a UUID: Use a sequential, auto-incrementing integer for your clustered index column and make another second column that is your primary key that is your UUID.
This article feels like the blind leading the blind.
Postgres doesn't have clustered indices. Index fragmentation and page split are not problems unique to UUIDs, especially in databases with clustered indexes (unless you never issue UPDATEs).
Also feel free to use the "bottom" of the keyspace instead of zero to start your keys if your database uses signed integers. (MSSQL I am staring at you)
> "Use UUIDs instead of integer PKs" is 95% of the time a HORRIBLE idea. As a rule of thumb, if you are a beginner, you should NEVER use a UUID instead of an integer PK.
Can you enlighten us as to why? Seems to me if you're not at the scale where you see the benefits of UUIDs you're also not at the scale to see the drawbacks either (bigger, slower indices?).
I'm suprised by this point: "the database tends to be an after-thought in application design".
Maybe it's a generational thing (I'm 40+), but I tend to always start with thinking about the data structures and design in conjunction with the UI design.
Getting a solid database design in place early in the development cycle is critical to building a solid, stable system- you can of course alter the database as you start building but having a good idea of the data structure should be a "before thought" rather than a after-thought.
I'm 30- and I always start with the data first as well. Linus said good programmers always think data first, and that resonated a lot with me when I first started coding. As I got further along, I realized how true it was that application logic often follows the data model.
Treating data as an afterthought is a great way to have to be re-defining your application logic every time you re-define your data because you forgot something.
I agree, and I prioritize data structures myself as well, but only because I've been up here in the hizzle for the past three or so years (in a development career of six years) and I kept being reminded of it via the linus quote, and the whole functional programming kazoo.
My experience is that lots of people, including people with many more years of development on me, don't take this approach though.
It was of all things a database book that advised me to begin with the user interface. It's counterintuitive because, isn't the database the foundation of the interface?
Don't get me wrong. I don't roll an application into production until I've gone back and forth over both the interface and the database, until both satisfy me, the database is well-normalized, and so on. But when the first line of anything has yet to be written, I think I save a few iterations by first sketching out the fields and behavior of the user interface.
I agree with database first. It's the essence of your application, meaning hypothetically, if you were to strip away all your UI and business logic, and all your users were experts at SQL and never made a mistake, the database is all you would have left.
Furthermore, a database mistake, once built on, will stay with your organization for life, only removable by a lot of pain. A coding mistake is fairly trivial to recover from.
I find it troubling that many self taught programmers don't take the time to learn databases. They aren't even that difficult to master. 3NF, Constraints, Indexes. That covers 90% of it. Everyone thinks they're making Google.
Please don't use UUID primary keys, at least by default. Not only will your PK indexes take up 4x the space, all of the foreign keys referring to it will also need indexes which also blow up in size, etc. I've seen very poor performance due to this issue. A surrogate PK is still a surrogate PK, so you're committing a relational algebra sin in any case (one which IMO is necessary for practicality concerns).
Point 1 is silly - this depends greatly on what your application needs to do. If you need to store the image together with the metadata or other information related to the image that is transactional data - then storing the image in the database is the way to go. This also helps a lot with database backups, migrations etc..
Not every PostgreSQL-backed application is a public-facing web app. A fat-client that connects directly to the PostgreSQL database to do "stuff" with transactional data will have a much easier time if any related images are stored alongside the transactional data.
Not sure about point 1 as a blanket statement... many times external services are not allowed and therefore aren't accessible. How is S3 storing them? Probably in a DB. In some DB and application frameworks, IO issues with image data can and have been streamlined. If not, better model design might help. Putting them in S3 limits what you can do with the images. For basic application images, yeah that doesn't belong in your DB...
As far as disk space... that could be a problem, but for most of the applications that developers work on, they never need to scale to the point where disk space is a problem. And if it does, you can always get more disk space.
This advice really only applies to public web sites.
PostgreSQL could be used as the backend for, as an example, an internal inventory tracking application. Something like this could allow users to upload a generic photo of the item in inventory. Putting these in S3 or a CDN makes no sense for an internal application.
And, of course, "internal application" doesn't necessarily mean a web application. It could be a fat-client (WinForms, Qt, Cocoa, etc) that connects directly to the database. It could connect to an application server (which then connects to the database) and speak some custom protocol. It could be a text-based terminal app. It could be that there are a variety of these applications that all connect to the same database.
Better advice would be:
Store images outside of the database. If you are building a public-facing web site or web application, something like S3 or a CDN may be a good fit. If you are building an internal web application, or are otherwise unable to use S3 or a CDN, storing images on a web server and storing the URL (or something that allows you to determine the URL) is a better option. In some cases, such as fat-clients that connect directly to the PostgreSQL database, you may find that storing images in the database is truly the best option. Keep in mind that this may impact performance in the following ways: (insert list of potential issues).
I agree with your "better advice"... Even if it's a public facing app, having them stored in the DB makes it much easier to restore, replicate, etc... Not to mention allowing fine grained authorization rules. Any time I have worked on a system (web app, fat-client, etc) where images, which are really data, are separate from the image metadata, overtime, a colossal mess occurs. Orphaned files without metadata, metadata without files.
Even in your public web app, you also might have other back-end systems that cannot access S3, but can access your application services or DB (yikes). There are definitely many cases where external providers like S3 is the better approach, but not all. Also, you don't have a performance problem until you have a performance problem.
Since there is some UUID hate here, some missing pros to using them:
- No round trip for generating keys, data can be sent in with an existing or new uuid without having to hit the autonumber/keymaster, removes a single point of failure for a small fee on each row. Storage is cheap so 16-byte uuid is not a deal-breaker, the benefit is speed and horizontal scalability. Optimized read-only tables and/or caching can be made where this has any impact at all.
- Some databases like Oracle you need a sequence to even do autoumbering, huge pain
- Autonumbering is a pain when having to replicate across environments or when you start getting multiple databases and clusters
- Numeric ids for important data is not exposed, prevents easily incrementing for next/previous (other ways to do this but this is one)
- Many databases have a UUID field or field optimized for unique ids/guids/uuids now
- If you were a piece of data wouldn't you want to be unique? All your data are unique snowflakes with UUIDs. On a serious note, this can help to identify data across all types and not just in the same table.
One way to generate UUIDs in a more index-friendly way, at least in Postgres, is to use uuid-ossp's uuid_generate_v1mc() function. They end up sequential, so maybe not the best if you need total unpredictability, but the lower order bits should be reasonably random.
As a beginner using MySQL I was expecting not using
innodb_file_per_table
or generally learning db configuration to be on the list. It's a huge pain when your little web server runs out of disk space because innodb is eating it all.
This is much more common in spreadsheets, where free-wheeling data entry is encouraged, but a novice problem I see in real-world data structuring scenarios is breaking a column's enumeration...e.g. for a column that is supposed to be zip codes, doing something like "90210 and 90240", instead of creating a second column to handle edge case notes.
Related to that: inconsistent values for NULL/False, e.g. "No", "", "False", "false", etc...(or rather, not using a boolean type to enforce this).
And related to that: general unawareness of what NULL means. It's not the same as "" or "False" or 0, both in a technical sense and in a real-world sense.
Nice starter list. Here's more, off the top of my head:
- failure to lock rows with pessimistic locking
- failure to consider optimistic locking when viable
- data sets within a table (undernormalization)
- too much duplicate app code that should be stored procedure
- overuse of stored procedures that should be in app code
- appending with insert
- appending to datasets with variable length
- "smart" keys that can never be changed without rewriting
- underuse of indexing to slow read performance
- overuse of indexing to slow update performance
- records too big for good hashing (undernormalization)
- columns with different typed data (when DBMS allows)
- columns with different logical data (app driven)
- enhancing by inserting columns instead of appending
- poor or missing audits
- poor or missing security
- poor or missing archiving
And once you've got over these initial issues you can move on to the Mistakes People Who've Figured Out a Thing or Two About Databases Make:
- storing their images externally, but forgetting to apply a consistent backup strategy to their image data
- Database is getting big, query returns lots of records, so just adding pagination. Forgetting that the user really doesn't actually want to page through results at all. The correct answer was probably to add proper faceted filters and full text search.
- using UUID keys everywhere, then making the mistake of mixing up UUID keys from one table with UUIDs from another one.
- Using nullable columns as a tool for schema change, but not deciding what it actually means for a particular column in a row to contain a NULL.
- Thinking that they will get away with just storing a chunk of structured data like an array in a particular column value because from the application point of view it's really just one blob of data anyway. In general I give a structured datatype like that two days before someone is writing a query that digs into the inner structure of it.
208 comments
[ 2.7 ms ] story [ 201 ms ] threadI've seen so many bone headed decisions (EAV "schemas" being a recurring one) because it was easier to program around the DB than learn how to use it proper. It's not because a few classes insulate you from the bone headed SQL that it's a good idea.
Is it ugly, it sure is. Moving read out to a cache layer, and search out a purpose built system, the DB becomes a persistence engine. You have solved the other issue you don't mention with EAV, and thats performance.
Edit (my ability to post should be forbidden till I have had coffee, cleaned up for clarity)
Basically the whole thing could've been replaced by 5-10 tables. Maybe taking advantage of Postgres OORDBMS capabilities for the common columns.
Queries tended to group complex CTEs and multiple self-joins. You know it's an anti-pattern when devs start complaining about postgres join performance and you see they got two tables being joined 15 times...
[0] http://www.joelonsoftware.com/articles/CollegeAdvice.html
This sentence seems contradictory. If you start with user design, you then know how the data will be accessed which allows you to store it with purpose. Obviously this shouldn't be at the expense of proper database design, but how your app functions defines how you should store your data. While not directly screen-by-screen design, one example I always see is that people default to creating an Address table. Many apps never store more than one address per user. If it's one-to-one, why force a join? (I think this is more of an overlap of both of our views though, and it popped into my head because Address tables are a pet-peeve)
I think this is the fallacy the parent you are replying to is attacking. Your data should stand apart from your application. Applications change over time. Many databases also serve multiple applications (web, mobile, api, stats, etc.). Your data store should make sense on its own without being tied to an application's design. If you build your database according to your application, then making changes to the application can be difficult or require refactoring the database to match the new application specs. If you instead build your database to make sense standalone, it's up to each application to use it appropriately.
While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. I've never experienced a case where our application changes dramatically enough that manipulating your database is anything serious enough to write home about. If you previously only had a shipping address and now you need a billing address as well, it's a pretty straightforward migration. Incremental change isn't hard.
Companies that become Medco are few and far between. Their database should default modeling one-to-one relationships as one-to-many just so someone might be able to use it more generically in the future. But that's because it's a realistic use-case. My point was that there is a balance to be had between stand-alone and real-life usage, and you shouldn't default to full generic just to make it stand-alone.
Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-)
What I have seen frequently enough is myopic data designs hurting flexibility later. For example - assuming 2 level customer relationships (Corporate parent and individual store) with things like regions appearing at tags, rather than flexible hierarchies. The assumptions behind this then gets built into the code base, and fixing it requires more than just a database update. (And even if you fix the database, you are missing the historical hierarchies)
This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a feature that requires a database refactor shouldn't impact the internal logic of another feature (unless it's intentional).
I'm not encouraging willy-nilly design. I'm not saying "stick everything on one row". Just don't design a one-to-one as a one-to-many just because it might theoretically change. However, you should still have the foresight to put yourself in a position where that change is easy. People seem to think that "refactoring" is a dirty word. I'm reasonably confident none of us have worked on an application that has never been refactored. Plan on those potential refactors, not convince yourself that "this is how proper design works". I find THAT is what inevitably leads to the painful refactors.
> And even if you fix the database, you are missing the historical hierarchies
I'm not following this one. Are you referring to an audit trail?
All I mean is that a database should make sense 100% on its own. Your database schema should represent what makes sense for your data, not what your application wants to see. I've never seen a case where designing the database first results in a poorly optimized application codebase. If your database-first design results in terrible access patterns for applications, then your database just wasn't properly designed in the first place. Your data should have a sensible structure on its own without catering specifically to application specs. If your structure really is sensible, no application should have a difficult time manipulating the data within it.
I'm not following this one. Are you referring to an audit trail?
No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in)
I hear you on avoiding over-generalizing. That creates problems too.
Maybe I'm nitpicking but even if you had that field in the database, it's still up to the application to collect it (unless it's something like a timestamp, but that's just a dumb mistake regardless of how you design your database)
The idea that an entire database should be designed and normalized purely to allow people with no knowledge of basic database mechanics to work with it shocks me. Instead of designing disgusting database structures, send your marketing analytics people to courses where they can learn the basics. I had one job in particular where I spent more than a year dealing with this crap, and it was the most frustrating thing I've ever had to deal with. Never again.
I think this isn't a question of ints being a mistake and UUIDs being better, it's about using the most appropriate type based on requirements.
I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them.
I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.
I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol...
Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.
Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space.
Really the article needs to bifurcate into
1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%.
2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.
That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.
If you want to use uuids, uuid1 may be a better bet.
[0] Having a string for a primary key will typically make there be a hidden integer primary key; better to have it explicit than implicit.
That said I'm not sure he was trying to make the point that upfront normalisation is bad in itself, just that doing too much "what if maybe someday we might want to separate this" kind of normalisation is a waste of time.
What they really MEAN is don't store images in your general purpose database, in particular as long strings.
There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc).
Additionally their "solution" is bizarre, they've jumped from using a database to a paid service by a third party, which is itself backed by a database. That seems very "apples & oranges" to me, I mean it would obviously work, but is a big jump from in-house development using a general purpose database.
To give one specific example have they not heard about Oracle Multimedia? That's exactly what it is designed to offer.
It also keeps them coherent, if you store images on a filesystem but metadata in a database, since they don't share transactional contexts you will eventually end up in an inconsistent state ("dead" files without metadata, or live metadata missing the corresponding image data).
>>The unfortunate part is: pagination is quite complex, and there isn’t a one-size-fits-all solution.
Probably not. But the example he gave is a one size fits most. I've used it on a dozen different projects and have never had performance issue.
One example: counter-example to #4. Oracle database (since 11g) has a "fast add column" feature - which allows adding a non-NULL column with a default value to an arbitrarily-large existing table, without a "rewrite" of the table. (Behind the scenes, the default value is stored as metadata - and the default value is then read from the metadata for preexisting rows, rather than updating each and every row in the table.)
Consistency of results may be an issue, but any scheme you use is either going to show inconsistency when you move between pages, or it's going to lie to you. That's the reality of concurrent modification.
- 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.
Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.
Or you end up hosting your app for customers and want to go multitennant.
(Edit: I realize I could go look this up myself, but 1) I like explaining things so perhaps you do too, and 2) I'm hoping perhaps at the very least you can point me to a good resource to properly understand this topic)
I'm on a train now, but I think there's also a timestamp element to it somewhere, making it less likely that a restart of the random number generator will cause you to have a collision.
If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....
In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.
It does avoid some issues with string passing which is nice, but that's just about all it solves.
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)
But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.
Or you could initialize all your tables with the first primary key being 2^32 and only lose one quarter billionth of your legal keyspace.
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.
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.
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.
So if you want to store lots and lots of tiny objects, it's very inefficient to store them each in a separate file. At that point, you could design your own compound data format, making sure it efficiently supports all the different operations you might want to do... or you could just use a database which has already solved the problem for you.
It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.
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.
In this case the security hole of /user/123 just needs to be properly locked down. That is all.
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.
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.
> in my country the rules for invoices are both murky and stringent,
I sympathize, as the rules were like that in Italy, where I lived for a long time.
[0] http://hashids.org/
password reset email #124 gets url /user/124
password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type.
This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Well lets check
/shippinglabel/345
/shippinglabel/346
/shippinglabel/347 oh look delivered today, sitting on back porch step, and the address is right there
Another fun one is online financial documents with sequential accounts.
That's the nature of password reset link.
But a junior dev just out of code school doesn't necessarily think of this. So when I ask one to build the basic scaffold and db schema I say "make sure you use UUID," then later I show them how security holes like this can manifest.
I've seen this security hole so many times in other sites that I feel like it's a good first principle to limit "guess-ability" in the schema wherever possible.
IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of how relying on obscurity for security can be a pernicious mistake, especially for a novice developer.
But sometimes you're working with a legacy API and/or a bad auth mechanism.
Not every project is greenfield or is maintained by senior devs.
You're misidentifying the actual security problem. Using URLs in this manner requires cryptographically secure random numbers, or my preferred method is to HMAC the URL to sign it's protected parameters. I actually wrote a small library for .NET called Clavis to demonstrate this idea [1]. The MAC acts as the cryptographically secure identifier needed to make the URL unguessable.
[1] https://higherlogics-trac.sourcerepo.com/higherlogics_clavis...
For example system-A gets id range for 1-1000, system-B gets id range for 1001-2000 and so on.
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.
The second generation of machines, where I did have a say in things, used UUID's and Postgres.
* 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.
https://bugs.mysql.com/bug.php?id=199
If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application.
Given the above, some important things concatenated and hashed works. Can always add an application ID or importer ID or process ID or a timestamp of high enough resolution.
Obviously needs are different if you're trying to create a bank user database vs deduplicating sampled engineering data.
So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.
Once you get into application ids / timestamps / whatever you're no longer really using a natural key in my mind, you're just making your own algorithm for a surrogate key.
That sounds very similar to UUID version 1 except that you replaced MAC address with some other identifier and added a hashing step.
We can all invent algorithms for nice unique values when we include the caveat "apart from the edge cases". It's those edge cases that bugger everything up.
No, for a good primary key, the "unique" parts can be due to concatenation, but the whole key (and thus all the elements) needs to be unchanging. [0]
[0] Modern databases can actually deal with changeable PKs, though with a potentially serious performance hit, but in many use cases where data travels outside the database for some kind of interaction where the results need to get reentered into the database, this is still a problem, since you can't (in any general way) cascade updates to things (which may not even be online systems, e.g., paper records feeding human processes) outside of the DB.
1) Social security #? Fails when you have international student.
2) Last Name, First Name, Middle Name? Fails when you have a repeat name.
3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys duplicated in the classes table to do the join.
Simple Integer primary keys "suck" in that you are adding bogus data to your database that has no value.. but man, they sure solve a LOT of problems. You need to think fairly hard before you get rid of them. In some cases you totally can. But making your default datamodel include an Integer PK solves a LOT of problems.
Where a valid natural key exists, it absolutely should be used. Surrogate keys should only be used where there isn't a natural attribute (or composite of such attributes) that corresponds to the unique identity of a tracked entity.
However, that's very common in the real world.
With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers.
UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.
> UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs.
Exactly.
Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.
That's basically the 'hello world' of web apps example where UUID's make sense already.
Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.
https://aphyr.com/posts/299-the-trouble-with-timestamps isn't bad.
UUIDs absolutely have their place - both the problems of distributed ID generation (mostly in remote clients / apps) and avoiding information leakage (-> German Tank Problem) are very real world examples where using them is a sensible thing.
However, my point was that using them as a default choice has a lot of drawbacks. All over our data model, we've got like 60-70 entities with serial IDs and about 2 with UUID columns.
Also, don't underestimate how fast PostgreSQL can deal you out from a sequence via nextval(), should you really go down that route. But yes, if the database is more than a millisecond away, UUIDs definitely have their time and place.
Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion.
Which, BIGINT is a much simpler solution.
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.
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.
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).
If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge.
IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.
What if 100 people hit the page at the same time for an uncached image?
I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...
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.
If you know the definition and the ins-and-outs of 'clustered index', 'index fragmentation' and 'page split' then feel free to use a UUID if you see fit-- otherwise, please don't.
If you are a beginner and for some reason have to have a UUID: Use a sequential, auto-incrementing integer for your clustered index column and make another second column that is your primary key that is your UUID.
This article feels like the blind leading the blind.
Can you enlighten us as to why? Seems to me if you're not at the scale where you see the benefits of UUIDs you're also not at the scale to see the drawbacks either (bigger, slower indices?).
Better more practical advice might be: if you don't need to use a X as a PK, don't use a X as a PK.
Where X can be either UUID or BIGINT.
If you don't expect your table to scale past a billion rows, INT is more than fine.
Maybe it's a generational thing (I'm 40+), but I tend to always start with thinking about the data structures and design in conjunction with the UI design.
Getting a solid database design in place early in the development cycle is critical to building a solid, stable system- you can of course alter the database as you start building but having a good idea of the data structure should be a "before thought" rather than a after-thought.
Treating data as an afterthought is a great way to have to be re-defining your application logic every time you re-define your data because you forgot something.
My experience is that lots of people, including people with many more years of development on me, don't take this approach though.
Don't get me wrong. I don't roll an application into production until I've gone back and forth over both the interface and the database, until both satisfy me, the database is well-normalized, and so on. But when the first line of anything has yet to be written, I think I save a few iterations by first sketching out the fields and behavior of the user interface.
Furthermore, a database mistake, once built on, will stay with your organization for life, only removable by a lot of pain. A coding mistake is fairly trivial to recover from.
I find it troubling that many self taught programmers don't take the time to learn databases. They aren't even that difficult to master. 3NF, Constraints, Indexes. That covers 90% of it. Everyone thinks they're making Google.
Also, functional programming teaches to first think about the data structures and then about the processes/operations.
Not every PostgreSQL-backed application is a public-facing web app. A fat-client that connects directly to the PostgreSQL database to do "stuff" with transactional data will have a much easier time if any related images are stored alongside the transactional data.
As far as disk space... that could be a problem, but for most of the applications that developers work on, they never need to scale to the point where disk space is a problem. And if it does, you can always get more disk space.
PostgreSQL could be used as the backend for, as an example, an internal inventory tracking application. Something like this could allow users to upload a generic photo of the item in inventory. Putting these in S3 or a CDN makes no sense for an internal application.
And, of course, "internal application" doesn't necessarily mean a web application. It could be a fat-client (WinForms, Qt, Cocoa, etc) that connects directly to the database. It could connect to an application server (which then connects to the database) and speak some custom protocol. It could be a text-based terminal app. It could be that there are a variety of these applications that all connect to the same database.
Better advice would be:
Store images outside of the database. If you are building a public-facing web site or web application, something like S3 or a CDN may be a good fit. If you are building an internal web application, or are otherwise unable to use S3 or a CDN, storing images on a web server and storing the URL (or something that allows you to determine the URL) is a better option. In some cases, such as fat-clients that connect directly to the PostgreSQL database, you may find that storing images in the database is truly the best option. Keep in mind that this may impact performance in the following ways: (insert list of potential issues).
Even in your public web app, you also might have other back-end systems that cannot access S3, but can access your application services or DB (yikes). There are definitely many cases where external providers like S3 is the better approach, but not all. Also, you don't have a performance problem until you have a performance problem.
http://instagram-engineering.tumblr.com/post/10853187575/sha...
- No round trip for generating keys, data can be sent in with an existing or new uuid without having to hit the autonumber/keymaster, removes a single point of failure for a small fee on each row. Storage is cheap so 16-byte uuid is not a deal-breaker, the benefit is speed and horizontal scalability. Optimized read-only tables and/or caching can be made where this has any impact at all.
- Some databases like Oracle you need a sequence to even do autoumbering, huge pain
- Autonumbering is a pain when having to replicate across environments or when you start getting multiple databases and clusters
- Numeric ids for important data is not exposed, prevents easily incrementing for next/previous (other ways to do this but this is one)
- Many databases have a UUID field or field optimized for unique ids/guids/uuids now
- If you were a piece of data wouldn't you want to be unique? All your data are unique snowflakes with UUIDs. On a serious note, this can help to identify data across all types and not just in the same table.
Also, on "storage is cheap": http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/
h/t to Wayne E. Seguin for his article http://www.starkandwayne.com/blog/uuid-primary-keys-in-postg...
Docs here: https://www.postgresql.org/docs/9.4/static/uuid-ossp.html
Related to that: inconsistent values for NULL/False, e.g. "No", "", "False", "false", etc...(or rather, not using a boolean type to enforce this).
And related to that: general unawareness of what NULL means. It's not the same as "" or "False" or 0, both in a technical sense and in a real-world sense.
As opposed to?
- storing their images externally, but forgetting to apply a consistent backup strategy to their image data
- Database is getting big, query returns lots of records, so just adding pagination. Forgetting that the user really doesn't actually want to page through results at all. The correct answer was probably to add proper faceted filters and full text search.
- using UUID keys everywhere, then making the mistake of mixing up UUID keys from one table with UUIDs from another one.
- Using nullable columns as a tool for schema change, but not deciding what it actually means for a particular column in a row to contain a NULL.
- Thinking that they will get away with just storing a chunk of structured data like an array in a particular column value because from the application point of view it's really just one blob of data anyway. In general I give a structured datatype like that two days before someone is writing a query that digs into the inner structure of it.