3 ms·

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

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

jandrewrogers 4y ago
I'm on a long road trip, so can't spend a lot of time responding right now (gotta hit the road). But some quick points: The page layout is important because many workloads are memory-bandwidth bound. Effective selectivity mechanisms outside the page reduce the need for intra-page selectivity optimization in terms of delivering performance but you still need to preserve memory bandwidth. This biases designs with good page-external selectivity mechanisms to optimize for widening the set of workloads they perform well on instead of squeezing out slightly more selectivity for narrow workloads. Some of these assertions are self-evident and non-controversial, such as the poor storage performance of Postgres and the issue of B-tree bloat generally. Using mmap() for storage is the low-performance option, articles regarding which are regularly posted on HN, so the fact that it "has mmap" is a good example of why it is expected to perform poorly (though it isn't the only reason in the case of Postgres). I believe there are plans in the works to potentially redesign the Postgres storage layer in future versions, so this may improve at some point. But more broadly, you seem to be a bit confused about the theory of database kernel design and the tradeoffs that can be made there? You are questioning things about how actual, real, databases, including most open source ones, are designed.
- zasdffaa 4y ago
  ok > Effective selectivity mechanisms outside the page reduce the need for intra-page selectivity optimization in terms of delivering performance but you still need to preserve memory bandwidth "mechanisms/optimization/delivering performance" - these are just aspirational words - exactly what 'selectivity mechanisms'? I can barely make sense of this. In fact, I can't. At all. > Some of these assertions are self-evident and non-controversial They aren't self-evident because they aren't evident to me, and as for non-controversial - this is just ducking the question. You're still not justifying anything. > and the issue of B-tree bloat generally A btree page will be between 1/2 and completely full. Random insertions should make them ~75% full. In MSSQL I just created a table of ints (clustered PK) and inserted about a million random ints and had a look at fullness (page = 8060 bytes): - Avg. Bytes Free per Page.....................: 2046.7 - Avg. Page Density (full).....................: 74.71% yep, so an average of 3/4 full. Bit of expected free space. Where's the bloat you talk of, and what's the alternative structure if this is unacceptable. Edit: this is just leaf nodes. You might claim it's ignoring non-leaf nodes. Check: 4MB data gets you 56K non-leaf. Massive 2,000 factor fanout (8K page / 4 byte ints) is why. So what bloat? > Using mmap() for storage is the low-performance option, articles regarding which are regularly posted on HN, for small files, so I understand. For large AFAIK they're good, which is why they're used. So what's the alternative? Point to where HN says mmap is slow for large files. > that it "has mmap" is a good example of why it is expected to perform poorly justify this please. > But more broadly, you seem to be a bit confused about the theory of database kernel design and the tradeoffs that can be made there? You may be right but you've just handwaved my doubts away without any evidence. > You are questioning things about how actual, real, databases, including most open source ones, are designed. I'm questioning your claims in the hope I can understand better. I'm not a total noob. I am wondering why I can't make sense of you posts.
  - electricshampo1 4y ago
    It might be helpful to read something like DB2 with BLU Acceleration: So Much More than Just a Column Store https://db.cs.pitt.edu/courses/cs3551/16-1/handouts/db2BLU.pdf https://db.cs.pitt.edu/courses/cs3551/16-1/handouts/db2BLU.p... or Real-Time Analytical Processing with SQL Server https://www.vldb.org/pvldb/vol8/p1740-Larson.pdf https://www.vldb.org/pvldb/vol8/p1740-Larson.pdf to get a sense of the gap between postgres/open source databases vs something designed (at least partially) to better support analytical workloads.

jandrewrogers 4y ago

I'm on a long road trip, so can't spend a lot of time responding right now (gotta hit the road). But some quick points: The page layout is important because many workloads are memory-bandwidth bound. Effective selectivity mechanisms outside the page reduce the need for intra-page selectivity optimization in terms of delivering performance but you still need to preserve memory bandwidth. This biases designs with good page-external selectivity mechanisms to optimize for widening the set of workloads they perform well on instead of squeezing out slightly more selectivity for narrow workloads. Some of these assertions are self-evident and non-controversial, such as the poor storage performance of Postgres and the issue of B-tree bloat generally. Using mmap() for storage is the low-performance option, articles regarding which are regularly posted on HN, so the fact that it "has mmap" is a good example of why it is expected to perform poorly (though it isn't the only reason in the case of Postgres). I believe there are plans in the works to potentially redesign the Postgres storage layer in future versions, so this may improve at some point. But more broadly, you seem to be a bit confused about the theory of database kernel design and the tradeoffs that can be made there? You are questioning things about how actual, real, databases, including most open source ones, are designed.

zasdffaa 4y ago
ok > Effective selectivity mechanisms outside the page reduce the need for intra-page selectivity optimization in terms of delivering performance but you still need to preserve memory bandwidth "mechanisms/optimization/delivering performance" - these are just aspirational words - exactly what 'selectivity mechanisms'? I can barely make sense of this. In fact, I can't. At all. > Some of these assertions are self-evident and non-controversial They aren't self-evident because they aren't evident to me, and as for non-controversial - this is just ducking the question. You're still not justifying anything. > and the issue of B-tree bloat generally A btree page will be between 1/2 and completely full. Random insertions should make them ~75% full. In MSSQL I just created a table of ints (clustered PK) and inserted about a million random ints and had a look at fullness (page = 8060 bytes): - Avg. Bytes Free per Page.....................: 2046.7 - Avg. Page Density (full).....................: 74.71% yep, so an average of 3/4 full. Bit of expected free space. Where's the bloat you talk of, and what's the alternative structure if this is unacceptable. Edit: this is just leaf nodes. You might claim it's ignoring non-leaf nodes. Check: 4MB data gets you 56K non-leaf. Massive 2,000 factor fanout (8K page / 4 byte ints) is why. So what bloat? > Using mmap() for storage is the low-performance option, articles regarding which are regularly posted on HN, for small files, so I understand. For large AFAIK they're good, which is why they're used. So what's the alternative? Point to where HN says mmap is slow for large files. > that it "has mmap" is a good example of why it is expected to perform poorly justify this please. > But more broadly, you seem to be a bit confused about the theory of database kernel design and the tradeoffs that can be made there? You may be right but you've just handwaved my doubts away without any evidence. > You are questioning things about how actual, real, databases, including most open source ones, are designed. I'm questioning your claims in the hope I can understand better. I'm not a total noob. I am wondering why I can't make sense of you posts.
- electricshampo1 4y ago
  It might be helpful to read something like DB2 with BLU Acceleration: So Much More than Just a Column Store https://db.cs.pitt.edu/courses/cs3551/16-1/handouts/db2BLU.pdf https://db.cs.pitt.edu/courses/cs3551/16-1/handouts/db2BLU.p... or Real-Time Analytical Processing with SQL Server https://www.vldb.org/pvldb/vol8/p1740-Larson.pdf https://www.vldb.org/pvldb/vol8/p1740-Larson.pdf to get a sense of the gap between postgres/open source databases vs something designed (at least partially) to better support analytical workloads.