How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

Post by wvenable » Mon, Jun 25, 2018, 1:37 AM UTC

ebikelaw wrote: post #50

purerandomness wrote: post #24

Earlier quoted context omitted.

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

"You describe what you want, not how to get it." The entire archive of the pgsql users' mailing list disagrees. Every wants to know why their plan is suboptimal, or why it changed. People also want to know why the planner takes 100ms to generate the plan and only 1ms to execute the query, and so forth. The idea that you just say what you want and you get the optimal result from your database is just ridiculous to any…

Post by mmt » Mon, Jun 25, 2018, 2:40 AM UTC

dagw wrote: post #26

jokoon wrote: post #6

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. Who really does understand how a SQL engine work? Don't you usually require to understand how something work before starting using it? Which SQL analyst or DB architect really knows about the internals of a SQL engine? Do they kn…

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

Post by cryptonector » Mon, Jun 25, 2018, 2:57 AM UTC

DenisM wrote: post #59

dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

Two reasons to not have indexes in the query: 1. Query expresses the result it produces, not the method that was used to obtain it. Semantic vs implementation. It may be a pain to write, but it will be easier to read later. 2. DBA could add/drop indexes on the fly to tune performance of a live system without making any changes to the application code. And being 100% certain he is not changing the semantics of what's…

    q = parse_query("...");
    q.hint(FIRST_TABLE, "a");
    q.hint(INDEX, "b", "b_idx1");
    c = q.compile();
    r = c.run(...);

Post by aardvark291 » Mon, Jun 25, 2018, 3:25 AM UTC

aardvark291 wrote: post #33

blattimwind wrote: post #13

Earlier quoted context omitted.

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

>Databases are not very complex and use pretty much only textbook data structures and algorithms skeptical expression

Post by nostalgeek » Mon, Jun 25, 2018, 3:51 AM UTC

ludsan wrote: post #10

jokoon wrote: post #6

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. Who really does understand how a SQL engine work? Don't you usually require to understand how something work before starting using it? Which SQL analyst or DB architect really knows about the internals of a SQL engine? Do they kn…

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