Dmitry Tomko

@scott_lowe C’mon, it’s not product name, it’s partnnumber. At every vendor part numbers names are silly.

OMG, the new IBM product names for the former Hashicorp products are to die for. I mean, who doesn’t love a name like “IBM Nomad Self-managed Premium Resource Value Unit Flexible Subscription License”? 🤣😆😂

BREAKING: @IBM just launched software for their revolutionary 86-bit quantum PC))

@VKukharik @TimescaleDB @pgvector @paradedb @mooncakelabs @orioledb @supabase pg_audit of course)

Extensions that provide superpowers for PostgreSQL (in my opinion): 1. timescaledb (@TimescaleDB) 2. pgvector (@pgvector) 3. pg_search (@paradedb) 4. pg_mooncake (@mooncakelabs) 5. orioledb (@orioledb, @supabase) What else should be added to this list? #postgres #postgresql

Damn, its amazing, hey @IBM, what a about @cursor_ai support?

Damn, big news for Qradar guys. End of Qradar era?

@CertDepot is it something wrong with rss feed on certdepot?

@geerlingguy @RedHat It's valid only for one year. As a workaround just create a fresh one

This frustrates me to no end. Trying to fix a bug that affects Rocky Linux/Alma Linux/CentOS Stream/(begrudgingly) RHEL users for some of my roles. I find the answer via Google Search for the error I'm getting. And... @redhat paywalls it (I'm logged in here!)

@ibmtechie @PostgreSQL gitlab.com/postgres-ai/po…

@samokhvalov @PostgreSQL Nikolay, will you add all those "days" to some blog or github page for history purposes?

#PostgresMarathon day 12: How to find query examples for problematic pg_stat_statements records // I post a new @PostgreSQL "howto" article every day. Join me in this journey – subscribe, provide feedback, share! In a few hours, I'm making my "Seamless Postgres query optimization" tutorial at @djangocon, so it's a good time to talk about transitioning from pg_stat_statements (pgss) to EXPLAIN. ## The problem of jumping from pgss to EXPLAIN Once a problematic pgss record is identified (which is the subject of query macro-optimization that we've discussed on days 5-7 x.com/samokhvalov/st…), the first thing to do is to understand the direction of optimization. Two most common basic situations of pgss records requiring optimization: 1. If "calls" is very high (a lot of QPS, queries per second), the main method to optimize is reduction of this number – this is to be done on client (app) side. 2. If "mean_exec_time + mean_plan_time" is high (for the OLTP context – web and mobile apps – 100ms should be considered slow: postgres.ai/blog/20210909-…), this is when we need to apply query micro-optimization – use EXPLAIN. Of course, it's also not uncommon to have a combination of these two basic cases – quite frequent query with poor latency. In this post, we won't discuss how to use EXPLAIN and EXPLAIN (ANALYZE, BUFFERS). Instead, we'll focus on finding proper materials for EXPLAIN – particular query examples that need to be studied and improved. It is worth remembering that a single pgss records can be associated with individual queries that are executed differently – using different plans. A basic example illustrating it: nik=# create table t1 as select 1::int8 as c1; SELECT 1 nik=# insert into t1 select 2 from generate_series(1, 1000000); INSERT 0 1000000 nik=# create index on t1(c1); CREATE INDEX nik=# vacuum analyze t1; VACUUM nik=# explain select from t1 where c1 = 1; QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using t1_c1_idx on t1 (cost=0.42..4.44 rows=1 width=0) Index Cond: (c1 = 1) (2 rows) nik=# explain select from t1 where c1 = 2; QUERY PLAN ------------------------------------------------------------ Seq Scan on t1 (cost=0.00..16981.01 rows=1000001 width=0) Filter: (c1 = 2) (2 rows) – both queries here will be registered as "select * from t1 where c1 = $1" in pgss. But plans are different, because for "c1 = 1", we have high selectivity, while for "c1 = 2" it is really bad (targeting all but 1 rows in the table). This means that looking at just pgss record demonstrating poor query latency, we cannot quickly jump to using EXPLAIN – we need to find particular query samples to work with. Below, we discuss options to solve this problem. ## Option 1: guessing In some cases, it may be fine to guess. But, I had really bad cases when I lost a lot of time making a mistake with guessing. For example, in one case, dealing with a boolean column, I decided to use the value that had a very bad selectivity, and spent a lot of time optimizing this situation, before realizing that application code never ever is going to use it. It might be tempting to use pg_statistic to improve the guesswork. But unfortunately, in general case, this doesn't work really well, because of lack of multi-column statistic (except when it's created explicitly) – without it, we're going to have unrealistic parameter variants in lots of cases. So this method is limited and can be used only for simple cases. ## Option 2: get examples from Postgres log It is possible to find examples in the Postgres log – of course, if they are logged (usually via the log_min_duration_statement parameter or the auto_explain extension). To find examples for a given pgss record, we need to be able to find association of logged queries and pgss records. Two options: 1. For PG14+, option compute_query_id (postgresqlco.nf/doc/en/param/c…) can provide the same queryid value that is used in pg_stat_statements, to the log entry. 2. Alternatively, we can use an excellent library libpg_query (github.com/pganalyze/libp…; Ruby, Go, Python and other options are also available). It can be applied both to normalized (pgss records) and individual queries, producing so-called fingerprint, that can be then used to find the relationships we need. In general, using Postgres logs to find query examples is a good method, but for heavily-loaded systems, where it is impossible to log all queries, it is going to supply us with very slow examples only – those that exceed log_min_duration_statatement postgresqlco.nf/doc/en/param/l…(usually set to some quite high value, e.g. 500ms). This situation can be improved with sampling and lowering the threshold for slow queries or even getting rid of it completely. Parameters for it: - log_min_duration_sample (PG13+) postgresqlco.nf/doc/en/param/l… - log_statement_sample_rate (PG13+) postgresqlco.nf/doc/en/param/l… - postgresqlco.nf/doc/en/param/l… (PG12+) postgresqlco.nf/doc/en/param/l… Alternatively, auto_explain can be used, it also supports sampling (auto_explain.sample_rate, all currently supported versions), and it can look really attractive since it brings plans as they were right on production. Installation of auto_explain should be done with careful testing and analysis of overhead. ## Option 3: sample queries from pg_stat_activity This method can be attractive since it doesn't require us to turn on the expensive logging of too many queries. And even low-latency queries, if they are frequent enough, are going to be eventually captured if we observe pg_stat_activity.query long enough. However, there are two important limitations here. First, the column pg_stat_statements.query_id, useful to connect samples from pg_stat_activity (pgsa) with pgss records, was added relatively recently, in PG14. For older versions, we would end up using some regular expressions (implementation can be cumbersome/fragile) of libpg_query's fingerprints (meaning that we need to sample all pgsa records and then post-process them). So this method is better to use in PG14+. Second, by default, pg_stat_activity.query is truncated to 1024 characters – this is defined by track_activity_query_size (track_activity_query_size), which is 1024 by default. It is recommended to increase it significantly – e.g., to 10k, to allow larger queries to be sampled and analyzed. Unfortunately, changing this setting requires a restart. ## Option 4: eBPF This option is not yet fully developed, but there is an opinion that it can be a very good alternative in the future: use eBPF for either sampling queries (alternative to pgsa), or even for sampling queries + normalizing them (being alternative to both pgsa+pgss). So – TBD. Meanwhile, check out these interesting resources: - pgtracer github.com/Aiven-Open/pgt… (presentation on PostgresTV: youtube.com/watch?v=tvJgMV…) - Analyzing Postgres performance problems using perf and eBPF – a talk video where Andres Freund describes how to combine pg_stat_statements + BPF youtu.be/HghP4D72Noc?si… (code: github.com/anarazel/pg-bp…) ## Summary - In PG14+, use compute_query_id to have query_id values both in Postgres logs and pg_stat_activity - Increase track_activity_query_size (requires restart) to be able to track larger queries in pg_stat_activity - Organize workflow to combine records from pg_stat_statements and query examples from logs and pg_stat_activity, so when it comes to query optimization, you have good examples ready to be used with EXPLAIN (ANALYZE, BUFFERS). As a reminder, once you have good examples, the best (fastest&cheapest) way to verify optimization ideas is to use properly tuned thin clones – check out DBLab @Database_Lab. ---

I've uploaded a selection of some of my favourite IBM Power sessions from TechXchange. I've also included the exercise guides from my hands-on AIX labs. Enjoy! #AIX #IBMAIX ibm.box.com/s/n9h2c4o5ovh7…

@andrewbrown Andrew, what about Pulumi bootcamp? Would love to participate.

I'm undetermined when I should run another cohort for the AWS Cloud Project Bootcamp. I should start gathering interest again... But anyway, what start date do you like?

@nheidloff Niklas, it seems something is wrong in articles sorting on your web site?

@ibmtechie Hi @ibmtechie ibm.com/products/watso… the „Tuning Studio* (coming soon)“

@tsuedbroecker Thanks!

This live stream is about how to integrate IBM Watsonx in a simple question-answering pipeline using Python and fastAPI and more. #codeengine #watsonax #promptlab #prompt #questionanswering #ai #fastapi #container #ibmcloud youtube.com/live/W39HBN7pe…

@GerhardPaulus1 Gerhard, maybe you know discord/telegram/slack channel for Cloud Pak users?

#IBM #XML in #DataStage on #CP4D XMLInput and XMLOutput are available again. This will make migrations to DataStage on CP4D much more easier. #IBMChampion

🚀🚀🚀🚀 I published an update for OpenChat (opensource ❤️), in few clicks, create impressive search bar with AI. or create a customer support chat widget for your web app! Done with @langchain 🔥 (best tool ever) github.com/openchatai/Ope… Watch the video ⬇️⬇️

@cgibbo Chris, it seems preso is from 2022?

"AIX Support Trends and Update" May 25th 2023. Session replay: ibm.ent.box.com/s/civqk0946bcx… Presentation material: ibm.ent.box.com/s/7k197yizzsnc… Q&A: ibm.box.com/s/1gzktlim5xco…

@The_Iron_Monger Unfortunately, I didn't get how to get announcement letters with RSS or any other way to my mailbox. How we supposed to track that now? Manually?

New spot for the news since the weekly email is now sunsetted. Follow the link to the MyNotifications page - See ya there ! #DontLetTheSunGoDownOnMe ibm.com/support/pages/… #NerdHerd #IBMChampion #FinTech #AIX #Unix #RHEL #CMC #Fintech

@nheidloff Would be cool to get access and rtest this stuff!

@ibmtechie I'm trying to find out how to get access to it for non IBMers

Prompt Tuning in the watsonx.ai Studio for Foundation Models #IBMThink