9 ms·

I don't like to use SQL engine because I don't understand how they work, I never really know if my query will be O(1), O(log(n)), O(n), etc, or what kind of alg

• josteink 8y ago
  This is what you get when developers are afraid to touch anything which doesn't look like Javascript or JSON. The incompetence and ignorance shown in this post is simply astounding.
• jnwatson 8y ago
  For Postgres at least, you can literally ask it how a query works, via EXPLAIN. Now, there’s a skill to understanding the output of that, but at least it isn’t a black box.
  • sebojanko 8y ago
    There's something similar for SQL Server too.
  • wolf550e 8y ago
    All RDBMS engines have some kind of EXPLAIN, otherwise it's impossible to troubleshoot performance issues. The differences are in the amount of detail you get from the optimizer/query planner, whether you get profile of actual run side-by-side with the plan, etc.
  • ebikelaw 8y ago
    And the query plan can literally change out from under you at any time. SQL sucks. You should be able to dictate the query plan to the engine directly. If SQL exists as a tool to create and serialize such plans via exploration and experimentation, that’s fine. As a runtime query system it is completely unsuitable.
    • ryanworl 8y ago
      Funny enough, in the more recent lecture I posted for SQLite below, it actually doesn’t work that way. The query planner will generate the same plan every time provided you don’t run the analyze command over your data to generate new statistics.
    • matthewmacleod 8y ago
      You are almost certainly going to be worse at deciding how your query should run that the query planner will be. A straightforward Postgres database will almost certainly fulfill your performance requirements unlsss you have a pretty edge-case scenario. Under those circumstances, it would be foolish to start by assuming that you are doing something that the database cannot easily accomplish. After all, it’s much easier to migrate the parts of your stack that require custom code to a new infrastructure when you reach scale, rather that trying to constantly patch bugs and performance issues in your shitty wannabe SWL engine!
    • purerandomness 8y ago
      SQL is a declarative language: You describe what you want, not how to get it. If that's not what you need, there are plenty of procedural languages whith which you describe how to get things. A query plan will change based on statistics. It's the engine's job to decide if your query is best served by parallelizing it to multiple cores, or deciding if it's worth JITing it before execution. The actual execution of the query is an implementation detail of the engine, and should be. That's the entire point of a SQL standard: To provide users with an interface to talk to engines, which then retrieve the data you asked for. It is its core strength and the reason why it's so successful.
      • 8y ago
• ludsan 8y ago
  Your concern about the opaque and abstract layers below you apply to language compilers as well (which I think is the "better" alternative you seem to prefer). There is literature legion on the implementation of the database that would assuage you, should you concern yourself with reading it. I don't think you need to. Trust that many many smart people have engineered many many decades of excellent software. That is, not to say, that you won't need to peek below or concern yourself with certain choices -- indices, commits, columnar-vs-row, etc. as your performance or access patterns dictate. More importantly, the relational model is still the gem that shines as a beacon to model logic and data and is too often undervalued due to its association with 'enterprise software' and the implementation language (SQL is a bit warty).
  • glhaynes 8y ago
    100% agreed - it's exactly the same pattern as other tools. Write it in the most simple/obvious/maintainable way; then, if you have a performance issue (quite rare IME when building something that doesn't obviously need a database FTE from the outset), spend a few minutes semi-educatedly poking at it to see if you can stumble onto a drastic improvement; then, if not, dive deeper.
  • nostalgeek 8y ago
    > There is literature legion on the implementation of the database that would assuage you, should you concern yourself with reading it. I don't think you need to. Trust that many many smart people have engineered many many decades of excellent software. can you recommend some material?
    • pg314 8y ago
      "Architecture of a Database System" by Hellerstein, Stonebraker and Hamilton [1], gives a good overview. The source code and documentation of PostgreSQL is excellent if you want to dive deeper. [1] http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
• blattimwind 8y ago
  You're not scared, you're just too lazy to learn the tools of your trade. Databases are not very complex and use pretty much only textbook data structures and algorithms. Understanding how they process a given query and how a query will probably perform/scale (even without EXPLAIN ANALYZE) is not hard to learn. You do need to learn it (at some point; you don't for small data, which is most). But it's far from difficult. > That's why I tend to avoid systematically using a SQL engine unless the data schema is very very simple, and manage and filter the data case by case in code. And that's the mentality that gives us webshops were applying a simple filter results in a couple seconds load time and uses hundreds of MB of RAM per request, server side.
  • aardvark291 8y ago
    >Databases are not very complex and use pretty much only textbook data structures and algorithms skeptical expression
    • squirrelicus 8y ago
      Whether they use highly tuned or proprietary or obscure algorithms (they do) is less important than the fact that understanding b-trees and basic normal forms will get you 90% of the way to understanding how to use one. It's just not that hard as a database user.
    • aardvark291 8y ago
      "________ is not very complex and uses pretty much only textbook data structures and algorithms" is pretty much a false statement for any engineered product.
      • blattimwind 8y ago
        And the difference is usually close to nil for understanding them. (Also see squirrelicus' comment). Whether my database uses a B-tree or a proprietary, patented, insanely complex and finely tuned data structure that does essentially the same thing as a B-tree just a little bit faster is, for all intents and purposes, not a relevant distinction. Now it's true that most databases have a ton of features and even more optional features that can (and will) interact in interesting ways, but I thought it was fairly obvious that most applications use very few or none of these. They are the 90/10-sort of features; 10 % of a database vendor's customers need 90 % of the specialized features in a database, and every single one of them uses a different handful. Obviously you don't need to understand all these specialized features to use a database; you only need to grasp the handful if any at all you actually need at a time. Applications striving for wide database compatibility tend to rarely use any of these, simply because they don't exist in all databases, or work differently, or have divergent interfaces. So any time you have an application that runs on MySQL or postgres in production but is developed and tested on SQLite (an antipattern itself, but I digress), you can be assured that you'll only see fairly basic DDL and SQL. (You also seem to be intermingling understanding and building. I can use and understand how a typewriter works without having a clue how to build one. Yes, there are lots of hard problems solved by databases, but how they do it is mostly a don't care. I don't have to care how SQLite does power-fail-safe transaction, it does and what that means for me, is all I have to know as a user.)
• coldtea 8y ago
  >I don't like to use SQL engine because I don't understand how they work, I never really know if my query will be O(1), O(log(n)), O(n), etc, or what kind of algorithm will optimize my query. Unless you're generating totally dynamic queries that's a moot point. You can always try it and measure it -- just like you know, you would profile a program in any programming language. And you can trivially have the database show you the query plan as well. Do you also not use APIs because you don't know a priori if a call is O(1) or O(N) or O(Nlog(N)) etc? >I think SQL was designed when RAM was scarce and expensive, so to speed up data access, it has to be properly indexed with a database engine. I really wonder who, today, have data that cannot fit in RAM, apart from big actors. That's really orthogonal. Speed and indexes still matter today with big data (or plain "tens of thousands of web users" loads), where we often have to denormalize or use indexed non-sql stores just to get more speed for the huge data we still need to be able to query fast. Besides, something indexed will be faster whether they are in disk or in RAM compared to something in the same storage that's not indexed. So unless we're coding something trivial, server side we still want all the speed we can get from our data than plain having them as simple structures RAM provides. You wouldn't use a linked link as opposed to a hash table just because your data "fit in RAM". Even in RAM ~O(1) vs ~ O(N) matters [1]. SQL was invented and caught on because: companies had tried and were burned by no-sql stores with incompatible storage standards, lax reliability guarantees, no interoperability between client programs, no over-econmpassing logical abstraction (compared to relational algebra) but ad-hoc reinventions of the square wheel, and so on. Ancient issues the wave of Mongo fanboys brought back all over again. [1] unless the linked list is so tiny as to fit in cache and avoid the hash table inderection, but I digress
• 1stranger 8y ago
  How do you prefer to persist your data?
  • tobyhinloopen 8y ago
    In memory database, dumped to file? Not saying how I would do it, just thinking alternatives. I’m using postgresql usually
    • purerandomness 8y ago
      Ideally, if your DB fits into RAM, it should be served from RAM entirely. If less frequently used parts of it don't fit, offload to disk. Ideally, the DB should guarantee data integrity after a crash even if the DB is served from RAM. That's the ideal scenario: You have the best of all worlds. Coincidentally, that's exactly what Postgres does. On top of that, it's the best NoSQL database currently available. Of course, you can use it with SQL if you ever feel the need.
      • collyw 8y ago
        > On top of that, it's the best NoSQL database currently available. Surely that depends on the load? From what I understand Postgress isn't easy to set up when the data spans more than one server, which many NoSQL database do actually handle fairly well.
    • jacquesm 8y ago
      That's got some seriously limiting characteristics that make it unusable for anything but the simplest settings. Note that the subject here (SQLite) is mostly used in an embedded context and that's roughly what you are describing. Even though that is a very large number of applications it is not the one you are likely to encounter when doing stuff in services for multiple users.
• greenyoda 8y ago
  > I really wonder who, today, have data that cannot fit in RAM, apart from big actors. Keeping all your data in RAM has significant problems, even if it all fits. For example, would you want to lose all your customers' orders and billing information if your code crashed? In addition to the relational database model, SQL databases offer ACID transactions, which are useful if you want to have consistent and reliable data: https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID
  • slow_donkey 8y ago
    To be fair, using redis or elasticsearch as a main datastore is doable. Although I'm not sure they're much better choices in terms of understanding how they work. You could summon Antirez I guess
    • kimdotcom 8y ago
      Doesn't redis by default have recovery via the filesystem enabled?
      • philjohn 8y ago
        It does - BUT depending on how often you have it syncing changes to disk you can lose data.
  • PuercoPop 8y ago
    > For example, would you want to lose all your customers' orders and billing information if your code crashed? There are things like WAL and snapshots. Having your dataset in RAM and querying directly doesn't exclude persisting it to disk. Read Stonebraker's "The End of an Architectural Era"[0]. Basically the OP is right in that SQL DBs were designed assuming that RAM was scarce and that asumption is no longer valid. They are innefficient for every common use case. By at least an order of magnitude. [0]: http://cs-www.cs.yale.edu/homes/dna/papers/vldb07hstore.pdf http://cs-www.cs.yale.edu/homes/dna/papers/vldb07hstore.pdf
    • 8y ago
• dagw 8y ago
  I really wonder who, today, have data that cannot fit in RAM, apart from big actors. It's also a question of price. Once you get above about 256 GB of RAM server prices start to go up really really fast. And while there are systems with dozens of TB of RAM they are stupidly expensive. So even if, in theory, most databases could fit in RAM, most people cannot afford that. And at the end of the day, 100+TB isn't that large a database in the grand scheme of things and you're not easily fitting that into RAM.
  • mmt 8y ago
    > Once you get above about 256 GB of RAM I think it might be as high as 1TB these days, though with what's going on with DDR4 prices, the situation is strange at the moment. Of course, I don't disput your point that a 100+TB database isn't all that large, especially with indexes. I suspect that it's this false dichotomy of "fit in RAM" and "big data" has resulted in many needless forays into distributed computing.
    • DreamSpinner 8y ago
      I like to break up data problems like this: * Trivially Small * Fits In RAM * Fits on one server (CPU / Storage) * Fits on one Big Hardware (Mainframe or other Specialized equipment) * Requires Distributed Storage And/Or Processing
      • mmt 8y ago
        I think it's important to know that other boundaries exist, at least conceptually, but, ultimately, it's the practical considerations that are important. For example, for what you listed, what would you say are the storage cutoffs for each tier today? > data problems I find there is also, occasionally, a lack of awareness of when a problem is data-heavy, encouraged by abstraction layers like ORMs. This can lead to casual or naive (neither meant derogatorily) distributed computing, where the app hoovers up the data from database to be processed. This can be great for anything CPU-intensive but terrible for the I/O-intensive. > Fits on one Big Hardware (Mainframe or other Specialized equipment) I don't think this really exists today, unless you're including an otherwise commodity high-end (e.g. 8-socket) server that carries up to a 4x price premium in "specialized equipment". I'm aware that mainframes still exist, but, for a variety of reasons [1], I'd consider them as being in a world of their own, rather than a step on this continuum. [1] e.g. inherently distributed architecture, not obvious if storage scale actually greater than high-end commodity, interop issues
• zzzcpan 8y ago
  > I never really know if my query will be O(1), O(log(n)), O(n), etc, or what kind of algorithm will optimize my query Aka leaky abstractions. SQL just wasn't designed for performance. A query language that takes performance into account should definitely ignore any ideas from SQL. Maybe have declared data structures instead of tables with operations that use known algorithms, explicitly chosen.
  • gaius 8y ago
    SQL just wasn't designed for performance. That is strictly true in the literal sense that SQL is just a textual representation of relational algebra and calculus, and noone says a mathematical notation is "designed for performance" or otherwise. But in a more practical, useful sense, it's the language most designed for performance, since the query planner has so much leeway to perform optimisation. It can do more dramatic transformations of the parse tree even than a C compiler.
    • zzzcpan 8y ago
      If you cannot predict performance of a query you cannot write a query with guaranteed performance characteristics and cannot guarantee performance of your application. So, no, SQL and RDBMSs in general are neither designed for performance nor are any good at it. Which is one of the reasons we have the whole world outside of them.
      • gaius 8y ago
        you cannot... you cannot... cannot You keep using that word. I don’t think it means what you think it means. Because people have been doing it since the 1980s...
      • mmt 8y ago
        > Which is one of the reasons we have the whole world outside of them. The other reason seems to be a failure to learn (or a belief in "this time it's different") the lessons from the 70s and 80s that informed many of the fundamental design decisions of RDBMSes. The "NoSQL" world has had a remarkable number of incidents with ACID failures. Unsurprisingly, a fix involves sacrificing performance. This isn't to say that the trade-off is never worth it. In fact, RDBMSes can and do offer such trade-offs as options. It's just not the default. It may be accurate that RDBMSes are designed and "shipped" with default configurations that are ACID-first [1] (to coin a term), whereas the "world outside" is performance-first [2]. However, it's nowhere near accurate, and maybe even disingenuous, to suggest that SQL or the relational model somehow prevents high performance. The reality of the actual tools contradicts your claim, as the sibling comment pointed out. [1] with the exception of early MySQL which defaulted to MyISAM as a storage engine [2] as the joke goes, so is writing to /dev/null and reading from /dev/zero
• dmoreno 8y ago
  I completely disagree. Unless your data requirements are very specific, mem only, or not adapted to the relational paradigm any SQL engine will provide you with the best and more efficient algorithms to manipulate your data in the most common situations. I think that if any, databases should be used more.
  • PuercoPop 8y ago
    > Unless your data requirements are very specific, mem only, or not adapted to the relational paradigm any SQL engine will provide you with the best and more efficient algorithms to manipulate your data in the most common situations. Too bad that Michael Stonebraker, Turing Award winner, disagrees with you. SQL are not the best solution for any common use case from the performance perspective. Nevermind what they do to the design of an application. IMHO less people should default to using a database upfront. At least while protyping the idea. https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf
    • Sean1708 8y ago
      > IMHO less people should default to using a database upfront. At least while protyping the idea. Surely it should be the opposite? While you're prototyping you should use a DB by default, then switch to your own implementation if you find out that it will speed things up (and you need that speed). It's not like the code that you would replace a DB with is going to be trivial, using a DB is going to keep the code simple until you need it to be complex.
    • manigandham 8y ago
      You missed the "in the most common situations" part. Modern RDBMS can handle millions of ops/sec and terabytes of data. They do the job fine 99% of the time and are constantly adding new features. If you have the 1% need for another data store, there are hundreds of options, and interestingly many of them are also starting to implement SQL as an interface now.
• dragonwriter 8y ago
  > Who really does understand how a SQL engine work? Presumably, at a minimum, all the people who work on such engines, including committees to the various open-source ones. But also lots of other people. > Don't you usually require to understand how something work before starting using it? No. Very few programmers understand how compilers work before they start using them. I'd say it's more common to require working on something to really understand how it works than the reverse. > I think SQL was designed when RAM was scarce and expensive, so to speed up data access, it has to be properly indexed with a database engine. I really wonder who, today, have data that cannot fit in RAM, apart from big actors. Indexing is no less important for in-memory data access.
• collyw 8y ago
  Understanding the relational model properly will allow you to write performant simple code. Unlike replacing all the well tested code you will have to write for yourself when you get rid of a relational database.