petergeoghegan

6 ms·

1.
by petergeoghegan 5mo ago
Why are you using hash indexes? They're much less widely used than standard B-Tree indexes. The bucket split code likely isn't very scalable [1]. I suggest testing the same workload with your existing hash indexes replaced with eq
2.
by petergeoghegan 8mo ago
> The section on multi-column indexes mirrors how I was taught and how I’ve generally handled such indexes in the past. But is it still true for more recent PG versions? No, it isn't. PostgreSQL 18 added support for index skip scan:
3.
by petergeoghegan 1y ago
> I may not be remembering fully, maybe the indexes never shrunk but the tables did in size. That only happens when it is possible to give back space to the OS filesystem using relation truncation in the first place -- which isn't a
4.
by petergeoghegan 1y ago
No, it does not
5.
by petergeoghegan 1y ago
> In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink. It's possible to recycle pages within indexes that have some
6.
by petergeoghegan 1y ago
Can you provide more detail/a reference? I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personal
7.
by petergeoghegan 1y ago
Recent versions of clangd show pahole-like struct size/alignment padding overhead annotations. I find this built-in support a lot more convenient than using pahole.
8.
PostgreSQL 17 Release Notes(postgresql.org)
3 points by petergeoghegan 2y ago|2 comments
9.
by petergeoghegan 2y ago
"So far, so good. What's the problem here? The DDL statement can simply wait patiently until it's able to acquire its ACCESS EXCLUSIVE lock, right? The problem is that any other statements that require a lock on the users tab
10.
by petergeoghegan 2y ago
> It is certainly possible that the plans are similar, and that improvements to the execution engine are being measured. The join order benchmark was designed to test optimizer quality. I don't think that it's possible to test
11.
by petergeoghegan 3y ago
> Your code can no longer be compiled by a compiler that does not support these special C dialects. What compiler might that be? It's true that MSVC doesn't have an equivalent of -fno-strict-aliasing, but that's because it
12.
by petergeoghegan 3y ago
> Postgres has commercial backing and inertia, but it seems like we are lacking a pipeline of (proficient) C developers. It's difficult to get into Postgres development, but that has little to do with C expertise. The hard part is h
13.
by petergeoghegan 3y ago
What most users want is a statement that allows them to UPSERT, with reasonable guarantees around that never throwing a duplicate violation error, and never deadlocking (at least not in any novel way caused by implementation details). Somet
14.
by petergeoghegan 3y ago
> insert...on conflict is a fine way to do upserts on older PostgreSQL versions. For modern ones (15+) there is a better alternative [SQL Standard: MERGE] This is incorrect. To quote the Postgres MERGE docs: "When MERGE is run concu
15.
by petergeoghegan 3y ago
> It's not taken seriously because it shouldn't be taken seriously I really don't know what you're arguing against. I never questioned the general usefulness of an abstract machine. I merely pointed out that a large a
16.
by petergeoghegan 3y ago
> No. This is what I call the "portable assembler"-understanding of undefined behavior and it is entirely false. "C has been characterized (both admiringly and invidiously) as a portable assembly language" - Dennis Ri
17.
by petergeoghegan 3y ago
> The real problem here is the x86 architecture Even if we could say for sure that x86 has been disproportionately affected by speculative execution bugs (which already seems dubious), that could easily be due to a kind of selection bias
18.
by petergeoghegan 4y ago
> On further reflection I think maybe reputation for performance matters rather than performance itself I think that you're vastly overestimating the importance C as an abstract specification and as a community of programmers with a
19.
by petergeoghegan 4y ago
> It reminds me of colonial Europeans passing judgement on the "savage" inhabitants of a place they've now decided belongs to them Okay!
20.
by petergeoghegan 4y ago
> The justification for monstrously unsafe languages like C was that they're faster. I don't think that that's true. I find the explanation given by "Some Were Meant for C" [1] far more plausible. But leaving tha
21.
by petergeoghegan 4y ago
> While I do sympathize with some of the user complaints with UB, and the issues with things like signed integer overflow and strict aliasing seem entirely gratuitous, I think most users complaining about UB fail to comprehend that the i
22.
by petergeoghegan 4y ago
> Planners are too smart for their own good. I know what you mean, but I don't think that that quite captures it. It's more like this: planners are built on a set of assumptions that are often pretty far from robust, but nevert
23.
by petergeoghegan 4y ago
> C got various other things subtly right, too: manifestly enough to make up for its blatant failings. If you would displace C, it is much more important to retain its strengths than to fix its flaws. I agree. I wonder how feasible it is
24.
by petergeoghegan 4y ago
> When you write C programs, you are coding on the C abstract machine as specified by the language standard. Are you really, though? I would argue that it's a matter of perspective and/or semantics. The Linux kernel is built wi
25.
by petergeoghegan 4y ago
> You are understating the limitations of B+trees for real workloads. I never said anything about workloads. All I said was that your statements about B+Trees having dwindling usage are clearly false. If you make a claim that is self-evi
26.
by petergeoghegan 4y ago
It's definitely possible, and can make a lot of sense -- MyRocks/RocksDB for MySQL seems like an interesting and well designed system to me. It is fairly natural to compare MyRocks to InnoDB, since they're both MySQL storage
27.
by petergeoghegan 4y ago
Thanks for the tip
28.
by petergeoghegan 4y ago
> You don't actually address the point. You said that B-Trees "use in indexing has dwindled with time". This is demonstrably false. > Back then I used them ubiquitously but I honestly don't remember the last time I
29.
by petergeoghegan 4y ago
> Modern hardware has evolved to the point where B+trees will often offer disappointing results, so their use in indexing has dwindled with time. This is pure nonsense. B+Trees are used extensively and by default by 5 out of 5 of the top
30.
by petergeoghegan 4y ago
> Some of the explanations are questionable: I think they were overly simplified, and while I applaud the goal, some things just aren't that simple. I am an expert on the subject matter, and I don't think that the overall appro

More ›