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

71–80 of 134 posts

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

#71

Post by sanderjd »

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…

Oh wow I totally disagree. I have always found the computer science usage of "orthogonal" to be very analogous to orthogonal sets in linear algebra. It is only in two dimensions that it boils down to right angles; That's the uninteresting case! The more general concept is one of independence, of something being broken down into its constituent parts, such that each part is pulling its weight in some way that even all the other parts combined could not. Which is exactly how I see it being used here. I still remember the moment my programming languages professor introduced the concept of orthogonality in that field; I intuitively grasped the meaning and was awed by that power of analogy.

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

#72

Post by PuercoPop »

greenyoda wrote: post #17
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. 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 da…

> 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

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

#73

Post by PuercoPop »

dmoreno wrote: post #36
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 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.

> 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

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

#74

Post by suj1th »

dmoreno wrote: post #25

I recently created a database engine (exosql [1]), only query and no storage. It uses postgres-like foreign data wrappers to get all data. It's not valid for big datasets, as it stores all in memory (or maybe it is?), but as a learning experience has been amazing to think and develop a real database: planner, executor, choose algorithms, implement features as lateral joins and so on. I will definetly listen very care…

A similar learning experience for me was when I was exploring Apache Calcite. That again is only query, and no storage. It has a concept of 'adapters' which, I assume, is similar to the postgres-like foreign data wrappers you mention.

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

#76

Post by reilly3000 »

logicallee wrote: post #28

Any video filter experts here? Request to any video filter expert ------------------------------------ I started watching this. The slides are unreadable but the camera is perfectly still and the slides are for several "key frames" where the compression algorithm decides to replace one set of compression artifacts for another. For example try to read the first keyword under "Translates into:": https://www.youtube.com…

There was a similar issue with the detection of text at angles faced when trying to decipher if the first lady was wearing jacket with a dog whistle insensitive message on it. The original source image was at an angle, so determining its authenticity was challenging until other images emgerged.

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

#77

Post by gaius »

zzzcpan wrote: post #29
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 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.

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.

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

#79

Post by logicallee »

peterwwillis wrote: post #64
logicallee wrote: post #28

Any video filter experts here? Request to any video filter expert ------------------------------------ I started watching this. The slides are unreadable but the camera is perfectly still and the slides are for several "key frames" where the compression algorithm decides to replace one set of compression artifacts for another. For example try to read the first keyword under "Translates into:": https://www.youtube.com…

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 if they would put the video through their setup (since the last paper update was just 3 months ago) and share their results.

2. Trying it myself...

After my downvotes I tried this small piece of software:

http://www.infognition.com/VideoEnhancer/

Which shows a before/after. Here is their page on their super-resolution algorithm:

http://www.infognition.com/articles/what_is_super_resolution...

I used their plugin on virtualdub on a sample of the video. The results weren't useable. Here is a picture which shows the before and after:

https://imgur.com/a/0rhy7q7

(The diagonal lines are a watermark because I didn't pay to register video enhancer.) Also note that though it might look like a sharpen mask was applied, in fact it was not: this is just the superresolution that video enhancer came up with.

Now granted I don't think that this particular site uses state of the art algorithms (its references on the page I linked are decades old) but it's the first one I found.

The site also has a page explaining when it doesn't work:

http://www.infognition.com/articles/when_super_resolution_do...

It specifically calls out "If your video is compressed to a low bitrate, in many cases this is very bad for super-resolution."

This certainly seems to be the case here. On my comparison picture above you can see that it certainly is an improvement, it is just not enough. I still can't read most of the lines. I think this also doesn't use as many keyframes as it could. (Which makes sense - it is rare that a rare static image is up for, in this case, 25 full seconds!)

There are at least 14 full keyframes there so I think there is more detail to be extracted, but it would, obviously, take longer analysis. I'll let you know if I find anything better or get an answer from the paper authors.

Post reply on HN