Re: Batch insert heavily affecting query performance.

we can also see cold vs warm cache performance. There is definitely something wrong as there is no way a query like that should take 500ms. Your instinct is correct there.

Re: Batch insert heavily affecting query performance.

)' > > > > Em 25 de dez de 2017 01:10, "Jean Baro" <jfb...@gmail.com> escreveu: > >> Thanks for the clarification guys. >> >> It will be super useful. After trying this I'll post the results! >> >> Merry Christmas! >> >> Em 25 de dez

Re: Batch insert heavily affecting query performance.

I had an opportunity to perform insertion of 700MM rows into Aurora Postgresql, for which performance insights are available. Turns out, that there are two stages of insert slowdown - first happens when max WAL buffers limit reached, second happens around 1 hour after. The first stage cuts insert

Re: Batch insert heavily affecting query performance.

Yes it would/does make a difference! When you do it with one connection you should see a big performance gain. Delayed, granted, extend locks (locktype=extend) can happen due to many concurrent connections trying to insert into the same table at the same time. Each insert request results

Re: Batch insert heavily affecting query performance.

concurrently? > > Sent from my iPhone > > > On Dec 24, 2017, at 2:51 PM, Jean Baro <jfb...@gmail.com> wrote: > > > > Hi there, > > > > We are testing a new application to try to find performance issues. > > > > AWS RDS m4.large 500GB storag

Re: Batch insert heavily affecting query performance.

Are the inserts being done through one connection or multiple connections concurrently? Sent from my iPhone > On Dec 24, 2017, at 2:51 PM, Jean Baro <jfb...@gmail.com> wrote: > > Hi there, > > We are testing a new application to try to find performance issues. >

Batch insert heavily affecting query performance.

Hi there, We are testing a new application to try to find performance issues. AWS RDS m4.large 500GB storage (SSD) One table only, called Messages: Uuid Country (ISO) Role (Text) User id (Text) GroupId (integer) Channel (text) Title (Text) Payload (JSON, up to 20kb) Starts_in (UTC

Re: insert and query performance on big string table with pg_trgm

Don't know if it would make PostgreSQL happier but how about adding a hash value column and creating the unique index on that one? May block some false duplicates but the unique index would be way smaller, speeding up inserts. 2017. nov. 25. 7:35 ezt írta ("Jeff Janes" ): >

Re: insert and query performance on big string table with pg_trgm

On Nov 21, 2017 00:05, "Matthew Hall" wrote: > Are all indexes present at the time you insert? It will probably be much faster to insert without the gin index (at least) and build it after the load. There is some flexibility on the initial load, but the updates in the

Re: insert and query performance on big string table with pg_trgm

Hi Jeff, Thanks so much for writing. You've got some great points. > On Nov 20, 2017, at 5:42 PM, Jeff Janes wrote: > While I have not done exhaustive testing, from the tests I have done I've > never found gist to be better than gin with trgm indexes. Thanks, this helps

insert and query performance on big string table with pg_trgm

_mem 32 MB * maintenance_work_mem 512 MB * effective_cache_size 10 GB * synchronous_commit off * wal_buffers 16 MB * max_wal_size 4 GB * checkpoint_completion_target 0.9 * auto_explain, and slow log for >= 1000 msecs (to debug this) I'm noticing that the performance of inserts starts slipping quite a

<    5   6   7   8   9   10