Previous HN discussion on the beta (<a href="https://news.ycombinator.com/item?id=17144221" rel="nofollow">https://news.ycombinator.com/item?id=17144221</a>) and pre-releases (<a href="https://news.ycombinator.com/item?id=18043425" rel="nofollow">https://news.ycombinator.com/item?id=18043425</a>).
Major enhancements in PostgreSQL 11 include:<p>* Improvements to partitioning functionality, including:<p>-- Add support for partitioning by a hash key<p>-- Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables<p>-- Allow creation of a “default” partition for storing data that does not match any of the remaining partitions<p>-- UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions<p>-- Improve SELECT performance through enhanced partition elimination strategies during query planning and execution<p>* Improvements to parallelism, including:<p>-- CREATE INDEX can now use parallel processing while building a B-tree index<p>-- Parallelization is now possible in CREATE TABLE ... AS, CREATE MATERIALIZED VIEW, and certain queries using UNION<p>-- Parallelized hash joins and parallelized sequential scans now perform better<p>* SQL stored procedures that support embedded transactions<p>* Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions<p>* Window functions now support all framing options shown in the SQL:2011 standard, including RANGE distance PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options<p>* Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX<p>* Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default<p><a href="https://www.postgresql.org/docs/11/static/release-11.html" rel="nofollow">https://www.postgresql.org/docs/11/static/release-11.html</a>
Anyone has an idea on what makes postgresql both such a robust and always evolving db ?
i’m amazed at the pace at which they’re adding deep features ( such as jit) without breaking anything release after release, in an open source environment.<p>Usually this kind of things are the consequences of a good architecture but i wonder if anyone has an more in-depth explanation on what in the architecture makes it that good.
<i>The inclusion of the keywords "quit" and "exit" in the PostgreSQL command-line interface to help make it easier to leave the command-line tool</i><p>YES, at last. This beats parallel queries any day.
I know we got 11 just today, but I can't wait for 12 already! Rumours about alternative storage systems are extremely exciting, the idea of having a columnar materialised view (just guessing, not sure what the actual implementation will be like).<p>I can't wait to throw out all the columnar databases and the ETLs I have to support them, all just for a few queries.
Maybe I'm just spoiled from how fast they implement all these features but I wish that, in the near future, table inheritance would also get some love. In its current state, inheritance needs a lot of manual checks[0]:<p>> unique, primary key, and foreign key constraints are not inherited.<p>[0]: <a href="https://www.postgresql.org/docs/11/static/ddl-inherit.html" rel="nofollow">https://www.postgresql.org/docs/11/static/ddl-inherit.html</a>
I'm very excited about the JIT query compilation stuff finally making it into postgres! A big win for OLAP queries, although it looks like they compile specific expressions within the query rather than the whole query operator, but that could change. I'm very curious to see how this interacts with the query planner (e.g. when it's worth the overhead or not).
PostgreSQL has been such a joy to use for both hobby and professional projects. It's so reliable. I wonder when Heroku will support 11 and I'm curious if upgrading from 10 to 11 with heroku using the simple pg_upgrade would break anything. If it's just a pure performance increase, I'm looking forward to doing it to a few production applications.
Piggybacking for a Postgres question: I have thousands of legacy software instances all around the world, running Postgres with very marginal internet connections (think IoT). I’d like to continuously get all their data into my cloud. Should I use Postgres built in async replication for that?
Congratulations and big thanks to PG community & Contributors.
It has been my choice for all kinds of systems, OLTP, OLAP and Data warehousing. Its just get better with PG 11<p>I would love to see native column store format, distributed sharding support and in memory tables in future releases.
Does anybody know where I can find an example of hash partitioning? I see it mentioned here in the docs but am having trouble finding an example: <a href="https://www.postgresql.org/docs/11/static/ddl-partitioning.html" rel="nofollow">https://www.postgresql.org/docs/11/static/ddl-partitioning.h...</a><p>edit: Found an example here: <a href="https://pgdash.io/blog/partition-postgres-11.html" rel="nofollow">https://pgdash.io/blog/partition-postgres-11.html</a><p>Followup question: Is there a way to re-partition? Say, if your data grows and you want to split the data up further?
Looks like a great release even to a Postgres noob like myself. Only just recently started using Postgres but I really enjoy it and it inspires me to dust of my database skills and learn more SQL.<p>I'd like to take the opportunity and ask if anyone has any recommendations or "must have" settings to the Postgres-terminal. Preferably on MacOSX.<p>For I found the formatting acting kind of weird in some cases when selecting multiple columns or writing long queries. I figure there must be some things you setup at the start and then can not live without...<p>Any other tips are also welcome! Thanks