pgaddict

9 ms·

31.
by pgaddict 2y ago
I believe some JIT systems already do PGO / might be extended to do what BOLT does.
32.
by pgaddict 2y ago
Yeah, I was worried using the "wrong" profile might result in regressions. But I haven't really seen that in my tests, even when using profiles from quite different workloads (like OLTP vs. analytics, different TPC-H queries,
33.
by pgaddict 2y ago
With the LTO, I think it's more complicated - it depends on the packagers / distributions, and e.g. on Ubuntu we apparently get -flto for years.
34.
by pgaddict 2y ago
Yeah, getting the profile is obviously a very important step. Because if it wasn't, why collect the profile at all? We could just do "regular" LTO. I'm not sure there's one correct way to collect the profile, though
35.
by pgaddict 2y ago
True. At the moment I don't have anything very "actionable" beyond "it's magically faster", so I wanted to investigate this a bit more before posting to -hackers. For example, after reading the paper I realized
36.
by pgaddict 2y ago
(author here) I agree the +40% effect feels a bit too good, but it only applies to the simple OLTP queries on in-memory data, so the inefficiencies may have unexpectedly large impact. I agree 30-40% would be a massive speedup, and I expecte
37.
by pgaddict 2y ago
No, that's a completely different thing - an index access method, building a bloom filter on multiple columns of a single row. Which means a query then can have an equality condition on any of the columns. That being said, building a b
38.
by pgaddict 3y ago
Yeah, I certainly understand ZFS was designed for different types of storage, but we now have flash storage as a default ... Which is why I do these benchmarks - to educate myself and learn what behavior to expect from different systems, an
39.
by pgaddict 3y ago
There's a link to a github repo [ https://github.com/tvondra/pg-fs-benchmark ] with all the configuration details, including the ZFS details for each of the runs. For example https://github.com/tvond
40.
by pgaddict 3y ago
I don't think there's a uniform agreement to not have hints, and even for devs opposing the idea of hints is there is not a single universal reason and it's more like every single dev has his own reason(s) to not like them. F
41.
by pgaddict 3y ago
>> Yeah. But the whole discussion here was about the dilemma the optimizer faces if it only has the two indexes on (created_at) and (organization_id), and why the assumption of independence/uniformity does not work for the skewed
42.
by pgaddict 3y ago
Yeah. But the whole discussion here was about the dilemma the optimizer faces if it only has the two indexes on (created_at) and (organization_id), and why the assumption of independence/uniformity does not work for the skewed case. Of
43.
by pgaddict 3y ago
Yeah, I meant May. Sorry :-( Too many conferences around that time, I got confused. That being said, submitting this into the pgconf.eu CfP is a good idea too. It's just that it seems like a nice development topic, and the pgcon unconf
44.
by pgaddict 3y ago
Would be a great topic for pgconf.eu in June (pgcon moved to Vancouver). Too bad the CfP is over, but there's the "unconference" part (but the topics are decided at the event, no guarantees).
45.
by pgaddict 3y ago
Not sure, but I see this ... I simplified the SQL a little bit - I don't think it's necessary to have two timestamps and cluster on one of them. The other timestamp is not correlated, so the access through that index will be rando
46.
by pgaddict 3y ago
I think this very much depends on what exactly is the problem - if it's with how costing for LIMIT works ( https://news.ycombinator.com/item?id=39728826 ), then yeah, this did not change since 9.x in a substantial way, s
47.
by pgaddict 3y ago
Now try adding a new organization with ID 11 and timestamps on the tail end (>2020-01-01). And query for that ID.
48.
by pgaddict 3y ago
I'd suggest reporting it to pgsql-performance mailing list [1], and continuing the discussion there. I'd expect more community members to join that discussion there. [1] https://www.postgresql.org/list/pgsql-p
49.
by pgaddict 3y ago
Abandoned? Certainly not. But it's a complex part of a mature database product, with many existing deployments/users, which means the improvement is affecting literally everyone. And query planning in general is a hard problem. So
50.
by pgaddict 3y ago
I don't know which "architectural peculiarities" you have in mind, but the issues we have are largely independent of the architecture. So I'm not sure what exactly you think we should be fixing architecture-wise ... The
51.
by pgaddict 3y ago
It's really hard to say why this is happening without EXPLAIN ANALYZE (and even then it may not be obvious), but it very much seems like the problem with correlated columns we often have with LIMIT queries. The optimizer simply assumes
52.
by pgaddict 3y ago
Thanks. I wonder how you plan to replicate stuff, considering how heavily it relies on WAL (I haven't found the answer in the code, but I'm not very familiar with rust). How large part of the plan you route to the datalake tables?
53.
by pgaddict 3y ago
Looks very nice. I have three random questions: 1) How does this deal with backups? Presumably the deltalake tables can't be backed up by Postgres itself, so I guess there's some special way to d backups? 2) Similarly for replicat
54.
by pgaddict 3y ago
The chances may be low, but either it's a draft or a final version. There's clearly little pressure to rush this, considering it's not difficult to add a custom function generating UUIDv7 ...
55.
by pgaddict 3y ago
I don't think reviewer bandwidth is the main issue for this patch. It's a 200-line change (considering C code, there's more in docs/tests), and the code is not overly complicated / sensitive (in the sense that it&#x
56.
by pgaddict 3y ago
Yes, internally it's the same as every other UUID (16 bytes, passed by reference). There's no reason to store / represent it differently.
57.
by pgaddict 3y ago
I did check the docs. According to the search, there are like 5 references to "consistency", 4 of which are talking about how traditional databases do that poorly and the 5th one seems to suggest to use "depot partitioner&quo
58.
by pgaddict 3y ago
Did I miss something, or does that post completely omit concepts like concurrency, isolation, constraints and such? And are they really suggesting "query topologies" (which seem very non-declarative and essentially making query pl
59.
by pgaddict 3y ago
Right. In a way, these new sequential UUIDs approach the fact that recent records are often accessed more frequently as something that can be exploited, not as an issue that needs to be ironed out by randomness. For tables this is not such
60.
by pgaddict 3y ago
This is a bit confusing, as it mixes two things - BRIN index and index on a timestamp. The main source of write amplification comes from updating random pages of the btree index. Imagine inserting 10 random UUID values into a large index -

More ›