4 ms·

Introduction to PostgreSQL physical storage

256 points by craigkerstiens 11y ago

scosman 11y ago
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/internalpics.pdf http://momjian.us/main/writings/pgsql/mvcc.pdf http://momjian.us/main/writings/pgsql/mvcc.pdf
pilif 11y ago
>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
- dexterdog 11y ago
  Um, how about TWAIN?
  - dspillett 11y ago
    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.
    - dexterdog 11y ago
      TIL - thank you.
daigoba66 11y ago
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.
- jud_white 11y ago
  > 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-engine-anatomy-of-a-record/ http://www.sqlskills.com/blogs/paul/inside-the-storage-engin... is a good example but is really just an introduction.
farresito 11y ago
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).
jl6 11y ago
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?
- anarazel 11y ago
  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.

256 points by craigkerstiens 11y ago

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/internalpics.pdf http://momjian.us/main/writings/pgsql/mvcc.pdf http://momjian.us/main/writings/pgsql/mvcc.pdf

pilif 11y ago

>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

dexterdog 11y ago
Um, how about TWAIN?
- dspillett 11y ago
  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.
  - dexterdog 11y ago
    TIL - thank you.

daigoba66 11y ago

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.

jud_white 11y ago
> 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-engine-anatomy-of-a-record/ http://www.sqlskills.com/blogs/paul/inside-the-storage-engin... is a good example but is really just an introduction.

farresito 11y ago

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).

jl6 11y ago

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?

anarazel 11y ago
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.