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.
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.
> 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.
But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys. There are a lot of ways to help others learn things like Ctrl + D, an even better experience might be a 1 second delay with a message informing them of other ways.
We could bikeshed all day on which is the more pristine way to exit a program, paint the shed whatever color you want, being intuitive for beginners is never a bad thing.
C-d is a great way to accidentally exit more than one shell with remote latency. I only use it then there's no other choice. Most frequent use case is ad-hoc file transfer via terminal paste to remote end running cat > out.txt.
Yet, principle of least surprise. What is more surprising, Ctrl+D working or quit/exit not working? Maybe I am an outlier but I would try exit/quit/bye first.
Control+C is used to kill a process with the signal SIGINT, and can be intercepted by a program so it can clean its self up before exiting, or not exit at all. ... Ctrl+D means end of file. It has no effect if the program isn't reading input from the terminal. Ctrl+C means “interrupt”, i.e., stop what you're doing.
What does Python do these days? I seem to remember it would catch common cases, but rather than quit, scold you and tell you how you should be doing it the other way, which seemed like the most Western European approach imaginable.
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!
I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows.
Also to note, PostgreSQL has too many features for my taste. I get it's the selling point. But that usually correlates with poor performance and code complexity, too.
Of course, there's a problem of all modern fast database engines being proprietary. But by not addressing the performance problem, perhaps with an smaller engine lighter in features, open source is left biting the dust. And that's not good for the industry.
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.
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.
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.
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...
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.
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.
Check out TimescaleDB, a Postgres extension that auto-creates partition tables for you while PG11 native partitioning you still need to manually run CREATE TABLE.
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.
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.
For future reference, you can use logical replication to do this online between versions with a minimal window at the end for a final sync when apps are off and not changing data. By that I mean have your 9.6 in prod running as normal, set up your 10/11 newprod and then kick off logical replication for ALL TABLES from prod->newprod. That'll chug along and as long as you have appropriate resources and replication configured correctly, it'll replicate your data over and maintain it while prod is still being used. Then you turn your apps off, let it finish the sync, switch the apps over to the new db and you're good. Obviously there's some missing detail like users, extensions, replication etc, but that's always very specific to an install.
JSON support, ability to query it, JSONB support, real spatial extensions, timescale extensions, consistent behaviour, online backups. We now have to migrate from MySQL to Postgres because of these features. Instead of MySQL 8 we will be probably usng Pg 11. Now we are using MariaDB.
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?
[+] [-] nathan_f77|7 years ago|reply
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-...
[+] [-] booleanbetrayal|7 years ago|reply
[+] [-] jorams|7 years ago|reply
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.
[+] [-] craigkerstiens|7 years ago|reply
Yes, it's great to learn Ctrl + D.
But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys. There are a lot of ways to help others learn things like Ctrl + D, an even better experience might be a 1 second delay with a message informing them of other ways.
We could bikeshed all day on which is the more pristine way to exit a program, paint the shed whatever color you want, being intuitive for beginners is never a bad thing.
[+] [-] barrkel|7 years ago|reply
[+] [-] tudelo|7 years ago|reply
[+] [-] lkbm|7 years ago|reply
1. Print "You can also quit using Ctrl + D"
2. Quit.
But I also believe that only doing step 2 is better than neither. psql should prioritize psql's usability over educating users on Unix standards.
[+] [-] t0mbstone|7 years ago|reply
[+] [-] peteretep|7 years ago|reply
[+] [-] pkulak|7 years ago|reply
;)
[+] [-] threeseed|7 years ago|reply
[+] [-] paulddraper|7 years ago|reply
I used to be able to Ctrl+C knowing that it would interrupt any currently running query but never lose my session.
If I want to quit the PostgreSQL shell, I'd Ctrl+D like any other shell.
What will I do now?
[+] [-] kstrauser|7 years ago|reply
[+] [-] mrep|7 years ago|reply
Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
[+] [-] alecco|7 years ago|reply
I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows.
Also to note, PostgreSQL has too many features for my taste. I get it's the selling point. But that usually correlates with poor performance and code complexity, too.
Of course, there's a problem of all modern fast database engines being proprietary. But by not addressing the performance problem, perhaps with an smaller engine lighter in features, open source is left biting the dust. And that's not good for the industry.
[+] [-] mmartinson|7 years ago|reply
[+] [-] sergiotapia|7 years ago|reply
[+] [-] ahartmetz|7 years ago|reply
[+] [-] adwhit|7 years ago|reply
[+] [-] twic|7 years ago|reply
[+] [-] kbumsik|7 years ago|reply
[1]: https://www.phoronix.com/scan.php?page=news_item&px=PostgreS...
[+] [-] anarazel|7 years ago|reply
[1] https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items
[+] [-] shrumm|7 years ago|reply
[+] [-] LunaSea|7 years ago|reply
So after initialising a new partition, I also create a unique index attached to that partition directly instead of the parent table.
[+] [-] welder|7 years ago|reply
https://news.ycombinator.com/item?id=18026699
[+] [-] Nelkins|7 years ago|reply
[+] [-] dkubb|7 years ago|reply
[+] [-] ggm|7 years ago|reply
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.
[+] [-] chillydawg|7 years ago|reply
[+] [-] RedCrowbar|7 years ago|reply
[+] [-] deytempo|7 years ago|reply
[+] [-] petre|7 years ago|reply
[+] [-] jpalomaki|7 years ago|reply
[+] [-] gaius|7 years ago|reply
[+] [-] sv12l|7 years ago|reply
[+] [-] brightball|7 years ago|reply
[+] [-] macdice|7 years ago|reply
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?