Comment by thegabez | Hacker News Reader

Comment by thegabez | Hacker News Reader

Parent Full thread

Major enhancements in PostgreSQL 11 include:

* Improvements to partitioning functionality, including:

-- Add support for partitioning by a hash key

-- Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables

-- Allow creation of a “default” partition for storing data that does not match any of the remaining partitions

-- UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions

-- Improve SELECT performance through enhanced partition elimination strategies during query planning and execution

* Improvements to parallelism, including:

-- CREATE INDEX can now use parallel processing while building a B-tree index

-- Parallelization is now possible in CREATE TABLE ... AS, CREATE MATERIALIZED VIEW, and certain queries using UNION

-- Parallelized hash joins and parallelized sequential scans now perform better

* SQL stored procedures that support embedded transactions

* Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions

* Window functions now support all framing options shown in the SQL:2011 standard, including RANGE distance PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options

* Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX

* Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default

https://www.postgresql.org/docs/11/static/release-11.html

small, but useful feature is that you can now use keywords quit or exit to exit the psql command line. Helpful change for those of us who forget that the only current command is "\q".

EDIT: ctrl+D also works, just be careful not to press twice, since doing so will exit psql and then exit the shell altogether

In bash, you can use `set -o ignoreeof` for your interative shells to not trigger logout upon receiving EOF/^D.

Thanks, that's super useful! I found myself accidentally quitting terminal multiple times by pressing Ctrl+D one too many times.

damn thats really helpful, thank you

Thanks!

Does ^D to send EOF not work like most programs?

It does, I think this is more aimed at novice shell users.

Vim should learn from psql, not even ctrl+d works there.

Sorry but I think this is a frivolous feature.

Standardizing commands and making PG more user-friendly isn't frivolous. Usability is one of the most important qualities of a good program or library.

I think this is why MySQL had such success compared to PgSQL in the past - compare "show tables" with "\d".

I've been using a terminal since the Minix days.

I honestly had never heard of the Ctrl-D escape sequence until a few weeks ago. So having common exit/quit commands makes life a lot easier at least for some of us.

Ctrl+D works for me. :)

Does anyone know if a plan to enable JIT by default exists? I would assume they made it optional to ease it in in case there's any baby problems?

Generally speaking yes, but it's not clear when exactly that will happen (if at all).

Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet.

Secondly, we need to improve the costing model, so that we make better decisions when to use JIT for a query. AFAIK this is the #1 reason why it's not enabled on JIT-enabled builds now.

> Generally speaking yes, but it's not clear when exactly that will happen (if at all).

It's still enabled by default in 12/master. I'd expect it to stay that way. But reports for v11 will obviously influence that.

> Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet.

I see very very little point in adding another JIT provider to core pg. If somebody wants to write an extension that does so, ok, go ahead, but why would we maintain two nontrivial pieces of code in core? Also the GCC based stuff isn't even close to being competitive.

> Secondly, we need to improve the costing model, so that we make better decisions when to use JIT for a query. AFAIK this is the #1 reason why it's not enabled on JIT-enabled builds now.

Right.

> > Generally speaking yes, but it's not clear when exactly that will happen (if at all). > > It's still enabled by default in 12/master. I'd expect it to stay that way. But reports for v11 will obviously influence that.

Sure, but that's a development version, and I'm not sure it'll stay like that at the time of release. I hope it will, but my understanding is it's enabled mostly to get exposure from the developers by default.

>> Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet. > > I see very very little point in adding another JIT provider to core pg. If somebody wants to write an extension that does so, ok, go ahead, but why would we maintain two nontrivial pieces of code in core? Also the GCC based stuff isn't even close to being competitive.

Yeah, unfortunately the state of JIT in gcc is a bit sad :-(

> > > Generally speaking yes, but it's not clear when exactly that will happen (if at all).

> > It's still enabled by default in 12/master. I'd expect it to stay that way. But reports for v11 will obviously influence that.

> Sure, but that's a development version, and I'm not sure it'll stay like that at the time of release. I hope it will, but my understanding is it's enabled mostly to get exposure from the developers by default.

Its the stated intent to have it on in 12. We'd pondered disabling JIT in 11 since before the original commit. I rockthic think this is broadly analogous what we did with parallelism (except we won't forget to document the change, as we already noted it in the 12 notesnotes ;)).

Well, stated intent is great, but it's not the same as certainty. Don't get me wrong - I hope we end up enabling it in 12. But underpromise + overdeliver ;-)

BTW what's "12 notesnotes"?

> BTW what's "12 notesnotes"?

Release notes. To avoid a repeat of the mishap where we enabled parallel query by default in 10, but didn't mention it because it was always kept enabled in master, we've already mentioned it in release-12.sgml.

Oh! I honestly haven't realized we already have release notes for 12 ...

It's more like a note, singular ;)

Right. It will probably be enabled by default in PostgreSQL 12.

I wish PostgreSQL had real clustered index like MS SQL and MySQL, not a readonly version that has to be rebuilt after mutation.

Effectively you can now do this; a PK index + INCLUDE all other columns in the table. This will be kept upto date without any need for manual reclustering.

Sure it's duplicating the table data, but it's functionally equivalent to the reclustering or rematerialising a view of the table methods that people use now, just without the hassle.

That's a lot worse performance-wise than a clustered index that is at the same time the primary ordering for the table.

True, but MSSQL style clustering has it's own performance trade-offs in terms of slower writes, fillfactor bloat etc.

It's not going to be slower vs an unordered heap + a separate index that includes all the columns. Hence MS SQL implementation is superior. If you don't want a PK, then MS SQL offers you an undordered option.

The partitioning support may now be good enough to rid myself of pg_partman (No offense to the author, great extension, but I'd much prefer not having the dependency).

Figuring out how to convert a partitioning scheme created using pg_partman to native Postgres 11 won't be fun.

Reply on news.ycombinator.com