3 ms·

Presumably some commercial databases like MS SQL Server and Oracle are better at this? I'd love to know how, and in what way the Postgres architecture is inferi

Presumably some commercial databases like MS SQL Server and Oracle are better at this? I'd love to know how, and in what way the Postgres architecture is inferior.

jandrewrogers 5y ago
This issue is specific to Postgres. Like I stated, it is design flaw in the statistics collection architecture. I would be surprised if many other databases have a similar defect. Most experienced database engine designers would immediately identify the approach as defective; there is even a comment in the Postgres source code that suggests it. All old database kernels have architectural limitations that emerge because there is no realistic way to anticipate distant future workloads and hardware, and it is nearly impossible to materially modify the architecture in practice.
- atombender 5y ago
  No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.
  - jandrewrogers 5y ago
    The statistical models in Postgres are stored as transactional rows in an ordinary table, which puts some pretty hard limits on what you can put into those models because Postgres doesn’t like large rows. There is no theoretical reason you’d want to design things this way, it is just pretty expedient because you don’t have to implement a separate statistics store and you can leverage all of the code for normal tables. IIRC, the fail safe code path puts a hard limit on the amount of statistical data that can be collected no matter how much data you should collect, so that it plays nicely with the Postgres storage model. This threshold is uncomfortably low for proper statistics collection purposes when your database becomes large. The saving grace is that for some common data distributions, the degraded statistical models are still pretty close to representative of the data. However, some data distributions badly expose the fact that the statistical models were improperly constructed to fit resource limits. In these cases, the statistical model is unpredictable and erratic, and will change every time the statistics collection is run even if the data does not change. We are not talking about an extraordinary amount of statistics data. I would expect databases to have a special structure optimized for this purpose rather than using an OLTP row. The Postgres approach to statistics collection was reasonable a couple decades ago, but modern workloads expose it more frequently now. FWIW, I still use Postgres a fair amount because it is very solid within its limits. It is the reason I am familiar with its sharp edges.
orthoxerox 5y ago
Not really. I had Oracle refuse to use the index written exactly for the query in question (select id from contracts where auth_status = 'U', with index on auth_status) because the planner decided that the index was useless (the statistics were gathered at night when the table contained no unauthorized records). But in Oracle you can use hints to force the planner to use the index, while in Postgres you can't.

Presumably some commercial databases like MS SQL Server and Oracle are better at this? I'd love to know how, and in what way the Postgres architecture is inferior.

jandrewrogers 5y ago

This issue is specific to Postgres. Like I stated, it is design flaw in the statistics collection architecture. I would be surprised if many other databases have a similar defect. Most experienced database engine designers would immediately identify the approach as defective; there is even a comment in the Postgres source code that suggests it. All old database kernels have architectural limitations that emerge because there is no realistic way to anticipate distant future workloads and hardware, and it is nearly impossible to materially modify the architecture in practice.

atombender 5y ago
No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.
- jandrewrogers 5y ago
  The statistical models in Postgres are stored as transactional rows in an ordinary table, which puts some pretty hard limits on what you can put into those models because Postgres doesn’t like large rows. There is no theoretical reason you’d want to design things this way, it is just pretty expedient because you don’t have to implement a separate statistics store and you can leverage all of the code for normal tables. IIRC, the fail safe code path puts a hard limit on the amount of statistical data that can be collected no matter how much data you should collect, so that it plays nicely with the Postgres storage model. This threshold is uncomfortably low for proper statistics collection purposes when your database becomes large. The saving grace is that for some common data distributions, the degraded statistical models are still pretty close to representative of the data. However, some data distributions badly expose the fact that the statistical models were improperly constructed to fit resource limits. In these cases, the statistical model is unpredictable and erratic, and will change every time the statistics collection is run even if the data does not change. We are not talking about an extraordinary amount of statistics data. I would expect databases to have a special structure optimized for this purpose rather than using an OLTP row. The Postgres approach to statistics collection was reasonable a couple decades ago, but modern workloads expose it more frequently now. FWIW, I still use Postgres a fair amount because it is very solid within its limits. It is the reason I am familiar with its sharp edges.

orthoxerox 5y ago

Not really. I had Oracle refuse to use the index written exactly for the query in question (select id from contracts where auth_status = 'U', with index on auth_status) because the planner decided that the index was useless (the statistics were gathered at night when the table contained no unauthorized records). But in Oracle you can use hints to force the planner to use the index, while in Postgres you can't.