19 ms·

Modern Data Practice and the SQL Tradition

• jackschultz 7y ago
  Alright here's a relevant question I've been having in terms of this. Let's say I have the code to gather / scrape / load some stats into postgres, but then want to run projections on them. For example, if I'm trying to predict the next day's stats by using stats in the past by simply taking the average, how many days in the past should I look at results and take the average of? Is the last 3 day average the best? 5 days? 8? 10? There are clearly a couple ways to do it. One is by getting a data frame of all the stats for all the objects, write the python logic to loop through the days, get back the stats from the past X days not including the current day, taking the average and then storing that back to postgres in a projections table. A function like set_projections(5) where 5 is the number of days in the past I'm taking the average of. Second way to do this is write that function as a plpgsql function where uses subqueries to find the past X day stats for the players and then creates or updates the projections table all in sql so we can run `select set_projections(5)` and that'll do it itself. So the question becomes, which ones is "best"? I have to imagine it's mostly a case by case basis. Since it's only me here, I've been doing it in postgres alone since it can be done in one query (with multiple sub queries, yes), but that's it. With python, it'd involve many more steps. On the other hand, the sql looks huge and then I've been running into the issue of do I split some of the sub queries into sub functions since that's what I'd be doing in python? If there were more people involved, would it be bad to have larger cases like that in postgres since we wouldn't know the skill of the others, where mostly they'd be coders and could write the projections in languages they'd want? Another example of this tradeoff is how should I interact with the database? I have a rails background, and ActiveRecord is an incredibly good ORM, whereas SqlAlchemy hasn't done it for me. In either case, there's a ton of overhead to getting the connections running with the correct class variables. So instead, I kind of created my own ORM / interface where I can quickly write queries on my own and use those in the code. This is especially easy since most the queries can be written in postgres functions so the strings of those queries is incredibly tiny! What I've learned from this project I'm doing is that sql is very, very powerful in what it does. I've shifted more to using it for actions than I would have in the past, and pretty much all thinking I do is making as little code as possible. Anyone make it through reading this and have comments about what I'm doing and what they like to do?
  • empthought 7y ago
    It sounds like you should research window functions: https://www.postgresql.org/docs/12/tutorial-window.html https://www.postgresql.org/docs/12/tutorial-window.html
  • aargh_aargh 7y ago
    What is "best" probably depends on your current metric for "best". Developer time spent? (Is developer and DBA the same person? How familiar are you with SQL?) Processing time? Amount of data transferred? Readable code? Maintainability? Versioning? I've also shifted more logic to Postgres recently and keep the queries in the code trivial. It's because I like that SQL is declarative and there's no intermediate state to mess up (the whole query can be processed within a transaction). As for readability (refactoring) you have several options in Postgres. Going the PL/pgSQL route probably means you're gravitating back towards procedural code. It does have its uses, but I try to avoid it whenever I reasonably can. Try using language SQL [1] instead. Another option is functions. Can be more readable but likely less efficient (they're a barrier for the query planner), just as PL/pgSQL. I've been mostly happy using nested views (and materialized views) lately. But whenever you can, use CTEs to structure your queries. YMMV, just providing ideas to think about. [1] https://www.postgresql.org/docs/12/xfunc-sql.html https://www.postgresql.org/docs/12/xfunc-sql.html
  • shantly 7y ago
    In general it's been my experience that if you try to do stuff SQL could do re: reporting or heavy data work in Ruby or Python or some other scripting language in a web application context, you're in for one or both of "why is this thing so damn slow?" and "why is this report crashing the application? (answer: it's eating all the memory)" I've also seen highly user-visible performance issues because some developer either didn't realize how slow performing a series of queries in a loop would be (didn't understand the network overhead of each request) or didn't know how to condense what they needed to one or two requests. High correlation of this with the ORM-dependent folks. So far as averaging a few days of stats my gut feeling is "this can just be a view with one or more medium-complexity selects behind it" but maybe there's some reason it can't. > So instead, I kind of created my own ORM / interface where I can quickly write queries on my own and use those in the code. This is especially easy since most the queries can be written in postgres functions so the strings of those queries is incredibly tiny! I haven't been in non-Rails Ruby land in quite a while but I remember liking Sequel quite a bit. Very much The Right Amount of abstraction (i.e. barely any) over the DB.
• cty 7y ago
  SQL is a functional programming language. No other imperative programming model will be cleaner or clearer in expressing intent. But, you have to understand SQL to begin with.
  • deleted 7y ago
    [deleted]
  • codetrotter 7y ago
    Did you mean to say declarative? That’s what makes it clear in intent isn’t it? That it’s declarative. Whether or not SQL is also functional is orthogonal to that isn’t it?
    • chasd00 7y ago
      further, afaik there's no assignment nor iteration in SQL. it's not a programming language at all. It's a.. query language.
      • yellowapple 7y ago
        I'd consider INSERTs and UPDATEs to technically be "assignment", even if they're very different from how other languages do it. Some SQL dialects do support both traditional variable assignment and iteration for those cases where an iterative/imperative approach makes more sense than trying to shoehorn the problem into something set-based / declarative. Some limit them to stored procedures (e.g. Postgres, and AFAICT Db2), while others allow them pretty much anywhere (e.g. SQL Server / T-SQL).
      • SAI_Peregrinus 7y ago
        I tend to lump it in as a "logic programming" language, along with Prolog and Datalog.
      • taffer 7y ago
        > further, afaik there's no assignment nor iteration in SQL That was until CTEs were introduced in SQL:1999 > it's not a programming language at all. It's a.. query language. The two are not mutually exclusive. SQL is used to tell a computer what to do, and it is very powerful at it: https://www.youtube.com/watch?v=wTPGW1PNy_Y https://www.youtube.com/watch?v=wTPGW1PNy_Y
• noobiemcfoob 7y ago
  “Why didn’t we use an RDBMS in the first place? “ Because initial application specifications are sparse and definitely wrong. If your application is still up and running 5 years later and your data definition hasn't changed much in the past 3, then maybe refactor around an RDBMS. Designing around rigid structures during your first pass is costly. This is why there's been a rise in NoSQL and dynamic languages.
  • brightball 7y ago
    It's really not. That's a myth and you get all of the benefits that you want just by designing with a dynamic language. It's less costly to create a relation table when you realize there may be multiple instances of a piece of data associated with a record than it is to just stick those pieces in an array. Because when you don't use the relational data, you get the extra work of modifying all of your existing NoSQL records to use the array structure. And as a bonus, you make it easy to do queries in both directions with the relational data. NoSQL offers virtually no efficiency benefit unless you're actually consuming unstructured and variable data.
    • 0xFACEFEED 7y ago
      With NoSQL solutions you're typically pushing data migrations to code. Yes this is technical debt. But not having to deal with SQL based data migrations is pretty big time saver early on.
      • nicoburns 7y ago
        Not really. Writing a SQL migration takes what, 10 minutes max? Or you add the columns as you go, and it all just merges into the normal dev time of the feature anyway. You'll easily make up this lost time just in not having to immediately clean up crappy data that you've written to the database while you're developing the feature. That's been my experience anyway.
  • throwaway0xb 7y ago
    I feel like this is where ActiveRecord shines, as the initial development is thought of as objects and their relations to each other and it's very easy to alter data definitions. And in the end you get a reasonably normalized database underneath everything. The reality is most of your models aren't going to be undergoing wild schema changes and if they are or you need traversable unstructured data you can defer to json columns for those scenarios.
• _pmf_ 7y ago
  The damage SQL has done to the relation of perception of he general public towards the relational model cannot be undone. The relational model is beautiful; it's in no way more complex than objects and attributes, but SQL makes it seem so by conflating orthogonal aspects into it.
  • ergothus 7y ago
    Care to elaborate? What you've said resonates, but I lack non-sql relational understanding with which to really evaluate.
    • XuMiao 7y ago
      Relational algebra didn't evolved into a relational programming language. SQL is merely a query language. The recursion is horribly done and there is no type systems. Imagine this, Friends(a: Person, b: Person) defines the relationship and the foreign keys at the same time. It makes the reasoning easier too. mary.Friends.Friends get all friends of friends of mary who is a Person. SQL requires you to write a lot of joins to achieve this. Error prone coding experience. That is why there are ORMs which end up with half baked solutions. In fact, logic programming language and SQL should consolidate into a relational programming language. Every thing we write as a program, automatically supports persistent and distributed storage. It can also support probabilistic computation to have machine learning involved. Then we will have a complete data driven software solution. Unfortunately, right now, we cook everything up with SQL, python, operational DBs, analytics DBs, Spark, Tensorflow. It could have been a better place.
• 0xFACEFEED 7y ago
  The author of this essay is beating a dead horse. "NoSQL" (I really dislike the term) has proven its value over the years and deserves a seat the table. People will always misuse technology or implement it poorly, but I don't think it warrants yet another oversimplified "SQL vs NoSQL" rant.
  • jppope 7y ago
    well said
  • epicide 7y ago
    I never really understood how anyone expected to have a sensible debate on "thing" vs "anything that isn't thing" (I realize "NoSQL" is somewhat better defined than that, but not by much).
    • yellowapple 7y ago
      Agreed. The more meaningful debate would be "relational" v. "not relational", albeit marginally (swapping "not relational" for something more specific like "document" or "key-value" would be even more meaningful). There are indeed relational databases that are technically "NoSQL", like Mnesia.
  • thanatropism 7y ago
    Immutable/stream databases are a fundamental innovation, even if everything else turns out to have been unsustainable performance hack.
  • ramraj07 7y ago
    Genuinely curious since I have only seen bad things said about NoSQL in most places over the years, what benefit does it provide other than scaling?
    • dodobirdlord 7y ago
      It's unlikely that a company needs to invest in data scientists or even thinking very hard about their data organization until the scale of their data is already pushing the bounds of what most RDMMSs can handle. NoSQL is nice if you are planning for scale, since it will seamlessly get big without much thought or any significant changes to the performance. There are no "gotchas" that will cause very long-running queries or that will lock a huge number of rows, so performance is very good and most importantly very stable. This is largely due to the fact that you have to think more deeply about your data access plan up front, since almost any query is a table scan (which, ideally, never happens). I think that this is secretly a benefit in that it forces people not to perform ad-hoc queries on databases, and to think of their databases in terms of the APIs that they have built over their databases, because the databases are not going to efficiently support any other sort of access than the access plans included to support those APIs. I would lean in favor of a NoSQL database to back a production networked service because the upsides are helpful in this case (stable performance, easy to scale) and the downsides are not significant (have to plan your APIs up front -> going to do that already, no ad-hoc queries -> not going to run ad-hoc queries on a production database anyway).
• danso 7y ago
  I've personally grown to love SQL and I think it is by far the clearest (if verbose) way to describe data transformation work. I learned traditional programming (e.g. languages like Python) long before I stumbled on SQL, but I'm not sure I could've understood Pandas/R as well without having learned SQL, particularly the concepts of joins. That said, my affection for SQL correlates with my overall growth as a software engineer, particularly grokking the concept of piping data between scripts: e.g. writing a SQL script to extract, filter, and transform data, and then a separate Python/R script that creates a visualization. I think SQL seems so unattractive to data scientists (i.e. people who may not be particularly focused on software development) because it seems so alien and separate from the frameworks (R/Pandas) they use to do the actual analysis and visualization. And so it's understandable to just learn how to do it all in R/Pandas, rather than a separate language and paradigm for data wrangling.
  • haolez 7y ago
    I love SQL as well, but SQL is too low level for "data scientists" that are used to copy paste scripts from the internet and working with pre-baked packages.
    • danso 7y ago
      Admittedly, there are data scientists in roles that consist of hacking together opague scripts until something comes out. But I do think people misperceive SQL as "low level" compared to R/Python, when ironically, SQL is actually high level, in terms of a programming language.
  • gigatexal 7y ago
    SQL is indeed a powerful too. But it's just one of many. Just today I used: SQL to dump data to csv, python to modify it, and gRPCurl to upload it to another service. Different tools, different problems, use the right one. That being said ... RDBMs still suffer from scale and there are many efforts to fix this with sharding to disaggregating the data layer from the query layer with examples like AWS's Aurora and GCP's BigQuery etc -- in all likelihood data will likely live in many different places and will need to be adapted into things like a datastore or queried from things like Hive and Presto, or even a traditional ETL + EDW setup -- my point being there's no one thing that will solve everything as most single solutions break down when the number of rows is measured in the billions or more.
• vkazanov 7y ago
  I's nice that the article mentions Codd and his relational model of data but what it doesn't mention is how badly SQL parrots relational algebra. The language is inspired by the idea ("based on a real story"(c)), yes, but it takes a really clean and sound model and makes an unbelievable mess out of it. SQL is just an ugly historical accident. Unfortunately, this how it often works... NoSQL are a different story, of course. BTW, I believe that they predate Codd's work. There were many examples of non-relational DBs in the 70s.
  • Pamar 7y ago
    Yes, there were various approaches to non-relational data stores but they were not so flexible in terms of "schema", which I believe is the main strength of NoSQL. A possible exception could be MUMPS https://en.wikipedia.org/wiki/MUMPS https://en.wikipedia.org/wiki/MUMPS but I have no direct experience with this (while I used something akin to https://en.wikipedia.org/wiki/Hierarchical_database_model https://en.wikipedia.org/wiki/Hierarchical_database_model at the start of my career).
    • beefhash 7y ago
      Pick[1] subscribes to a similar philosophy, merging the OS into a database. [1] https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system
      • ry4nolson 7y ago
        Wow. We use this (Universe "flavor") at work, can't believe I'm seeing it on HN.
  • aargh_aargh 7y ago
    I'd love to educate myself more on how SQL mangles rel. alg. and whether there's another purer implementation. Any links?
    • _jal 7y ago
• cube2222 7y ago
  I don't agree with the vilification of NoSQL, but I do agree that SQL is a great query language. That's partly why I wanted to create a tool to query various databases (NoSQL ones or files too) with SQL. We're still in an early stage with OctoSQL regarding the variety of databases, but take a look if that sound appreciable to you: https://github.com/cube2222/octosql/ https://github.com/cube2222/octosql/
  • chrisjc 7y ago
    What sets OctoSQL apart from the existing options such as Apache Drill (even Spark SQL for that matter) or future projects such as PartiQL? https://partiql.org/ https://partiql.org/ https://drill.apache.org/ https://drill.apache.org/
    • cube2222 7y ago
      We're aiming to have very ergonomic stateful stream processing with only SQL and we're working on it currently. That's basically what's meant to set us apart.
      • chrisjc 7y ago
        So tapping into the change-logs of the underlying data-sources and providing a stream processing layer that's expressible in a stream SQL dialect? btw, not being critical of your project, just trying to understand it.
        • cube2222 7y ago
          Mainly thinking of explicitly stream oriented data sources like kafka, but yeah, change logs are really solved with an analogous abstraction. There's a great paper on that: "One SQL to Rule Them All", check it out. We also want to scale well from single computer one-of data exploration queries, to full blown clustered long-term stateful stream processing. The point is to provide a well thought out SQL interface to as many data sources as possible, and like drill does, push down as much computation as possible. We actually learned about drill only after creating OctoSQL, but that's another story. (We're definitely less mature currently and support fewer datasources)
• psv1 7y ago
  As someone who has to use Elasticsearch as an only data store, yes.
  • noobiemcfoob 7y ago
    >use Elasticsearch Found your problem.
  • licnep 7y ago
    Can you elaborate on which issue(s) you encountered? We are considering Elasticsearch at the moment for document storage and search, versus postgres.
    • qohen 7y ago
      FYI, you might want to check out ZomboDB[0], which integrates Postgres and ElasticSearch. It's open source[1] and, fwiw, the developer was helpful when I pinged him with some questions a while back (and is available for consulting services). From the project's github page[1]: ZomboDB brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type. Its comprehensive query language and SQL functions enable new and creative ways to query your relational data. From a technical perspective, ZomboDB is a 100% native Postgres extension that implements Postgres' Index Access Method API. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING zombodb on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results. [0] https://www.zombodb.com/ https://www.zombodb.com/ [1] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
    • matwood 7y ago
      I typically keep search indexing separate from document storage and delivery. That way I can design each independently to do what they do best. Something like postgres can do both, but I would still logically separate the problems so I can move to better solutions when they arise.
  • mahkeiro 7y ago
    Come on now even ES has an SQL interface.
• innagadadavida 7y ago
  In the Hadoop world things have evolved to support SQL. Spark, Hive, Impala all have full support for SQL. The Spark implementation is actually faster than you doing low level RDD processing as there are optimizers that work very well. In addition, you can create UDF that are easy to integrate. The only reason you might choose other approaches is to make the problem look significantly more complicated, thereby justifying more maintenance and resources. SQL is just too easy and some super smart engineers don’t like it because if it. That said, NoSQL does have value in some corner cases where it could perform better when most of the logic is simple lookups.
• zzzeek 7y ago
  Give the data scientists SQL and relational algrebra. But please don't give them stored procedures and triggers.
  • will_pseudonym 7y ago
    (genuine question) What are the best alternatives to triggers? And what makes them a bad idea? I'm pretty much with you on stored procedures.
    • Ididntdothis 7y ago
      I am sure there are good use cases for triggers but I have seen quite a few databases that used triggers a lot and it almost always felt like the equivalent of spaghetti code. Things are happening and it takes forever to figure out why they are happening.
      • BareNakedCoder 7y ago
        Depends on your perspective. Yes, if you are an application developer with weaker skills to access the data integrity logic in the database. No, if are a skilled database guy with weaker knowledge of all the source code of all the applications (could be multiple) sharing the database. Things are happening and it takes forever pouring thru each app's code to find its data integrity logic to figure out why they are happening. By centralizing data integrity logic in the database, you know where to look and that all apps using the database will abide by it.
        • Ididntdothis 7y ago
          I agree with you but in the cases I saw I felt that the triggers were used to fix problems in the code more than being part of a consistent data strategy. I admire well designed databases but unfortunately there aren’t too many of them out there. I think part of the problem is that there is still this huge chasm between good coding skills and good database skills. It’s hard to have both.
    • 7y ago
• ComodoHacker 7y ago
  One point got me curious: >As a rule of thumb, vertical scalability is in most cases more economical than the horizontal one. Isn't the opposite the reason why we started to scale horozontally in the first place?
  • alexhutcheson 7y ago
    No, it's because projects with huge amounts of data were growing beyond the limits of what you can reasonably do on one machine. Machines were less capable then (smaller disks, less memory), and those limits are a lot higher now. If your data is small enough to fit on one (very beefy) machine, then it's probably still cheaper to pay for that high-end machine vs. distributing to a bunch of less capable ones. There are exceptions - distributing the data can be really helpful if you need to do a lot of bulk I/O (ETL jobs, analytical queries, etc.), but it comes at the cost of making "transaction" use-cases difficult and expensive. Using a scaled-up OLTP[1] database for user interaction and a scaled-out OLAP[2] database for analytics and ETL jobs is a common pattern. [1] https://en.wikipedia.org/wiki/Online_transaction_processing https://en.wikipedia.org/wiki/Online_transaction_processing [2] https://en.wikipedia.org/wiki/Online_analytical_processing https://en.wikipedia.org/wiki/Online_analytical_processing
  • simonw 7y ago
    Big machines got cheaper. Amazon will rent you a machine with a TB of RAM in it for an hour for the price of a fancy coffee. Horizontal scaling has enormous complexity costs. It's worth it if you are genuinely web scale (Facebook, Twitter etc) but very few projects are.
  • yellowapple 7y ago
    The more pertinent reason to scale horizontally rather than vertically is that horizontal scaling without downtime is easier (if you've nailed down / automated node deployment, which is a big "if", albeit one made easier if you're using any of the big PaaS/IaaS providers): With horizontal scaling: 1. Spin up the new node(s) 2. ??? 3. Profit With vertical scaling: 1. Spin down the node¹ (hopefully you've got more than one!) 2. Resize it 3. Spin the node back up 4. GOTO 1 unless all nodes are scaled up 5. ??? 6. Profit OR (somewhat simpler, but at this point you might as well just horizontally scale): 1. Spin up the replacement node(s) 2. Spin down the old node(s) 3. ??? 4. Profit That is: vertical scaling has more steps, even when done in a way similar to horizontal scaling. Sometimes it's necessary to scale vertically instead of horizontally, though (but in that case you might as well just horizontally scale with bigger nodes). ---- ¹ It's theoretically possible to add and remove resources to/from a running system; some mainframes offer it as a feature (Multics programmers in particular were known to yank hardware out of production systems during off-hours and plug 'em into dev systems for development/testing, then plug 'em back into production for on-hours, all without bringing down the system), and Linux supports hot-adding and hot-removing CPU and RAM provided the underlying hardware supports it (Windows supports hot-adding as of Windows Server 2008 Datacenter Edition, but not hot-removing, last I checked). However, I'm not aware of any PaaS/IaaS providers offering compute instances with this capability; EC2 instances definitely don't support it, last I checked (switching from one instance type to another absolutely requires stopping and restarting EBS-backed instances, and instance-storage instances have to be outright imaged and then reimaged onto a new instance of the desired type).
• commandlinefan 7y ago
  Not to step on anybody’s toes, but… I often suspect that there are lots of people who tried, and failed, to learn relational database techniques and gravitate toward schemaless solutions like Mongo just because they’re easier to understand. Maybe not everybody, but more than a few that I’ve interacted with.
  • michelpp 7y ago
    They're superficially easy to understand, but end up moving the complications of concurrency, transactions, and statistical query planning up into the application. Those are much harder problems to solve correctly than just learning SQL and understanding the output of EXPLAIN.
    • frenchyatwork 7y ago
      Even a superficial difficulty can make a big difference for some people. If you start out with a NoSQL database because it seems simpler, and then with each problem you run into, it seems simpler to solve it with NoSQL because that's what you're familiar with.
    • HelloNurse 7y ago
      It's a common ignorance pattern: hipsters reject complex and mature effective tools that require a learning investment (in this case a RDBMS and its fancy configuration options and SQL capabilities) because they don't know what they can do, and embrace inadequate familiar and/or shallow tools (in this case, misused fashionable and "schemaless" NoSQL databases) because they are confident that they can fill the gaps (in this case application code in familiar programming languages, to the extreme of "reinventing the wheel" with completely custom frameworks).
      • catalogia 7y ago
        I was trapped in this mode of thinking for a few years when I was younger. I really regret falling for it, and try to help other people when I see them falling for it. I wish somebody had set me straight, so I'd not have spent those years searching for excuses to perpetuate my ignorance.
• asdfman123 7y ago
  Coming from the boring enterprise world, where we didn't really get swept along in the NoSQL hype, this doesn't seem like it should remotely be a surprise to anyone. "Hey, turns out there's lots of great ways to use SQL!" Yeah, we know, they've been at the core of our business for the last 20 years at least. Lots of enterprise shops started out as SQL databases to keep track of all the data, and web apps grew up organically around them.
• barrkel 7y ago
  Start with Postgres. Don't start with SQLite. SQLite is a file format, not a database; it scales atrociously (I've seen simple queries run for 10s of seconds with 100MB of data), it basically doesn't work in concurrent update scenarios, and the more data you put into it, the more pain you'll have migrating to Postgres. Use SQLite if you want a file format where you don't want to rewrite the whole file for every update, or if you're in a situation or environment where it's not feasible to have an actual database server.
  • mongol 7y ago
    I think SQLite is terrific. I don't know what you mean with that it is not a database. It's SQL querying capabilities are definitely database-worthy.
  • ellimilial 7y ago
    Sure if your use case is an analysis of interconnected entities and you either work alone or have someone to help you maintain a shared server. Otherwise please don’t. I earn my living (amongst other things) due to organisations that went with this as blanket approach. Use a right tool for the job. SQLite is fantastic for small to medium size datasets, shines in immutable case. Plus its maintenance and sharing cost is close to zero(something you will cherish once docker comes to play or if you want to learn or test some SQL without going through a pain of setting local permissions for your schemas).
  • Koshkin 7y ago
    > SQLite is a file format Well, you could say the same about Postgres... They are both databases, and the lack of scalability or (not) being able to run as a service does not change that simple (and useful) fact.
• Dowwie 7y ago
  I've worked extensively with SQL and relational data modeling. It's been very useful for accomplishing my work. I haven't come across better tools and so I haven't adopted alternatives. If the time ever comes where there is truly a superior tool set to the one I am currently using, I will gladly stop using antiquated technology. Newness isn't sufficient to sway me. No dogma here. Just pragmatism.
• acd 7y ago
  Data structures still matters and very much so! If you run in the cloud data structures has to be very efficient! Data should be normalized. MySQL has built hash table support adaptive hash indexes. I think one should also not over complicate the data layer. Boring tech, I like the safety C D of SQLs ACID. http://boringtechnology.club/ http://boringtechnology.club/
  • nomel 7y ago
    > Data should be normalized. This is a rule of thumb, only, and depends on your definition of efficiency and your queries. If you normalize everything, especially with some analytics queries, you will quickly find silly barriers to query times caused by all of the required joins.
    • matwood 7y ago
      Sure, but now we're getting into higher level system design. The schema used to run the operational system may/will be different from the one used for reporting and analytics. This is what data warehouses were born out of with the flattening of data, star/snowflake schemas, dimensions, etc...
• QuadrupleA 7y ago
  I might be under-informed but "data science" seems to involve a lot of vague BS. Computing has always centered on data. "Data practitioners", "data practice", "data management" just seems like weird rebranding of stuff that businesses have been doing since the 1960s. Partly what the article seems to be relating regarding SQL. What is "data science" besides computing, data storage of some sort, and analyzing the data? Because it's "BIG" now? Because now we "extract knowledge and insights" from data, since apparently giant databases were amassed for no reason in the past? Because now we "combine multidisciplinary fields like statistics", since apparently nobody had thought to run statistics on their data before? Because "AI"?
  • ramraj07 7y ago
    A good data scientist is a jack of all trades - mediocre programmer, mediocre ML modeler, mediocre ETL architect, mediocre statistician and a mediocre analyst. You hire this person because you don't need a specialist in each of these fields But you need someone who can do these things. Also you don't want to pay a metric ton of money so you can't expect some superstar.
    • EForEndeavour 7y ago
      > Also you don't want to pay a metric ton of money Doesn't the job title of "data scientist" still correlate with high pay, or has the hype started to wane?
      • ptd 7y ago
        Depends on the location and company. In a smaller city working for a smaller company an entry level data scientist might be happy to pull in 60k. In a major city working for a large company you can at the very least double that number.
      • TomMarius 7y ago
        If the person can do all of these in a way that makes them a profitable investment, it doesn't really seem so high
• exabrial 7y ago
  The people that bemoan SQL are like the people that bemoan staff notation. Every [music|data] student thinks they can do better when they first learn it instead of embracing the though that's gone into it over many years.
  • WhompingWindows 7y ago
    Interesting analogy, the problem is staff notation has been used by every basically major composer for hundreds of years. SQL, while important, is far less universal and far more complex. In the end, staff notation is more like writing than it is like SQL. Writing and staff notation both have a limited number of characters with an insane number of possible combinations. With SQL, the main challenge lies in understanding the underlying data structures, not the declarative symbols of SQL itself.
• laichzeit0 7y ago
  It’s best not to take criticism seriously from people who have no formal CS education and can’t even define the premises of relational algebra. Ah well.
  • WhompingWindows 7y ago
    Do you have any particular critiques of the author's content? Otherwise, this feels like gate-keeping, since you provided no specific critiques of any content.
    • laichzeit0 7y ago
      Not the author, I’m referring to the people that drive the author to have to make a post like this at all.
• gfo 7y ago
  I've been working with SQL a lot in my job lately. For what it's worth, I'm a big fan. It seems the central argument here is NoSQL doesn't force you into good design habits so it's overrated. I'll concede this is partially true from my perspective because much of my work involves trying to sanitize, transform, and otherwise refactor poorly structured or designed NoSQL datasets. But I've also seen my fair share of SQL databases which are poorly designed, don't use features which are meant to benefit developers (I haven't seen a Foreign Key smartly implemented in a LONG time). It's not really fair to say NoSQL has encouraged poor design practices; from my experience it seems like data model implementation is given little effort in general. NoSQL takes the 'training wheels' off data model implementation where SQL is like keeping them on, but even with them you can still fall off the bike if you aren't careful though it's much harder.
  • lgl 7y ago
    >I haven't seen a Foreign Key smartly implemented in a LONG time This has also been my experience as a web developer mostly. I rarely see any applications that make use of foreign keys constraints supplied by the database server. Usually I see relations being handled by code on the application side. Even when I build apps that are using SQL, I always implement this stuff in code instead of FKs even though I know how they work and when designing a database schema in an app like (for example) mysql workbench, I have the option to add them easily. But I always use the "ignore foreign keys" option and then implement the constraints in code. I just find it a bit more sane to have all of the logic inside the app. I know I'm probably "doing it wrong" but would love to hear some other opinions about this from the hn crowd that does web development. I'm guessing that more "enterprisey" apps like CRM's and ERP's will probably use more of the native database stuff.
    • iamwil 7y ago
      When I started as a web dev, I simply didn't know about them. The frameworks I was using didn't really emphasize it, so I glossed over it.
    • Scarbutt 7y ago
      If you have to ask then should use them to maintain integrity, only devs who know what they are doing can have the luxury of ignoring them because of sharding, performance, complex schema migrations etc..
• lukev 7y ago
  Good article, but it and a lot of the comments here seem to be conflating SQL as a language and Postgres (or other RDBMS implementations.) For the record, I like both these things and they often go together well. But once your data gets too big for Postgres, you don't have to immediately jump to NoSQL: modern distributed SQL tools like Presto are quite good and can let you continue to use the same SQL interaction patterns, even at the terabyte or petabyte scale. You have to be a little more aware of what's going on under the hood, so you don't write something pathological, but it's quite a powerful approach. I am even using Presto for a lot of what would normally be considered ETL: using SQL to select from a "dirty" table and inserting into a "clean" table on a schedule.
  • dswalter 7y ago
    And presto's rich functionality for complex data types (maps and arrays) as well as window functions makes some pretty challenging things possible.
• patgrdj 7y ago
  RDBMS can be replaced by Impala, Spark SQL, Drill, Presto, or any SQL engine on top of Hadoop.
• maxdemarzi 7y ago
  Querying data in “Cypher” is so much easier. I have 20 years as a database developer, cypher is way better. I can live code 50 complex queries in Cypher before I get one done in SQL.
  • mycall 7y ago
    What is Cypher?
• kureikain 7y ago
  > On the other hand, if you check Postgres’ configuration file, most of the parameters are straightforward and tradeoffs not so hard to spot. The artcile also refer to MongoDB as hard to config It maybe a matter of taste. I found MongoDB document is way better than Postgres. They had thing like this: https://docs.mongodb.com/manual/administration/production-checklist-operations/ https://docs.mongodb.com/manual/administration/production-ch... https://docs.mongodb.com/manual/administration/analyzing-mongodb-performance/ https://docs.mongodb.com/manual/administration/analyzing-mon... Which I can easily follow and apply and they are action-able like set noatime on fstab, use XFS, max file handler etc. For Postgres https://www.postgresql.org/docs/12/index.html https://www.postgresql.org/docs/12/index.html I cannot easily find something similar to MongoDB one.
• hypfer 7y ago
  Hey you've scraped my email from GitHub and sent me this link in some kind of newsletter I don't think this is GDPR compliant.
• dsstudent 7y ago
  feedback tha paroume?