Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.
https://www.postgresql.org/message-id/flat/20170119213859.GA...
More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_serve...
Postgres has been amazing in shipping the foundation required to deliver complex feature.. Logical Replication is an example of it, all the piece commited in the last 6y allowed to make this patch achievable.
Even if that is possible, it may be a major performance killer. This has to be done internally, I think.
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.
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.
This is an interesting comment for two reasons. Firstly because a lot of people also complain about MySQL's archaic defaults which often stay too long because of upgrade concerns (though they fortunately are fixing a lot of them already or for MySQL 8.0 - hooray).
But also because it speaks volumes, in my opinion, about the MySQL documentation that these are documented in the first place. I worked at MySQL for 9 years and though it was always clear our manual was always a good source of information, now that I am working on Ubuntu & OpenStack it is painfully obvious just how good the MySQL documentation team and processes were compared to many other projects. Even just the version ChangeLog.
I'm not saying other projects don't get it right (and have no opinion at all about postgresql's documentation state), but MySQL seems to get it pretty right in general.
One of the reasons why it's done this way (through mailing lists and not e.g. through pull requests on github) is that all the history is tracked in a way that's fully under control of the community. So it's fairly easy to find who/when submitted the patch, how it looked like, etc.
Of course, another reason is history - most of the process was established long before git, when CVS was the VCS.
But I gotta say it's only working for those projects because they have an extremely high barrier of entry to the code itself in the first place (working on projects like Wine and Postgres is scary, even though you can get started with easy stuff).
It also works for them because they have maintainers and core committers used to the workflow, already tooled on the workflow etc. But I wonder how much productivity would be gained by using a github-like flow maybe enhanced a bit.
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.
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?
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+wTGK9R...
> In all reality though, Uber wanted a key-value store and not an RDBMS
Agreed on that.
InnoDB is a complex piece of software, supporting transactions, row-level locking, MVCC, schemas, secondary indexes, crash recovery, hot copy/backup, complex caching and buffering, many tunables, and extensive metrics visibility. Just because it's more appropriate for Uber's rather unusual EAV-like use-case, this doesn't mean InnoDB is a glorified K/V store.
Re: clustered indexes, it's a storage engine architecture choice with well-known trade-offs, both positive and negative. SQL Server also uses clustered indexes and is widely respected among database experts.
Regarding the topic overall, there are use-cases where Postgres is the best choice, and there are use-cases where it isn't. That doesn't inherently mean that other databases are uniformly worse. People like to trash MySQL, sometimes for completely valid reasons, but other times for FUD. But fwiw, several of the major features in Postgres 10 have already been supported in MySQL/InnoDB for a long time, in some cases for over a decade. Of course, that goes both ways; there are awesome major features that Postgres has had for a decade that MySQL still lacks.
Have the Postgres thought about adding support for more than one storage engine? Then they could implement new ideas in a fork, an one could run them side-by-side and migrate over to it.
https://www.postgresql.org/message-id/4CB597FF.1010403@cheap...
For example MySQL had been mocked for its old ISAM storage engine. Then MySQL added InnoDB as another storage engine, the SQL interface is the same.
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.
Also, pgAdmin?
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).
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.
About raft-based leader-election, I believe the current recommendation is to look at 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.
I quite confident that the postgresql from 5y in future will be quite different in term of storage / server topology support. I won't be surprise pg_bouncer capacity to finally make its way to core when we have a coordinator.
Postgres has steady progression (even if not fast enough for some people) but they are moving without compromising robustness of their product for the users.
Raft is not complex. I doubt leader elect would be terribly hard to implement.
1. Minimize cognitive load by minimizing the number of things you have to learn.
2. Minimize deployment complexity and dependencies.
3. Complexity is just evil I'm general. Linear increases in complexity result in exponential increases in bugs, vulnerabilities, and failure modes. It's just combinatorics.
You really don't understand it? It's less moving pieces to think about, worry about, read about, deploy, maintain, fix and almost always leads to better performance and security.
Something as simple as connection pooling should've already been part of the database and query redirection is even more important to have included.
It's not very good in a long-lived scenario where you're changing your replication topology for routine maintenance tasks. Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID implementation is much better in this regard. You can change masters and replicas all repoint them without rebuilding. You can't do that (currently) with Postgresql. It's a major pain point.
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.
(and it is in general a good idea to keep the configuration the same as much as possible between primary and standbys).
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.
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.
(On the other hand, some projects need to make things _less_ clear - https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db... - "users would (...) call [the developers] to wake them up at night and complain".)
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).
I found the best way to do migrations is with vanilla SQL. I wrote a little tool to read migrations from SQL files in a directory, send them to the server and keep track of which ones have already been applied. Simple and easy.
The big benefit of migration is that your app code doesn't have to deal with every possible schema that you've ever used; it can rely on the data being uniform.
I'm very happy with the switch; wouldn't go back to Mongo for anything.
We did the move maybe 4-5 years ago? At least in the JavaScript world, this makes your life so so much better.
Of course, you still have to handle migrations, but at least you have transactions :)
I tend to view PostgreSQL as a "Swiss Army knife" and having native RDF support would reinforce that.
But that's just my personal opinion, and other contributors and companies might very well disagree.
> [...] 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.
> 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.
"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.)"
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.
Patch releases are for bug fixes. If you can't handle any change in behavior, including a bug fix, then you shouldn't be upgrading.
I understand the problem, and kudos to Postresql for figuring out a solution, but railing on glibc for fixing bugs in patch releases makes about as much sense as breeding raccoon families to chuck into people's cars.
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.
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.
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!
Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitionin...
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.
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.
EXPLAIN (ANALYZE, BUFFERS) yourquery;
If you want that aggregated, rather than for an individual query, you should look into pg_stat_statements.
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?
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.