
Postgres 11 – A First Look - samaysharma
http://www.craigkerstiens.com/2018/09/20/postgresql-11-a-first-look/
======
nathan_f77
> Fear column addition no more

This section was really surprising to read! I use the strong_migrations [1]
gem to catch "NOT NULL column with a default value", because it's such a
common mistake.

I guess I had assumed that this was just something we had to accept, based on
decades of architecture decisions, and the way that Postgres was written. It
never occurred to me that it was actually possible to change the default
behavior of Postgres and fix the underlying issue.

[1] [https://github.com/ankane/strong_migrations#adding-a-
column-...](https://github.com/ankane/strong_migrations#adding-a-column-with-
a-default-value)

~~~
booleanbetrayal
This feature has me way more excited than I probably should be. Been splitting
up relatively simple schema migrations into multiple steps for years now, and
definitely welcoming this change.

~~~
mb4nck
Notably, that feature got written by somebody from Salesforce (I assume on
their work time, but not sure), and finished up by a Postgres major
contributor. It might be the first major contribution from Salesforce outside
of all the stuff Tom Lane did when he was working there.

~~~
petergeoghegan
Not so. I worked on UPSERT for several years as a Salesforce employee, as well
as quite a number of other things. I worked for Heroku, a Salesforce business
unit, from 2013-2017.

------
jorams
> Now you can quit Postgres by simply typing quit or exit. Previously you had
> to use Ctrl + D or \q

While this is good to help out beginners a bit, people should really learn
Ctrl + D. It works practically everywhere and saves tons of time
guessing/remembering and even typing the correct incantation for the program
you want to exit.

~~~
threeseed
What other apps use Ctrl+D ? I can't think of a single one.

~~~
1stranger
Practically any interactive shell. python or bash for instance. It's
everywhere.

~~~
tofflos
Except Powershell. :(

~~~
duckerude
It does on Linux.

------
kstrauser
A million things like this are why PostgreSQL is the only relational database
I consider for, well, pretty much anything. Their approach of “build it safe
and then make it fast” has been paying off in spades for a couple of decades
now. Thanks for everything you do, psql maintainers!

~~~
mrep
100% naive question: why is mysql and their similars so popular then?

Spanner and AWS Aurora base off of more mysql than postregsql from what I can
tell. Why?

~~~
dotancohen
> Why?

Notice that parallel to MySQL's rise a particular loosely-typed, never-except,
often-wrong programming language also became popular. To this day I feed my
family with that language.

The typical coder (not that I do not say "developer") who codes in PHP does
not care about correctness. He does not understand why monetary values cannot
be stored in floats, he does not know what bitwise manipulation is, he does
not know the difference between character encodings. What does he care what
MySQL does, he knows that he can put strings in if he calls them VARCHAR, and
he can pull the right one out with a WHERE. He cares not enough to check that
user bios fit in his VARCHAR, and when he learns to JOIN he does not
understand which constraints to put in the ON clause.

I do have nicer words for the L and A in the stack.

~~~
rgbrenner
In fairness, some of this is historical baggage that MySQL got stuck with from
it's early popularity.

mSQL was a free/low cost SQL database in the early 90s. Originally it was an
SQL translator built on top of Postgres (that used POSTQUEL). That was too
slow (because Postgres had higher system requirements), so a new lightweight
engine was developed for it.. and that's what later became MySQL. The point
was a lightweight SQL database that ran well on cheap early-90s computers.

The compromises to make mSQL fast were inherited by MySQL, and they can't be
easily changed without breaking the ecosystem that was developed around
mSQL/MySQL.

Postgres on the other hand, was a university project in the 80s and 90s being
developed on mainframes. It was always a slow moving, cautious project. Yes,
it didn't make the compromises mSQL made, because it didn't have to. It used
more memory, more CPU, but was more correct/safe than MySQL.

That was the same reason it lost in the 90s. mSQL was out being used by
websites and other projects on cheap computers. Postgres was mostly waiting
for computer tech to get fast enough so they wouldn't need to compromise their
code too much to take it off the mainframe an on to cheaper computers.
Postgres only adopted SQL in response to mSQL's popularity. Early Postgres was
also harder to setup, another point that wasn't addressed until after mSQL
took off.

MySQL was popular because of the compromises they made. If mSQL just kept
Postgres as the engine, MySQL never would have existed. And if they instead
waited (even a couple of years), and did things correctly, they wouldn't have
beat Postgres -- which had a decade lead in development and was more advanced
than mSQL/MySQL.

~~~
evanelias
> The compromises to make mSQL fast were inherited by MySQL, and they can't be
> easily changed without breaking the ecosystem that was developed around
> mSQL/MySQL.

This doesn't sound accurate to me. Which compromises are you referring to?

Historically, MySQL's major source of criticism related to leniency of type
safety / automatic data conversion -- which is unrelated to performance. It's
also essentially a solved problem with the advent of strict sql_mode. MySQL
made this the default in 2015, but it has been available (and recommended as a
best practice) since 2004.

Performance in MySQL, as a general topic, greatly depends on the storage
engine. Relative to Postgres, MySQL's pluggable storage engine API is both a
blessing and a curse -- it permits use of alternative engines that perform
significantly better for specific workloads, at the cost of substantial
administrative complexity.

Performance-wise, Postgres generally has the lead for things like number of
supported index types, join strategies, query planning for very large queries
(and OLAP workloads in general). These may or may not matter for you,
depending on your workload.

~~~
marcosdumay
The main selling point of MySQL at the earlier 2000's was that it used an in-
memory storage with only opportunistic disk writes. That made for an
incredibly fast database, with obvious downsides that many people refused to
notice.

~~~
evanelias
What storage engine are you referring to? I've been using MySQL since the
earlier 2000s, and your description doesn't match MyISAM (which uses the
filesystem cache for data blocks, only using its own caching for indexes) nor
InnoDB.

MyISAM does offer amazing write performance, at the cost of not being crash-
safe. But given its reliance on the fs cache, it would be unusual to describe
it as "in-memory storage". Anyway, the GP was talking about compromises that
"can't be easily changed without breaking the ecosystem", which doesn't
describe MyISAM either -- MyISAM is basically deprecated in modern MySQL
deployments.

------
mmartinson
Thank you pg maintainers, you've made such a wonderful thing.

~~~
sergiotapia
It really is incredible how we can get something as fundamental as a database
for free that is so well made and production ready. Can you imagine if there
was no postgres? We'd have to use mysql or oracle or _gasp_ mssql.

~~~
SOLAR_FIELDS
If MSSQL wasn't tied to the Microsoft ecosystem, I don't think it would get
such a bad rap - When I was a dev on the Microsoft product stack I found MSSQL
quite a joy to use - reasonably fast, pretty powerful admin tools (SSMS) and
pretty easy to interface with as long as you stayed within the MS ecosystem. I
did find myself missing SSMS specifically when I moved to non MS stack
development, since Postgres' 'official' open source options aren't nearly as
robust. There are a few proprietary tools that can rival SSMS for Postgres,
but then you are back into the realm of proprietariness.

~~~
nwatson
MSSQL runs on Linux now. There's even a docker image.
[https://docs.microsoft.com/en-us/sql/linux/sql-server-
linux-...](https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-
setup?view=sql-server-2017)

------
ahartmetz
I have a serious problem with Postgres. Every year at FOSDEM, some of the most
technically interesting talks are about Postgres, and the room is always far
too small so I can't get in.

~~~
adwhit
The AW block is tiny but has so many great talks. All the queues end up
merging with each other and jutting out into the foyer. Chaos!

------
kbumsik
One thing the OP didn’t mention is that the JIT is not enabled by default.
They found that it has performance improvements for only long and complex
queries yet. [1] But it can be enabled by a simple configuration command: jit
= on.

[1]:
[https://www.phoronix.com/scan.php?page=news_item&px=PostgreS...](https://www.phoronix.com/scan.php?page=news_item&px=PostgreSQL-
JIT-No-11-Default)

~~~
anarazel
It's expected to only have benefits for longer queries. The problem is
primarily that the current logic what is likely to be a long query isn't
perfect, so can trigger in the wrong moments. Since integrating the feature
we'd added an item in the "pre-release item list" [1], to decide whether to
enable/disable - but we definitely wanted it enabled during most of the beta
test period. This wasn't really a last-minute "whaaaaaaa" thing...

[1]
[https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items](https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items)

------
shrumm
I’m already loving PG10’s native partitioning support. PG11 closes the loop on
a few convenience features like being able to set foreign and primary keys at
the master table level. With PG10 I can’t use ON CONFLICT clauses - looking
forward to upgrading and sorting that out.

~~~
LunaSea
I encountered the same limitations as you while implementing partitioning a
few months ago and it's possible to use ON CONFLICT if the unique index is set
on the partitioned table.

So after initialising a new partition, I also create a unique index attached
to that partition directly instead of the parent table.

~~~
shrumm
wow thanks, I never thought to try it, just dismissed it and never dig
further. I’ll try this!

------
Nelkins
I'm excited for hash partitioning. Being able to partition on a uuid is going
to be very useful.

------
dkubb
I love Postgres, but the only thing I find myself wishing for was better
Unicode support in the regex engine. I like to add strong database
constraints, and sometimes lean on the regexp engine but I find it's missing a
lot of features available in other regexp engines. PCRE compatibility would be
ideal, but even just support for the Unicode \p classes would be a big step
up.

------
ggm
I love PG. heaps better than the alternatives, for many reasons. My main one,
is native IPv6 object support.

I found the migration from 9.x to 10.x deeply painful, I think the one-time
cost of the dump-restore cycle hurt me far more than I expected. I very much
hope the transition to 11 can be done more expeditiously.

~~~
RedCrowbar
Have you tried pg_upgrade?

~~~
ggm
Yes. I still don't understand why it failed. It gave me a huge list of locale
related reasons it felt unhappy, and since I hadn't selected a locale config
option installing either the prior, or the current (9.4 -> 10.x) I felt pretty
wierded out.

~~~
tourdownunder
Postgres 9.4.0 was released 2014-12-18

The version convention has changed with v10.0 so it should be trivial to
change from 10.x to 11.x . In your position I'd be updating 9.4.x -> 9.5.x ->
9.6.x -> 10.x

------
deytempo
What are some reasons I might consider Postgres over MySQL when choosing a DB
platform?

~~~
gaius
With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing,
Postgres does it all, and better than all the wannabes.

~~~
Avamander
Could you please elaborate how to use Postgres as a time series database (like
InfluxDB)?

~~~
StreamBright
Create a table and have a timestamp for your data? Not sure what you are
asking.

On hand we have: 15+ years old mature product that is widely supported and it
can be used as a time series database

Other hand: n+1 young database engine with all of the problems that any young
data storage engine has (only exception is FoundationDB because those guys
actually understood the challenges of writing a reliable data store)

The question is, what can InfluxDB offer over Postgres for storing time series
data?

~~~
threeseed
Well InfluxDB actually is a time series database. That's all it does.

The query language is optimised for time based queries. The storage is
optimised for time series. It is part of the metrics/monitoring ecosystem so
it has integrations with tools like Grafana.

PostgreSQL doesn't have any time series capabilities OOTB. It's just people
modelling the concept in a relational fashion. No different to how Excel can
be used as a time series database.

And seriously everyone should just get over FoundationDB. There are plenty of
people who have invented new databases that got it right e.g. Cassandra,
HBase, Redis etc.

~~~
mslot
PostgreSQL has excellent time series capabilities. It can load millions of
rows per second, efficiently scan by time range, build rollup tables, has
expressive SQL with excellent support for time (timezones, ranges, timestamps,
intervals, conversion, etc.), it can combine multiple indexes to query large
volumes of time series data quickly, it can do sampling, and it can expire
data efficiently through partitioning. Ok, it's not entirely ootb. To make it
work nicely you may need extensions like pg_partman to automate partitioning
and Citus to scale out, but once you do you have a time series database that's
faster and more powerful than anything else on the market.

~~~
threeseed
Congratulations. You just described the capabilities of ANY SQL database.

But again there are capabilities that exist only in InfluxDB because all it
does is time series data. It's not multi-purpose. Which again is why you see
it all over the place in the metrics/monitoring ecosystem but you never see
PostgreSQL, MySQL etc.

~~~
xdanger
Actually you can use PostgreSQL in those with timescaledb plugin. ;)

------
brightball
Great looking release. I’m really excited to see if the cstore extension can
make good use of the parallelism improvements.

~~~
macdice
It looks like someone would need to do the things described in here:
[https://www.postgresql.org/message-
id/flat/CA%2Bz6ocRFEnThhX...](https://www.postgresql.org/message-
id/flat/CA%2Bz6ocRFEnThhXye3F9_ZjSMDSfOGdnGOt8hnXsVgGmFpNTYFA%40mail.gmail.com)

There is an easy level "parallel safe" that would allow scans of different
cstore partitions in a parallel query, and a harder-to-code "parallel aware"
level that would allow parallel scans on one individual cstore_fdw relation.
AFAIK no FDW has attempted parallel scans yet, but cstore looks like it may be
an ideal case to be first?

