5 ms·

#1 seems a bit weird. You could theoretically have the DB automatically create new buckets for a hash partition, but hash partitions shouldn't have hot spots un

#1 seems a bit weird. You could theoretically have the DB automatically create new buckets for a hash partition, but hash partitions shouldn't have hot spots unless you hash on a bad column, and creating new buckets will require all existing rows to be moved. For other types of partitions, partition hotspots would be the fault of the DBA partitioning on a bad column. The DB could theoretically half partition ranges if a range partition is very large, but there are other things that are more important. #2 is a nice performance feature which has been talked about before, but do any DB's actually have this yet? I suspect that it is quite tricky, and won't be universally beneficial. Is #3 really a problem? I certainly wouldn't expect schema changes to perform well, and increasing complexity just to help in this area would seem silly.

rantanplan 8y ago
> Is #3 really a problem? I certainly wouldn't expect schema changes to perform well, and increasing complexity just to help in this area would seem silly. Not the parent, but obviously they refer to the online schema migration problem. Without that online schema migrations are basically impossible. Or you get handed the burden of implementing or finding a tool that does it for you. And yes, pt-online-schema-change[1] is such a tool, but for MySQL. MariaDB has this feature built-in with their `ALTER ONLINE TABLE` [2]. So something similar for Postgres would be godsent. [1] https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/LATEST/pt-online... [2] https://mariadb.com/kb/en/library/alter-table/ https://mariadb.com/kb/en/library/alter-table/
- perlwle 8y ago
  mysql 5.7 has pretty good online DDL support[1] I have been using it exclusively now instead of pt-online-schema-change. [1] https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.ht...
londons_explore 8y ago
> hash partitions shouldn't have hot spots unless you hash on a bad column In the nice world of all data having an even load, sure... But in the real world, you can easily get a handful of users in your "Users" table sending millions of requests per second, and in that case, you would really like to re-partition so that they don't happen to all end up on the same partition. Implementation can be as simple as allowing splittable partitions, so that whenever load/size gets too high, a partition can be split in half, and half the records moved to a new host. The partition map is only a few kilobytes, so is globally shared/updated. There are no concurrency issues, because during the splitting process, either the old or new partition is responsible for each record, and both the old and new partition hosts can respond to a read or write query, either themselves or by forwarding it to the other host. Whenever two neighbouring partitions both see low load/space usage, join them with the same method in reverse. By joining only neighbouring partitions, you can't suffer terrible fragmentation and blowing up the size of the global partition map.
- pgaddict 8y ago
  I'm not sure what you mean by "move to a new host" - all the built-in partitioning features are single-host for now, so this does not make much sense. There are plans to support remote (foreign) partitions, but that is in the future. A more immediate use case is partition-aware algorithms. For example joining equi-partitioned tables can be done at the partition level, and the smaller the partitions the faster the join (likely). Or partition-aware aggregation, when the partition key is included in the GROUP BY keys. And so on. In that case it makes sense to split the largest partitions to make them smaller.
- arghwhat 8y ago
  Partitions provide separation, not load balancing, and does thus not concern themselves with load or size. While a can imagine a way to make hashed partitioning sub-divide buckets, it won't really be useful, as you can still have a single user blowing their bucket out of proportions with others being almost empty, which partitioning can never solve. What you want is round robin distribution and parallel query execution, not partitioning. This of course only makes sense if the tables are on different disk systems/hosts or if all tables fit in memory.
hobs 8y ago
Oracle has had it for awhile (though I forget what its called.) SQL Server is getting it in 2017 https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/pe...
- gigatexal 8y ago
  Oracle is also your first born expensive and Postgres is free.
  - gigatexal 8y ago
    Really? Someone was offended and/ or defending the obscene pricing of Oracle?
    - howdydoo 8y ago
      Oracle has the right to charge money for their products if they want to. Most people here make a living from exchanging things for money that are better in some way than things you can get for free. (I didn't downvote you, btw)
      - gigatexal 8y ago
        They could charge a trillion dollars for their software. I am not poo-pooing that. I am just upset with their practices in extracting those payments.
      - arghwhat 8y ago
        Of course. And likewise, people have the right to disagree with or be upset about their pricing model, billing approach and business practices.

rantanplan 8y ago

> Is #3 really a problem? I certainly wouldn't expect schema changes to perform well, and increasing complexity just to help in this area would seem silly. Not the parent, but obviously they refer to the online schema migration problem. Without that online schema migrations are basically impossible. Or you get handed the burden of implementing or finding a tool that does it for you. And yes, pt-online-schema-change[1] is such a tool, but for MySQL. MariaDB has this feature built-in with their `ALTER ONLINE TABLE` [2]. So something similar for Postgres would be godsent. [1] https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/LATEST/pt-online... [2] https://mariadb.com/kb/en/library/alter-table/ https://mariadb.com/kb/en/library/alter-table/

perlwle 8y ago
mysql 5.7 has pretty good online DDL support[1] I have been using it exclusively now instead of pt-online-schema-change. [1] https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.ht...

londons_explore 8y ago

> hash partitions shouldn't have hot spots unless you hash on a bad column In the nice world of all data having an even load, sure... But in the real world, you can easily get a handful of users in your "Users" table sending millions of requests per second, and in that case, you would really like to re-partition so that they don't happen to all end up on the same partition. Implementation can be as simple as allowing splittable partitions, so that whenever load/size gets too high, a partition can be split in half, and half the records moved to a new host. The partition map is only a few kilobytes, so is globally shared/updated. There are no concurrency issues, because during the splitting process, either the old or new partition is responsible for each record, and both the old and new partition hosts can respond to a read or write query, either themselves or by forwarding it to the other host. Whenever two neighbouring partitions both see low load/space usage, join them with the same method in reverse. By joining only neighbouring partitions, you can't suffer terrible fragmentation and blowing up the size of the global partition map.

pgaddict 8y ago
I'm not sure what you mean by "move to a new host" - all the built-in partitioning features are single-host for now, so this does not make much sense. There are plans to support remote (foreign) partitions, but that is in the future. A more immediate use case is partition-aware algorithms. For example joining equi-partitioned tables can be done at the partition level, and the smaller the partitions the faster the join (likely). Or partition-aware aggregation, when the partition key is included in the GROUP BY keys. And so on. In that case it makes sense to split the largest partitions to make them smaller.
arghwhat 8y ago
Partitions provide separation, not load balancing, and does thus not concern themselves with load or size. While a can imagine a way to make hashed partitioning sub-divide buckets, it won't really be useful, as you can still have a single user blowing their bucket out of proportions with others being almost empty, which partitioning can never solve. What you want is round robin distribution and parallel query execution, not partitioning. This of course only makes sense if the tables are on different disk systems/hosts or if all tables fit in memory.

hobs 8y ago

Oracle has had it for awhile (though I forget what its called.) SQL Server is getting it in 2017 https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/pe...

gigatexal 8y ago
Oracle is also your first born expensive and Postgres is free.
- gigatexal 8y ago
  Really? Someone was offended and/ or defending the obscene pricing of Oracle?
  - howdydoo 8y ago
    Oracle has the right to charge money for their products if they want to. Most people here make a living from exchanging things for money that are better in some way than things you can get for free. (I didn't downvote you, btw)
    - gigatexal 8y ago
      They could charge a trillion dollars for their software. I am not poo-pooing that. I am just upset with their practices in extracting those payments.
    - arghwhat 8y ago
      Of course. And likewise, people have the right to disagree with or be upset about their pricing model, billing approach and business practices.