Hacker News Reader

Comment by nicodjimenez | Hacker News Reader

"We Can Do Better Than SQL", sure, but how much better?

Fundamentally, SQL is a declarative language for databases, that makes the easy stuff easy, and the hard stuff doable.

If people want to build new front ends for SQL databases, to make it easier to build apps, I can buy that.

But trying to replace SQL as the standard declarative language for data is a fool's errand. You'd have better luck getting developers to switch away from git. People don't use SQL databases because they're fun for developers to hack on, they use them because they solve a business need.

If you want to innovate in the database space, I think you need to provide something that benefits end users (global availability, auto scaling, speed, client syncing ala Firebase, ...).

View on HN

Git is severely suboptimal, and I would hope we develop a better alternative in the near future. While the underlying model is sound, the interface is confusing and deeply inconsistent. Much like RA vs SQL.

> makes the easy stuff easy, and the hard stuff doable

I like SQL, but I disagree. It also makes easy stuff hard.

Given many numbers (billions), how would you find the 1st, 3rd and 5th highest values?

The SQL to do this is simple and straightforward using analytic functions (specifically nth_value); the performance is likely to suck hard if you don't have an appropriate index, but that's not an SQL problem, but a “sorting billions of numbers is expensive” problem.

  SELECT 
    nth_value(num, 1) OVER ORDER BY num, 
    nth_value(num, 3) OVER ORDER BY num, 
    nth_value(num, 5) OVER ORDER BY num
  FROM t

nth_value can be done in a faster way than sorting and picking. It can be done in O(n) while sorting is O(n log n).

Which is why PostgreSQL uses heap sort with a fixed max heap size for sort with a small limit (called "top-N heapsort" when running explain analyze). Then the complexity for getting the kth value is O(n log k) which is O(n).

Maybe I have done too much SQL, but for me it is trivial and easier to do than in most other languages. PostgreSQL will execute the query below in O(n) if there is no index and O(1) if there is an index on num.

Does any other language implement this in a better way? While also still giving a O(n) time complexity and O(1) space? I may have to implement my own top-n heapsort then, or my own top-n insertion sort.

    SELECT * FROM (
      SELECT
        row_number() AS n, num
      FROM t
      ORDER BY num DESC
      LIMIT 5
    ) top5 WHERE n IN (1, 3, 5);

This is assuming we do not care about ties. If we care about ties it gets a bit messier but not that bad.

In other languages, this task does not require a sort. It's just a for loop. The fact that you need a nested table and a sort illustrates my point about SQL making some easy problems hard (maybe harder is more accurate).

Using an index for access path and ranking for selection

Reply on news.ycombinator.com