On Mon, Aug 26, 2019 at 4:29 PM Arcadiy Ivanov <arcadiy@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