Introduction to PostgreSQL physical storage | Hacker News Reader

Introduction to PostgreSQL physical storage

rachbelaid.com

256 pointscraigkerstiens10 comments

This is really awesome. I've spent a ton of time optimizing high throughput PG instances. Every time one of my optimizations failed it was from an incomplete understanding of the physical storage layer and/or MVCC.

These 2 docs really help with a deeper understanding if you want to learn more: http://momjian.us/main/writings/pgsql/internalpics.pdf http://momjian.us/main/writings/pgsql/mvcc.pdf

>First, TOAST stands for The Oversized-Attribute Storage Technique, probably the best acronym in the history of Computer Science.

Actually, that price IMHO goes to GIN (Generalized Inverted Index) which I actually believed to be a coincidence until the people behind Postgres' GIN index announced their new VODKA index format

Um, how about TWAIN?

TWAIN isn't actually an acronym - it comes from "and never the twain shall meet", replacing east and west in that prose with scanners, printers and other digital image sources/sinks, representing the difficulty people had getting examples of such technology to cooperate with each other.

TIL - thank you.

I took a database class in college which had a decent amount of theory behind it and found pretty interesting the way Postgres (and I presume other databases) handle multiple transactions simultaneously, including the isolation level of each transaction (read uncommited, read commited, repeatable read, and serializable).

Coming from pretty heavy background in MSSQL internals, this article is really great.

I keep telling myself that I need to invest time into learning PostreSQL as a (far less expensive) alternative to MSSQL for some use cases.

> Coming from pretty heavy background in MSSQL internals, this article is really great.

Do you know of any good resources for the undocumented function fn_dblog? I'm looking to understand the structure of RowLog Contents and Log Record in different Operations/Contexts to reconstruct DDL/DML.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engin... is a good example but is really just an introduction.

Does anybody have a view on whether the 1GB limit on TOAST data could ever be raised, thereby making the large object facility further obsolete?

It'd not be particularly hard to raise it, although it'd require some code restructuring/duplication (for on-disk backward compat). I doubt it's really worth it anytime soon, such large datums imo have biggers problems than the length limits.

Reply on news.ycombinator.com