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

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

youtube.com

Post reply on HN
Print view View original

81–90 of 134 posts

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

#81

Post by wwweston »

blackrock wrote: post #68

I'm not going to call anyone out here, but why do people keep using the word orthogonal? It doesn't even compute. It doesn't even make any sense, in how they use it in relation to the topic. Are the issues at right angles of one another? No. Are the issues statistically independent of one another? Perhaps. I suggest to use a more appropriate descriptive word to describe the situation. You folks should read the urban…

What does it mean for something to be at a right angle to something else?

There's a euclidean geometric answer to that statement, but it's hardly the only correct answer.

When people use it to mean that they're speaking of two issues that have a range of independent possibilities, it's not wrong to invoke linearly independent bases.

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

#82

Post by abiox »

blackrock wrote: post #68

I'm not going to call anyone out here, but why do people keep using the word orthogonal? It doesn't even compute. It doesn't even make any sense, in how they use it in relation to the topic. Are the issues at right angles of one another? No. Are the issues statistically independent of one another? Perhaps. I suggest to use a more appropriate descriptive word to describe the situation. You folks should read the urban…

is english a second language for you? 'orthogonal' is frequently used to indicate two things are not directly related or dependent.

> You folks should read the urban meaning of orthogonal

nope, nope, nope. that site's a hive of scum and villainy, and a massive number of entries are just random nonsense.

i'd rather go to wiktionary[0], which includes:

"Of two or more problems or subjects, independent of or irrelevant to each other."

[0] https://en.wiktionary.org/wiki/orthogonal

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

#83

Post by pg314 »

nostalgeek wrote: post #69
ludsan wrote: post #10

Earlier quoted context omitted.

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

> 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?

"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

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

#84

Post by DreamSpinner »

mmt wrote: post #63
dagw wrote: post #26

Earlier quoted context omitted.

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…

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

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

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

#85

Post by AmericanChopper »

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…

I’ve never worked anywhere where I had to worry about DBAs running around dropping indexes. The main reasons not to build an index are usually storage and write overhead. Every index a table has means you have to do another write operation on every insert, which can really start to add up. They can also add significant overhead to any migration operation that happens to require an index rebuild.

In my experience, the most common reason for an optimizer choosing not to use an existing index, is out of date statistics. For those who aren’t aware, the database collects table statistics for things like cardinality, number of distinct values, etc... This is the information the optimizer uses when it’s building a plan. If they get out of date the optimizer will start to come up with nonsense plans. Even worse, if your stats get too out of date, you can become scared to update them, because a new set of stats can potentially change the plans built for every single query in ways that are hard to predict.

As others have stated, you can put index hints directly into your queries, but this should be avoided as they’re hard to maintain. Most ‘enterprise’ RBDMS also have some form of plan management, but this should be avoided even more, as managed plans permanently bypass the optimizer, which is even harder to maintain.

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

#86

Post by pg314 »

ebikelaw wrote: post #19
jnwatson wrote: post #9

Earlier quoted context omitted.

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.

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.

> As a runtime query system it is completely unsuitable.

Millions of users beg to differ.

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

#87

Post by collyw »

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…

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.

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

#88

Post by collyw »

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

If you are used to imperative programming it does take a bit of time before you get to thinking is sets. Looking at the code I have inherited, even some reasonably experienced developers don't get to that stage.

Do you really think that replacing an "ORDER BY" statement with your own sort is going to be simpler?

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

#89

Post by collyw »

purerandomness wrote: post #35
tobyhinloopen wrote: post #20

Earlier quoted context omitted.

In memory database, dumped to file? Not saying how I would do it, just thinking alternatives. I’m using postgresql usually

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

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

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

#90

Post by acqq »

logicallee wrote: post #79
peterwwillis wrote: post #64

Earlier quoted context omitted.

I'm sorry you got downvoted for this comment. HN voting is the worst.

Perhaps I was naive about the state of the art. After the now-dead reply I received, I searched, and found a couple of papers like this https://arxiv.org/abs/1801.04590 - "Frame-Recurrent Video Super-Resolution" but if you look at p. 8, I think many of the algorithms still wouldn't end up with readable text. This paper is from this year, so it is an area of active research. I wrote a quick mail to the authors to see…

It's surely interesting that you try to figure out the "state of art" but as far as I see the amount of information is too low to recover more readability, and that was my estimate even before your experiments. The way I saw it, the algorithm would have to model the "compression interference patterns" to do that, and even if something like that existed the amount of information seems to be far too low.

If it's really about the given talk and not exploring state of art in algorithms for recovering of the (textual?) information lost due to video compression, you'd be better off to communicate

1) with Dr Hipp who gave the talk -- he published the more recent original slides on the sqlite site:

https://www.sqlite.org/talks/

so it could be reasonable he'd be willing to publish these older slides (which he probably considers in some aspects outdated). Then only if that fails:

2) with the author of the video who possibly still has a higher quality version of the video, if the quality was dropped during the video compression or preparation for youtube, e.g. while trying to reduce bandwidth or reencode from the native recording format.

Post reply on HN