3 ms·

I don't get it? Table partition is already supported in PostgreSQL now and has been for a long time now (at least since 8.1); Where I work we utilize table part

• fabian2k 10y ago
  As far as I understand, this is about declarative partioning. So you don't have to implement all the details yourself anymore, you just declare how a table should be partioned instead of defining tables, triggers, ...
  • amitlan 10y ago
    Note that there is no shorthand syntax (yet), where you define a partitioned schema in just one line of DDL. As of now, you still need to create the root partitioned table as one command specifying the partitioning method (list or range), partitioning columns (aka PARTITION BY LIST | RANGE (<columns>)) and then a command for every partition specifying the partition bounds. No triggers or CHECK constraints anymore though. Why that way? Because we then don't have to assume any particular use case, for which to provide a shorthand syntax -- like fixed width/interval range partitions, etc. That said, having the syntax described at the beginning of the last paragraph in the initial version does not preclude offering a shorthand syntax in later releases, as, and if we figure out that offering some such syntax for more common use cases is useful after all.
• sapling 10y ago
  It sounds like this is column level partitioning.Each column or columns (based on partitioning expression) is stored as different subtable (or something similar) on disk.If only few columns are frequently accessed, they can be put on cache/faster disk or other neat optimizations for join processing.
  • MarHoff 10y ago
    Maybe I don't get you, but i don't think so, PostgreSQL is not a columnar database. If i got this patch right, each partitioned table will have the same data structure and store whole rows (it's even more restrictive than previous inheritance mechanism that allowed extending by adding additional columns). Column or expression should only define in which table an inserted row is supposed to be stored. A single row will never been torn apart. Still it look like a foundation that facilitate sharding BigData(Set) between multiple servers when used in conjunction with foreign data. However a lot of performance improvements will still be needed to compete against solid NoSQL projects (in which you really have a BigData use case). But looking a bit forward, developing performances improvements on top of an ACID compliant distributed database seems less difficult than to develop a NoSQL project for it to become ACID.
    • spacemanmatt 10y ago
      Generally PostgreSQL is a row database but Citus has release column drivers that make it perform similar to other columnar databases. But this patch is about horizontal partitions.
  • lobster_johnson 10y ago
    No, it's row-level partitioning — splits a single logical table into multiple physical tables.
  • colanderman 10y ago
    It is not. The partitions are by row. The novelty is in that it is truly native support, rather than a couple optimizations to make roll-your-own palatable.
    • sapling 10y ago
      Sorry I am wrong.I saw the PARTITION BY {column name} and lazy me assumed things.
  • sapling 10y ago
    I was wrong.It isn't column partitioning.
• roller 10y ago
  The linked patch notes specifically mention the difference between this and table inheritance based partitioning. Because table partitioning is less general than table inheritance, it is hoped that it will be easier to reason about properties of partitions, and therefore that this will serve as a better foundation for a variety of possible optimizations, including query planner optimizations.
• rtkwe 10y ago
  "Supported" in so far as you could basically roll your own implementation, having it managed by the engine is massively more useful and easier to support and setup. A lot of things are supported if you're willing to bodge it together like that.