Content-Type: multipart/mixed; boundary="------------b5v7q97kEhMPfCsrYQ8b3S0z"
Message-ID: <fe99e0ca-e564-480e-b865-5f0cee30bc60@tantorlabs.com>
Date: Mon, 18 Nov 2024 21:33:16 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Subject: Sample rate added to pg_stat_statements
Archived-At: <https://www.postgresql.org/message-id/fe99e0ca-e564-480e-b865-5f0cee30bc60%40tantorlabs.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------b5v7q97kEhMPfCsrYQ8b3S0z
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi hackers,

Under high-load scenarios with a significant number of transactions per 
second, pg_stat_statements introduces substantial overhead due to the 
collection and storage of statistics. Currently, we are sometimes forced 
to disable pg_stat_statements or adjust the size of the statistics using 
pg_stat_statements.max, which is not always optimal. One potential 
solution to this issue could be query sampling in pg_stat_statements.

A similar approach has been implemented in extensions like auto_explain 
and pg_store_plans, and it has proven very useful in high-load systems. 
However, this approach has its trade-offs, as it sacrifices statistical 
accuracy for improved performance. This patch introduces a new 
configuration parameter, pg_stat_statements.sample_rate for the 
pg_stat_statements extension. The patch provides the ability to control 
the sampling of query statistics in pg_stat_statements.

This patch serves as a proof of concept (POC), and I would like to hear 
your thoughts on whether such an approach is viable and applicable.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
--------------b5v7q97kEhMPfCsrYQ8b3S0z
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-PATCH-Allow-setting-sample-ratio-for-pg_stat_stateme.patch"
Content-Disposition: attachment;
 filename*0="0001-PATCH-Allow-setting-sample-ratio-for-pg_stat_stateme.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSBiMTkwODFiZDI1NzAxNGYwYzQwNDY1NzAwOTdlNGJjN2IyOGEzMTI2IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBJbGlhIEV2ZG9raW1vdiA8aWx5YS5ldmRva2ltb3ZA
dGFudG9ybGFicy5jb20+CkRhdGU6IE1vbiwgMTggTm92IDIwMjQgMjE6MDU6NDMgKzAzMDAK
U3ViamVjdDogW1BBVENIXSBbUEFUQ0hdIEFsbG93IHNldHRpbmcgc2FtcGxlIHJhdGlvIGZv
ciBwZ19zdGF0X3N0YXRlbWVudHMKCk5ldyBjb25maWd1cmF0aW9uIHBhcmFtZXRlciBwZ19z
dGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGlvIG1ha2VzIGl0CnBvc3NpYmxlIHRvIGNvbnRy
b2wganVzdCBhIGZyYWN0aW9uIG9mIHRoZSBxdWVyaWVzIG1lZXRpbmcgdGhlIGNvbmZpZ3Vy
ZWQKdGhyZXNob2xkLCB0byByZWR1Y2UgdGhlIGFtb3VudCBvZiBjb250cm9sbGluZy4KLS0t
CiAuLi4vcGdfc3RhdF9zdGF0ZW1lbnRzL3BnX3N0YXRfc3RhdGVtZW50cy5jICAgfCAyMiAr
KysrKysrKysrKysrKysrKystCiBkb2Mvc3JjL3NnbWwvcGdzdGF0c3RhdGVtZW50cy5zZ21s
ICAgICAgICAgICAgfCAxNyArKysrKysrKysrKysrKwogMiBmaWxlcyBjaGFuZ2VkLCAzOCBp
bnNlcnRpb25zKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEvY29udHJpYi9wZ19z
dGF0X3N0YXRlbWVudHMvcGdfc3RhdF9zdGF0ZW1lbnRzLmMgYi9jb250cmliL3BnX3N0YXRf
c3RhdGVtZW50cy9wZ19zdGF0X3N0YXRlbWVudHMuYwppbmRleCA0OWM2NTdiM2UwLi41MWQx
YTQ4MjUyIDEwMDY0NAotLS0gYS9jb250cmliL3BnX3N0YXRfc3RhdGVtZW50cy9wZ19zdGF0
X3N0YXRlbWVudHMuYworKysgYi9jb250cmliL3BnX3N0YXRfc3RhdGVtZW50cy9wZ19zdGF0
X3N0YXRlbWVudHMuYwpAQCAtNDksNiArNDksNyBAQAogCiAjaW5jbHVkZSAiYWNjZXNzL3Bh
cmFsbGVsLmgiCiAjaW5jbHVkZSAiY2F0YWxvZy9wZ19hdXRoaWQuaCIKKyNpbmNsdWRlICJj
b21tb24vcGdfcHJuZy5oIgogI2luY2x1ZGUgImNvbW1vbi9pbnQuaCIKICNpbmNsdWRlICJl
eGVjdXRvci9pbnN0cnVtZW50LmgiCiAjaW5jbHVkZSAiZnVuY2FwaS5oIgpAQCAtNzMsNiAr
NzQsOCBAQAogCiBQR19NT0RVTEVfTUFHSUM7CiAKK3N0YXRpYyBkb3VibGUgcGdzc19zYW1w
bGVfcmF0ZSA9IDE7CisKIC8qIExvY2F0aW9uIG9mIHBlcm1hbmVudCBzdGF0cyBmaWxlICh2
YWxpZCB3aGVuIGRhdGFiYXNlIGlzIHNodXQgZG93bikgKi8KICNkZWZpbmUgUEdTU19EVU1Q
X0ZJTEUJUEdTVEFUX1NUQVRfUEVSTUFORU5UX0RJUkVDVE9SWSAiL3BnX3N0YXRfc3RhdGVt
ZW50cy5zdGF0IgogCkBAIC0yOTUsNiArMjk4LDggQEAgc3RhdGljIGJvb2wgcGdzc190cmFj
a19wbGFubmluZyA9IGZhbHNlOwkvKiB3aGV0aGVyIHRvIHRyYWNrIHBsYW5uaW5nCiAJCQkJ
CQkJCQkJCSAqIGR1cmF0aW9uICovCiBzdGF0aWMgYm9vbCBwZ3NzX3NhdmUgPSB0cnVlOwkv
KiB3aGV0aGVyIHRvIHNhdmUgc3RhdHMgYWNyb3NzIHNodXRkb3duICovCiAKKy8qIElzIHRo
ZSBjdXJyZW50IHRvcC1sZXZlbCBxdWVyeSB0byBiZSBzYW1wbGVkPyAqLworc3RhdGljIGJv
b2wgY3VycmVudF9xdWVyeV9zYW1wbGVkID0gZmFsc2U7CiAKICNkZWZpbmUgcGdzc19lbmFi
bGVkKGxldmVsKSBcCiAJKCFJc1BhcmFsbGVsV29ya2VyKCkgJiYgXApAQCAtNDE0LDYgKzQx
OSwxOSBAQCBfUEdfaW5pdCh2b2lkKQogCQkJCQkJCU5VTEwsCiAJCQkJCQkJTlVMTCk7CiAK
KwlEZWZpbmVDdXN0b21SZWFsVmFyaWFibGUoInBnX3N0YXRfc3RhdGVtZW50cy5zYW1wbGVf
cmF0ZSIsCisJCQkJCQkJICJGcmFjdGlvbiBvZiBxdWVyaWVzIHRvIHByb2Nlc3MuIiwKKwkJ
CQkJCQkgTlVMTCwKKwkJCQkJCQkgJnBnc3Nfc2FtcGxlX3JhdGUsCisJCQkJCQkJIDEuMCwK
KwkJCQkJCQkgMC4wLAorCQkJCQkJCSAxLjAsCisJCQkJCQkJIFBHQ19TVVNFVCwKKwkJCQkJ
CQkgMCwKKwkJCQkJCQkgTlVMTCwKKwkJCQkJCQkgTlVMTCwKKwkJCQkJCQkgTlVMTCk7CisK
IAlEZWZpbmVDdXN0b21FbnVtVmFyaWFibGUoInBnX3N0YXRfc3RhdGVtZW50cy50cmFjayIs
CiAJCQkJCQkJICJTZWxlY3RzIHdoaWNoIHN0YXRlbWVudHMgYXJlIHRyYWNrZWQgYnkgcGdf
c3RhdF9zdGF0ZW1lbnRzLiIsCiAJCQkJCQkJIE5VTEwsCkBAIC05ODksNiArMTAwNyw4IEBA
IHBnc3NfcGxhbm5lcihRdWVyeSAqcGFyc2UsCiBzdGF0aWMgdm9pZAogcGdzc19FeGVjdXRv
clN0YXJ0KFF1ZXJ5RGVzYyAqcXVlcnlEZXNjLCBpbnQgZWZsYWdzKQogeworCWN1cnJlbnRf
cXVlcnlfc2FtcGxlZCA9IChwZ19wcm5nX2RvdWJsZSgmcGdfZ2xvYmFsX3Bybmdfc3RhdGUp
IDwgcGdzc19zYW1wbGVfcmF0ZSk7CisKIAlpZiAocHJldl9FeGVjdXRvclN0YXJ0KQogCQlw
cmV2X0V4ZWN1dG9yU3RhcnQocXVlcnlEZXNjLCBlZmxhZ3MpOwogCWVsc2UKQEAgLTk5OSw3
ICsxMDE5LDcgQEAgcGdzc19FeGVjdXRvclN0YXJ0KFF1ZXJ5RGVzYyAqcXVlcnlEZXNjLCBp
bnQgZWZsYWdzKQogCSAqIGNvdW50aW5nIG9mIG9wdGltaXphYmxlIHN0YXRlbWVudHMgdGhh
dCBhcmUgZGlyZWN0bHkgY29udGFpbmVkIGluCiAJICogdXRpbGl0eSBzdGF0ZW1lbnRzLgog
CSAqLwotCWlmIChwZ3NzX2VuYWJsZWQobmVzdGluZ19sZXZlbCkgJiYgcXVlcnlEZXNjLT5w
bGFubmVkc3RtdC0+cXVlcnlJZCAhPSBVSU5UNjRDT05TVCgwKSkKKwlpZiAoY3VycmVudF9x
dWVyeV9zYW1wbGVkICYmIHBnc3NfZW5hYmxlZChuZXN0aW5nX2xldmVsKSAmJiBxdWVyeURl
c2MtPnBsYW5uZWRzdG10LT5xdWVyeUlkICE9IFVJTlQ2NENPTlNUKDApKQogCXsKIAkJLyoK
IAkJICogU2V0IHVwIHRvIHRyYWNrIHRvdGFsIGVsYXBzZWQgdGltZSBpbiBFeGVjdXRvclJ1
bi4gIE1ha2Ugc3VyZSB0aGUKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9wZ3N0YXRzdGF0
ZW1lbnRzLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcGdzdGF0c3RhdGVtZW50cy5zZ21sCmluZGV4
IDUwMWI0NjhlOWEuLjFlMjUzM2E4MDIgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9wZ3N0
YXRzdGF0ZW1lbnRzLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3Bnc3RhdHN0YXRlbWVudHMu
c2dtbApAQCAtOTM2LDYgKzkzNiwyMyBAQAogICAgIDwvbGlzdGl0ZW0+CiAgICA8L3Zhcmxp
c3RlbnRyeT4KIAorICAgPHZhcmxpc3RlbnRyeT4KKyAgICA8dGVybT4KKyAgICAgPHZhcm5h
bWU+cGdfc3RhdF9zdGF0ZW1lbnRzLnNhbXBsZV9yYXRlPC92YXJuYW1lPiAoPHR5cGU+cmVh
bDwvdHlwZT4pCisgICAgIDxpbmRleHRlcm0+CisgICAgICA8cHJpbWFyeT48dmFybmFtZT5w
Z19zdGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGU8L3Zhcm5hbWU+IGNvbmZpZ3VyYXRpb24g
cGFyYW1ldGVyPC9wcmltYXJ5PgorICAgICA8L2luZGV4dGVybT4KKyAgICA8L3Rlcm0+CisK
KyAgICA8bGlzdGl0ZW0+CisgICAgIDxwYXJhPgorICAgICAgPHZhcm5hbWU+cGdfc3RhdF9z
dGF0ZW1lbnRzLnNhbXBsZV9yYXRlPC92YXJuYW1lPiBjYXVzZXMgcGdfc3RhdF9zdGF0ZW1l
bnRzIHRvIG9ubHkKKyAgICAgIGNvbnRyb2wgYSBmcmFjdGlvbiBvZiB0aGUgc3RhdGVtZW50
cyBpbiBlYWNoIHNlc3Npb24uICBUaGUgZGVmYXVsdCBpcyAxLAorICAgICAgbWVhbmluZyBj
b250cm9sIGFsbCB0aGUgcXVlcmllcy4gT25seSBzdXBlcnVzZXJzIGNhbiBjaGFuZ2UgdGhp
cyBzZXR0aW5nLgorICAgICA8L3BhcmE+CisgICAgPC9saXN0aXRlbT4KKyAgIDwvdmFybGlz
dGVudHJ5PgorCiAgICA8dmFybGlzdGVudHJ5PgogICAgIDx0ZXJtPgogICAgICA8dmFybmFt
ZT5wZ19zdGF0X3N0YXRlbWVudHMuc2F2ZTwvdmFybmFtZT4gKDx0eXBlPmJvb2xlYW48L3R5
cGU+KQotLSAKMi4zNC4xCgo=

--------------b5v7q97kEhMPfCsrYQ8b3S0z--