4 ms·

It is hard to overstate how poorly optimized the Postgres architecture is for modern analytical workloads on modern hardware. It is an OLTP engine with 1990s de

It is hard to overstate how poorly optimized the Postgres architecture is for modern analytical workloads on modern hardware. It is an OLTP engine with 1990s design assumptions. You can modify it heavily to make it adequate for analytics but it won't be anything close to optimal due to architectural limitations. And I say that as a big fan of Postgres. There is a longstanding heuristic that, outside of the workload for which Postgres was designed, a state-of-the-art database implementation will be 100x faster on the same hardware. That heuristic is well-grounded in reality in my experience; I often measure new database kernel implementations relative to Postgres. Postgres is still great for classic OLTP workloads and you will find that the gap is smaller there. For analytical processing, 100x gain is pretty straightforward. No one should read this as reflecting poorly on Postgres. Database architecture and the hardware it runs on has evolved enormously in the decades since Postgres was designed. Most people have no sense of just how fast and scalable a modern database kernel is compared to the designs from 20+ years ago.

closeparen 4y ago
What are some modern databases in this context?
zasdffaa 4y ago
I did some utterly trivial benchmarks of aggregating over a (large) single table, and found PG and MSSQL to be about the same (both using row storage). Where does your 100X slowdown figure come from, and what 'architectural limitations' are you referring to? Genuine question as DBs architectures are interesting to me. TIA
- nicoburns 4y ago
  > both using row storage > Where does your 100X slowdown figure come from Given they specify analytical workloads, I'm betting a lot of it comes from column storage.
  - zasdffaa 4y ago
    I need some ELI5 explaining to: colstores allow you to pick out just the cols of data you need rather than get the full row and discard what you don't need. If that alone explained the 100X speedup that would mean the row is storing 99% of data that's not of interest to a query. That would a) be unlike pretty most table structures + queries I've ever seen, but in those cases cases such as when a fat blob is stored in the row, you hive off blobs to a separate table and only join to that when blob's wanted. I'm missing something big here.
    - nicoburns 4y ago
      I doubt it's that alone (and a 10% factor is much more reasonable than a 1% factor). But I think another factor is that column stores usually compress the data in each column. Which can be particularly effective for columns with a lot of repeated values.
      - zasdffaa 4y ago
        Missed compression, thanks!
    - eatonphil 4y ago
      Try it out yourself experimentally. You'll see the difference quickly. https://datastation.multiprocess.io/blog/2021-10-18-experimenting-with-column-and-row-oriented-datastructures.html https://datastation.multiprocess.io/blog/2021-10-18-experime...
      - zasdffaa 4y ago
        Might be missing something, but using an interpreted language that splashes its objects around in memory with pointers everywhere, using json, and gets a 50% speedup, depending, doesn't look like a convincing test of anything.
        eatonphil 4y ago
- buremba 4y ago
  Columnar storage, pre-aggregation, and advanced indexing techniques based on algorithms like bloom indexes, hyperloglog, etc.
  - zasdffaa 4y ago
    hyperloglog is not an indexing method
- menaerus 4y ago
  > and what 'architectural limitations' are you referring to? Row-based vs columnar-based storage. Latter is more suitable (read much faster) for OLAP workloads.
- jandrewrogers 4y ago
  Postgres and Sql Server are both similar generations of database architecture, so I would expect them to perform similarly in some aggregate sense. For Postgres, I would argue that the major architectural limitations that impact relative performance are in three areas: - The storage engine architecture is obsolete and highly suboptimal. It has no way to accommodate modern high-density or high-bandwidth storage, both of which are the norm now, nor is it possible to implement any of the (very successful) I/O schedule optimization concepts that have emerged in recent years. The net effect is a gross underutilization of the capabilities of modern storage hardware. - The internal page layout and table architecture in Postgres is a textbook classic but not appropriate for most workloads on modern hardware. Modern page engines need great performance across diverse categories of workloads and data models -- we use databases for a lot more than accounting systems these days -- while also being amenable to aggressive use of hardware optimization like SIMD. Thinking in terms of "row-oriented" and "column-oriented" is a false dichotomy; optimized page engines commonly have many elements of both but are not identifiable as pure expressions of either nor even a simple hybrid (e.g. PAX layouts). This is one of the easiest changes to backport into old database architectures. - Indexing in some modern systems have been completely reimagined to great effect. Tables are effectively index-organized with no secondary indexing structures but without loss of high query selectivity across diverse columns; there is a separation of the concerns regarding fast search and key constraint enforcement; major increases in index write throughput concurrent with major decreases in storage and memory footprint (no B-tree bloat). For small tables, this optimization has no effect and may even be a mild pessimization depending on the workload. As tables become larger the performance starts to diverge greatly, becoming multiple orders of magnitude faster. The Postgres architecture can't be modified to support this. If you built a database engine that was approximately state-of-the-art in these three areas, I would expect a 100x difference in performance relative to Postgres for many large data model workloads using server hardware like an AWS i3en.24xlarge. Obviously building such a database would be a hell of a lot of work, it isn't something you can reasonably do for fun on nights and weekends.
  - zasdffaa 4y ago
    Edit: this comes across rude (I suppose it is), and maybe I'm wrong to doubt you but I'm no DB n00b and pretty well everything about your post just doesn't make sense, given my knowledge. What? [deleted] A quick browse then... > The storage engine architecture is obsolete and highly suboptimal cite? > It has no way to accommodate modern high-density or high-bandwidth storage WTF does this even mean? Storage is a hardware issue, nothing to do with the DB engine. And it has mmap and huge pages <https://www.postgresql.org/docs/current/runtime-config-resource.html https://www.postgresql.org/docs/current/runtime-config-resou...> so what's it missing? > nor is it possible to implement any of the (very successful) I/O schedule optimization concepts that have emerged in recent years. The net effect is a gross underutilization of the capabilities of modern storage hardware. No evidence given. > Modern page engines need great performance across diverse categories of workloads and data models It's chunks in memory that's all, how is it failing then? > Indexing in some modern systems have been completely reimagined to great effect. Tables are effectively index-organized with no secondary indexing structures just what? > but without loss of high query selectivity across diverse columns selectivity is a feature of data, not how it is organised, at all > there is a separation of the concerns regarding fast search and key constraint enforcement what? what? what? what? what? what? what? what? Search is a read issue, key constraints are a write issue...? > no B-tree bloat seriously WTF? > As tables become larger the performance starts to diverge greatly, becoming multiple orders of magnitude faster "multiple orders of magnitude" - Wuuuut? cite please

closeparen 4y ago

What are some modern databases in this context?

zasdffaa 4y ago

I did some utterly trivial benchmarks of aggregating over a (large) single table, and found PG and MSSQL to be about the same (both using row storage). Where does your 100X slowdown figure come from, and what 'architectural limitations' are you referring to? Genuine question as DBs architectures are interesting to me. TIA

nicoburns 4y ago
> both using row storage > Where does your 100X slowdown figure come from Given they specify analytical workloads, I'm betting a lot of it comes from column storage.
- zasdffaa 4y ago
  I need some ELI5 explaining to: colstores allow you to pick out just the cols of data you need rather than get the full row and discard what you don't need. If that alone explained the 100X speedup that would mean the row is storing 99% of data that's not of interest to a query. That would a) be unlike pretty most table structures + queries I've ever seen, but in those cases cases such as when a fat blob is stored in the row, you hive off blobs to a separate table and only join to that when blob's wanted. I'm missing something big here.
  - nicoburns 4y ago
    I doubt it's that alone (and a 10% factor is much more reasonable than a 1% factor). But I think another factor is that column stores usually compress the data in each column. Which can be particularly effective for columns with a lot of repeated values.
    - zasdffaa 4y ago
      Missed compression, thanks!
  - eatonphil 4y ago
    Try it out yourself experimentally. You'll see the difference quickly. https://datastation.multiprocess.io/blog/2021-10-18-experimenting-with-column-and-row-oriented-datastructures.html https://datastation.multiprocess.io/blog/2021-10-18-experime...
    - zasdffaa 4y ago
      Might be missing something, but using an interpreted language that splashes its objects around in memory with pointers everywhere, using json, and gets a 50% speedup, depending, doesn't look like a convincing test of anything.
      - eatonphil 4y ago
buremba 4y ago
Columnar storage, pre-aggregation, and advanced indexing techniques based on algorithms like bloom indexes, hyperloglog, etc.
- zasdffaa 4y ago
  hyperloglog is not an indexing method
menaerus 4y ago
> and what 'architectural limitations' are you referring to? Row-based vs columnar-based storage. Latter is more suitable (read much faster) for OLAP workloads.
jandrewrogers 4y ago
Postgres and Sql Server are both similar generations of database architecture, so I would expect them to perform similarly in some aggregate sense. For Postgres, I would argue that the major architectural limitations that impact relative performance are in three areas: - The storage engine architecture is obsolete and highly suboptimal. It has no way to accommodate modern high-density or high-bandwidth storage, both of which are the norm now, nor is it possible to implement any of the (very successful) I/O schedule optimization concepts that have emerged in recent years. The net effect is a gross underutilization of the capabilities of modern storage hardware. - The internal page layout and table architecture in Postgres is a textbook classic but not appropriate for most workloads on modern hardware. Modern page engines need great performance across diverse categories of workloads and data models -- we use databases for a lot more than accounting systems these days -- while also being amenable to aggressive use of hardware optimization like SIMD. Thinking in terms of "row-oriented" and "column-oriented" is a false dichotomy; optimized page engines commonly have many elements of both but are not identifiable as pure expressions of either nor even a simple hybrid (e.g. PAX layouts). This is one of the easiest changes to backport into old database architectures. - Indexing in some modern systems have been completely reimagined to great effect. Tables are effectively index-organized with no secondary indexing structures but without loss of high query selectivity across diverse columns; there is a separation of the concerns regarding fast search and key constraint enforcement; major increases in index write throughput concurrent with major decreases in storage and memory footprint (no B-tree bloat). For small tables, this optimization has no effect and may even be a mild pessimization depending on the workload. As tables become larger the performance starts to diverge greatly, becoming multiple orders of magnitude faster. The Postgres architecture can't be modified to support this. If you built a database engine that was approximately state-of-the-art in these three areas, I would expect a 100x difference in performance relative to Postgres for many large data model workloads using server hardware like an AWS i3en.24xlarge. Obviously building such a database would be a hell of a lot of work, it isn't something you can reasonably do for fun on nights and weekends.
- zasdffaa 4y ago
  Edit: this comes across rude (I suppose it is), and maybe I'm wrong to doubt you but I'm no DB n00b and pretty well everything about your post just doesn't make sense, given my knowledge. What? [deleted] A quick browse then... > The storage engine architecture is obsolete and highly suboptimal cite? > It has no way to accommodate modern high-density or high-bandwidth storage WTF does this even mean? Storage is a hardware issue, nothing to do with the DB engine. And it has mmap and huge pages <https://www.postgresql.org/docs/current/runtime-config-resource.html https://www.postgresql.org/docs/current/runtime-config-resou...> so what's it missing? > nor is it possible to implement any of the (very successful) I/O schedule optimization concepts that have emerged in recent years. The net effect is a gross underutilization of the capabilities of modern storage hardware. No evidence given. > Modern page engines need great performance across diverse categories of workloads and data models It's chunks in memory that's all, how is it failing then? > Indexing in some modern systems have been completely reimagined to great effect. Tables are effectively index-organized with no secondary indexing structures just what? > but without loss of high query selectivity across diverse columns selectivity is a feature of data, not how it is organised, at all > there is a separation of the concerns regarding fast search and key constraint enforcement what? what? what? what? what? what? what? what? Search is a read issue, key constraints are a write issue...? > no B-tree bloat seriously WTF? > As tables become larger the performance starts to diverge greatly, becoming multiple orders of magnitude faster "multiple orders of magnitude" - Wuuuut? cite please