rabindranath-tiwari.com.npPostgres Multi Version Concurrency Control - MVCCIn 1986 database researcher named Michael Stonebraker was working on a problem that has plauged databases since their inception. how do you let many people read and write to the data simultaneously wi2d ago·6 min read
rabindranath-tiwari.com.npPostgreSQL TOAST Storage ModelsThe main problem TOAST solves is fundamental : postgres pages are of 8kb, and tuple must fit within that page. so what happens if you try to insert 1MB of text field ? Without toast you would get an error: "Row too big". With toast PostgreSQL handles...3d ago·4 min read
rabindranath-tiwari.com.npTuple LayoutWhen you insert a new row into a table, postgreSQL creates a tuple, a contiguous chunk of memory bytes that contains both system metadata and your actual data. A tuple starts with a 23 byte header. Every single tuple in every table has this same head...3d ago·5 min read
rabindranath-tiwari.com.npPostgreSQL Page Structure - (Slotted Pages)From the previous blog we knew that tables are made up of 8KB pages, lets crack open a page and see what's really inside it. This is where the things get interesting because jargons like MVCC, tuple storage, visiblity rules, freespace is dependent on...3d ago·6 min read
rabindranath-tiwari.com.npPostgres OID VS RelfilenodeWhen you create a table, PostgreSQL assigns it an OID (Object Identifier). This is just a logical identifier for the table. it is stored in the system catalog pg_class and remains constant for the lifetime of the table. But under the hood in physical...3d ago·2 min read
