New in PostgreSQL 10

Seconded. Postgres is such a high quality project in so many ways. I'm rarely left wanting when using it, and very often pleasantly surprised.

Third!

agreed. after years of having used Oracle and SQL Server at work, to find I could have a working database in about 10 seconds with

sudo apt-get install postgresql

was amazing to me!

Truly. We've recently moved from Oracle (after using it for 15 years) to Postgresql. It's like a breath of fresh air. The documentation for Postgres is unbelievably superior to Oracle. So far its performance is equal to or better than Oracle. We had to go through and rewrite thousands of queries, but the sql syntax of Postgres was always simpler and more logical than the equivalent in Oracle (I think Oracle has too much baggage from being around too long). All in all I'm so impressed by Postgres. I'm sure there are features in Oracle that Postgres doesn't have that keep people on Oracle, but I would imagine that the vast majority of Oracle installs could be moved to Postgres.

And they didn't even ICO

Parallel query was already there in 9.6 though this release improves on it further.

I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.

So far as the speed is concerned, we ran some tests a while back and it came back as nearly native performance when compared to interacting with a non-partitioned table:

https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/

I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support.

There's also still some performance kinks to work out with the current hash index performance - large when growing the index quickly or under very high concurrency.

Equality operations are also cheaper with hash indexes than btree because less data pages need to be fetched, so this can be a gain for certain columns with a high cardinality. You should really look at if those could be used or not, you may gain in performance with a switch.

> the "redis of databases"

That's like calling Swift "the Ruby of programming languages".

> "redis of databases".

That's like saying <good truck> is a "Prius of 8 wheelers".

That's really weird comparison. Especially that PostgreSQL is more of a database than redis is.

Maybe you meant the swiss knife of databases.

Yeah.. You should never use floating point when your use case wants uniform precision across the full range. That's the opposite of what floating point gives you.

Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages.

https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-col...

Thanks, I'm happy it got into PG10 too. Just to make it very clear - the current implementation is fairly simple, and may not be smart enough to deal with more complicated dependencies between columns. Hopefully that will be improved in PG11.

As someone working with json output from the gmail api I’m curious to see how people smarter than me take advantage of this new functionality so I can adopt it as well.

It's an improvement for sure, but I am curious -- does anyone situate a Postgres instance where it is publicly accessible? Who was asking for this feature?

Seems like this might make using passwords compliant with FIPS 140-2. (Not sure, so maybe someone else can share their opinion.) Previously I heard in a few places that people would use LDAP to delegate the auth to something else, e.g. here: https://news.ycombinator.com/item?id=12129906

do you find that it's significantly slower than just using psql?

This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs).

http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write...

Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.

There is no native solution as there is no unique scenario regarding failover. Some people prefer to do fencing using network tools like firewall rules or managed switches, other just reconfigure their applications connection string to remove the failed node, or even reconfigure connections with a middle-ware like pgbouncer. There surely are other fencing solutions that don't come to my mind now, and others I don't even know of. For failover you can use tools like repmgr which provides automatic failover, or set up your own monitoring which can either trigger an alert to your pager and you run the failover. Or you have it trigger some configuration manager to reset the nodes (I strongly suggest using Ansible ;) ) This is what makes postgres flexible, as it's not tied to one solution.

For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast.

Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby.

Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds.

Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover.

The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.

I would recommend repmgr[1] over solutions like DRBD suggested. The simple reason is that DRBD replicates on block level and doesn't understand postgres data structures, while repmgr relies on mechanisms provided by Postgres.

So if there is a failure, you're far less likely to learn that all your data is gone, because some important block on disk was not replicated and the database is not readable. With repmgr the worst what could happen is that you might lost few latest transactions, but your data should always be consistent and in working state.

[1] http://repmgr.org/

I think a reasonable option is to use pgbouncer. I wouldn't comment about it because I have long abandonned databases with no failover in favor of solution that works, but people who tried hard enough seem to get pgbouncer to a working point.

postgre fans will hate, but if you really need something with automated failover, you might have to take a look at better databases. Either Postgre/Oracle in the paid SQL world, or elasticsearch/cassandra/riak in the free NoSQL world.

Certifications or compliance?

Well, this Postgres release introduces automatic partitioning; in Oracle, it has been available for like 20 years.

Also, some shops are heavily invested in PL/SQL code.

The price of migration may far outweigh the license savings.

"best-in-class documentation"

?? what am I missing. I dread having to lookup how to do things in postgres because their documentation is so horrible to find things in.

Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.

> Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?

Oracle: because the enterprise is its own unique beast. Companies frequently stick to what they know and have invested into, so long as it still works. Enterprise customers buy/adopt software in a very different way than everyone else.

MySQL: if you've got a history with the product and it works well for what you're doing, there is absolutely no necessity-based reason to switch to PostgreSQL. You should switch from MySQL to PostgreSQL if there's a very good reason/s to, a meaningful gain to be had, it's that simple. Otherwise, it's unnecessary optimization. There are a vast number of considerations when operating a business, upgrading to the latest & greatest (speaking very broadly) just to do it, is not a good reason most of the time. There should be a specific prompt to action based on a gain/s or capability that PostgreSQL provides you, in/for your use case, over eg MySQL.

Dynamic SQL in Postgres is really frustrating. I understand why. It's because you shouldn't fucking write SQL that writes SQL.

On the other hand, sometimes you just have to. SQL Server . . . I won't say it makes it easy, but it doesn't make it so goddam impossible.

If I lived in a world without Postgres, I would use SQL Server without thinking twice. MySQL/Maria I'm not fond of, and I kind of don't want to touch them. Oracle has some cute features and language extensions, but you have to deal with Oracle.

I can't think of a use case where Postgres falls down compared to other RDBMSs.

What we are seeing is Postgres competing with NoSQL and Mongo-type stuff. With full-text search support for JSONB types, we're looking at Postgres moving into ElasticSearch territory with a vastly more simple deployment model and still carrying ACID guarantees.

It's pretty remarkable.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL. There are basically two common ways of organizing rows in a database: store everything in a tree according to the primary key, or store everything in a heap, and have a separate tree relating values to pointers into the heap. MySQL stores rows in a tree, and PostgreSQL stores rows in a heap.

The strategy used by PostgreSQL is simpler, more flexible, and cleaner, but there are some cases where MySQL's way of storing things can be significantly faster.

There has been talk of PostgreSQL supporting clustered indexes, but that would probably require reimplementing a ton of stuff to support both storage formats efficiently. Also, it turns out that there are a bunch of ways in which PostgreSQL can cheat such that in most workloads there's no difference at all - though not all of these cheats have been implemented.

Note that I didn't say MySQL is faster or slower than PostgreSQL. Such statements don't make sense, because it depends entirely on your workload, and as long as you aren't literally using PostgreSQL as a key/value store instead of a relational database, you can probably always find a way to make it as fast as necessary.

MySQL has native bitcount operator (and more data types last time I checked). Using it ended up being the fastest way to implement one problem I had - I tried Python with Numpy, Java, but generating large combinations in MySQL (joining a table to itself multiple times) and using bitshift and bitcount operators to filter the rows ended up being far faster than generating the combinations either language that I knew or Postgres lacking the bitcount operator. Probably spending a fair bit more time in C or Rust would have been the real solution. But that would have involved way more time than my work were willing to let me spend on it.

Also initial setup is easier with MySQL. It is installed everywhere and setting it up uses involves less messing about. (I would imagine there are far more insecure MySQL install around as a result).

Not a priority for 2Q at the moment. Still general interest in the community, but hard to predict right now.

There seems to be an in progress patch for pluggable storage:

https://www.postgresql.org/message-id/CAJrrPGdMu8TzxpRZQKK5E...

The PostgreSQL 11 roadmap also mentions "multi-model" database

https://wiki.postgresql.org/wiki/Fujitsu_roadmap#Multi-model...

See this discussion also: https://news.ycombinator.com/item?id=15258166

Define "big data"? You can buy normal tier 1 server vendor hardware with 6T of memory.

If it fits into 1 machine or a few, it isn't "big data".

I am interested in the answer to this question, too.

By no means an expert, but isn’t it Apples and Oranges?

Cassandra is an eventual consistent database.

Postgres is ACID.

My 2c: you don’t need Cassandra or other BigData databases. If you did, you probably wouldn’t ask the question.

Does Cassandra allow aggregate functions like sum() or avg() yet?

Ultimately it depends on your use case and how much tuning/schema design/explicit management you are willing to do.

My team lead did a talk at NGINX Conf 2017 which touched on how we tuned Postgres to handle 1 Billion metrics per day for NGINX Amplify (https://www.nginx.com/products/nginx-amplify/). I would link it, but it seems it hasn't been published by our Conf team yet.

We did this on 9.4 so the changes outlined here regarding native partitioning, parallel queries, replication improvements, etc. would likely make doing a similar scaling structure easier and just straight up more performant.

I wouldn't classify 1 Billion metrics a day as "Big Data". I also would point out that our use case is unfairly biased to "recent" data rather than "all historical" data...which allows us to make some query optimizations that wouldn't be acceptable in the latter use case.

But we are using Postgres to host a constant, write-heavy workload that conventional wisdom dictates it is unsuited for. Moreover, it was/is fairly simple to implement, understand, and scale indefinitely. Personally, I think both Redshift and CitusData are various degrees of proof that scaling Postgres to handle these types of workloads is possible.

For me, the reason you want to consider using a Big Data focused tool (such as Cassandra) is at some point you end up fighting your tool to make a square peg fit in a round hole. At some point it makes more sense to adopt a specialized tool rather than work to specialize your implementation of a specific tool.

In all fairness, we are considering just that. Our road map actually has Scylla in the near future. It's not that we don't think Postgres could handle the load, but rather we think it will be easier to move specific data to Scylla and in the long run will save us resources (primarily in man hours and potentially in compute requirements as well).

Even if we move all our timeseries data to a specialized tool, we won't be getting rid of Postgres. For most/if not all our other persistence requirements Postgres is more than adequate. This is one of the reasons we chose to start with Postgres: even once we hit a scale that specialized tools made sense, Postgres would still be useful for everything else that doesn't require a specialized tool.

Moral of the story: Postgres will get you a lot farther than many give it credit for...and if you are just starting out I would highly encourage you to just pick Postgres and see how far it can take you. If you hit the limit, replace that specific part.

Edit: Also, there appears to be some movement on Columnar indexes which is worth keeping an eye on:

https://blog.2ndquadrant.com/postgresql-10-roadmap/

They are completely different:

Postgres - Solid single-node relational database with SQL standards support, JSON, fulltext search, custom data types, ACID transactions, foreign data access, and more. HA/scaling through replication and some 3rd party offerings for automatic sharding, failover and multi-master if you need it. Great as the operational source-of-truth for your core data.

Cassandra - Distributed wide-column (basically advanced key/value) meant to run as a cluster of machines with native support for multiple data centers. Limited "SQL" support which is really only used for defining tables. Data access is very different (everything is an upsert or delete) with variable per-query consistency settings. Great if you need data spread globally, 100% availability, eventual consistency fast key/value, and some interesting data access patterns.

If you want high-performance Cassandra, look at ScyllaDB first. If you need something in between Cassandra and Postgres, look at CockroachDB.

Seconded, this is a pretty important feature for some use cases

No it can't currently. There has been some discussion on how to do that for pg 11 though.

No. You need Global indexes for that.

It will throw an error. Partitioning support in PostgreSQL 10 only solves a few simple use cases well, but the next version should get much more generally useful partitioning. This is how PostgreSQL usually releases major features.

I'd start with the docs: https://www.postgresql.org/docs/

They are pretty good.

You'll likely find the PostgreSQL docs[0] very useful. They're organized differently than the MySQL reference making it easier (in my experience) for reading stand-alone.† They're quite well-written and up-to-date.

[0]: https://www.postgresql.org/docs/

† The MySQL reference is very useful particularly when you're looking to compare feature differences between versions.

You may like PostgreSQL Exercises: https://www.pgexercises.com/

Advanced compression of data is really something that should be looked at separately from database design. Look into using ZFS beneath Postgres for some pretty good compression.

Any XML document that you can address via XPath in a sensible way. If your XML document is a mess, you'll only be able to get out a mess. :)

To get XML from a table, functions already existed in previous releases (XMLELEMENT etc.).

"This means that users no longer need to create triggers for routing data; it's all handled by the system. "

It basically just adds a convenient syntax for creating partitions plus adds automatic tuple routing (routing inserts on the partitioned table to the right partition). This is for most users not a huge improvement over using PostgreSQL's table inheritance to implement partitioning, but it is a very important stepping stone for implementing competitive partitioning in PostgreQL. There are lots of promising patches in the PostgreSQL 11 release cycle which improve on the native partitioning, making it useful for more users.

Learn normalisation from wikipedia, then read "The Art of SQL" to get a feeling for the shape of the weirdnesses, then read the postgresql documentation from end to end.

(disclaimer: worked for me, may not for anybody else)