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

41–50 of 134 posts

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

#41

Post by jacquesm »

tobyhinloopen wrote: post #20
1stranger wrote: post #16

Earlier quoted context omitted.

How do you prefer to persist your data?

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

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.

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

#42

Post by wvenable »

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.

> You should be able to dictate the query plan to the engine directly.

That's like arguing you should be able to dictate the assembly that your compiler produces. The entire point of SQL (and most compilers) is that they can use their knowledge to optimize the result in ways that are too difficult or too involved for humans to do. Most people cannot out-optimize a compiler in the general case. And most people cannot out-optimize a SQL DBMS.

Also, with SQL, the result might be highly dependent on the data itself. A table with 1,000 rows yesterday might be queried entirely differently from the same table now with 100,000 rows. Are you going to constantly go back and dictate the query plan to the engine every few months as the data changes? Probably not. Use the tool as intended and you'll be fine. Anything else is premature optimization at best.

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

#43

Post by barrkel »

blattimwind wrote: post #13
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…

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 amongst the most complex systems you will ever use as a developer. At the limit, they rival operating systems for complexity - distributed concurrent systems with lots of low-level memory and filesystem action, along with a parser, optimizing compiler and often a code generator too.

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

#44

Post by bpicolo »

barrkel wrote: post #43
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 amongst the most complex systems you will ever use as a developer. At the limit, they rival operating systems for complexity - distributed concurrent systems with lots of low-level memory and filesystem action, along with a parser, optimizing compiler and often a code generator too.

The performance characteristics of them, though, are easy to learn, and easier to get a grasp on via EXPLAIN and such.

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

#46

Post by PretzelFisch »

pipu wrote: post #7

I truly recommend CMU's Andy Pavlov's video lectures on the topic (and also more advanced stuff) https://www.youtube.com/playlist?list=PLSE8ODhjZXjYutVzTeAds...

I found Prof. Dr. Jens Dittrich database playlists interesting and pleasant to watch. https://www.youtube.com/channel/UCC9zrtAkl6yY4dpcnWrCHjA

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

#47

Post by prudhvis »

ryanworl wrote: post #3

There is a more recent lecture on the same topic from 2015 at CMU: https://youtu.be/gpxnbly9bz4

At 34:30 mark he goes on to say that there are some buggy implementations for mmap. Is that in anyway related to how Linux handles pages marked as free?

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

#49

Post by GhostlyHacker »

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.

You can force a certain query plan in SQL Server’s newer version(https://www.red-gate.com/simple-talk/sql/database-administra...) but most of the time query plans won’t change unless the statistics or cardinality of the data changes. You control both of those things.

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

#50

Post by ebikelaw »

purerandomness wrote: post #24
ebikelaw wrote: post #19

Earlier quoted context omitted.

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.

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 anyone who has had to use them under any significant load.

Post reply on HN