Re: What is equivalent of v$sesstat and v$sql_plan in postgres?

RDS does allow you to now create your customized extensions via the pg_tle extension.  Regarding your desire to capture session metrics and sql plan information, I do not know anything that can do that in PG. Regards, Michael Vitale kunwar singh wrote on 4/20/2023 9:37 AM: If not , how can I ge

Re: ALTER STATEMENT getting blocked

Do something like this to get it without being behind other transactions...You either get in and get your work done or try again DO language plpgsql $$ BEGIN FOR get_lock IN 1 .. 100 LOOP BEGIN ALTER TABLE mytable ; EXIT; END; END LOOP; END; $$; Tom Lane wrote on 1/19/2023 12:45 P

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

Well if you find out for sure, please let me know.  I'm very interested in the outcome of this problem. Maxim Boguk wrote on 1/5/2023 6:44 AM: On Thu, Jan 5, 2023 at 1:31 PM MichaelDBA <mailto:michael...@sqlexec.com>> wrote: What happens if you takepg_stat_stateme

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

What happens if you take pg_stat_statements out of the picture (remove from shared_preload_libraries)?  Does your BIND problem go away?

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

You said it's a dedicated server, but pgbouncer is running locally, right?  PGBouncer has a small footprint, but is the CPU high for it? Maxim Boguk wrote on 1/1/2023 11:51 AM: On Sun, Jan 1, 2023 at 6:43 PM MichaelDBA <mailto:michael...@sqlexec.com>> wrote: Hi Maxim,

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

Hi Maxim, 10-20 active, concurrent connections is way below any CPU load problem you should have with 48 available vCPUs. You never explicitly said what the load is, so what is it in the context of the 1,5,15? Maxim Boguk wrote on 1/1/2023 11:30 AM: 1)usual load (e.g. no anomalies) 10-20 con

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

Howdy, Few additional questions: 1. How many concurrent, active connections are running when these BIND problems occur?  select count(*) from pg_stat_activity where state in ('active','idle in transaction') 2. Are the queries using gigantic IN () values? 3. Perhaps unrelated, but islog_tem

Re: Identify root-cause for intermittent spikes

Hello, Your problem is probably, too many active, concurrent connections.  Get it from here the db directly: select datname, usename, application_name, substring(query, 1, 80) query  from pg_stat_activity where state in ('active','idle in transaction'); Compare the number of rows returned wi

Re: DB connection issue suggestions

Please show output of "show max_connections" to validate your assumptions. On 05/10/2022 12:59 PM Sudhir Guna wrote: Dear All, We have recently upgraded Postgresql 9.4 standalone server to Postgres

Re: Postgresql TPS Bottleneck

While setting these 2 parameters to off will make things go faster (especially for fsync), it is unrealistic to have these settings in a production environment, especiall fsync=off.  You might get by with synchronous_commit=off, but with fsync=off you could end up with corruption in your databa

Re: Slow plan choice with prepared query

diffs between 5th iteration and 6th one:  explain (analyze, summary, buffers true) execute foo_test(null, null, 5, 500, true); It appears the SORT is the problem and a mismatch between text and integer for base.text_id? --> WHERE  base.test_id = $4 Regards, Michael Vitale MichaelDBA wrote o

Re: Slow plan choice with prepared query

Dag, if you ain't right!  I can duplicate this on the ones I tested with: PG v11 and v14.  Gonna start diving into this myself... Regards, Michael Vitale Mark Saward wrote on 2/23/2022 10:37 PM: Hi, I've experienced a situation where the planner seems to make a very poor choice with a prep

Re: Lock contention high

1.Is there a way to tune the lock contention ? 2.Is any recommendations to tune/reduce the lock contention via postgres.conf I think you'd want to find *which* LW locks are being waited on, to see if it's something that can be easily tuned. You can check pg_stat_activity, or maybe create a cronj

Re: temporary file log lines

below elsewhere.  This seems to be a dark area of PG that is not convered well. Regards, Michael Vitale Laurenz Albe wrote on 7/12/2021 8:01 AM: On Thu, 2021-07-08 at 17:22 -0400, MichaelDBA wrote: I got a question about PG log lines with temporary file info like this: case 1: log line with no conte

temporary file log lines

Hi all, I got a question about PG log lines with temporary file info like this: case 1: log line with no contextual info 2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336 case 2: log line with contextual

Re: AWS forcing PG upgrade from v9.6 a disaster

Hi Lance, Did you customize the PG 12 DB Parameter group to be in sync as much as possible with the 9.6 RDS version?  Or are you using PG12 default DB Parameter group? Are you using the same AWS Instance Class? Did you vacuum analyze all your tables after the upgrade to 12? Regards, Michael

Re: Potential performance issues

Ha, Andrew beat me to the punch! Andrew Dunstan wrote on 3/1/2021 7:59 AM: On 2/28/21 10:04 AM, Jung, Jinho wrote: # install DBMS sudo su make install adduser postgres rm -rf /usr/local/pgsql/data mkdir /usr/local/pgsql/data chown -R postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql

Re: Potential performance issues

Hi, It is worthy work trying to compare performance across multiple database vendors, but unfortunately, it does not really come across as comparing apples to apples. For instance, configuration parameters:  I do not see where you are doing any modification of configuration at all.  Since DB

Re: Too many waits on extension of relation

Are you having locks where the type = extend? If so, this is a symptom of slow insertsdue to many concurrent connections trying to insert into the same table at the same time. Each insert request may result in an extend lock (8k extension), which blocks other writers. What normally happens is

Re: sizing / capacity planning tipps related to expected request or transactions per second

Hi Dirk, There are a bunch of other things to consider besides just TPS and size of database.  Since PG is process-bound, I would consider connection activity: How many active connections at any one time?  This greatly affects your CPUs.  SQL workload is another big factor: a lot of complex q

Re: When to use PARTITION BY HASH?

The article referenced below assumes a worst case scenario for bulk-loading with hash partitioned tables.  It assumes that the values being inserted are in strict ascending or descending order with no gaps (like a sequence number incrementing by 1), thereby ensuring every partition is hit in or

Re: When to use PARTITION BY HASH?

Hi, I use it quite often, since I'm dealing with partitioning keys that have high cardinality, ie, high number of different values.  If your cardinality is very high, but your spacing between values is not uniform, HASH will balance your partitioned tables naturally.  If your spacing between

Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.

Hi all, Since we are talking about multi-tenant databases, the citus extension fits in neatly with that using horizontal partitioning/shards. Regards, Michael Vitale Avinash Kumar wrote on 5/8/2020 6:14 AM: Hi, On Fri, May 8, 2020 at 3:53 AM La

Re: TOAST table performance problem

Yes, I would concur that this planning time and execution time do not take into account the network time sending the data back to the client, especially since your are sending back the entire contents of the table. Regards, Michael Vitale Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM: På fred

Re: autovacuum locking question

And Just to reiterate my own understanding of this... autovacuum priority is less than a user-initiated request, so issuing a manual vacuum (user-initiated request) will not result in being cancelled. Regards, Michael Vitale Jeff Janes wrote on 12/6/2019 12:47 PM: On Fri, Dec 6, 2019 at 10:55

Re: Make recently inserted/updated records available in the buffer/cache

Yep, I concur completely!  For tables treated like queues you gotta do this stuff or deal with bloat and fragmented indexes. Michael Lewis wrote on 12/3/2019 12:29 PM: "I am going to use it as a queue" You may want to look at lowering fillfactor if this queue is going to have frequent updates

Re: Make recently inserted/updated records available in the buffer/cache

All updated/dirty records go through PG internal memory buffer, shared_buffers.  Make sure that is configured optimally.  Use pg_buffercache extension to set it correctly. Regards, Michael Vitale Hüseyin Demir wrote on 12/2/2019 12:13 PM: I guess there won't be any adverse effect

Re: Optimising a two column OR check

ierth wrote on 10/12/2019 11:46 AM: "MichaelDBA" == MichaelDBA writes: MichaelDBA> Nope, vacuumed it and still got the bitmap index scans. Let's see your explains. Here's mine: # set enable_seqscan=false; -- because I only have a few rows SET # insert into friend va

Re: Optimising a two column OR check

Perhaps the fix by Alexander Kuzmenkov in V11 added this VM consideration for having a preference of bitmap index scan over an index only scan.  Looks like I'm goin' down the rabbit hole... Regards, Michael Vitale MichaelDBA wrote on 10/12/2019 11:35 AM: Nope, vacuumed it and sti

Re: Optimising a two column OR check

Nope, vacuumed it and still got the bitmap index scans. Andrew Gierth wrote on 10/12/2019 11:33 AM: "MichaelDBA" == MichaelDBA writes: MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in MichaelDBA> bitmap index scans from my testing. You probably forg

Re: Optimising a two column OR check

Yikes, apologies to all, my wording is the opposite of what I meant! Index only scans are preferred for infrequently updated ones, not heavily updated ones where the visibility map is updated often. Regards, Michael Vitale MichaelDBA wrote on 10/12/2019 11:27 AM: Another thing to consider

Re: Optimising a two column OR check

Another thing to consider is the visibility map.  From what I understand, index only scans are preferred for heavily updated tables, not infrequently updated ones.  Even though index only scans imply ONLY they really aren't in the sense that they may need to visit the Visibility Map for the hea

Re: Some observations on very slow pg_restore operations

Hi Ogden, You didn't mention any details about your postgresql.conf settings.  Why don't you set them optimally for your loads and try again and see if there is any difference.  Make sure you do a DB restart since some of these parameters require it. == pa

Re: Slow query on a one-tuple table

Hi all, I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also set autovacuum_vacuum_threshold to some non-zero number to force vacuums after a certain number of rows are updated.  It takes the math out of it by setting the threshold explicitly. But in this case he has also

Re: Extremely slow count (simple query, with index)

Hi Marco, Since you said approximates would be good enough, there are two ways to do that.  Query pg_class.reltuples or pg_stat_user_tables.n_live_tup. Personally, I prefer the pg_stat_user tables since it is more current than pg_class table, unless you run ANALYZE on your target table before

Re: Erratically behaving query needs optimization

Yes, adding another index might help reduce the number of rows filtered --> Rows Removed by Filter: 1308337 Also, make sure you run vacuum analyze on this query. Regards, Michael Vitale Luís Roberto Weck wrote on 8/20/2019 10:58 AM: Em 20/08/2019 10:54, Barbu Paul - Gheorghe escreveu: Hello,

Re: Planner performance in partitions

Queries against tables with a lot of partitions (> 1000) start to incur an increasing planning time duration even with the current version, V11.  V12 purportedly has fixed this problem, allowing thousands of partitioned tables without a heavy planning cost.  Can't seem to find the threads on th

Re: Strange runtime partition pruning behaviour with 11.4

I too got the same plan (non runtime partition pruning plan) with or without the statistics.  So it looks like the workaround until this is fixed is to re-arrange the query to do a subselect to force the runtime partition pruning as Andreas suggested, which I tested and indeed does work for me

Re: Strange runtime partition pruning behaviour with 11.4

I too am a bit perplexed by why runtime partition pruning does not seem to work with this example.  Anybody got any ideas of this? Regards, Michael Vitale Thomas Kellerer wrote on 8/2/2019 9:58 AM: I stumbled across this question on SO: https://stackoverflow.com/questions/56517852 Disregardi

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)

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: Shared_buffers

what procedure you suggest ? Justin MichaelDBA <mailto:michael...@sqlexec.com> Tuesday, March 12, 2019 4:03 PM Set shared_buffers more accurately by using pg_buffercache extension and the related queries during high load times. Regards, Michael Vitale Michael Lewis <mailto:mle...@entrata.c

Re: Shared_buffers

Set shared_buffers more accurately by using pg_buffercache extension and the related queries during high load times. Regards, Michael Vitale Michael Lewis Tuesday, March 12, 2019 3:23 PM On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe >

Re: How to get the content of Bind variables

If you set log_min_duration_statement low enough for your particular query, you will see another line below it showing what values are associated with each bind variable like this: 2019-02-28 00:07:55CST 2019-02-2800:02:09CST ihr2 10.86.42.184(43460) SELECT LOG: duration: 26078.308 ms execut

Re: Query slow for new participants

Regarding shared_buffers, please install the pg_buffercache extension and run the recommended queries with that extension during high load times to really get an idea about the right value for shared_buffers. Let's take the guess work out of it. Regards, Michael Vitale Justin Pryzby

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

Was wondering when that would come up, taking queuing logic outside the database. Can be overly painful architecting queuing logic in relational databases. imho. Regards, Michael Vitale Jeff Janes Monday, February 25, 2019 3:30 PM On Sat, Feb 23, 2019 at 4:06 PM

Re: autovacuum big table taking hours and sometimes seconds

Hi all, In the myriad of articles written about autovacuum tuning, I really like this article by Tomas Vondra of 2ndQuadrant: https://blog.2ndquadrant.com/autovacuum-tuning-basics/ It is a concise article that touches on all the major aspects of autovacuuming tuning: thresholds, scale factors

SCRAM question

I am using pgadmin4 version 3.4 with PG 11.0 and I get this error when I try to connect with scram authorization: User "myuser" does not have a valid SCRAM verifier. How do I get around this? And also how would I do this for psql? Regards, Michael Vitale

Re: Indexes on UUID - Fragmentation Issue

or prepend the UUID with a timestamp? Regards, Michael Vitale Andreas Karlsson Monday, October 29, 2018 10:52 AM On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have How is it implemented? I can personally see two ways of generating sequential UUID:s. Either you us

Re: Automated bottleneck detection

Wow, freakin cool, can't wait to start fiddling with pg_wait_sampling. Reckon we can get lightweight locks and spinlocks history with this cool new extension instead of awkwardly and repeatedly querying the pg_stat_activity table. Regards, Michael Vitale Thomas Güttler

Re: PG 9.6 Slow inserts with long-lasting LWLocks

Sporadic insert slowness could be due to lock delays (locktype=extend) due to many concurrent connections trying to insert into the same table at the same time. Each insert request may result in an extend lock (8k extension), which blocks other writers. What normally happens is the these extend

Re: need advice to tune postgresql

What caught my eye is the update count can be up to 10K. That means if autovacuum is not keeping up with this table, bloat may be increasing at a high pace leading to more page I/O which causes degraded performance. If the table has become bloated, you need to do a blocking VACUUM FULL on it

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 in a