RE: BUG #18016: REINDEX TABLE failure

Hi Gurjeet,

Thank you for the follow-up. I was worried my message got buried in the middle of the thread. I also appreciate your work on the patch to fix/improve the REINDEX TABLE behavior even though most people would never encounter it in the wild.

As a preface I would first like to say that I can appreciate the emphasis on general maintainability of the codebase, trying to avoid having some overly clever hacks that might impede understanding, having ideally one way of doing things like having a common database page structure, etc. The more one keeps to this "happy" path the better the general state of the project end up by keeping it accessible to the rest of the community and attracting more contributions in turn.

That being said, PostgreSQL can be extremely conservative in scenarios where it might not be warranted while giving a limited opportunity to influence said behavior. This often leads to a very low hardware resource utilization. You can easily find many instances across StackOverflow, dba.stackexchange.com, /r/postgres and pgsql-performance where people run into ingress/egress bottlenecks even though their hardware can trivially support much larger workload.

In my experience, you can be very hard-pressed in many cases to saturate even a modest enterprise HDD while observing the official guidelines (https://www.postgresql.org/docs/current/populate.html), e.g. minimal WAL and host of other configuration optimizations, having no indexes and constraints and creating table and filling it with binary COPY within the same transaction. And the situation with pg_dump/pg_restore is often much worse.

Is there an interest in improving the current state of affairs? I will be rewriting the indexing first to get the whole picture, but I can already tell you that there is a -lot- of performance left on the table even considering the effort to improve COPY performance in PostgreSQL 16. Given sufficient hardware, you should always be heavily IO-bound without exception and saturate any reasonable number of NVMe SSDs.

Best regards,
Richard

-----Original Message-----
From: Gurjeet Singh <gurjeet(at)singh(dot)im>
Sent: Monday, July 10, 2023 6:44 PM
To: Richard Veselý <richard(dot)vesely(at)softea(dot)sk>; Postgres Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Michael Paquier <michael(at)paquier(dot)xyz>
Subject: Re: BUG #18016: REINDEX TABLE failure

On Sun, Jul 9, 2023 at 7:21 AM Richard Veselý <richard(dot)vesely(at)softea(dot)sk> wrote:
>
> ... there's no shortage of people that suffer from sluggish pg_dump/pg_restore cycle and I imagine there are any number of people that would be interested in improving bulk ingestion which is often a bottleneck for analytical workloads as you are well aware. What's the best place to discuss this topic further - pgsql-performance or someplace else?

(moved conversation to -hackers, and moved -bugs to BCC)

> I was dissatisfied with storage layer performance, especially during
> the initial database population, so I rewrote it for my use case. I'm
> done with the heap, but for the moment I still rely on PostgreSQL to
> build indexes,

It sounds like you've developed a method to speed up loading of tables, and might have ideas/suggestions for speeding up CREATE INDEX/REINDEX. The -hackers list feels like a place to discuss such changes.

Best regards,
Gurjeet
http://Gurje.et