7 ms·

Plans for partitioning in PostgreSQL v11

241 points by anarazel 9y ago

gfodor 9y ago
One of the things that is continually great about PostgreSQL is how they consistently deliver a solid v1 of new features that are reliable, well engineered, and semantically sane. I'm sure partitioning will be another example of this kind of thing where in a few years you will have both all the bells and whistles and also firm footing in solid RDBMS engineering.
- X86BSD 9y ago
  PostgreSQL and BSD are standouts in that regard.
  - ksec 9y ago
    And I wish both are used more often.
- cat199 9y ago
  .. agree with the overall sentiment. As a nit, postgres has had tablespace style partitioning for some time, but these feature adds much more control. Not sure if that was your meaning or not.
joaodlf 9y ago
The direction being taken with data partitioning is great. I truly believe this will solve the issues of many companies when it comes to "big data"... This, to me, is the middle ground we have been missing between rdbms and nosql. Congrats to the postgres team, what an interesting product.
- cwyers 9y ago
  Every other major RDBMS has had this for years. The only people who have been missing this are Postgres users.
  - dfox 9y ago
    On the other hand, PostgreSQL had some notion of partitioning essentially since it's inception. Ten years ago PostgreSQL's rules and table inheritance based partitioning was probably only DBMS-integrated solution that was actually usable.
andy_ppp 9y ago
This looks very good but after using Cassandra I'd be scared of bumping into features that are not supported or suddenly very slow if used. For example I'm sure sorted aggregations and group by queries across partitions will take a long time. I think there should be a list of Postgres partitioning gotchas somewhere to accompany this, e.g. partitioning goes hand in hand with denormalisation (due to the effects of the chosen partition key being so important). With Cassandra I can simply add more machines as I increase the data duplication, I'm not sure how easy it is to rebalance data when adding new Postgres instances?
- pgaddict 9y ago
  I'm not sure what you mean by "adding new Postgres instances." Postgres is not a distributed database, at least not on it's own. There are ways to scale using e.g. hot standbys (streaming replicas for read-only queries) and multiple projects that implement distributed databases on top of Postgres (Citus, Postgres-XL, ...). But then it depends mostly on that project, not on Postgres. Sure, Partitioning does negatively affect some queries. But the thing is - it's a significant improvement compared to the previous partitioning implementation, which had almost no insight into the partitioning rules. And so optimizer could not really do advanced tricks (e.g. partition-wise joins) etc.
  - andy_ppp 9y ago
    I'm not sure what the point of partitioning would be if the data isn't distributed between nodes? I'm probably not understanding. All I'm saying is I can go and read about the pain points in say Cassandra for this type of stuff because of where it comes from and it tries to protect you from doing things that are slow. With Postgres I think it's going to be harder to know when I can't use a feature that's suddenly going to kill my performance. It would be great if there was a resource for how to avoid such issues in partitioned Postgres but it'd take a lot of work to write such a guide - I'm guessing things like JSONB queries, Geo, Transactions, Aggregations across partitions and many extensions are all going to cause pain points?
    - 9y ago
rorygibson 9y ago
As a long time user of MySQL, I've switched to PostgreSQL for my most recent startup precisely because the PostgreSQL team continue to innovate in a sensible way. One of the core requirements is a stable, fast and reliable initial import of existing data from 3rd party services, and because of throttling concerns that led us to a batched, retryable solution - and a requirement for simple, fast job control. We've built this around a parallelised solution using the FOR UPDATE/SKIP LOCKED feature from PostgreSQL 9.5 onwards. It's not bleeding edge, but for us it was a great example of how the pg team pick up the useful features from e.g. Oracle, and incorporate them into the product in a solid way, without all the enterprisey marketing crap that Oracle make you swallow before you can understand the actual feature-set. [the startup is a SaaS search tool that helps you index across Trello, email, GitHub, Slack, Drive - https://getctx.io https://getctx.io if you're interested.]
- grogers 9y ago
  It might be a good choice to switch to postgres, but it isn't like MySQL isn't innovating too. 8.0 will have a ton of features. This includes a bunch of "catch up" things like CTEs and SKIP LOCKED, but also stuff like synchronous replication built in.
  - rhaas 9y ago
    The first release of PostgreSQL that had synchronous replication was released in September 2011. https://www.postgresql.org/docs/9.1/static/release-9-1.html https://www.postgresql.org/docs/9.1/static/release-9-1.html
  - jimktrains2 9y ago
    I know the defaults have been changed to be safe, but I will forever feel like my trust was broken with MySQL the day I learned how bad the defaults are. Not just inefficent/generic, but actually bad. Additionally, I feel that postgresql is better engineered, more featureful, and has support for a much larger variety of use-cases (e.g. hstore, json, postgis, pg-routing, uuids, trigram indecies, &c). So between engineering perks, and the loss of trust, I only use MySQL in legacy applications that I cannot port.
qaq 9y ago
The rate of evolution in PG is truly inspiring at this rate commercial RDBMS will not have much market left for them to play in.
- systems 9y ago
  if i take ms sql server for example it covers a lot more use cases out of the box compared to postgresql ms sql is making significant progress for in memory optimization and in memory oltp .. i dont think postgres have any plans in this area also ms sql, comes with ssis, ssrs and ssas, all under the same license you pay for ms sql server ssas has no serious competition in the open source world and i would even argue it has no serious competition in the commercial world the point is, commercial RDBMS comes packaged with a lot more than just the db engine .. postgresql will never compete with that
  - riku_iki 9y ago
    > commercial RDBMS comes packaged with a lot more than just the db engine It may be strength of psql and not weakness, they are focused on one specific niche, which actually covers 95% of business use cases, and trying to do it very well. For all other cases, there are other products as well. Most of the data science world talks R and SQL now days, and not mdx.
- Thaxll 9y ago
  Well it's pretty much all about support and integration at some point, Postgres is like MySQL they have integration with open source app but nothing in the enterprise world.
  - dfox 9y ago
    In my enterprise niche (logistics) it seems that PostgreSQL is the go to solution for essentially every new project (and surprisingly often is proposed by customers themselves).
- lobster_johnson 9y ago
  While Postgres is indeed fantastic, Oracle and Microsoft SQL Server are both ahead of Postgres in many areas. For example, the feature that this article is talking about — table partitioning — has been in Oracle for at least 20 years. Oracle's replication support is also extensive, and supports many scenarios such as multimaster, not to mention sophisticated solutions such as RAC and Dataguard. Then there's materialized views, flashback queries, clustered tables (this is manual in Postgres), parallel queries, etc. So Postgres is still mostly catching up to other databases. It still has features others don't, of course, such as transactional DDL statements. And it's open source.
btilly 9y ago
Hopefully the work on better indexing of partitioned tables will also apply to inherited tables. I regularly work with tables with over 1000 child tables, using single value lookups which would be trivial in an index. But having to query 1000 indexes makes the inheritance useless and we have to jump hoops to query the right child table instead. That this is a serious limitation has been recognized for many releases. But the limitation is still there.

241 points by anarazel 9y ago

One of the things that is continually great about PostgreSQL is how they consistently deliver a solid v1 of new features that are reliable, well engineered, and semantically sane. I'm sure partitioning will be another example of this kind of thing where in a few years you will have both all the bells and whistles and also firm footing in solid RDBMS engineering.

X86BSD 9y ago
PostgreSQL and BSD are standouts in that regard.
- ksec 9y ago
  And I wish both are used more often.
cat199 9y ago
.. agree with the overall sentiment. As a nit, postgres has had tablespace style partitioning for some time, but these feature adds much more control. Not sure if that was your meaning or not.

joaodlf 9y ago

The direction being taken with data partitioning is great. I truly believe this will solve the issues of many companies when it comes to "big data"... This, to me, is the middle ground we have been missing between rdbms and nosql. Congrats to the postgres team, what an interesting product.

cwyers 9y ago
Every other major RDBMS has had this for years. The only people who have been missing this are Postgres users.
- dfox 9y ago
  On the other hand, PostgreSQL had some notion of partitioning essentially since it's inception. Ten years ago PostgreSQL's rules and table inheritance based partitioning was probably only DBMS-integrated solution that was actually usable.

andy_ppp 9y ago

This looks very good but after using Cassandra I'd be scared of bumping into features that are not supported or suddenly very slow if used. For example I'm sure sorted aggregations and group by queries across partitions will take a long time. I think there should be a list of Postgres partitioning gotchas somewhere to accompany this, e.g. partitioning goes hand in hand with denormalisation (due to the effects of the chosen partition key being so important). With Cassandra I can simply add more machines as I increase the data duplication, I'm not sure how easy it is to rebalance data when adding new Postgres instances?

pgaddict 9y ago
I'm not sure what you mean by "adding new Postgres instances." Postgres is not a distributed database, at least not on it's own. There are ways to scale using e.g. hot standbys (streaming replicas for read-only queries) and multiple projects that implement distributed databases on top of Postgres (Citus, Postgres-XL, ...). But then it depends mostly on that project, not on Postgres. Sure, Partitioning does negatively affect some queries. But the thing is - it's a significant improvement compared to the previous partitioning implementation, which had almost no insight into the partitioning rules. And so optimizer could not really do advanced tricks (e.g. partition-wise joins) etc.
- andy_ppp 9y ago
  I'm not sure what the point of partitioning would be if the data isn't distributed between nodes? I'm probably not understanding. All I'm saying is I can go and read about the pain points in say Cassandra for this type of stuff because of where it comes from and it tries to protect you from doing things that are slow. With Postgres I think it's going to be harder to know when I can't use a feature that's suddenly going to kill my performance. It would be great if there was a resource for how to avoid such issues in partitioned Postgres but it'd take a lot of work to write such a guide - I'm guessing things like JSONB queries, Geo, Transactions, Aggregations across partitions and many extensions are all going to cause pain points?
  - 9y ago

rorygibson 9y ago

As a long time user of MySQL, I've switched to PostgreSQL for my most recent startup precisely because the PostgreSQL team continue to innovate in a sensible way. One of the core requirements is a stable, fast and reliable initial import of existing data from 3rd party services, and because of throttling concerns that led us to a batched, retryable solution - and a requirement for simple, fast job control. We've built this around a parallelised solution using the FOR UPDATE/SKIP LOCKED feature from PostgreSQL 9.5 onwards. It's not bleeding edge, but for us it was a great example of how the pg team pick up the useful features from e.g. Oracle, and incorporate them into the product in a solid way, without all the enterprisey marketing crap that Oracle make you swallow before you can understand the actual feature-set. [the startup is a SaaS search tool that helps you index across Trello, email, GitHub, Slack, Drive - https://getctx.io https://getctx.io if you're interested.]

grogers 9y ago
It might be a good choice to switch to postgres, but it isn't like MySQL isn't innovating too. 8.0 will have a ton of features. This includes a bunch of "catch up" things like CTEs and SKIP LOCKED, but also stuff like synchronous replication built in.
- rhaas 9y ago
  The first release of PostgreSQL that had synchronous replication was released in September 2011. https://www.postgresql.org/docs/9.1/static/release-9-1.html https://www.postgresql.org/docs/9.1/static/release-9-1.html
- jimktrains2 9y ago
  I know the defaults have been changed to be safe, but I will forever feel like my trust was broken with MySQL the day I learned how bad the defaults are. Not just inefficent/generic, but actually bad. Additionally, I feel that postgresql is better engineered, more featureful, and has support for a much larger variety of use-cases (e.g. hstore, json, postgis, pg-routing, uuids, trigram indecies, &c). So between engineering perks, and the loss of trust, I only use MySQL in legacy applications that I cannot port.

qaq 9y ago

The rate of evolution in PG is truly inspiring at this rate commercial RDBMS will not have much market left for them to play in.

systems 9y ago
if i take ms sql server for example it covers a lot more use cases out of the box compared to postgresql ms sql is making significant progress for in memory optimization and in memory oltp .. i dont think postgres have any plans in this area also ms sql, comes with ssis, ssrs and ssas, all under the same license you pay for ms sql server ssas has no serious competition in the open source world and i would even argue it has no serious competition in the commercial world the point is, commercial RDBMS comes packaged with a lot more than just the db engine .. postgresql will never compete with that
- riku_iki 9y ago
  > commercial RDBMS comes packaged with a lot more than just the db engine It may be strength of psql and not weakness, they are focused on one specific niche, which actually covers 95% of business use cases, and trying to do it very well. For all other cases, there are other products as well. Most of the data science world talks R and SQL now days, and not mdx.
Thaxll 9y ago
Well it's pretty much all about support and integration at some point, Postgres is like MySQL they have integration with open source app but nothing in the enterprise world.
- dfox 9y ago
  In my enterprise niche (logistics) it seems that PostgreSQL is the go to solution for essentially every new project (and surprisingly often is proposed by customers themselves).
lobster_johnson 9y ago
While Postgres is indeed fantastic, Oracle and Microsoft SQL Server are both ahead of Postgres in many areas. For example, the feature that this article is talking about — table partitioning — has been in Oracle for at least 20 years. Oracle's replication support is also extensive, and supports many scenarios such as multimaster, not to mention sophisticated solutions such as RAC and Dataguard. Then there's materialized views, flashback queries, clustered tables (this is manual in Postgres), parallel queries, etc. So Postgres is still mostly catching up to other databases. It still has features others don't, of course, such as transactional DDL statements. And it's open source.

btilly 9y ago

Hopefully the work on better indexing of partitioned tables will also apply to inherited tables. I regularly work with tables with over 1000 child tables, using single value lookups which would be trivial in an index. But having to query 1000 indexes makes the inheritance useless and we have to jump hoops to query the right child table instead. That this is a serious limitation has been recognized for many releases. But the limitation is still there.