5 ms·

How close is this to the way MySQL does it with InnoDB? It appears to be about the same.

seunosewa 1y ago
Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.
- ARandomerDude 1y ago
  Can you elaborate on this B-tree part of your comment? I know B-tree is the default index type in pg, but it sounds like there’s more to the story that I’m not familiar with.
  - greenavocado 1y ago
    PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file InnoDB uses a clustered index approach. The primary key index is a B-tree. The actual table data is stored in the leaf nodes of this B-tree. Secondary indexes point to the primary key. One is not better than the other in general terms. InnoDB's clustered B-tree approach shines when: You frequently access data in primary key order Your workload has many range scans on the primary key You need predictable performance for primary key lookups Your data naturally has a meaningful ordering that matches your access patterns PostgreSQL's heap approach excels when: You frequently update non-key columns (less page splits/reorganization) You have many secondary indexes (they're smaller without primary keys) Your access patterns vary widely and don't follow one particular field You need faster table scans when indexes aren't applicable I personally find PostgreSQL's approach more flexible for complex analytical workloads with unpredictable access patterns, while InnoDB's clustered approach feels more optimized for OLTP workloads with predictable key-based access patterns. The "better" system depends entirely on your specific workload, data characteristics, and access patterns.
    - farazbabar 1y ago
      Don't forget high speed committed writes to append only tables (the opposite of scans), postgres approach is better here as well.
    - saltcured 1y ago
      It's also deeply entwined with the MVCC concurrency control and the ability to do DDL in transactions, right?
      - jiggawatts 1y ago
        SQL Server supports every combination of heap storage, clustered storage, MVCC, and DDL in transactions.
    - Sesse__ 1y ago
      Indexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrelated column, InnoDB doesn't need to update those indexes to point to the location of the new row. But I'm generally very rarely annoyed by that in comparison.)
      - sroussey 1y ago
        On commercial databases from Oracle and Microsoft, you can cluster the DB on any index. Really would love for Postresql to do the same.
        cwbriscoe 1y ago
        You can but new rows will not be clustered until you re-cluster. CLUSTER table_name USING index_name;
        1y ago
  - cryptonector 1y ago
    SQLite3 only has b-trees for files and indices. PostgreSQL only has heaps for tables, and various other data structures for indices, with b-tree being the default for indices. There are many cases where having b-trees for tables would make performance better. GP is basically poking fun at PG for still having nothing but heaps for tables.
- tehlike 1y ago
  There's a lot more PG needs to do for storage layout / access pov, and they have been working on it for a while. Orioledb has shown what might be opssible, and they have been upstreaming it. Having the ability to do something LSM as a storage engine would be great - and potentially allow better compression than what we currently get with TOAST - which is not a lot... PG doesn't even have oob page compression...
- ksec 1y ago
  Despite having relatively fewer improvement on the MySQL side, especially the OSS version and not the enterprise version, there are still many areas Postgres have not exceeded MySQL, and doesn't look like will be doing so in next 3 - 4 years.

Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.

ARandomerDude 1y ago
Can you elaborate on this B-tree part of your comment? I know B-tree is the default index type in pg, but it sounds like there’s more to the story that I’m not familiar with.
- greenavocado 1y ago
  PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file InnoDB uses a clustered index approach. The primary key index is a B-tree. The actual table data is stored in the leaf nodes of this B-tree. Secondary indexes point to the primary key. One is not better than the other in general terms. InnoDB's clustered B-tree approach shines when: You frequently access data in primary key order Your workload has many range scans on the primary key You need predictable performance for primary key lookups Your data naturally has a meaningful ordering that matches your access patterns PostgreSQL's heap approach excels when: You frequently update non-key columns (less page splits/reorganization) You have many secondary indexes (they're smaller without primary keys) Your access patterns vary widely and don't follow one particular field You need faster table scans when indexes aren't applicable I personally find PostgreSQL's approach more flexible for complex analytical workloads with unpredictable access patterns, while InnoDB's clustered approach feels more optimized for OLTP workloads with predictable key-based access patterns. The "better" system depends entirely on your specific workload, data characteristics, and access patterns.
  - farazbabar 1y ago
    Don't forget high speed committed writes to append only tables (the opposite of scans), postgres approach is better here as well.
  - saltcured 1y ago
    It's also deeply entwined with the MVCC concurrency control and the ability to do DDL in transactions, right?
    - jiggawatts 1y ago
      SQL Server supports every combination of heap storage, clustered storage, MVCC, and DDL in transactions.
  - Sesse__ 1y ago
    Indexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrelated column, InnoDB doesn't need to update those indexes to point to the location of the new row. But I'm generally very rarely annoyed by that in comparison.)
    - sroussey 1y ago
      On commercial databases from Oracle and Microsoft, you can cluster the DB on any index. Really would love for Postresql to do the same.
      - cwbriscoe 1y ago
        You can but new rows will not be clustered until you re-cluster. CLUSTER table_name USING index_name;
        1y ago
- cryptonector 1y ago
  SQLite3 only has b-trees for files and indices. PostgreSQL only has heaps for tables, and various other data structures for indices, with b-tree being the default for indices. There are many cases where having b-trees for tables would make performance better. GP is basically poking fun at PG for still having nothing but heaps for tables.
tehlike 1y ago
There's a lot more PG needs to do for storage layout / access pov, and they have been working on it for a while. Orioledb has shown what might be opssible, and they have been upstreaming it. Having the ability to do something LSM as a storage engine would be great - and potentially allow better compression than what we currently get with TOAST - which is not a lot... PG doesn't even have oob page compression...
ksec 1y ago
Despite having relatively fewer improvement on the MySQL side, especially the OSS version and not the enterprise version, there are still many areas Postgres have not exceeded MySQL, and doesn't look like will be doing so in next 3 - 4 years.