35 ms·

New Features Coming in PostgreSQL 10

517 points by ioltas 10y ago

MR4D 10y ago
You guys are awesome - keep up the good work!
qaq 10y ago
Even a single feature from the list would make 10 an amazing release, all of them together is just unbelievable. Very happy we are using PG :)
Normal_gaussian 10y ago
Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?
- anarazel 10y ago
  > The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this? The background is that, over the years, a number of people deleted the pg_xlog and pg_clog directories when they noticed they're running out of space, thinking it's just server logs. Unfortunately that's the directories containing the database journal, and transaction status (committed/aborted/in-progress). Which means they'll loose data. The idea is to rename them to something that's less likely to be mistaken for unimportant data.
- JoachimSchipper 10y ago
  Having good directory names helps a lot; I'd be very surprised if other projects didn't also need to make it clear to admins what's happening. (On the other hand, some projects need to make things _less_ clear - https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db733dd38f96896cd825f/src/os.h#L43 https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db... - "users would (...) call [the developers] to wake them up at night and complain".)
- frik 10y ago
  It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure. Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed things. It was okay with WinXP, just the long user folder was trouble some because of 260 chars MAX_PATH limit. But with Vista and 64-bit support the fucked up and it's now a big mess in Win7+ (syswow64, system32, registry, winsxs, dotNet folders, ... such a big mess and sometimes also waste of HDD space by duplicates of files).
  - barrkel 10y ago
    winsxs uses hard links - space wastage is more likely from more versions than just dupes. Also, many windows tools won't account t correctly for hard links in disk usage stats.
- pgaddict 10y ago
  To be fair, the extended statistics available in PG10 are about the most primitive ones possible. If the PG10 stats help with your queries, great, but otherwise it's mainly laying infrastructure for the more advanced stuff - histograms, MCV lists, expression statistics, and ultimately join statistics (which is about the main source of estimation issues). Oleg also mentioned it might be useful for JSON statistics, which would be cool. Hopefully those bits will get in faster - I first submitted the patch in 2014, just before pgconf.eu, I think. OTOH I can't really complain, because I'm shitty developer so the initial versions were far from committable. The quality requirements for PostgreSQL patches are damn high these days. BTW if you have examples of real-world queries hurt by poor estimates, report them to pgsql-performance mailing list. It's an important piece of information about what cases to look at first. Obviously, we already have already collected various queries, but having more is good.
bladecatcher 10y ago
This is great because I couldn't go to production with earlier releases of logical decoding. Now we don't have to depend on a third party add on!
- felixge 10y ago
  We're currently experimenting with logical decoding in 9.6, so I'd be curious to hear what problems you've been running into.
avar 10y ago
This bit about ICU support v.s. glibc: > [...] Furthermore, at least on Red Hat, glibc regularly whacks > around the behavior of OS-native collations in minor releases, > which effectively corrupts PostgreSQL's indexes, since the index > order might no longer match the (revised) collation order. To > me, changing the behavior of a widely-used system call in a > maintenance release seems about as friendly as locking a family > of angry racoons in someone's car, but the glibc maintainers > evidently don't agree. Is a reference to the PostgreSQL devs wanting to make their index order a function of strxfrm() calls and to not have it change when glibc updates, whereas some on the glibc list think it should only be used for feeding it to the likes of strcmp() in the same process: > The only thing that matters about strxfrm output is its strcmp > ordering. If that changes, it's either a bug fix or a bug > (either in the code or in the locale data). If the string > contents change but the ordering doesn't, then it's an > implementation detail that is allowed to change. -- https://sourceware.org/ml/libc-alpha/2015-09/msg00197.html https://sourceware.org/ml/libc-alpha/2015-09/msg00197.html
- JoachimSchipper 10y ago
  Florian Weimer's reply is also interesting: "Why do you think that? I don't see this documented anywhere, and I doubt it is something many readers of the C standard, the man page, or the glibc manual would expect. The manual suggests to store the strxfrm output and use it for sorting. I expect that some applications put it into on-disk database indexes as a result. This will lead to subtle breakage on glibc updates. (The larger problem is that there are definitely databases out there which use B-tree indexes in locale collation order, which break in even more subtle ways if we make minor changes to the collation order.)"
  - ajross 10y ago
    Which manual suggests storing the output of strxfrm? The glibc man page doesn't seem to. I don't know that this is resolvable. The documented behavior of strxfrm() is just about its output properties. Improvements to the transformation algorithm would be expected to be made, if it's improvable. If a database needs this to be static over time it needs to pick a particular transformation algorithm and specify it exactly, not just rely on whatever the C library happens to provide. I mean, not only are PostgreSQL locale-sorted-indexes not portable across glibc releases. They aren't portable across any other system change either. No moving between distros or doing distro upgrades, etc... Those are all misfeatures probably worth fixing.
elvinyung 10y ago
Dumb question: does declarative partitioning pave the way for native sharding in Postgres? I'm not super super familiar, but it seems like along with some other features coming in Postgres 10, like parallel queries and logical replication, that this is eventually the goal.
- rhaas 10y ago
  I hope that it will have that effect. We need a few other features first: partitionwise join, partitionwise aggregate, asynchronous query, and ideally hash partitioning.
  - elvinyung 10y ago
    I see -- thanks! Really cool stuff.
  - qeternity 10y ago
    Doesn't this basically replicate all of the work done by Citus?
hodgesrm 10y ago
Impressive feature list. Glad to see logical replication is finally making it in.
- brianwawok 10y ago
  What is you use case for it? My only thought was sending just one table to replica to be used to do analytics on ..
  - bladecatcher 10y ago
    Isn't analytics a massive usecase in itself?
  - rhaas 10y ago
    Replication across major versions, for example to upgrade without downtime. Partial replication, to distribute shared data across a series of clusters, or for analytics and reporting as you mention. Replicating the data without replicating any table bloat. Being able to do limited writes (e.g. to temporary tables) on the standby. http://rhaas.blogspot.com/2011/02/case-for-logical-replication.html http://rhaas.blogspot.com/2011/02/case-for-logical-replicati...
    - hodgesrm 10y ago
      Indeed--anything where you want the secondary to be other than a bit-for-bit copy of the primary. It's also convenient for HA in some cases due to the fact that the DBMS copies are fully independent, hence free from propagated bit-level errors and also available for unimpeded reads. MySQL started with logical replication very early on and it has proven extraordinarily useful. One of the more interesting use cases is feeding log transactions into data warehouses, which should be possible in PostgreSQL 10.
djcj88 10y ago
I did read the article, but I can't find any mention of addressing the "Write amplification" issue as described by Uber when they moved away from postgres. https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ I had heard talk on Software Engineering Daily that this new major revision was supposed to address that. Is this issue resolved by the new "Logical replication" feature? It doesn't seem directly related, but it seems like maybe that is what he is referring to in this blog post?
- anarazel 10y ago
  There's a patch reducing write amplifications (when caused by indexes), by a significant degree. Unfortunately it didn't quite get ready in time for the feature freeze of 10 - as it affects the on-disk format, we considered the risk to be too high.
  - pavanvd 10y ago
    As the author of the patch I don't quite agree to it. But it's true that the patch did not receive adequate review even though most of the on-disk changes were known and coded at least 7 months before the feature freeze. So it's hard to tell which part of the patch wasn't ready. But there is always next cycle. So lets work towards getting it ready for v11.
- snuxoll 10y ago
  Write amplification is a result of PostgreSQL's decision to not used clustered indexes, there's not much that can be done to avoid it without a massive redesign of the storage engine - though there are patches out there to reduce the penalty in some cases. In all reality though, Uber wanted a key-value store and not an RDBMS, MySQL was a better choice for this since InnoDB isn't much more than a fast K/V store (hence why MySQL uses clustered indexes).
  - anarazel 10y ago
    > Write amplification is a result of PostgreSQL's decision to not used clustered indexes, there's not much that can be done to avoid it without a massive redesign of the storage engine I don't think that's entirely accurate - the issue is more that indexes contain pointers to the heap position (simplified) of a tuple, rather than being indirect and pointing to the primary key, which then is resolved by another index (be that clustered / primary or not). Updates already don't have to update indexes iff none of the indexed columns change (HOT - Heap-Only-Tuples). The proposed change (WARM - write amplification reduction method), allows to avoid updating indexes on non-changing columns, even if other indexes change. https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9RVd8iw+CzqHeQSGpW7Yka_4RSZ_LOQ@mail.gmail.com https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9R... > In all reality though, Uber wanted a key-value store and not an RDBMS Agreed on that.
StreamBright 10y ago
For analytical loads the following is going to be great: While PostgreSQL 9.6 offers parallel query, this feature has been significantly improved in PostgreSQL 10, with new features like Parallel Bitmap Heap Scan, Parallel Index Scan, and others. Speedups of 2-4x are common with parallel query, and these enhancements should allow those speedups to happen for a wider variety of queries.
mark_l_watson 10y ago
I know that several RDF data stores use PostgreSQL as a backend data store. With new features like better XML support, as well as older features for storing hierarchical data, I am wishing for a plugin or extension for handling RDF with limited (not RDFS or OWL) SPARQL query support. I almost always have PostgreSQL available, and for RDF applications it would be very nice to not have to run a separate service. I tend to view PostgreSQL as a "Swiss Army knife" and having native RDF support would reinforce that.
- anarazel 10y ago
  As one of the people regularly working on postgres, I'm honestly a bit doubtful that it's realistic to add SPARQL frontend. Doing that well is a considerable amount of work, and there's relatively little overlap in experience between the communities. I suspect that focusing on other areas will have a bigger ROI. But that's just my personal opinion, and other contributors and companies might very well disagree.
- oever 10y ago
  Can you say which RDF data stores these are? Are they generic or special purpose?
- frik 10y ago
  Which RDF data store uses Postgres as DB backend? And can one import WikiData? (does it scale) (I would rather avoid these old school RDF special case stores from SematicWeb days 10 years ago.)
  - kuschku 10y ago
    https://github.com/cayleygraph/cayley https://github.com/cayleygraph/cayley is currently on the frontpage of HN, and it does use PGSQL as backend.
    - jerven 10y ago
      Does it scale is a very valid question for Cayley. Running sparql.uniprot.org I know virtuoso scales easily to 27 billion triples. Best benchmark I have seen for Cayley is 21 million RDF triples. RDF and relational are similar but free form RDF is hard to put into a pure relational schema, as schema is derived from the data in RDF. There are ways to recover relational schema's from RDF but these are not yet in production state.
hartator 10y ago
I am considering more and more a move back from MongoDB to PostgreSQL. I will be missing being schema less so much though. Migrations - particularly Rails migrations - left a bad taste in my mouth. Anyone did the move recently and what are their feelings?
- stemcc 10y ago
  You can easily have schema-less with Postgres's jsonb data type.
  - hartator 10y ago
    Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.
    - stickfigure 10y ago
      You're right that ORMs aren't really set up to use jsonb but it can be done. I've had pretty good success with a kind of "hybrid" putting most relational data in regular columns (with FK constraints) and adding flexible data in jsonb. The main trick is understanding how to build custom column types in whatever ORM you actually use. I can imagine a future with specialized ORMs designed around jsonb, but the current state of the art is probably not as bad as you think.
    - StreamBright 10y ago
      You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.
    - manigandham 10y ago
      In the .NET world, ServiceStack ORMLite has custom Postgres type attributes to use the jsonb and other fields automatically. http://docs.servicestack.net/releases/v4.5.6#postgresql-data-types http://docs.servicestack.net/releases/v4.5.6#postgresql-data... There is also Marten which is an ORM that offers a full document database interface all backed by Postgres JSON columns. http://jasperfx.github.io/marten/documentation/documents/ http://jasperfx.github.io/marten/documentation/documents/
jacques_chester 10y ago
I deeply appreciate the great care that Postgres committers take in writing their merge messages. I think of it as a sign of respect for future developers to take the time to write a clear account of what has happened.
- anarazel 10y ago
  Being one of them, though not a native speaker which is more than sometimes noticeable, I'd not even describe it as caring for future developers. It's self-care. I've spent enough time staring at code changes made long ago, trying to understand the reasoning, that providing enough context for my future self is justification enough.
  - jacques_chester 10y ago
    I agree with that sentiment. I consider my future self to be an example of "other developers". When I am working with peers on writing a commit message, I sometimes use the analogy of a newspaper. Any given newspaper is out of date very quickly. But we keep newspaper archives and store copies of every single newspaper. Why? Because we don't know when we will need to refer to them, or which ones we will refer to. All that we know is that some of them will vital in future, as the journal of record. And so it is with commit messages. We owe readers the courtesy of explaining our thinking.
- atombender 10y ago
  Postgres is one of the few projects that still use a strict patch-oriented development process that's based almost entirely around mailing-list communication. While core team members can commit directly to the repo, everyone else must submit the code changes for review to the pgsql-hackers mailing list as a clean, self-contained patch, where it's discussed and considered for inclusion. An accepted patch might be committed right away, or it will be queued up for the next scheduled "commitfest" [2], when patches are reviewed and finally committed to mainline. (I don't know how the commitfest interacts with git exactly; the commitfest database doesn't even link to git, only to email discussions.) From the outside it seems a bit antiquated, but it's apparently been working well for them. The Postgres team is a pretty conservative bunch; they only switched from CVS to git in late 2010, for example. They also really care about code quality, getting the design right early, and covering all possible edge cases. As a result, Postgres solid, clean, has unusually few legacy oddities, and almost never any subtle, suprising breaking changes. If you read the MySQL manual, it's absolutely littered with sloppy little breakages throughout its history: Like how, until 5.0.something, when comparing a "date" value with a "datetime" value, the time portion would be silently ignored and ('2017-04-08 14:04' = '2017-04-08') would return true; but they fixed that, and broke a lot of client code because they didn't stop to realize that a lot of developers depended on that behaviour. [1] https://wiki.postgresql.org/wiki/Submitting_a_Patch https://wiki.postgresql.org/wiki/Submitting_a_Patch [2] https://commitfest.postgresql.org https://commitfest.postgresql.org
acdha 10y ago
What's the ops experience for a replicated setup like these days? i.e. assuming you want basic fault-tolerance at non-exotic size / activity levels, how much of a job is someone acquiring if, say, there are reasons they can't just use AWS RDS?
- Heliosmaster 10y ago
  Streaming replication isn't hard at all: http://davide.im/setting-up-a-failover-database-for-postgresql/ http://davide.im/setting-up-a-failover-database-for-postgres...
  - acdha 10y ago
    Thanks! I was asking here mostly out of curiousity about how people felt after running it for awhile since it has certainly sounded like it has improved massively since I last dealt with it in the 8.x era.
  - api 10y ago
    Yes but here's the problem. Consider common scenarios like: Master goes down. Slave takes over. Master comes back. Slave goes down 10 minutes later. Repeat. This is common in e.g. multi data center replication and is often due to transient network failures. Netflix has a great open source tool called chaos monkey that can induce lots of random failure scenarios like this or much worse. Don't get me started on transient partial failures due to latency and packet loss spikes. The manual nature of pg replication setup makes me really nervous here. What happens when it finds itself in a state where manual intervention is needed? You are now down. This is tolerable for big companies with dedicated SREs and DBAs and enough of them that it's easy to always have someone on call, but it's a nightmare for smaller ventures. Even for larger ventures this adds a lot of cost overhead. Like I said elsewhere this was really the true killer feature of the more successful NoSQL document store type databases. Everything else was largely hype. We switched recently to RethinkDB for this reason. We miss the richness of SQL (to the point that we still use PG too for warehousing and analytics) but in return we got incredible robustness across three data centers. Of course our app does not need rich queries or strong consistency 99% of the time so YMMV. For some jobs ACID and complex queries on live data are not optional.
fiatjaf 10y ago
Ok, I'm not a database manager for enormous projects, so these changes may be great, but I don't understand them and don't care about them. Postgres is already the most awesome thing in Earth to me. Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.
- petepete 10y ago
  How do you mean? Couldn't you use a trigger to update the view?
  - phamilton 10y ago
    Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.
  - mozumder 10y ago
    Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.
  - ams6110 10y ago
    IMHO triggers are almost always best avoided. There are some exceptions but most of the time you want changes to be explicit not happening "by magic" behind the scenes as a side-effect of something else.
  - okket 10y ago
    A trigger on what? Every update, insert, delete, etc.? On every table in the view? Even if that is possible, it may be a major performance killer. This has to be done internally, I think.
    - jeltz 10y ago
      I am not sure it necessarily would be that bad. After all foreign keys are implemented with triggers and they are usually fast enough. You just need to write trigger functions which are fast enough.
smac8 10y ago
Wow, so awesome. I do hope at some point we can see some language improvements to PLPGSQL. More basic data structures could go a long way in making that language really useful, and I still consider views/stored procedures a superior paradigm to client side sql logic
- rhaas 10y ago
  I agree with you that stored procedures are superior to client-side logic, because it means that you can have multiple routes of access to the database and all of them enforce the same business logic. But what exactly do you mean by "more basic data structures"?
  - pgaddict 10y ago
    PL/SQL has various types of collections, for example, that are super-useful when you need to do more complicated processing without having to create temporary tables and such.
mozumder 10y ago
I could use a count of the number of file I/Os that each query takes, in order to optimize my queries further...
- anarazel 10y ago
  That's been there for a while: EXPLAIN (ANALYZE, BUFFERS) yourquery; If you enable track_io_timing (has some overhead on platforms with slow timestamps, e.g. older VMware), you even get timing. If you want that aggregated, rather than for an individual query, you should look into pg_stat_statements.
  - mozumder 10y ago
    The BUFFERS count is more for row count info as it operates on large chunks of data, instead of index optimization that needs to count how many times index structures are accessed. Counting IOs directly would be more useful for tuning indexes.
    - anarazel 10y ago
      Huh? It shows you the number of io operations.
nickpeterson 10y ago
Can anyone recommend a decently up to date book on postgres administration? Or are docs really the only way? I've used SQL Server for years but would likely choose postgres for an independent project if I intended to commercialize it. That said, I don't use it at work so it's hard to get in depth experience.
- chillydawg 10y ago
  I'm not familiar with any books, but the docs really are excellent and have various sections for beginners and getting to know the system. A good way in is to look at external tools like barman which manage dumps+streaming replication along with point-in-time restoration automatically for you rather than manually invoking all the stuff directly. Mostly, postgres just works.
- fiatjaf 10y ago
  There are no better docs than Postgres docs.
- arc_of_descent 10y ago
  I understand your need for a book. I prefer to read a book when diving into a new tech. That being said, when I started out using PostgreSQL years back, there were only online docs, and I must say they are although lengthy at time, very good. Also, pgAdmin?
- pgaddict 10y ago
  There's PostgreSQL 9 Admin Cookbook from Simon Riggs, for example (disclosure: I work for Simon). Packt has several other good books about PostgreSQL, but always check the author - they started publishing books authored by people entirely unknown in the community, that are "inspired" by book published before (you might also use "plagiarism" instead).
  - nickpeterson 10y ago
    Yeah packtpub is a real crapshoot. They're great in that they'll seemingly publish whatever tech subject you want to write about. The downside is they publish anything...
api 10y ago
The feature I'd really love is master selection with Raft or similar and automatic query redirection to the master for all write queries (and maybe for reads with a query keyword). That would make it very easy and robust to cluster pg without requiring a big complicated (a.k.a. high admin overhead and failure prone) stack with lots of secondary tools. This kind of fire and forget cluster is really the killer feature of things like MongoDB and RethinkDB. Yes people with really huge deployments might want something more tunable, but that's only like 1% of the market. Of course those NoSQL databases also offer eventual and other weaker but more scalable consistency modes, but like highly tuned manual deployment these too are features for the 1% of the market that actually needs that kind of scale. A fire and forget cluster-able fully consistent SQL database would be nirvana for most of the market.
- mb4nck 10y ago
  About redirection of write queries to the master, from 10 on, you will be able to specify all members of the cluster in the connection string and demand to connect to the master (like "postgresql://host1:5432,host2:5432/somedb?target_session_attrs=read-write"); libpq will do this automatically for you then, see the parameters "host" (now plural) and "target_session_attr" in section 33.1.2. here: https://www.postgresql.org/docs/devel/static/libpq-connect.html https://www.postgresql.org/docs/devel/static/libpq-connect.h... About raft-based leader-election, I believe the current recommendation is to look at patroni ( https://github.com/zalando/patroni https://github.com/zalando/patroni), which has been built for docker and is now being integrated with Kubernetes; however, I don't think there is an inherent limitation that it couldn't be run on bare-metal.
  - api 10y ago
    Yes on the first one, and a big nope on the second for now. I passionately loathe Rube Goldberg machine deployments and am the kind of engineer who constantly asks "do we really need that?". I love exterminating complexity. But maybe that will change when we get to millions of concurrent users and tens of millions of devices and actually need Kubernetes to scale. Raft is not complex. I doubt leader elect would be terribly hard to implement.
  - 10y ago
knv 10y ago
Any recommendations for scaling Postgresql's best practices? Really appreciate it.
ams6110 10y ago
A question on this statement, in the SCRAM authentication description: stealing the hashed password from the database or sniffing it on the wire is equivalent to stealing the password itself How is that the case? That's exactly the thing that hashed passwords prevent. Of course, if it's just an MD5 hash that's feasibly vulnerable to brute-forcing today, but it's still not "equivalent" to having the clear-text password.
- jhgg 10y ago
  The point is that you only send the hash to the database to connect. If you steal the hash, you can connect to the database using said hash, not needing the plaintext. The password might as well be the hash in this case. Hence the equivalency. Using that scheme, all you prove is that you know the hash of the password. SCRAM allows you to prove you know the plaintext password without actually transmitting it.
  - xyzzy_plugh 10y ago
    If you steal the hash from the database, yes. I don't know how stealing the hash over-the-wire is equivalent to having the password, since it is salted (with a salt generated by the server) and is not reusable.
    - MBCook 10y ago
      Because the next time you connect to the server you provide the same hash. The person doesn't know your plane text, but they can get into the server just fine.
      - xyzzy_plugh 10y ago
        You can't provide the same salted wire hash. You'd need the pre-salted hash, which only the client and server now. I fail to see how this answers my question.
iEchoic 10y ago
I'm so excited for table partitioning. I use table inheritance in several places in my current project, but have felt the pain of foreign key constraints not applying to inherited children. Reading about table partitioning, I'm realizing that this is a much better fit for my use case. Postgres continues to amaze me with the speed at which they introduce the right features into such a heavily-used and production-critical product. Thanks Postgres team!
- amitlan 10y ago
  Unfortunately, foreign keys won't be supported right away. Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#ddl-partitioning-declarative https://www.postgresql.org/docs/devel/static/ddl-partitionin...
  - iEchoic 10y ago
    Thanks, I hadn't read this. That's too bad, hopefully we'll see that in the future (if it's technically possible at all?). That'd be a huge feature for me.
lazzlazzlazz 10y ago
How is Postgres so consistently the best open-source DB project from features to documentation? It's unreal.
- dhbx9 10y ago
  I'd say it is the best DB hands down. Open source or otherwise.
- int_19h 10y ago
  Not just a DB project, either. I'd say it's one of the best executed (in a very broad sense of the word) open source projects around, in general. From end user perspective, they have stable, quality releases with a predictable cycle and subsequent maintenance releases. They have great documentation - one of the best in the industry, much less open source. Things generally work as you'd expect them to, and when not (e.g. for historical or implementation reasons), you have clear and convincing explanations. And so on. I haven't seen their developer side, but based on other people's feedback, it's also good - high quality bar for code, stringent review process etc. More importantly, they seem to be making the right (= leading to more stable quality releases with great features) technical decisions consistently, which to me is a hallmark of a very well run team. I also can't remember any publicized "drama" around Postgres, either on the inside (dev disagreements etc), or between the team and the users. It looks like everyone's happy, or at least happy enough. I don't know what the magic sauce is here, but it feels like many other open source projects could learn a lot from the Postgres team and community.
- akurilin 10y ago
  I second this, the Postgres contributors are consistently setting the bar for the rest of OSS projects out there, it's consistently been my favorite part of the stack for a long time now. Don't forget about their phenomenal #postgresql channel on Freenode. The folks working on Postgres have been gracious enough to patiently answer my not always fully baked questions for the past 5 years on there, they're a bottomless treasure trove of best practices and pragmatic advice.
qxmat 10y ago
DECLARE @please VARCHAR(3) = '???';
awinter-py 10y ago
the join speedup for provably unique operands sounds awesome
awinter-py 10y ago
fascinating that the road to improving the expr evaluator is better opcode dispatch and jit -- same tradeoffs every programming language project is looking at right now.
jordanthoms 10y ago
Will DDL replication for the logical replication be landing in 10 or later? We have some use cases where logical replication would be very helpful, but keeping the schema in sync manually seems like a pain - will there be a documented workaround if DDL replication doesn't make it in?

517 points by ioltas 10y ago

MR4D 10y ago

You guys are awesome - keep up the good work!

qaq 10y ago

Even a single feature from the list would make 10 an amazing release, all of them together is just unbelievable. Very happy we are using PG :)

Normal_gaussian 10y ago

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?

anarazel 10y ago
> The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this? The background is that, over the years, a number of people deleted the pg_xlog and pg_clog directories when they noticed they're running out of space, thinking it's just server logs. Unfortunately that's the directories containing the database journal, and transaction status (committed/aborted/in-progress). Which means they'll loose data. The idea is to rename them to something that's less likely to be mistaken for unimportant data.
JoachimSchipper 10y ago
Having good directory names helps a lot; I'd be very surprised if other projects didn't also need to make it clear to admins what's happening. (On the other hand, some projects need to make things _less_ clear - https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db733dd38f96896cd825f/src/os.h#L43 https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db... - "users would (...) call [the developers] to wake them up at night and complain".)
frik 10y ago
It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure. Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed things. It was okay with WinXP, just the long user folder was trouble some because of 260 chars MAX_PATH limit. But with Vista and 64-bit support the fucked up and it's now a big mess in Win7+ (syswow64, system32, registry, winsxs, dotNet folders, ... such a big mess and sometimes also waste of HDD space by duplicates of files).
- barrkel 10y ago
  winsxs uses hard links - space wastage is more likely from more versions than just dupes. Also, many windows tools won't account t correctly for hard links in disk usage stats.
pgaddict 10y ago
To be fair, the extended statistics available in PG10 are about the most primitive ones possible. If the PG10 stats help with your queries, great, but otherwise it's mainly laying infrastructure for the more advanced stuff - histograms, MCV lists, expression statistics, and ultimately join statistics (which is about the main source of estimation issues). Oleg also mentioned it might be useful for JSON statistics, which would be cool. Hopefully those bits will get in faster - I first submitted the patch in 2014, just before pgconf.eu, I think. OTOH I can't really complain, because I'm shitty developer so the initial versions were far from committable. The quality requirements for PostgreSQL patches are damn high these days. BTW if you have examples of real-world queries hurt by poor estimates, report them to pgsql-performance mailing list. It's an important piece of information about what cases to look at first. Obviously, we already have already collected various queries, but having more is good.

bladecatcher 10y ago

This is great because I couldn't go to production with earlier releases of logical decoding. Now we don't have to depend on a third party add on!

felixge 10y ago
We're currently experimenting with logical decoding in 9.6, so I'd be curious to hear what problems you've been running into.

avar 10y ago

This bit about ICU support v.s. glibc: > [...] Furthermore, at least on Red Hat, glibc regularly whacks > around the behavior of OS-native collations in minor releases, > which effectively corrupts PostgreSQL's indexes, since the index > order might no longer match the (revised) collation order. To > me, changing the behavior of a widely-used system call in a > maintenance release seems about as friendly as locking a family > of angry racoons in someone's car, but the glibc maintainers > evidently don't agree. Is a reference to the PostgreSQL devs wanting to make their index order a function of strxfrm() calls and to not have it change when glibc updates, whereas some on the glibc list think it should only be used for feeding it to the likes of strcmp() in the same process: > The only thing that matters about strxfrm output is its strcmp > ordering. If that changes, it's either a bug fix or a bug > (either in the code or in the locale data). If the string > contents change but the ordering doesn't, then it's an > implementation detail that is allowed to change. -- https://sourceware.org/ml/libc-alpha/2015-09/msg00197.html https://sourceware.org/ml/libc-alpha/2015-09/msg00197.html

JoachimSchipper 10y ago
Florian Weimer's reply is also interesting: "Why do you think that? I don't see this documented anywhere, and I doubt it is something many readers of the C standard, the man page, or the glibc manual would expect. The manual suggests to store the strxfrm output and use it for sorting. I expect that some applications put it into on-disk database indexes as a result. This will lead to subtle breakage on glibc updates. (The larger problem is that there are definitely databases out there which use B-tree indexes in locale collation order, which break in even more subtle ways if we make minor changes to the collation order.)"
- ajross 10y ago
  Which manual suggests storing the output of strxfrm? The glibc man page doesn't seem to. I don't know that this is resolvable. The documented behavior of strxfrm() is just about its output properties. Improvements to the transformation algorithm would be expected to be made, if it's improvable. If a database needs this to be static over time it needs to pick a particular transformation algorithm and specify it exactly, not just rely on whatever the C library happens to provide. I mean, not only are PostgreSQL locale-sorted-indexes not portable across glibc releases. They aren't portable across any other system change either. No moving between distros or doing distro upgrades, etc... Those are all misfeatures probably worth fixing.

elvinyung 10y ago

Dumb question: does declarative partitioning pave the way for native sharding in Postgres? I'm not super super familiar, but it seems like along with some other features coming in Postgres 10, like parallel queries and logical replication, that this is eventually the goal.

rhaas 10y ago
I hope that it will have that effect. We need a few other features first: partitionwise join, partitionwise aggregate, asynchronous query, and ideally hash partitioning.
- elvinyung 10y ago
  I see -- thanks! Really cool stuff.
- qeternity 10y ago
  Doesn't this basically replicate all of the work done by Citus?

hodgesrm 10y ago

Impressive feature list. Glad to see logical replication is finally making it in.

brianwawok 10y ago
What is you use case for it? My only thought was sending just one table to replica to be used to do analytics on ..
- bladecatcher 10y ago
  Isn't analytics a massive usecase in itself?
- rhaas 10y ago
  Replication across major versions, for example to upgrade without downtime. Partial replication, to distribute shared data across a series of clusters, or for analytics and reporting as you mention. Replicating the data without replicating any table bloat. Being able to do limited writes (e.g. to temporary tables) on the standby. http://rhaas.blogspot.com/2011/02/case-for-logical-replication.html http://rhaas.blogspot.com/2011/02/case-for-logical-replicati...
  - hodgesrm 10y ago
    Indeed--anything where you want the secondary to be other than a bit-for-bit copy of the primary. It's also convenient for HA in some cases due to the fact that the DBMS copies are fully independent, hence free from propagated bit-level errors and also available for unimpeded reads. MySQL started with logical replication very early on and it has proven extraordinarily useful. One of the more interesting use cases is feeding log transactions into data warehouses, which should be possible in PostgreSQL 10.

djcj88 10y ago

I did read the article, but I can't find any mention of addressing the "Write amplification" issue as described by Uber when they moved away from postgres. https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ I had heard talk on Software Engineering Daily that this new major revision was supposed to address that. Is this issue resolved by the new "Logical replication" feature? It doesn't seem directly related, but it seems like maybe that is what he is referring to in this blog post?

anarazel 10y ago
There's a patch reducing write amplifications (when caused by indexes), by a significant degree. Unfortunately it didn't quite get ready in time for the feature freeze of 10 - as it affects the on-disk format, we considered the risk to be too high.
- pavanvd 10y ago
  As the author of the patch I don't quite agree to it. But it's true that the patch did not receive adequate review even though most of the on-disk changes were known and coded at least 7 months before the feature freeze. So it's hard to tell which part of the patch wasn't ready. But there is always next cycle. So lets work towards getting it ready for v11.
snuxoll 10y ago
Write amplification is a result of PostgreSQL's decision to not used clustered indexes, there's not much that can be done to avoid it without a massive redesign of the storage engine - though there are patches out there to reduce the penalty in some cases. In all reality though, Uber wanted a key-value store and not an RDBMS, MySQL was a better choice for this since InnoDB isn't much more than a fast K/V store (hence why MySQL uses clustered indexes).
- anarazel 10y ago
  > Write amplification is a result of PostgreSQL's decision to not used clustered indexes, there's not much that can be done to avoid it without a massive redesign of the storage engine I don't think that's entirely accurate - the issue is more that indexes contain pointers to the heap position (simplified) of a tuple, rather than being indirect and pointing to the primary key, which then is resolved by another index (be that clustered / primary or not). Updates already don't have to update indexes iff none of the indexed columns change (HOT - Heap-Only-Tuples). The proposed change (WARM - write amplification reduction method), allows to avoid updating indexes on non-changing columns, even if other indexes change. https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9RVd8iw+CzqHeQSGpW7Yka_4RSZ_LOQ@mail.gmail.com https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9R... > In all reality though, Uber wanted a key-value store and not an RDBMS Agreed on that.

StreamBright 10y ago

For analytical loads the following is going to be great: While PostgreSQL 9.6 offers parallel query, this feature has been significantly improved in PostgreSQL 10, with new features like Parallel Bitmap Heap Scan, Parallel Index Scan, and others. Speedups of 2-4x are common with parallel query, and these enhancements should allow those speedups to happen for a wider variety of queries.

mark_l_watson 10y ago

I know that several RDF data stores use PostgreSQL as a backend data store. With new features like better XML support, as well as older features for storing hierarchical data, I am wishing for a plugin or extension for handling RDF with limited (not RDFS or OWL) SPARQL query support. I almost always have PostgreSQL available, and for RDF applications it would be very nice to not have to run a separate service. I tend to view PostgreSQL as a "Swiss Army knife" and having native RDF support would reinforce that.

anarazel 10y ago
As one of the people regularly working on postgres, I'm honestly a bit doubtful that it's realistic to add SPARQL frontend. Doing that well is a considerable amount of work, and there's relatively little overlap in experience between the communities. I suspect that focusing on other areas will have a bigger ROI. But that's just my personal opinion, and other contributors and companies might very well disagree.
oever 10y ago
Can you say which RDF data stores these are? Are they generic or special purpose?
frik 10y ago
Which RDF data store uses Postgres as DB backend? And can one import WikiData? (does it scale) (I would rather avoid these old school RDF special case stores from SematicWeb days 10 years ago.)
- kuschku 10y ago
  https://github.com/cayleygraph/cayley https://github.com/cayleygraph/cayley is currently on the frontpage of HN, and it does use PGSQL as backend.
  - jerven 10y ago
    Does it scale is a very valid question for Cayley. Running sparql.uniprot.org I know virtuoso scales easily to 27 billion triples. Best benchmark I have seen for Cayley is 21 million RDF triples. RDF and relational are similar but free form RDF is hard to put into a pure relational schema, as schema is derived from the data in RDF. There are ways to recover relational schema's from RDF but these are not yet in production state.

hartator 10y ago

I am considering more and more a move back from MongoDB to PostgreSQL. I will be missing being schema less so much though. Migrations - particularly Rails migrations - left a bad taste in my mouth. Anyone did the move recently and what are their feelings?

stemcc 10y ago
You can easily have schema-less with Postgres's jsonb data type.
- hartator 10y ago
  Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.
  - stickfigure 10y ago
    You're right that ORMs aren't really set up to use jsonb but it can be done. I've had pretty good success with a kind of "hybrid" putting most relational data in regular columns (with FK constraints) and adding flexible data in jsonb. The main trick is understanding how to build custom column types in whatever ORM you actually use. I can imagine a future with specialized ORMs designed around jsonb, but the current state of the art is probably not as bad as you think.
  - StreamBright 10y ago
    You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.
  - manigandham 10y ago
    In the .NET world, ServiceStack ORMLite has custom Postgres type attributes to use the jsonb and other fields automatically. http://docs.servicestack.net/releases/v4.5.6#postgresql-data-types http://docs.servicestack.net/releases/v4.5.6#postgresql-data... There is also Marten which is an ORM that offers a full document database interface all backed by Postgres JSON columns. http://jasperfx.github.io/marten/documentation/documents/ http://jasperfx.github.io/marten/documentation/documents/

jacques_chester 10y ago

I deeply appreciate the great care that Postgres committers take in writing their merge messages. I think of it as a sign of respect for future developers to take the time to write a clear account of what has happened.

anarazel 10y ago
Being one of them, though not a native speaker which is more than sometimes noticeable, I'd not even describe it as caring for future developers. It's self-care. I've spent enough time staring at code changes made long ago, trying to understand the reasoning, that providing enough context for my future self is justification enough.
- jacques_chester 10y ago
  I agree with that sentiment. I consider my future self to be an example of "other developers". When I am working with peers on writing a commit message, I sometimes use the analogy of a newspaper. Any given newspaper is out of date very quickly. But we keep newspaper archives and store copies of every single newspaper. Why? Because we don't know when we will need to refer to them, or which ones we will refer to. All that we know is that some of them will vital in future, as the journal of record. And so it is with commit messages. We owe readers the courtesy of explaining our thinking.
atombender 10y ago
Postgres is one of the few projects that still use a strict patch-oriented development process that's based almost entirely around mailing-list communication. While core team members can commit directly to the repo, everyone else must submit the code changes for review to the pgsql-hackers mailing list as a clean, self-contained patch, where it's discussed and considered for inclusion. An accepted patch might be committed right away, or it will be queued up for the next scheduled "commitfest" [2], when patches are reviewed and finally committed to mainline. (I don't know how the commitfest interacts with git exactly; the commitfest database doesn't even link to git, only to email discussions.) From the outside it seems a bit antiquated, but it's apparently been working well for them. The Postgres team is a pretty conservative bunch; they only switched from CVS to git in late 2010, for example. They also really care about code quality, getting the design right early, and covering all possible edge cases. As a result, Postgres solid, clean, has unusually few legacy oddities, and almost never any subtle, suprising breaking changes. If you read the MySQL manual, it's absolutely littered with sloppy little breakages throughout its history: Like how, until 5.0.something, when comparing a "date" value with a "datetime" value, the time portion would be silently ignored and ('2017-04-08 14:04' = '2017-04-08') would return true; but they fixed that, and broke a lot of client code because they didn't stop to realize that a lot of developers depended on that behaviour. [1] https://wiki.postgresql.org/wiki/Submitting_a_Patch https://wiki.postgresql.org/wiki/Submitting_a_Patch [2] https://commitfest.postgresql.org https://commitfest.postgresql.org

acdha 10y ago

What's the ops experience for a replicated setup like these days? i.e. assuming you want basic fault-tolerance at non-exotic size / activity levels, how much of a job is someone acquiring if, say, there are reasons they can't just use AWS RDS?

Heliosmaster 10y ago
Streaming replication isn't hard at all: http://davide.im/setting-up-a-failover-database-for-postgresql/ http://davide.im/setting-up-a-failover-database-for-postgres...
- acdha 10y ago
  Thanks! I was asking here mostly out of curiousity about how people felt after running it for awhile since it has certainly sounded like it has improved massively since I last dealt with it in the 8.x era.
- api 10y ago
  Yes but here's the problem. Consider common scenarios like: Master goes down. Slave takes over. Master comes back. Slave goes down 10 minutes later. Repeat. This is common in e.g. multi data center replication and is often due to transient network failures. Netflix has a great open source tool called chaos monkey that can induce lots of random failure scenarios like this or much worse. Don't get me started on transient partial failures due to latency and packet loss spikes. The manual nature of pg replication setup makes me really nervous here. What happens when it finds itself in a state where manual intervention is needed? You are now down. This is tolerable for big companies with dedicated SREs and DBAs and enough of them that it's easy to always have someone on call, but it's a nightmare for smaller ventures. Even for larger ventures this adds a lot of cost overhead. Like I said elsewhere this was really the true killer feature of the more successful NoSQL document store type databases. Everything else was largely hype. We switched recently to RethinkDB for this reason. We miss the richness of SQL (to the point that we still use PG too for warehousing and analytics) but in return we got incredible robustness across three data centers. Of course our app does not need rich queries or strong consistency 99% of the time so YMMV. For some jobs ACID and complex queries on live data are not optional.

fiatjaf 10y ago

Ok, I'm not a database manager for enormous projects, so these changes may be great, but I don't understand them and don't care about them. Postgres is already the most awesome thing in Earth to me. Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.

petepete 10y ago
How do you mean? Couldn't you use a trigger to update the view?
- phamilton 10y ago
  Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.
- mozumder 10y ago
  Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.
- ams6110 10y ago
  IMHO triggers are almost always best avoided. There are some exceptions but most of the time you want changes to be explicit not happening "by magic" behind the scenes as a side-effect of something else.
- okket 10y ago
  A trigger on what? Every update, insert, delete, etc.? On every table in the view? Even if that is possible, it may be a major performance killer. This has to be done internally, I think.
  - jeltz 10y ago
    I am not sure it necessarily would be that bad. After all foreign keys are implemented with triggers and they are usually fast enough. You just need to write trigger functions which are fast enough.

smac8 10y ago

Wow, so awesome. I do hope at some point we can see some language improvements to PLPGSQL. More basic data structures could go a long way in making that language really useful, and I still consider views/stored procedures a superior paradigm to client side sql logic

rhaas 10y ago
I agree with you that stored procedures are superior to client-side logic, because it means that you can have multiple routes of access to the database and all of them enforce the same business logic. But what exactly do you mean by "more basic data structures"?
- pgaddict 10y ago
  PL/SQL has various types of collections, for example, that are super-useful when you need to do more complicated processing without having to create temporary tables and such.

mozumder 10y ago

I could use a count of the number of file I/Os that each query takes, in order to optimize my queries further...

anarazel 10y ago
That's been there for a while: EXPLAIN (ANALYZE, BUFFERS) yourquery; If you enable track_io_timing (has some overhead on platforms with slow timestamps, e.g. older VMware), you even get timing. If you want that aggregated, rather than for an individual query, you should look into pg_stat_statements.
- mozumder 10y ago
  The BUFFERS count is more for row count info as it operates on large chunks of data, instead of index optimization that needs to count how many times index structures are accessed. Counting IOs directly would be more useful for tuning indexes.
  - anarazel 10y ago
    Huh? It shows you the number of io operations.

nickpeterson 10y ago

Can anyone recommend a decently up to date book on postgres administration? Or are docs really the only way? I've used SQL Server for years but would likely choose postgres for an independent project if I intended to commercialize it. That said, I don't use it at work so it's hard to get in depth experience.

chillydawg 10y ago
I'm not familiar with any books, but the docs really are excellent and have various sections for beginners and getting to know the system. A good way in is to look at external tools like barman which manage dumps+streaming replication along with point-in-time restoration automatically for you rather than manually invoking all the stuff directly. Mostly, postgres just works.
fiatjaf 10y ago
There are no better docs than Postgres docs.
arc_of_descent 10y ago
I understand your need for a book. I prefer to read a book when diving into a new tech. That being said, when I started out using PostgreSQL years back, there were only online docs, and I must say they are although lengthy at time, very good. Also, pgAdmin?
pgaddict 10y ago
There's PostgreSQL 9 Admin Cookbook from Simon Riggs, for example (disclosure: I work for Simon). Packt has several other good books about PostgreSQL, but always check the author - they started publishing books authored by people entirely unknown in the community, that are "inspired" by book published before (you might also use "plagiarism" instead).
- nickpeterson 10y ago
  Yeah packtpub is a real crapshoot. They're great in that they'll seemingly publish whatever tech subject you want to write about. The downside is they publish anything...

api 10y ago

The feature I'd really love is master selection with Raft or similar and automatic query redirection to the master for all write queries (and maybe for reads with a query keyword). That would make it very easy and robust to cluster pg without requiring a big complicated (a.k.a. high admin overhead and failure prone) stack with lots of secondary tools. This kind of fire and forget cluster is really the killer feature of things like MongoDB and RethinkDB. Yes people with really huge deployments might want something more tunable, but that's only like 1% of the market. Of course those NoSQL databases also offer eventual and other weaker but more scalable consistency modes, but like highly tuned manual deployment these too are features for the 1% of the market that actually needs that kind of scale. A fire and forget cluster-able fully consistent SQL database would be nirvana for most of the market.

mb4nck 10y ago
About redirection of write queries to the master, from 10 on, you will be able to specify all members of the cluster in the connection string and demand to connect to the master (like "postgresql://host1:5432,host2:5432/somedb?target_session_attrs=read-write"); libpq will do this automatically for you then, see the parameters "host" (now plural) and "target_session_attr" in section 33.1.2. here: https://www.postgresql.org/docs/devel/static/libpq-connect.html https://www.postgresql.org/docs/devel/static/libpq-connect.h... About raft-based leader-election, I believe the current recommendation is to look at patroni ( https://github.com/zalando/patroni https://github.com/zalando/patroni), which has been built for docker and is now being integrated with Kubernetes; however, I don't think there is an inherent limitation that it couldn't be run on bare-metal.
- api 10y ago
  Yes on the first one, and a big nope on the second for now. I passionately loathe Rube Goldberg machine deployments and am the kind of engineer who constantly asks "do we really need that?". I love exterminating complexity. But maybe that will change when we get to millions of concurrent users and tens of millions of devices and actually need Kubernetes to scale. Raft is not complex. I doubt leader elect would be terribly hard to implement.
- 10y ago

knv 10y ago

Any recommendations for scaling Postgresql's best practices? Really appreciate it.

ams6110 10y ago

A question on this statement, in the SCRAM authentication description: stealing the hashed password from the database or sniffing it on the wire is equivalent to stealing the password itself How is that the case? That's exactly the thing that hashed passwords prevent. Of course, if it's just an MD5 hash that's feasibly vulnerable to brute-forcing today, but it's still not "equivalent" to having the clear-text password.

jhgg 10y ago
The point is that you only send the hash to the database to connect. If you steal the hash, you can connect to the database using said hash, not needing the plaintext. The password might as well be the hash in this case. Hence the equivalency. Using that scheme, all you prove is that you know the hash of the password. SCRAM allows you to prove you know the plaintext password without actually transmitting it.
- xyzzy_plugh 10y ago
  If you steal the hash from the database, yes. I don't know how stealing the hash over-the-wire is equivalent to having the password, since it is salted (with a salt generated by the server) and is not reusable.
  - MBCook 10y ago
    Because the next time you connect to the server you provide the same hash. The person doesn't know your plane text, but they can get into the server just fine.
    - xyzzy_plugh 10y ago
      You can't provide the same salted wire hash. You'd need the pre-salted hash, which only the client and server now. I fail to see how this answers my question.

iEchoic 10y ago

I'm so excited for table partitioning. I use table inheritance in several places in my current project, but have felt the pain of foreign key constraints not applying to inherited children. Reading about table partitioning, I'm realizing that this is a much better fit for my use case. Postgres continues to amaze me with the speed at which they introduce the right features into such a heavily-used and production-critical product. Thanks Postgres team!

amitlan 10y ago
Unfortunately, foreign keys won't be supported right away. Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#ddl-partitioning-declarative https://www.postgresql.org/docs/devel/static/ddl-partitionin...
- iEchoic 10y ago
  Thanks, I hadn't read this. That's too bad, hopefully we'll see that in the future (if it's technically possible at all?). That'd be a huge feature for me.

lazzlazzlazz 10y ago

How is Postgres so consistently the best open-source DB project from features to documentation? It's unreal.

dhbx9 10y ago
I'd say it is the best DB hands down. Open source or otherwise.
int_19h 10y ago
Not just a DB project, either. I'd say it's one of the best executed (in a very broad sense of the word) open source projects around, in general. From end user perspective, they have stable, quality releases with a predictable cycle and subsequent maintenance releases. They have great documentation - one of the best in the industry, much less open source. Things generally work as you'd expect them to, and when not (e.g. for historical or implementation reasons), you have clear and convincing explanations. And so on. I haven't seen their developer side, but based on other people's feedback, it's also good - high quality bar for code, stringent review process etc. More importantly, they seem to be making the right (= leading to more stable quality releases with great features) technical decisions consistently, which to me is a hallmark of a very well run team. I also can't remember any publicized "drama" around Postgres, either on the inside (dev disagreements etc), or between the team and the users. It looks like everyone's happy, or at least happy enough. I don't know what the magic sauce is here, but it feels like many other open source projects could learn a lot from the Postgres team and community.
akurilin 10y ago
I second this, the Postgres contributors are consistently setting the bar for the rest of OSS projects out there, it's consistently been my favorite part of the stack for a long time now. Don't forget about their phenomenal #postgresql channel on Freenode. The folks working on Postgres have been gracious enough to patiently answer my not always fully baked questions for the past 5 years on there, they're a bottomless treasure trove of best practices and pragmatic advice.

qxmat 10y ago

DECLARE @please VARCHAR(3) = '???';

awinter-py 10y ago

the join speedup for provably unique operands sounds awesome

awinter-py 10y ago

fascinating that the road to improving the expr evaluator is better opcode dispatch and jit -- same tradeoffs every programming language project is looking at right now.

jordanthoms 10y ago

Will DDL replication for the logical replication be landing in 10 or later? We have some use cases where logical replication would be very helpful, but keeping the schema in sync manually seems like a pain - will there be a documented workaround if DDL replication doesn't make it in?