Postgres System Columns Explained (ctid, xmin,xmax)

In this video I explain the difference between ctid,xmin and xmax columns in postgres with repeatable read and read committed.
0:00 Intro
2:00 ctid tuple id
7:00 xmin (minimum transaction id) with read committed
8:00 xmin with read committed
13:00 xmin with repeatable read
19:00 xmax (maximum transaction id)
Fundamentals of Backend Engineering Design patterns udemy course (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
/ 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 RUclips
/ @hnasr
Buy me a coffee if you liked this
www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️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/6...
Stay Awesome,
Hussein

[@hnasr]

Fundamentals of Database Engineering course database.husseinnasser.com

[@diwakarparajuli12]

Beautifully explained .. one of the best videos I have seen in a long time.. Thanks man!

[@sagepages9504]

Another great video. Would love to see a backend dive into Auth0's private cloud platform!

[@JohnZakaria]

I have always been reading about isolation levels, tombstones but never have I ever seen them in action.
Thank you for Video!

[@lakhveerchahal]

Fascinating stuff! I also recently discovered these columns and pg_stat_user_tables and now very happy to see your video on the same! I guess we were reading the same stuff 😄
Some relevant stuff around this to read would be:
- Fillfactor (how it affects ctid)
- HOT updates (because postgres always does INSERT operation because of MVCC reasons, it also needs to update the reference pointers in indexes which are pointing to dead tuples, there is definitely cost associated to it but postgres has a smart way to reduce it using HOT chaining)
Thanks for the great video Hussein!!

[@lakhveerchahal]

From all this, the takeaway for me was to avoid UPDATE heavy workflows as much as possible.

[@lakhveerchahal]

Amazing video

[@krishnaMurari48]

excellent video

[@blank001]

Your content is amazing, I always learn something new ever time I open your videos,
if possible can you provide a documentation link or some kind of written article link, I am thinking of making notes of your content, and having a quick access which I can search through.

[@user-vp1qk6fz5b]

Great stuff. I think this is too advanced for me, can you please provide real life scenarios where I can benefit from this?

[@lautarojayat7914]

Excelent

[@priyaghosh5406]

nice video

[@darpanmalhotra2]

Waiting for another video on Repeatable Read v/s Serializable.

[@Adi-yi6qq]

Hi Hussein! If ctid returns the page & slot number of the row on disk then how come running ctid on multiple tables (in postgres) gives same set of (page_id, slot_id) in output? I tried for 3-4 tables and I see a lot of common ctid among them.

[@ayuobmousa6589]

GOLD

[@streetfashiontv9149]

Does anyone know how to access a postgre SQL database from another computer (windows) other than local Host (loop back 127.10 etc). I have tried modifying the config file but still no connection. I want to be able to log onto another windows computer and open a database application that I packed into a jar using launch4j?

[@PiroCoder]

[@wexwexexort]

Holly shit.