Re: High concurrency same row (inventory)

On Mon, Jul 29, 2019 at 2:16 AM Jean Baro <jfbaro(at)gmail(dot)com> wrote:

>
> We have a new Inventory system running on its own database (PG 10 AWS
> RDS.m5.2xlarge 1TB SSD EBS - Multizone). The DB effective size is less than
> 10GB at the moment. We provided 1TB to get more IOPS from EBS.
>
> As we don't have a lot of different products in our catalogue it's quite
> common (especially when a particular product is on sale) to have a high
> rate of concurrent updates against the same row. There is also a frequent
> (every 30 minutes) update to all items which changed their current
> stock/Inventory coming from the warehouses (SAP), the latter is a batch
> process. We have just installed this system for a new tenant (one of the
> smallest one) and although it's running great so far, we believe this
> solution would not scale as we roll out this system to new (and bigger)
> tenants. Currently there is up to 1.500 transactions per second (mostly
> SELECTS and 1 particular UPDATE which I believe is the one being
> aborted/deadlocked some tImes) in this inventory database.
>
> I am not a DBA, but as the DBAs (most of them old school Oracle DBAs who
> are not happy with the move to POSTGRES) are considering ditching
> Postgresql without any previous tunning I would like to understand the
> possibilities.
>
> Considering this is a highly concurrent (same row) system I thought to
> suggest:
>
>
>
Another thing which you might want to investigate is your checkpoint
tunables. My hunch is with that many writes, the defaults are probably not
going to be ideal.
Consider the WAL tunables documentation:
https://www.postgresql.org/docs/10/wal-configuration.html