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

Show HN: PRQL in PostgreSQL

github.com

Post reply on HN
Print view View original

61–70 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#61

Post by jpgvm »

CuriouslyC wrote: post #60
jpgvm wrote: post #59

Earlier quoted context omitted.

One issue with functions though is that they can change performance in unpredictable ways. For example a colleague of mine recently altered a function I had written that was used in multiple hot-path queries. The change he made accidentally caused the function to no longer be inline-able on PostgreSQL. Once the function couldn't be inlined then the PostgreSQL planner wasn't able to select the appropriate indices and…

If a function is marked stable it should not impact the query plan at all, since stable functions are essentially in-lined before planning. If logic is unstable a view is probably going to be a better abstraction than a function.

Well I thought so but even with the functions marked IMMUTABLE, which is even more stringent than STABLE the in-lining was not successful, this was apparent in the query plan.

This might be a special case however as the function called another function internally (also IMMUTABLE) which was essentially memoized using an expression index. This is the index that was no-longer hit when inlining failed.

If you think this is bug I think I can create a minimal reproduction.

Re: Show HN: PRQL in PostgreSQL

#62

Post by ttfkam »

haspok wrote: post #32
CuriouslyC wrote: post #23

Earlier quoted context omitted.

Database agnosticism is so 2010. There's very little reason to choose a DB other than postgres, and if you have a reason to choose a specific niche db you're not probably not going to be migrating away from it any time soon. CTEs are a first step in structuring queries to make them decomposable. You can extract CTEs to functions and mark them stable and it's logically equivalent to the original query.

> There's very little reason to choose a DB other than postgres Sure, if you are a startup, or write your own code. But for most people the choice of database(s) is a given, and they are not in a position to challenge that. At the end of the day, Oracle has to make a living, too...

From parent:

> you're not probably not going to be migrating away from it any time soon.

Oracle has its own optimizations and foot-guns that extend well beyond what you can represent in a database-agnostic API. And once you're on that DB, you can write DB-agnostic and have performance be relatively horrible or require a careful rewrite of your schema and stored procedures when you migrate. There is not door number three.

Writing a common layer for any and all relational databases is like using a Java UI library for all operating systems. Sure, it will work, but it will have obvious shortfalls, be immediately recognizable as such to anyone familiar with the underlying platform, be inconsistent with other apps on that platform, and leave any opportunities for efficiency and performance on the floor.

Say you want a pivot table. In Oracle and MS SQL, it's built in. In Postgres, it's possible but noticeably more annoying. In MySQL, it's simply not possible. How would you represent this in a database-agnostic way? And yet performing in the app layer is very much slower/less efficient.

Did you know Oracle supports parallel DML for enhanced performance and lower multi-query latency? You have to intentionally use though, and neither Postgres nor MySQL support it at all.

What about global temporary tables? Those especially aren't found in Postgres or MySQL and are not easily swapped into the app layer without a massive performance penalty.

Per-user namespaces are yet another Oracle-ism that just doesn't translate to other DB engines, but you definitely should know about.

If you're making a living from Oracle, earn your pay. Make the most of what you've got.

Re: Show HN: PRQL in PostgreSQL

#63

Post by da_chicken »

hans_castorp wrote: post #9
snthpy wrote: post #7

Earlier quoted context omitted.

This comes up every time PRQL makes it onto HackerNews and is a fair question: Short answer: DX Slightly longer answer: Developer productivity and experience, especially for EDA and interactively writing complex analytical queries. Most people that have tried PRQL just find it more convenient to write their analytical queries in it. PRQL compiles to SQL so it can't express anything you can't already do in SQL, but yo…

> How long would it take you to write the SQL for that? select distinct on (album_id) * from tracks order by album_id, milliseconds desc;

Eh, `DISTICT ON` is a custom PostgreSQL extension.

A standard* method would be:

    SELECT *
    FROM tracks
    QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1;
But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using:

    SELECT *
    FROM (
      SELECT *
        ,row_number() over (partition by album_id order by milliseconds desc) rn
      FROM tracks
      ) x
    WHERE x.rn = 1;
That said, I still don't think PRQL is particularly amazing. I can't tell if it's merely syntactic sugar for SQL, or if it's actually meant to control query execution. If it's the former, it's likely to frustrate developers because it's actually just another layer of abstraction. If it's the latter, then it requires the developer to not only understand the data model well enough to be able to write SQL queries, they need to be able to understand the RDBMS impementation details well enough to be able to write queries that best take advantage of the current database's indexes, statistics, and configuration. Even something as simple as sorting before filtering or projecting can be a significant performance issue. Nevermind the fact that relational algebra done in the wrong order can be non-deterministic or not equivalent transformations, so even if the query processor is smart enough to do rewrites whatever the developer enters might be logically different unintentionally.

Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown.

*: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.

Re: Show HN: PRQL in PostgreSQL

#64

Post by da_chicken »

orlp wrote: post #45
gigatexal wrote: post #22

Earlier quoted context omitted.

select id from tracks qualify row_number() over partition by (album_id order by milliseconds desc) =1 That should work Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive. Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.

QUALIFY is not part of the SQL standard.

I'm pretty sure QUALIFY was added in SQL 2023. Maybe it was only discussed and didn't make it.

Re: Show HN: PRQL in PostgreSQL

#65

Post by ttfkam »

andix wrote: post #44
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

Because SQL can be cumbersome to write. It's often repetitive, requires nesting, aliases, and a specific order of statements.

1. Notice language is complicated for some tasks

2. Propose newer, simpler language to take care of these

3. Newer, simpler language lacks features of original language

4. Newer language adds features, making it more complicated

5. GOTO 1

Re: Show HN: PRQL in PostgreSQL

#66

Post by lowercased »

quesera wrote: post #40
GordonS wrote: post #13

Earlier quoted context omitted.

> How long would it take you to write the SQL for that? I don't want to appear rude, but unless I'm missing something, this is a pretty simple SQL query, of the kind anyone with mimimal SQL experience could write off the top of their head in seconds. I like the idea of PRQL, but I think a better example is needed to sell it.

I think the logic here is: SQL is hard and most people don't know it well. So PRQL is perhaps easier to learn. The same logic is applied to TypeScript vs JavaScript. Or C vs assembly. Or Nano vs vim. Etc etc. It's a quandary though. SQL is clearly difficult for most people to get their heads around. It does require a different way of thinking about data, and you can get by with a minimal SQL knowledge for a long time…

> making the investment is worthwhile and pays off in small and large ways

And... 'making the investment' takes time, and means that time is not able to be invested someplace else.

If the majority of your job is writing SQL or similar (data access, etc) then sure - yes, learn more of those tools. Some folks have a wider range of responsibilities that means you have to decide what to make more time investments in, and saying 'yes' to something is necessarily saying 'no' to other things.

Re: Show HN: PRQL in PostgreSQL

#67

Post by Terretta »

quesera wrote: post #35
throwaway143829 wrote: post #29

Earlier quoted context omitted.

HN has some really non-obvious UX, between this and the mysterious green usernames I still don't understand. And the fact that only some users can downvote. Or the weird logic behind which words cannot appear in titles.

Green usernames are new accounts. Only users with 500 karma can downvote. Both are mechanisms to dull the potency of new users until they have a chance to learn how HN is expected to work. This might sound gatekeeping, and it literally is, but consider than HN signup is open and takes 15 seconds with no verification. HN likes the way HN works and these provide simple rate-limits on destructive or oblivious change.

> this might sound gatekeeping, and it literally is

That term presumes unkept gates are preferable.

I grew up farming. One keeps one's gates or both wildlife and livestock run amuck.

Re: Show HN: PRQL in PostgreSQL

#68

Post by beeboobaa »

snthpy wrote: post #7
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

This comes up every time PRQL makes it onto HackerNews and is a fair question: Short answer: DX Slightly longer answer: Developer productivity and experience, especially for EDA and interactively writing complex analytical queries. Most people that have tried PRQL just find it more convenient to write their analytical queries in it. PRQL compiles to SQL so it can't express anything you can't already do in SQL, but yo…

Won't this just lead to developers wrecking performance because they don't understand what's happening?

Re: Show HN: PRQL in PostgreSQL

#70

Post by canadiantim »

fforflo wrote: post #51

Nice work. A few months back, I experimented with having a DSL like PRQL in Postgres, but back then, I found the language a bit cumbersome; however, it was great as an idea. IMHO, the best "data transformation" language is jq and awk is second. PRQL and EdgeQL (EdgeDB) are the most interesting ones to watch how they evolve, though. I've also written a PG extension to make jq available in Postgres [0] I believe Postgr…

Would love to see EdgeQL become adopted beyond EdgeDB. I don't like the vendor lock-in with EdgeDB, but I think they're doing great work
Post reply on HN