Re: High concurrency same row (inventory)

Michael Vitale --> No, there is only postgreSQL running in this server... it is in fact an RDS server. SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_tup_hot_upd as "hot updates", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples" FROM pg_stat_user_tables WHER

Re: High concurrency same row (inventory)

Looks like regular updates not HOT UPDATES Jean Baro wrote on 7/29/2019 8:26 PM: image.png The dead tuples goes up at a high ratio, but then it gets cleaned. if you guys need any further information, please let me know! On Mon, Jul 29, 2019 at 9:06 PM Jean Baro > wr

Re: High concurrency same row (inventory)

[image: image.png] The dead tuples goes up at a high ratio, but then it gets cleaned. if you guys need any further information, please let me know! On Mon, Jul 29, 2019 at 9:06 PM Jean Baro wrote: > The UPDATE was something like: > > UPDATE bucket SET qty_available = qty_available + 1 WHERE

Re: High concurrency same row (inventory)

The UPDATE was something like: UPDATE bucket SET qty_available = qty_available + 1 WHERE bucket_uid = 0940850938059380590 Thanks for all your help guys! On Mon, Jul 29, 2019 at 9:04 PM Jean Baro wrote: > All the failures come from the Bucket Table (see image below). > > I don't have access to

Re: High concurrency same row (inventory)

All the failures come from the Bucket Table (see image below). I don't have access to the DB, neither the code, but last time I was presented to the UPDATE it was changing (incrementing or decrementing) *qty_available*, but tomorrow morning I can be sure, once the developers and DBAs are back to t

Partial join

Hello, I attached one example of a partitioned table with multi column partition key. I also attached the output. Disabling the hash_join is not really necessary, it just shows the more drastic result in the case of low work_mem. Comparing the first and the second query I was surprised to se

Re: High concurrency same row (inventory)

Can you share the schema of the table(s) involved and an example or two of the updates being executed?

Re: High concurrency same row (inventory)

Thanks guys! This is really good and useful information! :) During the day we can see some exceptions coming from Postgres (alway when the load is the highest), only in the MAIN UPDATE: - How to overcome the error "current transaction is aborted, commands ignored until end of transaction block" -

Re: High concurrency same row (inventory)

Does pg_stat_user_tables validate that the major updates are indeed "hot updates"?  Otherwise, you may be experiencing bloat problems if autovacuum is not set aggressively.  Did you change default parameters for autovacuum?  You should.  They are set very conservatively right outa the box.  Als

Re: High concurrency same row (inventory)

On Mon, Jul 29, 2019 at 11:46 AM Jean Baro wrote: > Hello there. > > I am not an PG expert, as currently I work as a Enterprise Architect (who > believes in OSS and in particular PostgreSQL 😍). So please forgive me if > this question is too simple. 🙏 > > Here it goes: > > We have a new Inventory

Re: High concurrency same row (inventory)

On Mon, Jul 29, 2019 at 2:16 AM Jean Baro 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

Re: Standard uuid vs. custom data type uuid_v1

On 27/07/2019 15:47, Tomas Vondra wrote: > On Thu, Jul 25, 2019 at 11:26:23AM +0200, Ancoron Luciferis wrote: >> Hi, >> >> I have finally found some time to implement a custom data type optimized >> for version 1 UUID's (timestamp, clock sequence, node): >> https://github.com/ancoron/pg-uuid-v1 >>