2806
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
yep, it is already 0, I didn't change it or configure it, so that's why I asked if I could increase it?
Maybe to 500ms?
But here archive_timeout if I increase it, it will take more time to switch to other wal segment, which will benefit in not have a frequent short time of writing or increasing of wals.
I mean I don't care about the total size of the repository, I just care about the rate or the frequency of wals archiving, so I guess by increaing the archive_timeout too, it will make a reasonable sense here? Is it right?
we are using GraphQL to fetch data from the IoT apps used to represent and store the data in timescaledb, and also we are using continuous materialized views to fetch new data from the app.
Not sure if there are more updating statements more than just inserting new data, will check that... But yeah, most probably we don't update any data, it is just new a lot of data being inserted
Our workload is heavy, especially because we use Timescaledb, which yes it has new data updates in a short time.
Note: the database and workload in terabytes, so we have a huge data there.
so, what do you think?
Should I increase the checkpoint_timeout and archive_timeout too?
yeah, but I mean also Archive_timeout which is the time taken for the last wal segment for archive or switching to other wal segment.
I didn't configure checkpoint_timeout, I guess by default it takes 5 mins or so??
Should I increase this parameter too?
Is it a good idea to increase both?
Can I increase the archive_timeout parameter, so it will trigger the checkpoint in a long interval time, so it will decrease the wal writing?
But I guess even if I increased the time of the archive timeout, the page is already taking minimal time to full, so by default postgres will write to a new segment file, so I guess it won't make sense??
What's your retention policy? Are you concerned with total size or just rate of growth?
Читать полностью…
There is no perfect solution. You might consider using a logical replica instead of a binary replica for very long-running queries.
Читать полностью…
okay. I tried to see the session running on the aurora side and I could not see the session related to my query there.
Is there something that the query has even crossed my Server(FWD extension could be the issue)?
AFAIK the last release broke some ABIs, which is why there was an intermediate release end of November. Are you on 17.2?
Also, did you set
shared_preload_libraries = 'bgw_replstatus'
Well, apart from hundreds of snake oil products that you can probably buy for the purpose, there's e.g. https://www.gpg4win.org/
Читать полностью…
Hi
I have PostgreSQL db on prem and I am running a query which includes few foreign tables sit on AWS aurora PostgreSQL read only instance.
This query stuck for ever with wait event "External" anad event type "External"
Other query which is having foreign tables are running and giving output .
Any thoughts? Any suggestions how to debug it?
thank u for your response about
https://gist.github.com/rogergcc/913dee43310d35cda9a3383e094bed1b#file-aqp-geojson
The default of archive_timeout is 0. Maybe try that first?
Just checkpoint_timeout. The only thing that archive_timeout will give you is even more WAL volume.
But let me ask: you're UPDATEing your timeseries data? I'm not sure if TimescaleDB is optimized for that... or do you mean you INSERTing a lot of new data?
archive_timeout is only relevant if your system is really low traffic. A WAL segment gets archived when it's reached (usually) 16MB. If you set archive_timeout, it will be written with less data in it after the timeout.
You seem be be producing too much WAL. So archive_timeout will not help you at all, you'd only produce more files.checkpoint_timeout (defaulting to 5 minutes) in combination with full_page_writes (defaulting to on) on the other hand can lead to 8kb being written to the WAL (& hence archived) every 5 minutes, even if you only change one byte on a page every 5 minutes.
Is your workload UPDATE-heavy? Maybe even something that UPDATEs rows that don't need updating (re-writing unchanged data)?
You mean checkpoint_timeout, and yes, that's probably a good choice.
Nothing to do from pgbackrest side, you should look at what's writing that much Wal and optimise. Compression type zstd or lz4 works best but not going to help with rate of growth.
Читать полностью…
Hey guys,
I use pgbackrest as my backup tool. However, I noticed that my repository keep on growing so quickly and largely.
Is there any way to optimise this pls? anything to do other than decide on archive_timeout parameter or compressing?
What is the best way to use a long running query in standby node? It has streaming configured. Looks vacuum issue occur when Hot_standby_feedback=on
Читать полностью…
Probably aurora is the issue. Does it have access (is anything else connecting using same fdw) or does it kill/block that session? Did you check security groups settings in aurora?
Читать полностью…
hello, just install pg17 cluster, and try to get cluster info from bgw_replstatus, but pg throws
ERROR: could not find function "bgw_replstatus_main" in file "/usr/pgsql-17/lib/bgw_replstatus.so"
is there bug or something I miss
Wait event message is clear. You need to debug on Aurora. They have performamce insight, explain plan and AWS support.
Читать полностью…
Well, pg_stat_activity from AWS, wireshark locally.
You may want to log ssl keys if you use ssl (something like https://github.com/wpbrown/openssl-keylog ), however even without that keys wireshark gathers some reasonable info.
Also, it could be connection/mtu issues. Wireshark also could help, but just disabling path mtu discovery and using minimal packets — could solve this problem.
Anyone have idea about data engineers and skills required for same or the tools which are helpful?? Kindly share me the path!! DM me..
Читать полностью…