6 ms·
> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case y
by owenmarshall 9y ago
> Stuff like this is bread and butter SQL.
Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that.
But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)
- cookiecaper 9y agoYeah, sadly, this is not too much of an exaggeration. I've worked on teams that insisted they needed DynamoDB, because, well, Dynamo is for "Big Data", and they certainly wouldn't work somewhere that had "Small Data"! Replace the buzzwords/products as applicable; you could actually probably just scramble them and it'd work just as well, since someone out there thinks "RabbitMQ means Web Scale", etc. SQL databases are amazing, robust examples of engineering. They are your friends and they're the appropriate choice for the vast majority of software. They are not outmoded or passe. Though I acknowledge there is a separate use case for K-V stores, I almost want to make policy preventing their use just because I know so many developers will abuse them badly and then stare back at you blankly during the semi-annual massive downtime event, muttering something like "Well, it's based on research at Google, so I'm sure there's a way to recover the data..."
- toddBarkus 9y agoI think this is a case for the return of the traditional "sysadmin" as "devops"/"SRE" is now the role of unblocking deploying a solution instead of questioning it's complexity/fitness.
- cookiecaper 9y agoWhile I agree, I also think that overt gating and approval processes create a high tension dynamic that frequently breaks down, whether it's ops v. dev, security v. dev, or others. It's easy for people to get their pride wounded, and they end up encouraged to find workarounds to the process. The simple answers to this are pretty much imaginary, unfortunately.
- toddBarkus 9y agoSure, of course. I was hoping to point out that there's an increasingly overlooked value in having someone question complexity. The "no, you don't need React" of the frontend dev or the "our data is actually relational" of the back end dev.
- rbranson 9y agoIf your “SRE” team is only “unblocking deploying a solution” then I’m sad to say they are an operations team who has rebranded themselves to appear more relevant.
- toddBarkus 9y agoThat's most "SRE" -- it's a title arms race in that field between the underqualified and those that wish to convey they know how to do more than write system scripts in DSLs
- EGreg 9y agoQuestion, though... If your columns have types, how can you encrypt them using custom keys for each one? Is it possible? I want the keys to reside on the client and search for encrypted data. Basically single row lookups at a time.
- pandapunchpower 9y agoThe column type would have to be the type of the encrypted value. The type of the unencrypted data could not be enforced by the DB and you would have to rely on code doing the correct thing. I am however extremely wary of doing it that way. I don't know your requirements of course.
- EGreg 9y agoHere is the thing - encrypted stuff is just a weird encoded string. So I can’t really use columns normally. What I really need is just a huge table with two fields: “token”, “content” And the token is basically the primary key but encrypted with whatever encryption. You could even do foreign keys this way. Hmm I suddenly have an idea. What about a layer above the database that basically enforces foreign keys and joins in this way to support end to end encryption? The content would reference ENCRYPTED foreign keys. Only clients would decrypt stuff.
- pandapunchpower 9y ago>What I really need is just a huge table with two fields: “token”, “content” Sounds more like a key value store and less like a relational database. Although you can store key value data in a relational db of course, there may be a better tool for the job.
- JoachimSchipper 9y agoYou'll be interested in CryptDB.
- nurettin 9y agoAlso, how do you index that?
- MrMorden 9y agoEvery fool knows that MongoDB is web scale. (Not as web scale as /dev/null, but MongoDB has a better logo.)
- reificator 9y agoI disagree. /dev/null's logo is way cooler. https://www.nasa.gov/sites/default/files/cygx1_ill.jpg https://www.nasa.gov/sites/default/files/cygx1_ill.jpg
- Matheus28 9y agoIf you aren't implementing your own tabular database on top of your existing tabular database, you aren't 2018 enough :)
- mathgladiator 9y agoI'm changing the game by code generating a tabular database on top of an eventualy consistent store. 2020 here we come!!!
- elvinyung 9y agoRows by any other name would smell as sweet...
- mgkimsal 9y agobetter make sure it's all immutable too.
- LoSboccacc 9y agooh boy do I have news for you https://www.cockroachlabs.com/ https://www.cockroachlabs.com/
- elvinyung 9y agonot an eventually consistent store though.
- Regardsyjc 9y agoDo you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is worth storing (like calculations) and how to choose which data to group in tables.
- matte_black 9y agoOk, first you need to decide: is this going to be a purely transactional database (for business processes) or do you also plan to do data analysis straight inside this database (meaning you won’t be extracting, transforming and loading data into another database and analyzing it there). If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point. If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events) An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track. If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can. *Disclaimer: most of what I’m talking about is from a Postgres perspective.
- jonah 9y agoI read SQL Antipatterns[0] - Avoiding the Pitfalls of Database Programming by Bill Karwin a while ago and really liked it. [0] https://pragprog.com/book/bksqla/sql-antipatterns https://pragprog.com/book/bksqla/sql-antipatterns
- fnord123 9y ago
- jehlakj 9y agoYou can thank full stack developers for that
- chiefalchemist 9y agoThem, or the manager that hired them? If no one up the chain brings on a DBA what are they supposed to do? I hear ya. But this is as much a symptom of naive (and budget stretched) leadership as it is of the hands on deck.
- olalonde 9y agoYour solution costs 150K$/year while theirs took a weekend and saves 50K$/year...
- chiefalchemist 9y agoAllow me to clarify. 1) It took a weekend to complete. The friction was building for far longer. There's a cost to that, esp if it effects customer satisfaction and retention. They didn't refactor for fun, did they :) How many dev teams aren't so lucky? Is this article a no choice outlier, or a best practice? 2) My comment wasn't directed at the article but on another comment that blamed the developers. These problems should be owned by ownership / leadership / management more and engineers less. 3) That said, hire a DBA? I don't think that's necessary.
- flukus 9y agoIf they don't know how to look at a query plan and craft a solution can you really call them full stack?
- vishnugupta 9y agoThe stack has to bottom our somewhere and it's usually at the application code
- 9y ago