anarazel

7 ms·

31.
by anarazel 10mo ago
The issue is more fundamental - if you have purely random keys, there's basically no spatial locality for the index data. Which means that for decent performance your entire index needs to be in memory, rather than just recent data. An
32.
by anarazel 10mo ago
VACUUM FULL is about cleanup up things above the level of a single page. Moving stuff around within a page doesn't allow you to reclaim space on the OS level, nor does it "compact" tuples onto fewer pages. But it's imp
33.
by anarazel 10mo ago
> Every time Postgres advice says to “schedule [important maintenance] during low traffic period” (OP) or “outside business hours”, it reinforces my sense that it’s not suitable for performance-sensitive data path on a 24/7/365
34.
by anarazel 10mo ago
> > When VACUUM runs, it removes those dead tuples and compacts the remaining rows within each page. > No it doesn’t. It just removes unused line pointers and marks the space as free in the FSM. It does: https://github.c
35.
by anarazel 10mo ago
It's true - otherwise the space couldn't freely be reused, because the gaps for the vacuumed tuples wouldn't allow for any larger tuples to be inserted. See https://github.com/postgres/postgres/blob&
36.
by anarazel 10mo ago
You can just set fsync=off if you don't want to flush to disk and are ok with corruption in case of a OS/hw level crash.
37.
by anarazel 11mo ago
You don't need to deal with them as a patch author :)
38.
by anarazel 11mo ago
If you just make a change of code, you don't need to handle translations at that time. That will get done by the various translation teams closer to the release. However you do need to make sure that the code is translatable (e.g. in
39.
by anarazel 11mo ago
That should be trivial to change: https://github.com/postgres/postgres/blob/d2f24df19b7a42a094...
40.
by anarazel 11mo ago
We don't have the complete version history of postgres, so that's not easy to know. There definitely are still lines from Postgres95 that haven't been changed since the initial import into our repository. Somewhere there&#x27
41.
by anarazel 1y ago
It got reverted for now: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
42.
by anarazel 1y ago
The docs for 18 also show it, where do you get from that it's not available for 18?
43.
by anarazel 1y ago
It is. I tried to repro it without success. I wonder if it's just being executed on a different VMs with slightly different performance characteristics. I can't tell based on the formulation in the post whether all the runs for on
44.
by anarazel 1y ago
Afaict nothing in this benchmark will actually use AIO in 18. As of 18 there is aio reads for seq scans, bitmap scans, vacuum, and a few other utility commands. But the queries being run should normally be planned as index range scans. We&#
45.
by anarazel 1y ago
On the 48 core system, building linux peaks at about 48GB/s; LLVM peaks at something like 25GB/s. The system has well over 450GB/s of memory bandwidth.
46.
by anarazel 1y ago
> Nowhere in my comment have I used Linux kernel as an example. It's not a great example neither since it's mostly trivial to compile in comparison to the projects I had experience with. It's true across a wide range of pr
47.
by anarazel 1y ago
That workstation has 2x10 cores / 20 threads. I also executed the test on a newer workstation with 2x24 cores with similar results, but I thought the older workstation is more interesting, as the older workstation has a much worse mem
48.
by anarazel 1y ago
This is just wildly wrong. On an older 2 socket workstation, with relatively poor memory bandwidth, I ran a linux kernel compile. perf stat --topdown --td-level 2 indicates that memory bandwidth is not a bottleneck. Fetch latency ,
49.
by anarazel 1y ago
>> Currently the best PG driver[1] depends on a single guy. > Definitely a problem, but funding good Postgres/MongoDB/SQLite should be handled by AWS, Microsoft, Google, and other orgs that sell database services. A good
50.
by anarazel 1y ago
Easier said than done in this case. Actually effective crosschecks preventing this issue from occurring would entail rather massive I/O and CPU amplification in common operations.
51.
by anarazel 1y ago
A few questions: - Are you using pg_repack? I'm fairly sure its logic has some holes - last time I checked its bug tracker listed potential for data corruption that could cause issues like this. - Have you done OS upgrades? Did affecte
52.
by anarazel 1y ago
Without parallelism, each commit will be at least one fdatasync (or fsync, O_SYNC/O_DSYNC write, depending on configuration). With parallelism, concurrent transaction might be flushed together, reducing the total number of fsyncs.
53.
by anarazel 1y ago
> It also seemed to be calling fsync much more than once per transaction. If it's called many more times than once per transaction the likely reason is that wal_buffers is sized small. Whenever generated WAL exceeds wal_buffers, pos
54.
by anarazel 1y ago
This has absolutely nothing to do with the problem at hand.
55.
by anarazel 1y ago
> Postgres codebase is a shining example of good engineering. They walk a very fine line between many drawbacks. Code is battle tested (and surprisingly readable). Not many people (or groups) can do much better - and if they think they c
56.
by anarazel 1y ago
> The fundamental problem is that a synchronous, lock-based approach is being used in an asynchronous, event-driven context (logical replication). This isn't hit in ongoing replication, this is when creating the resources for a new
57.
by anarazel 1y ago
A connection definitely has overhead in PG, but "5000 concurrent connections will already start to deadlock your Postgres server" is bogus. People completely routinely run with more connections. Check the throughput graphs from t
58.
by anarazel 1y ago
The usleep here isn't one that was intended to be taken frequently (and it's taken in a loop, so a too short sleep is ok). As it was introduced, it was intended to address a very short race that would have been very expensive to a
59.
by anarazel 1y ago
That depends on what you're hosting. Good luck if it's e.g. a web interface for a bunch of git repositories with a long history. You can't cache effectively because there's too many pages and generating each page isn&#x2
60.
by anarazel 1y ago
He's still idling in a bunch of irc channels...

More ›