Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Mistakes Beginners Make When Working with Databases

craigkerstiens.com

Post reply on HN
Print view View original

31–40 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#31

Post by JoeAltmaier »

endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

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

Re: Mistakes Beginners Make When Working with Databases

#32

Post by Jweb_Guru »

stevecalifornia wrote: post #19

"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 seq…

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).

Re: Mistakes Beginners Make When Working with Databases

#33

Post by ReedRothchild »

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.

Re: Mistakes Beginners Make When Working with Databases

#34

Post by donpdonp »

mjw_byrne wrote: post #4

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…

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.

Re: Mistakes Beginners Make When Working with Databases

#35

Post by Jweb_Guru »

JoeAltmaier wrote: post #31
endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

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

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.

Re: Mistakes Beginners Make When Working with Databases

#37

Post by JoeAltmaier »

Jweb_Guru wrote: post #35
JoeAltmaier wrote: post #31

Earlier quoted context omitted.

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

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.

Re: Mistakes Beginners Make When Working with Databases

#38

Post by prashnts »

mathattack wrote: post #3

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 perfor…

But, aren't they frontend developers, and not the Backend developers?
Post reply on HN