Sequential Scans in Postgres just got faster (Postgres 17 feature) @hnasr
Sequential Scans in Postgres just got faster (Postgres 17 feature)  @hnasr
Uploaded September 2024 | Updated November 2026, 18 hours ago
Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
https://databases.win

Fundamentals of Operating Systems Course
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 medium.com/@hnasr/combining-i-os-in-postgresql-17-39fe81d2981a


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
performance.husseinnasser.com


Fundamentals of Backend Engineering (link redirects to udemy with coupon)
backend.husseinnasser.com

Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com

Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
database.husseinnasser.com

Follow me on Medium
medium.com/@hnasr/membership


Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com

Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com

Become a Member on YouTube
youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join

Buy me a coffee if you liked this
buymeacoffee.com/hnasr

Arabic Software Engineering Channel
youtube.com/channel/UChWZsjdoRvZ0T9QWZOD6UpA

🔥 Members Only Content
youtube.com/playlist?list=UUMO_ML5xP23TOWKUcc-oAE_Eg



🏭 Backend Engineering Videos in Order
backend.husseinnasser.com

💾 Database Engineering Videos
youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2

🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast

Gears and tools used on the Channel (affiliates)

🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/647168/10068


Stay Awesome,
Hussein
Sequential Scans in Postgres just got faster (Postgres 17 feature)Switching Threads is FasterEncrypted TLS Client Hello | The Backend Engineering ShowThe core of backend system designFrontend and Backends TimeoutsHow Alt-Svc switches HTTP/2 clients to use HTTP/3This is how MongoDB and MySQL are similarHow Fast is MySQL on HTTP/3?New Course - Node Internals and ArchitectureThe genius of long pollingThe Virgin Media ISP outage - What happened?Now what?
Hussein Nasser |

Sequential Scans in Postgres just got faster (Postgres 17 feature)

SHARE TO X SHARE TO REDDIT SHARE TO FACEBOOK WALLPAPER