17 ms·
PostgreSQL 11 Released
- Doches 8y agoPrevious HN discussion on the beta (https://news.ycombinator.com/item?id=17144221 https://news.ycombinator.com/item?id=17144221) and pre-releases (https://news.ycombinator.com/item?id=18043425 https://news.ycombinator.com/item?id=18043425).
- dang 8y agoAlso https://news.ycombinator.com/item?id=17119785 https://news.ycombinator.com/item?id=17119785, https://news.ycombinator.com/item?id=17864837 https://news.ycombinator.com/item?id=17864837, and https://news.ycombinator.com/item?id=18150727 https://news.ycombinator.com/item?id=18150727.
- flyinglizard 8y agoPiggybacking 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?
- _verandaguy 8y agoIf you added your design goals and constraints (do you want it to be fast? frequent? power-efficient? which ones can you trade off?), this would make a good question for the DBA stackexchange.
- jeltz 8y agoHard to say for sure without knowing more but I would probably build my own custom protocol for syncing this, because the replication (both binary and logical) is built with the assumption that you have a reasonably constant connection from the master to the replica. If not you will start to build up large amounts of write-ahead logs.
- Cthulhu_ 8y agoNever invent the wheel if you haven't done your research yet. Colleagues of mine successfully employed CouchDB in a situation where devices were offline for an x amount of time (running on tablets in airplanes), once they had internet again they would reliably start syncing data with the main database again. This was a number of years ago though, I haven't heard anything about CouchDB since.
- megous 8y agoIt works perfectly with intermittent connectivity, restarts, etc. That will not be a problem. I'm not sure about thousands of nodes syncing to a single replica though. That may be a bit over the top.
- pgaddict 8y agoWho says you need to feed it directly to the replica. Logical replication provides infrastructure for decoding changes, and you may fetch it any way you want / how often you want / feed it wherever you want (file, another database, ...). It would be trivial to write something that connects to a bunch of machines regularly, fetches the decoded increment and feed it somewhere (say, to a single database over a shared connection). See test_decoding / pg_recvlogical for examples.
- pgaddict 8y agoYou need to accumulate the data somewhere. If you don't fetch if from the node, it has to accumulate there. Custom sync protocol does not eliminate this.
- sroussey 8y agoMight look into https://gun.eco/ https://gun.eco/
- ibotty 8y agoI would occasionally send the WAL-log from the client to the cloud. pg_basebackup, etc.
- Heliosmaster 8y agoCongratulations for the release! PostgreSQL is, imho, a great example of how open-source software can be stable and performant. Kudos to the team
- bsaul 8y agoAnyone 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. 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.
- iagooar 8y agoI guess it's simple: they have world-class engineers behind it. Of the smartest people in tech, that truly understand their craft. That, and a broad, open community.
- Coding_Cat 8y agoAnd probably (because of that) a very robust codebase-base. Adding a JIT to a 'textual query'->'results' pipeline is a lot easier if you took the effort to silo off each individual step than it is when you consider 'text'->'result' to be one fixed step. Even if the initial partioning is a lot of work. (pure speculation, I haven't seen their code yet, but I am curious now).
- abledon 8y agoYou gotta be insanely good and enjoy your craft to enjoy writing low level DB code for open source lol
- simias 8y agoDBs might not be glamorous but I'm sure there are many very interesting problems to solve when developing them. Of course on such a large, old project there's got to be a lot of tedious maintenance work as well, but that's true for all software in my experience. It's often a conversation I have with people looking to get into software development. Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack. On the other hand some of the most interesting pieces of software I've written were for very unsexy industrial applications. And I actually have good working conditions unlike people working in the videogame industry apparently.
- netcraft 8y agoI've used lots of different databases over the years and PG is my pick. Thanks to all who were involved, this looks like a great release.
- rb666 8y agoThe inclusion of the keywords "quit" and "exit" in the PostgreSQL command-line interface to help make it easier to leave the command-line tool YES, at last. This beats parallel queries any day.
- sofaofthedamned 8y agoAgreed! It's the small things sometimes...
- bschwindHN 8y agoI'd highly recommend taking a look at pgcli, I find it much more pleasant to use than psql.
- pgaddict 8y agohttps://www.reddit.com/r/ProgrammerHumor/comments/9gtq70/lady_gaga_tries_to_exit_vim/ https://www.reddit.com/r/ProgrammerHumor/comments/9gtq70/lad...
- overcast 8y agoSeriously, that alone was very disorienting jumping into PGSQL for the first time. The command line definitely not as user friendly as MySQL.
- ibotty 8y agoThere are widely different opinions about that. I have never heard someone proclaim MySQL has a great command line, while I heard that about PostgreSQL many times. I am sure it's more a matter of familiarity.
- briffle 8y agoI came to Postgresql from Oracle. the ability to hit the up arrow in the CLI was groundbreaking :)
- Nelkins 8y agoDoes 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: https://www.postgresql.org/docs/11/static/ddl-partitioning.html https://www.postgresql.org/docs/11/static/ddl-partitioning.h... edit: Found an example here: https://pgdash.io/blog/partition-postgres-11.html https://pgdash.io/blog/partition-postgres-11.html Followup question: Is there a way to re-partition? Say, if your data grows and you want to split the data up further?
- AtlasBarfed 8y agoTheoretically that should just be a data sync while maintaining double-write read-primary, and then delete the data from the nodes you don't need anymore once the data has been synced? Of course with non-hash indexes the deletions start to slow down with size... I'm assuming joins, indexes, etc are all isolated to the shard data?
- amitlan 8y agoThe documentation of creating partitions [1] says this: "When creating a hash partition, a modulus and remainder must be specified. The modulus must be a positive integer, and the remainder must be a non-negative integer less than the modulus. Typically, when initially setting up a hash-partitioned table, you should choose a modulus equal to the number of partitions and assign every table the same modulus and a different remainder (see examples, below). However, it is not required that every partition have the same modulus, only that every modulus which occurs among the partitions of a hash-partitioned table is a factor of the next larger modulus. This allows the number of partitions to be increased incrementally without needing to move all the data at once. For example, suppose you have a hash-partitioned table with 8 partitions, each of which has modulus 8, but find it necessary to increase the number of partitions to 16. You can detach one of the modulus-8 partitions, create two new modulus-16 partitions covering the same portion of the key space (one with a remainder equal to the remainder of the detached partition, and the other with a remainder equal to that value plus 8), and repopulate them with data. You can then repeat this -- perhaps at a later time -- for each modulus-8 partition until none remain. While this may still involve a large amount of data movement at each step, it is still better than having to create a whole new table and move all the data at once." [1] https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-PARTITION https://www.postgresql.org/docs/current/static/sql-createtab...
- enraged_camel 8y agoSerious question: when you need a relational database, are there any technical reasons why you would use anything other than Postgres these days?
- jeltz 8y agoI can only think of three cases: 1) You need an embedded relational database with a small foot print. Here PostgreSQL cannot compete with SQLite. 2) You have an application which does not support PostgreSQL. 3) You have petabytes worth of data. Here you want to look into something like Greenplum, a PostgreSQL fork.
- truth_seeker 8y agoPG works okay with Peta Bytes of data. Various types of indexes, Table partitioning and parallel query computation can help. Citus and other community extensions can help if you want to go distributed and fault tolerant
- jacques_chester 8y agoWorth noting that the Greenplum team are working to converge back to mainline.
- majewsky 8y agoBecause the app you want to run does not support it. OpenStack used to support both MySQL and Postgres, but they removed the Postgres support, which is a decision that completely baffles me.
- drej 8y agoHorizontal scaling and/or alternative storage systems (I know, pg can theoretically do both, but...). That and perhaps global consistency aka "sort of but not really defeating the CAP theorem using atomic clocks and all that" in Spanner.
- pgaddict 8y agoYeah, we're not particularly good at those out of the box. 1) Horizontal scaling: In some cases it's doable using streaming replication, but it depends if you need to scale reads or writes. Or if you need distributed queries. There are quite a few forks and/or projects built on PostgreSQL that address different use cases (CitusDB, Greenplum, Postgres-XL, BDR, ...). And the features slowly trickle back. One reason why it's like this is extensibility/flexibility - the project is unlike to hard-code one particular approach to horizontal scaling, because that would not work for the other use cases. So we need something that does not have that effect, which takes longer. It's a bit annoying, of course. 2) Storage systems: We don't really have a way to do that now - there are extensions using FDW to do that, but I'd say that's really a misuse of the FDW interface, and it has plenty of annoying limitations (backups, MVCC, ...). But it's something we're currently working on so there's hope for PG12+: https://commitfest.postgresql.org/20/1283/ https://commitfest.postgresql.org/20/1283/
- romed 8y agoHave fun dumping and reloading your entire database.
- drej 8y agoI 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). 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.
- jeltz 8y agoIt is very hard to say right now what will make it into PostgreSQL 12. Some further improvements to partitioning have already landed and I expect more to land, but for other features I have no idea. There are some very ambitious projects in the pipeline.
- anarazel 8y ago> 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). Note that even if we get the pluggable storage work into v12 - which I hope and think we can do, I'm certainly spending more time on it than I'd like - it'll not include a columnar storage on its own. There's others working on storage engines, but the furthest along intended for core aren't, to my knowledge, columnar. And even if somebody submits one for core, it might be a while till it's fast enough to satisfy your demands ;)
- munk-a 8y agopg12 may be removing the (mandatory) optimization fences in CTEs, this is really exciting to me as it'll enable much more maintainable complex queries to not suffer a performance hit. As a general rule of programming, I never want syntax sugaring or a chosen expression pattern to impact runtime performance. I have found it's optimal to write expressive code first and performant code second - and if there's a trivial expression transformation between the two a compiler or interpreter better be equipped to do it for you.
- smilliken 8y agoI'll play devil's advocate: the optimization fence on CTEs is a godsend. The query planner is often the wrong kind of smart. A big table can get autovacuumed in the middle of the night, cause the planner to use a new query plan, and a query that used to be instantaneous takes minutes. If you know the query plan you want, you can usually force it using CTEs. Really I'd be happier without a query planner at all.
- generalpf 8y agoI'm really surprised pgsql procedures weren't already able to start, commit, and rollback transactions. Well done devs!
- thegabez 8y agoMajor 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.
- SnowingXIV 8y agoPostgreSQL 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.
- egeozcan 8y agoMaybe 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]: > unique, primary key, and foreign key constraints are not inherited. [0]: https://www.postgresql.org/docs/11/static/ddl-inherit.html https://www.postgresql.org/docs/11/static/ddl-inherit.html
- shroom 8y agoLooks 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. 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. 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... Any other tips are also welcome! Thanks
- jeltz 8y agoMy only settings are setting extended query mode to automatic to handle output with many columns and to turn on query timing. But it is common for people to also change the NULL symbol from empty string to something else or to have unicode borders. My .psqlrc: \set QUIET 1 \x auto \timing on \set QUIET 0
- makmanalp 8y agoI'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).
- anarazel 8y agoRight, it's "just" expressions and tuple deforming right now. Especially the former required significant refactoring (landing in v10), but that's nothing against the refactoring required to do proper whole query compilation. I'm working on the necessary changes (have posted prototype), which have independent advantages as well. There's planning logic to decide whether JIT is worthwhile, but that's probably the weakest part right now.
- makmanalp 8y agoRight - I don't mean to minimize the effort at all, it must have been a massive undertaking already! Thank you for all your work!
- truth_seeker 8y agoCongratulations 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 I would love to see native column store format, distributed sharding support and in memory tables in future releases.