Andres Freund (Tech)
@AndresFreundTec
Followers
9,251
Following
105
Media
44
Statuses
2,425
FWD: @AndresFreundTec @mastodon .social Postgres developer, working at Microsoft. For politics: @AndresFreundPol
San Francisco, CA
Joined August 2017
Don't wanna be here?
Send us removal request.
Explore trending content on Musk Viewer
Drake
• 1529449 Tweets
Kendrick
• 1383386 Tweets
Meet the Grahams
• 271055 Tweets
#بدر_بن_عبدالمحسن
• 229734 Tweets
Family Matters
• 202752 Tweets
みどりの日
• 166632 Tweets
LA GLACE WITH GEMINI FOURTH
• 150413 Tweets
結束バンド
• 68903 Tweets
Óscar Puente
• 68894 Tweets
スペイン村
• 39029 Tweets
Bournemouth
• 36628 Tweets
#ライブ・エール
• 36221 Tweets
Bad Apple
• 26934 Tweets
الامير الشاعر
• 24315 Tweets
الفردوس الاعلي
• 18758 Tweets
رياض الجنه
• 18629 Tweets
#TUBEFIRST
• 13812 Tweets
غاب نور البدر
• 12037 Tweets
@binitamshah
FWIW, I didn't actually start looking due to the 500ms - I started looking when I saw failing ssh logins (by the usual automated attempts trying random user/password combinations) using a substantial amount of CPU. Only after that I noticed the slower logins.
68
281
2K
@binitamshah
(I'd probably not have noticed my own logins being slow due to ControlMaster hiding the connection startup overhead much of the time)
3
4
262
Postgres now has pluggable table storage. Phew, this took longer than planned.
Brought to you in collaboration with Haribabu Kommi, Alvaro Herrera, Alexander Korotkov, David Rowley, Dimitri Golgov and others!
8
100
216
Aaand it's bed time.
7
41
167
My aio/io_uring branch of postgres is now checkpointing 98GiB of dirty buffers at 5325MiB/s.
Two NVMe SSDs striped together, otherwise PCIe on my intel box is the limit.
18.4s of writing, 2.2s sorting the 12'567'371 buffers.
7
26
166
@riskybusiness
Absurdly enough, I was listening to the episode on a cooking break while writing the xz issue up. Couldn't make it up.
2
8
116
Wohoo! Just pushed JIT compliation of expressions (WHERE clauses, projections, aggregates) to
@postgresql
.
JITed tuple deforming, inlining, docs are still pending.
9
50
103
Progress: ~3GB/s of WAL, maxing out drive speed. Unsurprisingly a (somewhat artificial) bulk workload, not OLTP.
io_uring + O_DIRECT + aio patchset + rewrite of xlog.c write & wait logic...
2
12
99
Significant VACUUM and WAL replay performance improvements in PG 14.
Implemented and explained by my colleague David Rowley (who I think smartly is not on twitter).
5
25
95
Woohooo. Worked on this for a long while. I think this graph, of the first set of improvements, says enough:
4
22
91
I am a bit concerned by all the focus on small-ish projects with overwhelmed maintainers. There indeed are a lot of problems in that area.
2
9
89
Wrote about measuring the memory overhead of a postgres connection, and how the obvious ways of doing so are misleading.
And how, with the right config, it's less than 2MiB.
(Yes, finally got around to set up a blog, only 10-15 years too late)
2
27
81
Getting closer to a reviewable Postgres AIO patch series. Lots squashing, splitting and cleaning up...
Main patch still needs a lot of splitting and cleaning up:
56 files changed, 9813 insertions(+), 558 deletions(-)
3
13
81
Just pushed perf support for LLVM! Only took me nearly two years. Yay!
As background, I needed this to develop the LLVM based JIT support in PostgreSQL.
(Some issues with mcjit remain)
1
20
79
Just gave a short talk about using perf and bpftrace to investigate postgres performance problems and how bpftrace can also be used for monitoring!
Slides, with bad graphics, are at:
Next up
#CitusCon
: Analyzing Postgres performance problems using perf and eBPF with
@AndresFreundTec
3/
1
0
0
4
16
77
I gave a talk at about the path to using AIO in postgres at
@pgconfnyc
yesterday. Here are my slides:
Happening now at
#pgconfnyc
@pgconfnyc
my teammate
@AndresFreundTec
is on stage giving a talk about the path to asynchronous IO in
#PostgreSQL
. And yes there are performance charts (including some recovery prefetching charts from
@MengTangmu
!)
1
1
17
1
6
63
But I am certain that lots of experienced OSS devs can think of a few large and crucial projects where they fairly easily could have hidden something small in a larger change. Without a lot of prior contributions to the project.
5
3
63
Parallel Hash Joins support has landed in PostgreSQL!
Just merged
@MengTangmu
's patch, after a lot of preparatory work had already been merged.
Very cool stuff.
2
39
59
In my upcoming "Pluggable Table Storage in PostgreSQL" talk - 3PM at
@pgcon
Ottawa - I'll explain what is possible in PG12, what the limitations are, and what we might do about them.
Slides:
0
17
58
Just merged Melanie Plageman's patch to add pg_stat_io into postgres.
View:
Infrastructure:
2
6
58
The often repeated recommendation to not exceed 8GB/25% of RAM for shared buffers in postgres is wrong.
For pgbench scale 1500, on laptop with fast SSD and 32GB of RAM, parallelism of 16.
s_b of 1GB, 8GB, 16GB, using huge pages.
r/o: 115k, 100k, 185k
r/w: 16300, 15100, 21500
6
17
57
This introduces important infrastructure for use of AIO/DIO in postgres. Users that have been converted to that will "automatically" benefit from AIO/DIO when that's introduced.
PostgreSQL 17 dev: Provide vectored variant of ReadBuffer().
Anthor:
@MengTangmu
and
@AndresFreundTec
1
3
25
2
9
59
About to talk (*) about my efforts to add asynchronous IO support to PostgreSQL. The, pre-recorded, talk will be streamed. Followed by a bit of live Q&A.
1
9
57
"Zedstore - compressed in-core columnar storage" - second (after a toy one earlier today) pluggable access method for postgres posted to the list. That's some crazy and amazing stuff.
3
29
53
The joy of being an open source developer / maintainer
7
4
54
Slides for my
@postgresopen
talk "PostgreSQL's IO subsystem: Problems, Workarounds, Solutions".
0
25
54
postgres AIO + io_uring + high latency cloud storage:
Concurrent WAL flushes are a small benefit for a pgbench rw/ workload - WAL pages constantly are only partially filled.
But if I add padding of partially filled pages: > 2x throughput at *better* avg latency.
5
7
53
Wee, first of a two part blog series about postgres connection scalability is out! In this one I am analyzing what the limiting factors are. The next one will be about some actual improvements (in PG 14).
Hot off the press on the Azure Postgres Blog, a deep dive from
@AndresFreundTec
on analyzing the limits of Connection Scalability in
#PostgreSQL
🐘💥
1
20
41
4
14
51
@julianor
TBH, that referred to trying to understand what the backdoor was doing. I.e. that I didn't go all that deep in understanding the code in the backdoor. I did have to read some of the liblzma, sshd and glibc code to understand whether what I was seeing was "OK".
2
0
47
Slides for my "Pluggable Storage in PostgreSQL" talk:
1
26
45
Slides (somewhat repaired) for my
@fosdempgday
talk Asynchronous IO for PostgreSQL
(and probably also Direct IO)
are at
2
6
43
I wonder when this "databases are nearly always bottlenecked on IO" perception finally is going to die. I think it's been false for > 50% of instances for at least 15 years.
And it's just plainly wrong when we can have small-ish servers with >16 internal NVMe drives.
9
11
42
Adopting a new version of the C standard postgres today arrives, checks notes, in the last millennium...
1
17
39
Historically pgcon in Ottawa was the postgres conference most oriented towards postgres internals developers. Next year the name, format and location is evolving. 2024 will be in Vancouver.
6
7
38
If your r/w workload is bigger than shared_buffers, seriously consider enabling backend_flush_after. Both throughput *and* jitter are considerably better with it enabled.
3
17
38
Postgres 14, welcome!
My alternative release name: "PG 14 - I'm tired" ;)
3
3
37
I wish CS papers would include the date of publication prominently in the header.
2
1
36
@julianor
Without debug symbols I certainly wouldn't even have been able to understand that something odd was going on.
3
1
34
To think that I thought I occasionally did large refactorings...
2.2k patches, 25,288 files changed, 178,024 insertions(+), 74,720 deletions(-).
1
5
34
JITed tuple deforming, function inlining, EXPLAIN support, docs are now committed!
Wohoo! Just pushed JIT compliation of expressions (WHERE clauses, projections, aggregates) to
@postgresql
.
JITed tuple deforming, inlining, docs are still pending.
9
50
103
4
17
33
Some news about me (and more):
Azure HDInsight and Azure Database for PostgreSQL news
0
3
13
0
5
32
s/recursive make considered harmful/recursive make not considered/
Finally landed the initial commit for building postgres with meson!
5
9
31
A post about the new / expanded infrastructure in Postgres 14 to prevent collation changes from causing database corruption (specifically index corruption):
1
14
31
@avdi
What just doesn't compute for me is that RMS hasn't done squat except block progress in projects for *decades*. And yet him loosing his perch is going to end the world as we know it? Give me a break.
1
1
29
Wanted: An in-memory block device that has realistic but perfectly repeatable performance characteristics.
It'd be nice to be able to execute IO bound workloads in a somewhat comparable manner without needing the same hardware, with the same "aging" characteristics.
5
1
31
Makes me want to play around with using this to optimize postgres' expression evaluation interpreter...
And to finally rewrite our COPY parser, which desperately needs a speedup.
5
3
30
This is some fucked up stuff. Being an open source maintainer is hard enough already, we really don't need universities playacting being an APT.
6
2
30
The second part of my two part postgres connection scalability blog "series" is now out. This time about the actual code changes and their results.
Follow
@AndresFreundTec
down into the rabbit hole of improving
#PostgreSQL
connection scalability, by addressing snapshot scalability in Postgres 14. Hot off the press in his newest blog post:
1
16
38
0
13
30
Wohoo, PostgreSQL 16 Beta 1 Released!
Please go forth and test.
1
7
28
@matthew_d_green
I don't think the payload space is restricted? The certificate can contain all kinds of additional content, e.g. for the list of principals the certificate covers.
1
0
29
Random useful developer discovery for postgres development: git's diff.orderFile option. Allows to change the order in which different files appear in diffs.
3
7
28
Interesting.
Doing the necessary rituals of sacrifice to get postgres started with the text segment mapped as huge pages yields nearly 10% speedup in a highly concurrent workload.
2
4
26
Isn't it obvious that pgstat_init(), pgstat_initialize(), pgstat_initstats(), pgstat_beinit(), pgstat_bestart() all have distinct purposes?
3
1
26
"Redesigning the executor (async, JIT, memory efficiency)" - my prototype and rough plan for how Postgres's should evolve:
2
14
26
"io_uring: add support for IORING_OP_GETDENTS64"
This would be quite awesome. Seems like there's still some more work on the interface needed, but...
1
2
24
Just finished giving the talk below. Video will be available at some point, for now there's the slides at
Today's Vaccination Database Speaker: Andres Freund (
@AndresFreundTec
) on the past, present, and future of
@PostgreSQL
of disk ops. Zoom talk is open to public at 4:30pm ET. YouTube video will be available afterwards:
This is the last talk of the semester.
1
24
87
2
5
24
Well, that was a bit dramatic.
While walking to give the presentation, my laptop shut down. Refused to boot again with something like "No TPM found".
Luckily booted after disabling the TPM in bios. Certainly doesn't make for a relaxed start into presenting.
Sorry for that!
Coffee break is over, and now
@AndresFreundTec
is talking about async I/O for
@PostgreSQL
.
#FOSDEMPGDay
#FOSDEM
#FOSDEM2020
1
5
23
1
0
23
Spent yesterday afternoon/evening/night and this morning debugging a weird lock contention issue in postgres. Turned out to be essentially quadratic behaviour in a normally rare path.
Not too bad a win for ~10h.
1
2
23
PostgreSQL 10 RC1 will be wrapped Monday, released Thursday:
0
23
22
To be clear, there's still just the venerable heap storage built into postgres. But within some constraints, new types of table storage can now be added without modifying core postgres.
Off to do some mop-up work now ;)
1
5
21
The way that some pragmatic short-term implementation choices end up being defended by many as the long term "right approach™" is one of the least attractive things about open source.
2
2
22
giving_up_on_catching_up_on_pgsql_hackers_thus_mark_all_read++
1
1
22
Yay, PostgreSQL 11 beta 1 has released. Go forth and test.
0
3
21
should-have-done-something-else-but-couldn't-resist hack of the weekend:
Remove use of single user mode during initdb, by doing everything as part of bootstrap.
Decent speedup.
Getting closer to removal of single user mode is even more exciting though.
1
0
21
Hackety hack hack from a few weeks back, which I feel free to tweet about now that PG 14 reached feature freeze.
Building PG with meson, and using its test runner instead of our current recursive make. So much less ugly...
1
2
21
@axboe
Wow, initial results evaluating benefits of completion callback based io_uring buffered reads are extremely good. To the point of wondering whether I broke something somewhere.
Gotta compare more similar kernels, rather than 5.7rc7 and linux-block/async-buffered.6.
1
3
21
Wohooo.
Expect a nice improvement by this for quite a few workload.
I committed bottom-up index deletion today, which is slated for Postgres 14. See for a detailed description. In short: it makes index bloat/VACUUM issues much less likely to get out of hand. Updaters are now *required* to clean up indexes after themselves.
4
25
86
0
1
20
GAAAHAH!"#$#?#$%%"
The latest (a different one than earlier) io_uring vs sync IO slowdown I was trying to fix turns out to just be power management / frequency scaling effects.
When. Will. I. Learn.
1
0
20
Our WIP AIO branch just had a (still pretty crappy) IOCP backend pass for the first time!
Thanks
@MengTangmu
and
@davidrowley_pg
!
2
1
20
I don't understand how all linux kernel related lists have these inconsistent and poor archives.
For postgres you can download archives, properly respond to threads, the archives are kept up2date, and there's only a single archive for all lists.
2
8
20
Yeha!
That's amazing news.
I have spent the last N days furiously porting
@AndresFreundTec
's amazing Linux/io_uring based AIO subsystem for PostgreSQL to run on many other operating systems using the blursed POSIX AIO API. And just now for the first time CHECK WORLD PASSES!
2
9
77
1
1
18
backpatching a fix to a ~5 year old release branch, seeing no conflict for the first time in a while when doing so, and being like: Wait, what did I do wrong, no conflicts?
1
1
19
Just before every single talk: If I had worked a day or two on my talk, instead of hacking on what I am talking about, I would have a much better talk.
A bit longer before every single talk: Hackety hack hack.
Afterwards: Tired, gotta do something else.
8
1
19
The cgroup memory controller can have quite the overhead, even when no limits are present. This is a simple pgbench (-c 32 -j 32) workload.
/me reboots
2
1
19
@steveklabnik
A trick for when that causes too many conflicts:
Each time the formatting changes, make a fixup commit, and immediately revert it. Then, in a second rebase, combine the fixups with the prior commits, and the reverts with the following commit.
1
1
19
Performance numbers for the upcoming intel hardware bug workaround (page-table-isolation):
2
20
19
Which *** idiot added the header in this chaotic order. *checks commit log*. Oh, a very very nice person (it me).
0
1
17
I've looked at more pending postgres patches aimed at v11 than I can bare in the last 24h.
It's day 1 of 31 of the final v11 merge window.
Tired.
6
1
18
Ah. The joy of being annoyed about a bug that I found while writing a feature patch, being pissed about that having been introduced,
just to discover - while looking through the git log - that it was myself.
(typo fix edition)
1
0
17
Here are the slides for my "State of Postgres JIT - 2018 Edition" talk. I'll probably still update them a bit further... :)
Just how much JITing infrastructure has been integrated into
#Postgres
?
@AndresFreundTec
examined just in time (JIT) compilations and has some benchmarks in his
@PGCon
talk The State of Postgres JIT – 2018 Edition. June 1, 2pm
#Database
0
0
1
0
7
17
Thanks to
@magnushagander
for implementing the metadata to make postgres mailing list posts "unfurl"able...
1
1
17
Things I spend way too much time debating in my head
#37373
: Should commit message subjects end with a trailing period or not.
8
0
17
@matthew_d_green
I think the entire "certblob" is passed to sshkey_verify() (and thus RSA_public_decrypt()), which does seem to contain the list of principals etc. So the backdoor looks to have access to that. What precisely it does, idk.
0
0
17
You know you've worked too much on postgres if you can type pademelon, guaibasaurus, culicidae et al correctly from memory.
0
2
16
@magnushagander
@lukaseder
@FedericoRazzol1
@thinkx
@MarkusWinand
We've long claimed:
- our planner is so good we don't need hints and that when it's not you should tell us and we fix it (we don't)
- our replication story is good
- that nobody wants/should want automated failover
- our IO stack is a good idea
- not using threading is awesome
…
4
4
17
@pwramsey
@fuzzychef
@johnmark
@mjasay
@Dee_Marketing
@nehanarkhede
@markcallaghan
@matkeep
@confluentinc
@RedisLabs
Very much so, but reality of Amazon's contributions so far have never come even close to the promises, and the promises fall far short of being a sustainable community participant. Especially given how their existence makes other business models for employing PG devs less viable.
1
0
15
@Adrien_nayrat
@PostgreSQL
I've been committing several changes towards that end in the last few months, several of them significantly bigger than this :)
See e.g. and , but even the WITH OIDS removal was partially motivated by pluggable storage ;)
1
3
16
Yay.
@petereisentraut
just committed "Add UNIQUE null treatment option". Sometimes the existing behaviour is really cumbersome.
0
5
16
I am terrible at hitting the spot between "too short for anybody but myself to understand" and "everyone will give up before the intro is over".
4
0
15
And WITH OIDS was no more.
Bad life choice completed, for now ("18853 77764 727869"):
0
0
1
5
6
15
1) work from easy chair
2) put pillow under laptop
3) compile llvm
4) relax with a nicely warmed pillow under your head
3
1
15
Going to talk about JITing in
@postgresql
at
#SCALE16x
tomorrow!
Should anybody else arrive via Burbank airport tomorrow morning, happy to share a ride.
0
3
15