PostgreSQL 11 Released
postgresql.org
postgresql.org
* Improvements to partitioning functionality, including:
-- Add support for partitioning by a hash key
-- Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables
-- Allow creation of a “default” partition for storing data that does not match any of the remaining partitions
-- UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions
-- Improve SELECT performance through enhanced partition elimination strategies during query planning and execution
* Improvements to parallelism, including:
-- CREATE INDEX can now use parallel processing while building a B-tree index
-- Parallelization is now possible in CREATE TABLE ... AS, CREATE MATERIALIZED VIEW, and certain queries using UNION
-- Parallelized hash joins and parallelized sequential scans now perform better
* SQL stored procedures that support embedded transactions
* Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions
* Window functions now support all framing options shown in the SQL:2011 standard, including RANGE distance PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options
* Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX
* Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default
EDIT: ctrl+D also works, just be careful not to press twice, since doing so will exit psql and then exit the shell altogether
I honestly had never heard of the Ctrl-D escape sequence until a few weeks ago. So having common exit/quit commands makes life a lot easier at least for some of us.
Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet.
Secondly, we need to improve the costing model, so that we make better decisions when to use JIT for a query. AFAIK this is the #1 reason why it's not enabled on JIT-enabled builds now.
It's still enabled by default in 12/master. I'd expect it to stay that way. But reports for v11 will obviously influence that.
> Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet.
I see very very little point in adding another JIT provider to core pg. If somebody wants to write an extension that does so, ok, go ahead, but why would we maintain two nontrivial pieces of code in core? Also the GCC based stuff isn't even close to being competitive.
> Secondly, we need to improve the costing model, so that we make better decisions when to use JIT for a query. AFAIK this is the #1 reason why it's not enabled on JIT-enabled builds now.
Right.
Sure, but that's a development version, and I'm not sure it'll stay like that at the time of release. I hope it will, but my understanding is it's enabled mostly to get exposure from the developers by default.
>> Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT providers in the future (e.g. GCC) but that's not done yet. > > I see very very little point in adding another JIT provider to core pg. If somebody wants to write an extension that does so, ok, go ahead, but why would we maintain two nontrivial pieces of code in core? Also the GCC based stuff isn't even close to being competitive.
Yeah, unfortunately the state of JIT in gcc is a bit sad :-(
> > It's still enabled by default in 12/master. I'd expect it to stay that way. But reports for v11 will obviously influence that.
> Sure, but that's a development version, and I'm not sure it'll stay like that at the time of release. I hope it will, but my understanding is it's enabled mostly to get exposure from the developers by default.
Its the stated intent to have it on in 12. We'd pondered disabling JIT in 11 since before the original commit. I rockthic think this is broadly analogous what we did with parallelism (except we won't forget to document the change, as we already noted it in the 12 notesnotes ;)).
BTW what's "12 notesnotes"?
Release notes. To avoid a repeat of the mishap where we enabled parallel query by default in 10, but didn't mention it because it was always kept enabled in master, we've already mentioned it in release-12.sgml.
Sure it's duplicating the table data, but it's functionally equivalent to the reclustering or rematerialising a view of the table methods that people use now, just without the hassle.
Figuring out how to convert a partitioning scheme created using pg_partman to native Postgres 11 won't be fun.
Usually this kind of things are the consequences of a good architecture but i wonder if anyone has an more in-depth explanation on what in the architecture makes it that good.
(pure speculation, I haven't seen their code yet, but I am curious now).
It's often a conversation I have with people looking to get into software development. Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack.
On the other hand some of the most interesting pieces of software I've written were for very unsexy industrial applications. And I actually have good working conditions unlike people working in the videogame industry apparently.
Often they'll aim for video games or something like that
but I often warn them that it might not be nearly as cool
as they imagine. You're more likely to end up scripting
crappy menu systems than being the next Carmack.
I enjoy lots of different areas of it [programming]...
I'm taking a great deal of enjoyment writing device
drivers for Linux. I could also be having a good time
writing a database manager or something because there
are always interesting problems.Seems afterwards quite logical and clever considering the turn based gameplay.
We might do the same type of work on a proprietary database, but that would lack the community/cooperation aspect of the project.
Of course, enjoying the craft is somewhat natural in both cases. And yes, it's a bit scary how smart some of the contributors are.
And if your patch adds a good feature but in a way that is bad architecturally then it will be rejected or fully rewritten with no care about what that means from a timing perspective.
It means that it takes awhile for things like parallel query to really become valuable but when they do you know they work and work well.
I think that pretty much explains it: Cutting edge features are proposed way earlier than commercial DBs start thinking about implementing them, and the community hones them over years before they are released to the mainstream public.
The very biggest difference between working there and working anywhere else, is that developers spent only a tiny fraction of time actually writing code.
The biggest chunk of it was spent on peer review. Every change required sign-off from everyone on the immediate team, and, if it was being made to a component that other teams interacted with, they'd also have members reviewing the changes. And, as a newcomer, I found the reviews to be brutal. Over time, though, I came to recognize that what originally felt like others getting all up in my business about pedantic little variable naming issues was actually everyone having my back about legitimate maintainability concerns.
Next biggest chunk was spent on planning - understanding requirements, talking over changes with anyone who might be impacted by them, etc.
The second biggest difference was just how fast we could move. I think a lot of that came from a certain "wei wu wei" that was attributable to the deep understanding of the systems we were working on that developed out of all that time with our hands off our keyboards.
Allow me the vice of inserting a mangled Hitchhiker's quote here: "If human beings don’t keep exercising their [fingers], he thought, their [hands] probably seize up. After a few months’ consideration and observation he abandoned this theory in favor of a new one. If they don’t keep on exercising their [fingers], he thought, their brains start working."
This is not true. SQL Server & Oracle already have pretty every feature just released in 11 and have had most of them for many years.
I wouldn’t try to use jsonb attribute in a primary key though (even if it’s practically possible as it’s a fully featured base type).
I've seen the json features used at multiple serious organizations. It turned what would have otherwise been unnecessarily complicated queries into really reasonable queries in our ETL pipeline.
Postgres includes many features that increase the number of simple use-cases for the existing data in their database, thus allowing you to replace a Postgres complement (like Mongo) with Postgres for that use-case.
"Enterprise" DBMSes like Oracle and MSSQL, meanwhile, don't focus so much on expanding the use-cases of their offering, as they do on expanding the number of ways a sysadmin can take the existing use-cases and make them scale better, with less manual maintenance. Oracle and Microsoft allow you to replace their ecosystem of tools and extensions with a DBMS-internal feature for the given sysadmin need.
So Postgres has an expanding ecosystem of tooling and extensions, but is slowly eating its complements; while Oracle and MSSQL have an expanding space of complementary offerings, but are slowly eating their own product ecosystems.
Or, to put that another way: Postgres focuses more on making life easier for people who write SQL; Oracle and Microsoft focus more on making life easier for the DBAs who manage the DBMS cluster that people are running SQL against.
Postgres is an amazing RDBMS and the clearly the most advanced open source offering (I use it for my startup), but it will always trail the big commercial systems like SQL Server and Oracle. The query planners for both of those RDBMS' are still considerably more advanced.
Definitely not true. Postgres development is slow, deliberate, and the opposite of cutting edge. It's what has lead to a great platform that is reliable and flexible, but every single commercial database out there has far modern tech implemented.
But I'm not sure it's a fair comparison, precisely because of that word, "pricing". It's amazing what you can get people to do when you're paying them big bucks to do it. Part of what makes PostgreSQL so impressive is what it does given the budget it's working with.
native support for TimescaleDB ? That would be nice.
Full support for both kinds of temporal data ("valid time" and "transaction time") requires a bit more engineering.
The PG support for the TSTZRANGE data type, operators built on it, and indices built on those operations, was part of a previous phase of adding support for "valid time" temporal data.
https://pgxn.org/dist/temporal_tables/ is an extension implementing a prototype of "transaction time" temporal data, though in modern Postgres, you can do this much yourself.
In both cases, none of the SQL syntax specific to temporal queries has been implemented yet. Rather than a distinct view and backing "current" and "history" tables and etc., you should be able to just have a table that represents all of those things, and do queries on its components using different syntax. (Sort of like how S3 versioned bucket resources work.)
1) careful step-by-step engineering (vision, reviews, bug-fixing, ...)
2) open and civil community
3) not founded/owned by particular company (no one has the upper hand, companies serve customers with different needs, compete but also cooperate, ...)
4) code / documentation quality
I could probably go on, but those four are probably the most important ones. (At least for me.)
Regarding the high quality, we don't do anything magical - we simply apply well-known tools in a careful and methodical way.
1) careful reviews - Not just the code, but appropriate tests, comments and docs, and most importantly overall design/direction. It's not uncommon to reject the patch on the basis that there's a better approach, and get it reworked from scratch. Evan patches from the most senior contributors generally go through it too (with the exception of bugfixes, ...).
2) not accepting technical debt - No quick hacks that would make our life harder down the road. No features that are arbitrarily incompatible with other features. No compiler warnings.
3) incremental development - Split large patches, even if it means it's not really useful initially.
4) testing - We have a range of tests, that are executed on a variety of machines/platforms/compilers ... (https://buildfarm.postgresql.org/)
5) adopt useful technologies - static analysis, test coverage, valgrind, fuzzing (sqlsmith found quite a few bugs), ...
6) attention to bug reports / fixing them promptly (instead of just stashing them somewhere forever)
I'm sure there are many additional things contributing to the quality. In any case, there's no secret sauce, except for applying those principles consistently.
I still use PostgreSQL in my startup for almost all critical production workload. This in itself a testament to its improvements. I remember I starting with c libraries of msql and postgres95 and continued to work with them as they evolved into postgreSQL and a new kid on the block mysql. I liked postgreSQL due to its adherence to SQL. Since I like working with relational algebra. So even though MySQL was more popular in later years, I still used PostgreSQL more often, except for building qmail servers with mySQL backend.
This is the last call of a hungry manager crowing on top of the body of an already dead software project.
We're talking about an open source project used by millions of people that has gotten CONTINUOUSLY better over the course of decades - to the point where it is now - arguably - the best of its kind.
Given this track record, I think postgres devs are entitled to use whatever management-speak euphemism they like. In this case, the idiom's original meaning/intent seems apt.
PostgreSQL is willing to accept delays in some features -- even if highly desired -- to enable high overall feature development and quality.
Also, more developers are willing to put time into refactoring and simplifying what they need before starting on their project. In other words the groundwork is laid first and then the new feature.
Another thing that might help is that there is no bug tracker. Bugs are fixed and the fixes backported after a report comes in. Bugs don't end up stuck in a backlog.
https://lwn.net/Articles/645020/
Anyone more involved in the pgsql community able to comment on how this balance is being struck? It doesnt' seem like the release schedule has changed much, though the version numbers seem to be incrementing faster (no v10.1, 10.2, etc?)
YES, at last. This beats parallel queries any day.
I am sure it's more a matter of familiarity.
OpenStack used to support both MySQL and Postgres, but they removed the Postgres support, which is a decision that completely baffles me.
That and perhaps global consistency aka "sort of but not really defeating the CAP theorem using atomic clocks and all that" in Spanner.
1) Horizontal scaling: In some cases it's doable using streaming replication, but it depends if you need to scale reads or writes. Or if you need distributed queries. There are quite a few forks and/or projects built on PostgreSQL that address different use cases (CitusDB, Greenplum, Postgres-XL, BDR, ...). And the features slowly trickle back.
One reason why it's like this is extensibility/flexibility - the project is unlike to hard-code one particular approach to horizontal scaling, because that would not work for the other use cases. So we need something that does not have that effect, which takes longer. It's a bit annoying, of course.
2) Storage systems: We don't really have a way to do that now - there are extensions using FDW to do that, but I'd say that's really a misuse of the FDW interface, and it has plenty of annoying limitations (backups, MVCC, ...). But it's something we're currently working on so there's hope for PG12+: https://commitfest.postgresql.org/20/1283/
Here are some of the success stories. https://www.citusdata.com/customers/
1) You need an embedded relational database with a small foot print. Here PostgreSQL cannot compete with SQLite.
2) You have an application which does not support PostgreSQL.
3) You have petabytes worth of data. Here you want to look into something like Greenplum, a PostgreSQL fork.
Citus and other community extensions can help if you want to go distributed and fault tolerant
We had Rackspace servers in the place I worked a couple of years ago and they didn't offer Postgress as far as I remember. (You could install and manage it on a virtual machine but then you had to do everything yourself, while they had managed MySQL servers available).
Also setting up master / slave replication seems easier with MySQL as I understand - I never tried with Postgress but did with MySQL.
How does their design differ?
What are the tradeoffs to consider?
Some storage engines allow for in-place updates of records if the fields are fixed-length or if the updated variable-length value fits in the existing space. I've used some of those (e.g., MS SQL Server) to good effect in high-update scenarios.
I used to work in online gambling where we had plenty of row churn and not much bloat at all without having to use any of the workarounds.
You're right UPDATEs may be an issue (because we handle them essentially as DELETE+INSERT). Generally speaking, row churn in the table alone is not an major issue - it's easy to clean up by vacuum, and it will be reused for new data. And you can limit the amount of bloat by tweaking the autovacuum parameters.
What's more painful is bloated indexes (e.g. due to UPDATEs that modify indexed columns), because that's much harder / more expensive to get rid of.
The thing is - this is part of the MVCC design, and it has some significant advantages too. It's not like the alternative approaches have no downsides.
I use PostgreSQL in an embedded device. There is a high insertion rate, and eventually when the disk starts to get full I need to get rid of old rows.
Using plain DELETE and VACUUM does not work. The deletes aren't fast enough to keep up with the inserts, and vacuuming reduces performance to the point that I have to drop data that is waiting to be inserted. This is on a high performance SSD and I've tuned postgresql.conf. (Bigger/better hardware is not possible in my application).
Instead, I think partitions with DROP PARTITION are the only way to handle high volume row churn. Dropping a partition is practically instant and incurs no vacuum penalty.
Not sure what postgresql.conf tuning you've tried, but in general we recommend making autovacuum more frequent, but performing the cleanup in smaller chunks. Also, batching inserts usually helps a lot. But maybe you've already tried all that. There's definitely a limit - a balance between ingestion and cleanup.
Don't wait until the disk gets full.
Autovacuum works great for most small to moderate sized databases. And it works great for larger databases with a few tweaks.
* http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...
At the moment, PostgreSQL keeps all versions of all tuples in its heap files. Inserts, updates, and deletes all result in addition of new tuples to the heap, and the engine keeps track of which transactions can see which tuples. The vacuum process deletes tuples which are no longer needed, but in the meantime, there is bloat.
zheap would keep only the latest version of each tuple in its heap files. When a tuple got updated or deleted, the engine would move the old version into separate storage, the "undo" log. A vacuum process would need to clean up the undo log, but the heap would remain unbloated.
This is obviously very practical. But it's a shame that it introduces an asymmetry, where some transactions will be reading tuples from the heap, and some will need to root around in the undo log.
I note that this is the approach that Oracle has always used - as explained in this fine article by the same chap who wrote the blog post about zheap above:
https://www.enterprisedb.com/blog/databases-different-approa...
this MVCC implementation couple with the way indexing works also causes write amplification. the indices have pointers to the physical place where the row is stored (some other databases might instead record the primary key and do a lookup on the primary key to get the record). so if you update a row and it causes it to move to a different physical page (you need to keep the original row for MVCC so there needs to be space for the new row in the current page) then you need to update all the indices as well. PG has some optimisation around trying to write the update to the same physical page to reduce the number of writes.
The need to manually update clustered indexes, and the fact that an exclusive lock is needed to do so, is also annoying.
You can also index ARRAY columns, meaning if you need a "tags" feature it's ridiculously easy to implement, no need for additional tables.
There's also PostGIS, which has no MySQL analog, and makes doing actual real-world GIS work possible.
There's also neat stuff like exclusion constraints: http://nathanmlong.com/2016/01/protect-your-data-with-postgr...
And there's the ability to do all kinds of neat geospatial queries with PostGIS - eg "list all properties within X distance from the curvy coastline".
The real knowledge gap would come into things like all the various tactics you need to learn to get MySQL to produce good plans for your queries. PG has a much smarter optimizer which is harder to control - if it does the wrong thing, it's harder to encourage it to do the right thing, and philosophically they don't support query hints. OTOH, worst case in PG is usually much better than worst case on MySQL.
Lack of case and accent insensitive collations is inexcusable at this point. Is there anyone that enjoys sprinkling every bit of SQL with upper() comparisons?
Non materialized CTEs. I like materialized CTEs sometimes and wish MSSQL had them as an option but I also need it to work the other way.
Native point-in-time recovery out of the box. This looks like it might be possible but the process doesn't fill me with a lot of confidence.
Better connection scaling.
Ability to load custom TS dictionaries in user space (for hosted postgres on google/aws/etc..)
Use a tool like pgbackrest, and everything just works really well.
Does using a citext column not solve this?
MySQL out of the box is more secure and easier to configure than Postgresql because of pg's public and the legacy config files.
It's also much easier to hire DBAs.
1. Incrementally updated materialized views (in MS SQL Server, these are called 'indexed views').
2. SQL Server Management Studio is better than anything I've seen elsewhere.
3. Reporting and analytics (SSRS/SSAS) built in. These are actually pretty good, but not the approach I would recommend.
4. Very solid clustering (I haven't used PostgreSQL's clustering, so this point might be out of date).
The license fees are steep (not as steep as Oracle's...) though, so you've got to really want those features.
I think report builders tools miss the sweet spot. Instead, build the specific reports that your business needs. Or, do a regular export to flat files (tab delimited causes fewest problems) and let people build what they need in Excel/R/whatever. Or both.
In practice, I try SQLite first, and fall back to Postgres if I need concurrent writes.
I can't wait to throw out all the columnar databases and the ETLs I have to support them, all just for a few queries.
Note that even if we get the pluggable storage work into v12 - which I hope and think we can do, I'm certainly spending more time on it than I'd like - it'll not include a columnar storage on its own. There's others working on storage engines, but the furthest along intended for core aren't, to my knowledge, columnar. And even if somebody submits one for core, it might be a while till it's fast enough to satisfy your demands ;)
As a general rule of programming, I never want syntax sugaring or a chosen expression pattern to impact runtime performance. I have found it's optimal to write expressive code first and performant code second - and if there's a trivial expression transformation between the two a compiler or interpreter better be equipped to do it for you.
If you know the query plan you want, you can usually force it using CTEs.
Really I'd be happier without a query planner at all.
I think being happy not having any query planner at all is a bit extreme, but there are times when queries need to be tuned to force early data culling or adherence to indexes, I find these times to ultimately be pretty rare and generally have to review all the instances of our manual optimization whenever we update postgres to see which ones the query planner can now do better. This change would still allow you to sidestep the query planner, but would also open up expressive CTEs to use in performance sensitive queries.
> unique, primary key, and foreign key constraints are not inherited.
[0]: https://www.postgresql.org/docs/11/static/ddl-inherit.html
There's planning logic to decide whether JIT is worthwhile, but that's probably the weakest part right now.
Colleagues of mine successfully employed CouchDB in a situation where devices were offline for an x amount of time (running on tablets in airplanes), once they had internet again they would reliably start syncing data with the main database again. This was a number of years ago though, I haven't heard anything about CouchDB since.
I'm not sure about thousands of nodes syncing to a single replica though. That may be a bit over the top.
It would be trivial to write something that connects to a bunch of machines regularly, fetches the decoded increment and feed it somewhere (say, to a single database over a shared connection).
See test_decoding / pg_recvlogical for examples.
I would love to see native column store format, distributed sharding support and in memory tables in future releases.
edit: Found an example here: https://pgdash.io/blog/partition-postgres-11.html
Followup question: Is there a way to re-partition? Say, if your data grows and you want to split the data up further?
I'm assuming joins, indexes, etc are all isolated to the shard data?
"When creating a hash partition, a modulus and remainder must be specified. The modulus must be a positive integer, and the remainder must be a non-negative integer less than the modulus. Typically, when initially setting up a hash-partitioned table, you should choose a modulus equal to the number of partitions and assign every table the same modulus and a different remainder (see examples, below). However, it is not required that every partition have the same modulus, only that every modulus which occurs among the partitions of a hash-partitioned table is a factor of the next larger modulus. This allows the number of partitions to be increased incrementally without needing to move all the data at once. For example, suppose you have a hash-partitioned table with 8 partitions, each of which has modulus 8, but find it necessary to increase the number of partitions to 16. You can detach one of the modulus-8 partitions, create two new modulus-16 partitions covering the same portion of the key space (one with a remainder equal to the remainder of the detached partition, and the other with a remainder equal to that value plus 8), and repopulate them with data. You can then repeat this -- perhaps at a later time -- for each modulus-8 partition until none remain. While this may still involve a large amount of data movement at each step, it is still better than having to create a whole new table and move all the data at once."
[1] https://www.postgresql.org/docs/current/static/sql-createtab...
I'd like to take the opportunity and ask if anyone has any recommendations or "must have" settings to the Postgres-terminal. Preferably on MacOSX.
For I found the formatting acting kind of weird in some cases when selecting multiple columns or writing long queries. I figure there must be some things you setup at the start and then can not live without...
Any other tips are also welcome! Thanks
My .psqlrc:
\set QUIET 1
\x auto
\timing on
\set QUIET 0Or use logical replication to reduce the downtime even more.