PostgreSQL Begins Landing LLVM JIT Support For Faster Performance

[wizard69]

One has to wonder how much further the FOSS ecosystem

LLVM is part of the FOSS community!

would have progressed if GCC had a modular codebase and a different licence.

The GPL is an absolutely evil license especially in the context of software developed by large groups of people. One of the good things that the LLVM/Clang community did was to force people responsible for GCC to improve GCC.

While GCC may emit better code no one else can use the technology.

GCC doesn't universally emit better code. In any event CLang most certainly stimulated many improvements to GCC especially in code generation and error reporting. Friendly competition is a good thing and it is my understanding that the two teams are on good terms. The reality is LLVM/CLang has been a positive impact on GCC.

As for the issue of using GCC in other projects one should not assume that an advantage of LLVM is something GCC needs. We don't need two look a like compilers in this world. Rather the community is much better off having technology explored on two different tracks. This can be likened to new computer languages like D, Swift, Rust and others trying to become the ideal language for future development. In the end it is all good.

[oiaohm]

Is the data type nvarchar part of the SQL standard? Or is it a proprietary data type?

PostgreSQL does not support the nvarchar data type, however all columns are Unicode anyways. But it means SQL scripts does not work and cant be imported without modifying the script and changing nvarchar to varchar. 😢

If you get the SQL standard from ISO you will find that not even varchar is part of the standard let alone nvarchar. The long multi word descriptions of those nvarchar and varchar are part of the standard. So nvarchar in standard is NATIONAL CHARACTER VARYING. Next part by standard what format is NATIONAL CHARACTER VARYING is totally up to who ever is implementing database and is totally optional if it implemented at all.

Yes even the CHARACTER VARYING(varchar) contents are not solid defined by standard. Yes this is also optional to implement for standard conforming implementation.

So changing databases you always do need to double check if their implementation of character variables match what you expect because the SQL standard is many forms of grey on the topic. There are a lot of things people presume are part of the SQL standard that are not.

The SQL:2016 standard added support for JSON. PostgreSQL have JSON support, but its non-standard. 😢

I hope you like Oracle Database as that is the only one with so call standard JSON. Please note Postgresql does not even get a seat at the standard table so Postgresql differences to way JSON is done to get best performance on the Postgresql back end did not get into the standard as allowed.

PostgreSQL scatters binaries all over my /usr/bin/ directory. 😢

That is a distribution packaging choice.not something postgresql built from source mandates. People forget you can embedded postgresql in applications.

[uid313]

If you get the SQL standard from ISO you will find that not even varchar is part of the standard let alone nvarchar. The long multi word descriptions of those nvarchar and varchar are part of the standard. So nvarchar in standard is NATIONAL CHARACTER VARYING. Next part by standard what format is NATIONAL CHARACTER VARYING is totally up to who ever is implementing database and is totally optional if it implemented at all.

Yes even the CHARACTER VARYING(varchar) contents are not solid defined by standard. Yes this is also optional to implement for standard conforming implementation.

So changing databases you always do need to double check if their implementation of character variables match what you expect because the SQL standard is many forms of grey on the topic. There are a lot of things people presume are part of the SQL standard that are not.

Thank you for for your informative reply!

The more I learn about SQL the more I hate it. I really wish it was better standardized.

When I imagine how bad SQL it is, then I find out it is even worse.

[fuzz]

The GPL is an absolutely evil license

The GPL is the best legal idea in the modern world. It ensures the only important unalienable right one needs: freedom.

BSD ideas are nice in theory, but GPL helps keeps capitalist ideology from ruining software. Capitalist douche bags dislike that... whatever, go back to your Mac and suck one.

[bpetty]

I am totally sold on PostgreSQL. I think it is the best open source database out there. Its locking implementation is better than MySQL's so it can be used in larger enterprise software without fear of dead-locking all over itself. It is also the option everyone I know of goes for that uses RDB in the cloud since SQL Server is too costly.

As for non standard JSON, PostreSQL had JSON support long before SQL Server... and I think it is implemented much, much better.

From a DBA standpoint PostgreSQL was/is lacking in some fundamental areas in terms of database recovery and the like.
From an implementation perspective, they went the old Oracle route of how they spawn off threads per connection. I also wish they were designed more like MySQL, being able to plugin different database types into the same framework. Seems pretty slick. If Oracle didn't keep its thumb on MySQL, making sure it stays a web-site only database, it could have a real chance too.

There was a company project posted on Phoronix a year or two ago that combined PostgreSQL with OpenCL or something to massively parallelize some of the calculation work within the engine. I wish they would have contributed that, but I realize people need to make a living.

[pal666]

One of the good things that the LLVM/Clang community did was to force people responsible for GCC to improve GCC.

what are you trying to say, that gcc was not improving before clang(i.e. it existed in unchanging state since 1989), or that it would stop improving if clang wouldn't come out and you checked this is true by travelling to parallel universe?

[pal666]

The GPL is an absolutely evil license especially in the context of software developed by large groups of people.

it is only evil for people who want to steal gpled code. for everyone else it is not evil

[oiaohm]

From a DBA standpoint PostgreSQL was/is lacking in some fundamental areas in terms of database recovery and the like.

Database recovery is a little more complex than that.
https://www.2ndquadrant.com/en/resources/barman/ postgresql with barman is very dependable. Barman does not extend postgresql just uses the built in functionality. Even recovery from databases damaged due to bad harddrive postgresql is good at.

There is one universal problem the core engine of postgresql has all the support for great database recovery just no good GUI and times no good TUI to use it included in the default package so people incorrectly presume postgresql is missing this.

From an implementation perspective, they went the old Oracle route of how they spawn off threads per connection. I also wish they were designed more like MySQL, being able to plugin different database types into the same framework. Seems pretty slick. If Oracle didn't keep its thumb on MySQL, making sure it stays a web-site only database, it could have a real chance too.

https://wiki.postgresql.org/wiki/Foreign_data_wrappers The foreign data wrappers covers a lot of usage cases its part of the reason why plugin storage has drifted around as a possible idea without development force. Fairly much everything that can be done with the foreign data wrappers is.

https://wiki.postgresql.org/wiki/FOSDEM/PGDay_2018_Developer_Meeting

Yes Pluggable storage was voted for at the last postgresql developer meeting. Of course that leave Alexander Korotkov with the hard job of how it will be implemented. So if you have any particular requirements speak up now to Alexander or risk being stuck with implementation that does not suite.

There was a company project posted on Phoronix a year or two ago that combined PostgreSQL with OpenCL or something to massively parallelize some of the calculation work within the engine. I wish they would have contributed that, but I realize people need to make a living.

https://github.com/heterodb/pg-strom

They have ended up implementing using CUDA so Nvidia dependant for now. But it shows GPU acceleration is down right possible.

Merging mainline is a very hard process with Postgresql.

[Delgarde]

The more I learn about SQL the more I hate it. I really wish it was better standardized.

When I imagine how bad SQL it is, then I find out it is even worse.

Yep. My pet peeve is that unlike every other RDBMS I've used, Oracle doesn't support smallint/integer types (i.e 16-bit and 32-bit integers) - the closest match is to use a number(5) or number(10) instead, but that's a pain because it results in a mismatch between the database type and the variables used in applications which interface with it. And so a field that started out as a 32-bit integer in the code becomes a 10-digit number in the database, and then some other coder decides that because a 10-digit number won't fit into a 32-bit integer, he needs to use 64-bit integers to read the data.

[starshipeleven]

The GPL is an absolutely evil license especially in the context of software developed by large groups of people.

Nice personal opinion you have there.