Re: autovacuum: change priority of the vacuumed tables

er of pages/tuples a list contains. A more fine-grained approach would be to have workers make a new selection after every vacuum they complete. That would provide the ultimate in control, since you'd be able to see exactly what all the other workers are doing. -- Jim Nasby, Chief Dat

Re: autovacuum: change priority of the vacuumed tables

up all the time. There's plenty of other ways to shoot yourself in the foot in that regard already. We can always have safeguards in place if we get too close to wrap-around, just like we currently do. -- Jim Nasby, Chief Data Architect, Austin TX OpenSCG http://OpenSCG.com

Re: Confine vacuum skip logic to lazy_scan_skip

On 1/11/24 5:50 PM, Melanie Plageman wrote: On Fri, Jan 5, 2024 at 5:51 AM Nazir Bilal Yavuz wrote: On Fri, 5 Jan 2024 at 02:25, Jim Nasby wrote: On 1/4/24 2:23 PM, Andres Freund wrote: On 2024-01-02 12:36:18 -0500, Melanie Plageman wrote: Subject: [PATCH v2 1/6] lazy_scan_skip remove

Re: Make NUM_XLOGINSERT_LOCKS configurable

e_time) and WAL Wait for In-progress Inserts to Finish Time (wal_wait_for_insert_to_finish_time). Unfortunately this leaves the question of how frequently is WaitXLogInsertionsToFinish() being called and by whom. One possibility here is that wal_buffers is too small so backends are constantly having to write WAL data to free up buffers. -- Jim Nasby, Data Architect, Austin TX

Re: Emit fewer vacuum records by reaping removable tuples during pruning

or code where correctness is so crucial. -- Jim Nasby, Data Architect, Austin TX

Re: index prefetching

G has used batched IO for a very long time (it also *only* reads blocks that aren't marked all visble/frozen; none of the "only skip if skipping at least 32 blocks" logic is used). 1: https://d1.awsstatic.com/events/reinvent/2019/REPEAT_1_Deep_dive_on_Amazon_Aurora_with_Postgr

Re: Should vacuum process config file reload more often

On 3/2/23 1:36 AM, Masahiko Sawada wrote: For example, I guess we will need to take care of changes of maintenance_work_mem. Currently we initialize the dead tuple space at the beginning of lazy vacuum, but perhaps we would need to enlarge/shrink it based on the new value? Doesn't the dead tuple

Re: SET ROLE x NO RESET

On 12/31/23 1:19 PM, Joe Conway wrote: On 12/30/23 17:19, Michał Kłeczek wrote: On 30 Dec 2023, at 17:16, Eric Hanson wrote: What do you think of adding a NO RESET option to the SET ROLE c

Re: Next step towards 64bit XIDs: Switch to FullTransactionId for PGPROC->xid and XLogRecord->xl_xid

On 1/2/24 1:58 PM, Robert Haas wrote: Maybe this analysis I've just given isn't quite right, but my point is that we should try to think hard about where in the system 32-bit XIDs suck and for what reason, and use that as a guide to what to change first. Very mu

Re: Things I don't like about \du's "Attributes" column

e" than explicit SU on roles). I'm on the fence when it comes to SQL syntax vs what we have now. What we currenly have is more readable, but off-hand I think the other places we list attributes we do it in SQL syntax. It might be worth changing just for consistency sake. -- Jim Nasby, Data Architect, Austin TX

Re: add function argument names to regex* functions.

` is not as precise but would avoid the conceptual overloading of ordinary indices. I'm not a fan of "index" since that leaves the question of whether it's 0 or 1 based. "Position" is a bit better, but I think Jian's suggestion of "occurance" is best. -- Jim Nasby, Data Architect, Austin TX

Re: Password leakage avoidance

cure method of setting passwords. -- Jim Nasby, Data Architect, Austin TX

Re: Extension Enhancement: Buffer Invalidation in pg_buffercache

orce = PG_GETARG_BOOL(1); I think you also need to test PG_ARGISNULL with force parameter. Actually, that's true for the first argument as well. Or, just mark the function as STRICT. -- Jim Nasby, Data Architect, Austin TX

Re: add function argument names to regex* functions.

ia \df or whatever, so presumably misspelling wouldn't be a big issue. But I think "instance" is OK as well. -- Jim Nasby, Data Architect, Austin TX

Re: SET ROLE x NO RESET

st to set their role back to themselves, but RESET ROLE removes the risk of typos. -- Jim Nasby, Data Architect, Austin TX

Re: doing also VM cache snapshot and restore with pg_prewarm, having more information of the VM inside PostgreSQL

://Data-Bene.io PostgreSQL Expertise, Support, Training, R&D -- Jim Nasby, Data Architect, Austin TX

Re: the s_lock_stuck on perform_spin_delay

easier than code analysis. Another possibility might be using the CPUs timestamp counter. 1: https://valgrind.org/docs/manual/lk-manual.html -- Jim Nasby, Data Architect, Austin TX

Re: Confine vacuum skip logic to lazy_scan_skip

ent vacuum changes, but I have to wonder if the concept of skipping should go away in the context of vector IO? Instead of thinking about "we can skip this range of blocks", why not maintain a list of "here's the next X number of blocks that we need to

Re: Random pg_upgrade test failure on drongo

ng the problem (non-POSIX behavior on SMB and ReFS) under the carpet? I realize that synthetic test workloads like pg_upgrade in a loop aren't themselves real-world scenarios, but what about other cases? Even if we're certain it's not possible for these issues to wedge a serv

Re: Emit fewer vacuum records by reaping removable tuples during pruning

for some cases. It's hard to conceive of this breaking for indexes on integers, for example. But we'd still need to be cautious. -- Jim Nasby, Data Architect, Austin TX

Re: Add BF member koel-like indentation checks to SanityCheck CI

vim-autoformat[2] (which also supports line-by-line formatting if the format tool allows it); presumably any modern editor has similar support. 1: Literally 3rd item at https://go.dev/doc/effective_go 2: https://github.com/vim-autoformat/vim-autoformat -- Jim Nasby, Data Architect, Austin TX

Re: Direct I/O

On 11/1/22 2:36 AM, Thomas Munro wrote: Hi, Here is a patch to allow PostgreSQL to use $SUBJECT. It is from the This is exciting to see! There's two other items to add to the TODO list before this would be ready for production: 1) work_mem. This is a significant impediment to scaling shar

Re: refactoring relation extension and BufferAlloc(), faster COPY

On 10/28/22 9:54 PM, Andres Freund wrote: b) I found that is quite beneficial to bulk-extend the relation with smgrextend() even without concurrency. The reason for that is the primarily the aforementioned dirty buffers that our current extension method causes. One bit that stumped m

Re: refactoring relation extension and BufferAlloc(), faster COPY

On 2/21/23 3:12 PM, Andres Freund wrote: CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe. Hi, On 2023-02-21 15:00:15 -0600, Jim Nasby wrote: Some food for thought: I

Re: Vacuum statistics

On Oct 29, 2024, at 7:40 AM, Andrei Zubkov wrote: > > Hi, > > Thanks for your attention to our patch! > > On Mon, 2024-10-28 at 16:03 -0500, Jim Nasby wrote: >>> Yes, but as Masahiko-san pointed out, PgStat_TableCounts is almost >>> tripled in space

Planner issue with BitmapScan recheck on external TOAST

I’ve been testing use of a BRIN index on record creation date (timestamptz) on a snapshot of a production system. Note that after creating the BRIN index the number of buffers being accessed jumps from 23838 to 191663. Based on what EXPLAIN is showing, I believe the issue is that the planner doe

Re: Vacuum statistics

> On Oct 28, 2024, at 2:07 PM, Alexander Korotkov wrote: > >> I suppose that if we turn off statistics collection for a certain object, we >> can miss it. In addition, the user may not enable the parameter for the >> object in time, because he will forget about it. > > I agree with this poin

Re: sunsetting md5 password support

> On Oct 28, 2024, at 3:21 PM, Greg Sabino Mullane wrote: > > On Sat, Oct 26, 2024 at 11:55 AM Nathan Bossart > wrote: >> rebased > > Patch applied without issue and looks good to me. Patch itself looks good, but it does leave me wondering if cleartext should

Re: Vacuum statistics

> On Nov 2, 2024, at 7:22 AM, Alena Rybakina wrote: > >>> The second is the interrupts field. It is needed for monitoring to know >>> do we have them or not, so tracking them on the database level will do >>> the trick. Interrupt is quite rare event, so once the monitoring system >>> will catch

Re: Vacuum statistics

On Nov 10, 2024, at 2:09 PM, Alena Rybakina wrote: On 08.11.2024 22:34, Jim Nasby wrote: On Nov 2, 2024, at 7:22 AM, Alena Rybakina wrote

Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Jan 2, 2025, at 9:21 PM, Tomas Vondra wrote: > >> That said, I do think a workload manager would be more effective than >> trying to limit total connections. > > The "workload management" concept is so abstract I find it very > difficult to discuss without much more detail about how would it

Re: Vacuum statistics

On Jan 2, 2025, at 4:33 PM, Sami Imseih wrote: > >> While backwards compatibility is important, there’s definitely precedent for >> changing >> what shows up in the catalog. IMHO it’s better to bite the bullet and move >> those fields >> instead of having vacuum stats spread across two differen

Re: Add the ability to limit the amount of memory that can be allocated to backends.

> On Dec 31, 2024, at 5:41 PM, Tomas Vondra wrote: > > On 12/31/24 21:46, Jim Nasby wrote: >> On Dec 30, 2024, at 7:05 PM, James Hunter wrote: >>> >>> On Sat, Dec 28, 2024 at 11:24 PM Jim Nasby wrote: >>>> >>>> IMHO none of th

Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Dec 28, 2024, at 12:26 PM, Jeremy Schneider wrote: > > While I don't have a detailed design in mind, I'd like to add a strong > +1 on the general idea that work_mem is hard to effectively use because > queries can vary so widely in how many nodes might need work memory. > > I'd almost like to

Re: PoC: history of recent vacuum/checkpoint runs (using new hooks)

On Dec 25, 2024, at 11:25 AM, Tomas Vondra wrote: > But maybe it'd be possible to just write the entries to a file. We don't > need random access to past entries (unlike e.g. pg_stat_statements), and > people won't query that very often either. Assuming this doesn’t add significant complexity I t

Re: should we have a fast-path planning for OLTP starjoins?

On Tue, Feb 4, 2025 at 3:42 PM Tomas Vondra wrote: > On 2/4/25 21:23, Tom Lane wrote: > > Tomas Vondra writes: > >> On 2/4/25 20:43, Jeff Davis wrote: > >>> If you base it on the join conditions rather than the size of the > >>> table, then detection of the star join would be based purely on the

Re: Vacuum statistics

> On Jan 2, 2025, at 2:12 PM, Sami Imseih wrote: > > Alternatively, we can remove the vacuum related stats from pg_stat_all_tables, > but that will break monitoring tools and will leave us with the (auto)analyze > metrics alone in pg_stat_all_tables. This sounds very ugly. While backwards compa

Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Dec 30, 2024, at 7:05 PM, James Hunter wrote: > > On Sat, Dec 28, 2024 at 11:24 PM Jim Nasby wrote: >> >> IMHO none of this will be very sane until we actually have cluster-level >> limits. One sudden burst in active connections and you still OOM the >>

Re: PoC: history of recent vacuum/checkpoint runs (using new hooks)

On Dec 31, 2024, at 9:20 AM, Tomas Vondra wrote: > >> Speaking of retention, it would be nice if this feature allowed users to >> DELETE from the view that presented the data. That would allow for any >> kind of custom config that someone could dream up. > > I really don't intend / want to do th

Re: Parallel heap vacuum

On Mon, Feb 17, 2025 at 12:11 PM Masahiko Sawada wrote: > > If the idea is to never allow parallelism in vacuum, then I think > > disabling eager scanning during manual parallel vacuum seems > > reasonable. People could use vacuum freeze if they want more freezing. > > IIUC the purpose of paralle

Re: Vacuum statistics

On Wed, Mar 12, 2025 at 2:41 PM Alena Rybakina wrote: > Hi! > > On 10.03.2025 12:13, Ilia Evdokimov wrote: > > Hi, > > > > After commit eaf5027 we should add information about wal_buffers_full. > > > > Any thoughts? > > > > -- > > Best regards, > > Ilia Evdokimov, > > Tantor Labs LLC. > > > I thi

Re: Vacuum statistics

On Fri, Mar 21, 2025 at 2:42 PM Alena Rybakina wrote: > On 13.03.2025 09:42, Bertrand Drouvot wrote: > > Hi, > > On Wed, Mar 12, 2025 at 05:15:53PM -0500, Jim Nasby wrote: > > The usecase I can see here is that we don't want autovac creating so much > WAL traffic