
PostgreSQL 11 and Just in Time Compilation of Queries - ScottWRobinson
https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/
======
minxomat
HPE did an excellent technical, no marketing BS writeup on PG11[0]. I highly
recommend everyone interested in the new features read this.

[0]
[https://h50146.www5.hpe.com/products/software/oe/linux/mains...](https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf)

~~~
thisgoodlife
Cool! Is there a similar doc for pg10? I'm still using 9.5. Thanks!

~~~
amitlan
There is:

PostgreSQL 10 New Features With Examples
[https://h50146.www5.hpe.com/products/software/oe/linux/mains...](https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_10_New_Features_en_20170522-1.pdf)

------
citilife
I'm curious if we will see similar improvements for full-text search. If
PostgreSQL continues to improve at this rate, there will be little reason to
use many of alternatives...

Personally, I already view PostgreSQL comparable (for many use cases) to paid
options[1].

Interesting, for queries such as:

    
    
       EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM sort1 ORDER BY 1) AS a LIMIT 5 ;
    

You'll see an order of magnitude speed improvement (page 47 of [2]).

[1] [https://austingwalters.com/fast-full-text-search-in-
postgres...](https://austingwalters.com/fast-full-text-search-in-postgresql/)

[2]
[https://h50146.www5.hpe.com/products/software/oe/linux/mains...](https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf)

~~~
SigmundA
Seriously if PG gets better text search to put it on par with Lucene (Rum
indexes, BM25 and TF*IDF) I don't see much reason to use anything else either.

~~~
b9b10eb736
From the top of my head, I can list at least four reasons to actually use
anything else:

\- Not distributable

\- Poor resiliency

\- Hard to upgrade

\- Schemas remains at postgres core

Every solution to address any of these points in PG are hacks (bucardo,
londist, slony, pgbouncer, things relying on triggers or proxies). Quite
honestly, PG contributors are doing an impressive job. PG is arguably the best
relational database in the market as of today and still improving very fast.
However it's also one of the most (if not the most) bloated. And despite all
these features nobody needs and after all these years, it's still lacking some
very basic yet essential items anyone would ask for in any modern application.
It feels like things haven't changed that much since the 90's. DBAs have to
hack around things and root issues never gets addressed. Implementing JIT
optimizations looks like fun. It will undoubtedly improve some queries by 2x,
4x or even 10x, but it won't be a game changer anyways. It'll just increase
the postgres bloat and the overall complexity of the system. Meanwhile, we'll
still be lacking essential things that would make PG suitable for pretty much
any use-case.

~~~
threeseed
Shame comments like this get downvoted. Because it's 100% true.

PostgreSQL still is lacking with its horizontal scalability story and there is
no reason to choose it over something like Solr/ElasticSearch right now
largely because of this.

~~~
digitalzombie
You're the reason why I inherit project that uses only ES and then end up
doing relational schema in ES because they didn't use relational database in
general.

If you had to choose between PostgreSQL or ElasticSearch then you have no idea
what you're doing.

~~~
mmt
I recently interviewed at a startup that explicitly uses both, with PG being
something of an authoritative backend store that feeds into ES as the primary
frontend store (since the frontend is search-heavy).

It might be _possible_ to get it to work without PG, but I imagine it would
require a huge engineering effort (akin to an entire product on its own,
which, IIRC, is what CrateDB is).

(Although I believe I understand your point, I fear that your tone and ad-
hominem language detracts from it).

------
jarym
Very exciting stuff happening in the PG world. One of the items I'm watching
is support for foreign keys in arrays -
[https://commitfest.postgresql.org/17/1252/](https://commitfest.postgresql.org/17/1252/)

Author is currently stuck and I hope someone with the right knowledge can help
him out so he can continue the work.

~~~
danharaj
Oh damn that'd be great. Similarly, foreign keys in json(b).

~~~
mlevental
why? so that you can simulate nosql hacks in an rdbms...?

~~~
joevandyk
Sometimes joins (and left joins) can be much more expensive than if the
foreign keys are in a single indexable column.

------
munk-a
It looks like (reading through the release notes and some not very recent
mailing list threads) that CTE optimization fences will not be removed in
PostgreSQL 11.

This makes me very sad.

~~~
jeltz
There is a work in progress patch
([https://commitfest.postgresql.org/19/1734/](https://commitfest.postgresql.org/19/1734/))
which you can help out with by reviewing and testing it.

~~~
netcraft
I wasnt aware this was being worked on. This is great news. Best case scenario
though this would come in 12?

~~~
anarazel
Right.

