6 ms·
Major enhancements in PostgreSQL 11 include: * Improvements to partitioning functionality, including: -- Add support for partitioning by a hash key -- Add su
by thegabez 8y ago
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 https://www.postgresql.org/docs/11/static/release-11.html
- chadash 8y agosmall, 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
- aquabeagle 8y agoDoes ^D to send EOF not work like most programs?
- hultner 8y agoIt does, I think this is more aimed at novice shell users.
- beatgammit 8y agoCtrl+D works for me. :)
- c0l0 8y agoIn bash, you can use `set -o ignoreeof` for your interative shells to not trigger logout upon receiving EOF/^D.
- bartlomieju 8y agoThanks, that's super useful! I found myself accidentally quitting terminal multiple times by pressing Ctrl+D one too many times.
- BorisMelnik 8y agodamn thats really helpful, thank you
- millerm 8y agoThanks!
- vladsanchez 8y agoSorry but I think this is a frivolous feature.
- smt88 8y agoStandardizing commands and making PG more user-friendly isn't frivolous. Usability is one of the most important qualities of a good program or library.
- Drdrdrq 8y agoI think this is why MySQL had such success compared to PgSQL in the past - compare "show tables" with "\d".
- threeseed 8y agoI'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.
- Apaec 8y agoVim should learn from psql, not even ctrl+d works there.
- hultner 8y agoDoes 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?
- petereisentraut 8y agoRight. It will probably be enabled by default in PostgreSQL 12.
- pgaddict 8y agoGenerally 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.
- anarazel 8y ago> 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.
- pgaddict 8y ago> > 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 :-(
- polskibus 8y agoI wish PostgreSQL had real clustered index like MS SQL and MySQL, not a readonly version that has to be rebuilt after mutation.
- adwf 8y agoEffectively 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.
- polskibus 8y agoThat's a lot worse performance-wise than a clustered index that is at the same time the primary ordering for the table.
- adwf 8y agoTrue, but MSSQL style clustering has it's own performance trade-offs in terms of slower writes, fillfactor bloat etc.
- polskibus 8y agoIt'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.
- jcadam 8y agoThe 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.