On Mon, Aug 26, 2019 at 4:29 PM Arcadiy Ivanov <arca...@gmail.com> wrote: > This problem is not limited to IoT but to RT financial transaction > ingestion as well.
Not surprising, since the TPC-E benchmark models a financial trading application. Perhaps it exhibits this behavior because it is actually representative of a real trading application. Note that pg_stats.correlation is 1.0 for the leading indexed column (in the trade_history PK index), indicating *perfect* correlation. It's not perfectly correlated when you look at it under a microscope, though. > I found BRIN indices to work exceptionally well for that, while B-tree > taking enormous amounts of space with no performance difference or win > going to BRIN. That won't work with the TPC-E example, though, since it's a primary key index. > The situation gets even worse when B-tree index is subjected to > identical tuples which often happens when you have an avalanche of > timestamps that are within less than 1ms of each other (frequent TS > rounding resolution). The good news there is that that will almost certainly be a lot better in Postgres 12. TPC-E also has a number of very low cardinality indexes, despite being an OLTP benchmark. Some of these indexes are also listed in the 2012 problem report I linked to. Those same indexes will be a lot smaller on Postgres 12. It should also generate a lot less WAL compared to previous versions. (Plus we may get dynamic B-Tree deduplication in Postgres 13, which would improve matters further with low cardinality indexes.) -- Peter Geoghegan
