Re: IoT/sensor data and B-Tree page splits

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