Postgres just got even faster
Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
https://databases.win
Fundamentals of Operating Systems Course
https://os.husseinnasser.com
This new PostgreSQL 17 feature is game changer.
You see, postgres like most databases work with fixed size pages. Pretty much everything is in this format, indexes, table data, etc. Those pages are 8K in size, each page will have the rows, or index tuples and a fixed header. The pages are just bytes in files and they are read and cached in the buffer pool.
To read page 0, for example, you would call read on offset 0 for 8192 bytes, To read page 1 that is another read system call from offset 8193 for 8192, page 7 is offset 57,345 for 8192 and so on.
If table is 100 pages stored a file, to do a full table scan, we would be making 100 system calls, each system call had an overhead (I talk about all of that in my OS course).
The enhancement in Postgres 17 is to combine I/Os you can specify how much IO to combine, so technically while possible you can scan that entire table in one system call doesn’t mean its always a good idea of course and Ill talk about that.
This also seems to included a vectorized I/O, with preadv system call which takes an array of offsets and lengths for random reads.
The challenge will become how to not read too much, say I’m doing a seq scan to find something, I read page 0 and found it and quit I don’t need to read any more pages. With this feature I might read 10 pages in one I/O and pull all its content, put in shared buffers only to find my result in the first page (essentially wasting disk bandwidth, memory etc)
It is going to be interesting to balance this out.
Good work Pg team! More here / combining-i-os-in-postgresql-17
0:00 Intro
1:30 Combined IO summarized
6:30 Kernel read-ahead
11:50 A Page read in Postgres
20:00 Combining page rads
24:00 Summary
Backend Troubleshooting Course
https://performance.husseinnasser.com
Fundamentals of Backend Engineering (link redirects to udemy with coupon)
https://backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
https://network.husseinnasser.com
Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
https://database.husseinnasser.com
Follow me on Medium
/ membership
Introduction to NGINX (link redirects to udemy with coupon)
https://nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
https://python.husseinnasser.com
Become a Member on YouTube
/ @hnasr
Buy me a coffee if you liked this
https://www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
https://backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
https://husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
https://partner.canva.com/c/2766475/6...
Stay Awesome,
Hussein
