
Discovering less-known Postgres v12 features - samsk
https://www.cybertec-postgresql.com/en/discovering-less-known-postgresql-12-features/
======
Deimorz
It was added in 11, but I want to mention again how great of an addition the
websearch_to_tsquery() function was. You can now get a respectable search
system immediately in PostgreSQL without having to implement any sort of
query-parsing or special data indexing yourself. All you need to do is add a
column/index for fulltext search, which you can even do now using the
generated columns added in 12 (instead of a trigger).

Then just pass user-entered queries through websearch_to_tsquery() and have
access to multiple search capabilities easily. It supports searching for
alternatives with "or", phrases in quotes, and excluding terms with a minus
sign, which is all syntax that a lot of people are already used to. For
example it can handle a query like:

    
    
        blizzard or overwatch -"hong kong"
    

That would find anything with "blizzard" or "overwatch" in it, but exclude
anything with "hong kong" (if you wanted to avoid results about that recent
controversy).

It's really useful, and makes it so that you can add reasonable search
functionality with almost no work at all.

~~~
ComputerGuru
That is really cool and definitely a massive timesaver (I've written my own
primitive query NLP for SQLite fstext indices and can definitely appreciate
this feature!)

That said, (and only because your example reminded me) I absolutely hate the
NLP `or` operator. I feel like any language to offer an `or` operator needs to
require the simultaneous usage of a grouping operator (parentheses, etc.) to
clarify the order of operations. A space is an implicit `and` so I never know
if the `or` is working on directly adjacent keywords or the query as a whole.

------
tbrock
Inlining CTEs is a big deal but they also finally have a way to see the
progress of an index build without a PhD in postgresology!

Check it out here: [https://www.postgresql.org/docs/12/progress-
reporting.html](https://www.postgresql.org/docs/12/progress-reporting.html)

~~~
paulddraper
My #1 request for indexes from PostgreSQL or 3rd party tool is concurrently
rebuilding indexes.

I have had several cases where query performance dropped significantly
(unfortunately in managed AWS Aurora DB, so I have limited inspection
abilities).

Index rebuilds is the solution but it locks the entire table.

You can get a concurrent rebuild by creating a new index concurrently, then
transactionally drop the old one and rename the new one. It's error prone
though.

~~~
jeltz
You will need to pick a new #1 request since PostgreSQL 12 finally delivered
REINDEX CONCURRENTLY. :)

~~~
paulddraper
!!!!!

Wonderful! (Now I only have to wait 12 months for AWS Aurora to upgrade.)

------
cdcarter
SQL/JSON path expressions look very powerful for making queries easier to
understand, they’re a part of the SQL spec I hadn’t seen yet.

I’m a little sad though to see yet another JSON location mini-language appear.
We’ve got JSONPointer (IETF standard for the web, used by jsonschema),
JSONPath, the more expressive but cross language JMESPath, the CLI favorite
jq, and presumably a billion more. It’s wearing me out.

------
macdice
One nice thing about the CTE inlining its that materialised CTEs were stopping
parallelism from being used. WITH is useful in big OLAP queries, where
parallelisms should really help.

~~~
megous
OTOH, it slowed down one of my queries from a few ms to 3s just by upgrading.
So it's not some magic absolute benefit.

I had to go and add "materialized" to a few places.

~~~
jeltz
Yeah, if the planner estimates are bad the inlining can produce a worse query
plan.

------
Boulth
Are there any good advanced SQL books out there?

~~~
purerandomness
I found Dimitri Fontaine's
[https://theartofpostgresql.com/](https://theartofpostgresql.com/) advanced.
Learned lots of new tricks out of it.

~~~
Boulth
Looks nice and it's on discount because of Black Friday. Thanks!

------
GordonS
Wasn't aware of log_transaction_sample_rate - should be useful for not
spamming the logs.

------
zonidjan
Site blocking the page with an empty overlay named "BorlabsCookie". No thanks.

~~~
tln
The cookie acceptance on this site is as good as it gets IMO. Clear, detailed,
privacy policies linked, with the ability to accept a reasonable, minimal
subset.

