7 ms·
Explaining the Postgres iceberg
- azophy_2 3y agofound another youtube video that explain the seme meme https://youtu.be/JZRWkfXNQOk https://youtu.be/JZRWkfXNQOk
- hernantz 3y agoLaw of Leaky Abstractions states: All non-trivial abstractions, to some degree, are leaky.
- upon_drumhead 3y agoI struggle to understand why the Halloween Problem is listed as a Postgres issue. The concept that a where condition would run until all rows are condition false is just categorically false for Postgresql. https://en.wikipedia.org/wiki/Halloween_Problem https://en.wikipedia.org/wiki/Halloween_Problem
- smitty1e 3y agoThe Famous Article points out that the meme is generally applicable to any RDBMS, but was being explored in a PostGreSql context.
- upon_drumhead 3y agoYes, and they call it out as a concern, which it isn't with Postgresql. > This could even cause an infinite loop in some cases where updates continually place the updated record ahead of the scan performing the update operation. This just is not true for Postgresql and they don't specify anywhere that they switched from talking about Postgresql to a different database. So either they believe it's an concern with Postgresql or they aren't being clear when they are talking about different databases.
- MrPowerGamerBR 3y agoThey didn't call it out as a concern, they said at the beginning of the section "Halloween Problem is a database error that a database system developer needs to be aware of." To be honest, just like you I also thought that it was related to PostgreSQL or other popular SQL databases somehow, until I re-read the section multiple times.
- smitty1e 3y agoI was pointing to the article as a whole, not just the Halloween Problem section, and regret if I read too quickly.
- pmcjones 3y agoNo, it was being explored in the context of System R at IBM San Jose Research -- see this link in the Wikipedia article: http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System...
- deleted 3y ago[deleted]
- gsk22 3y agoAre "topic icebergs" really a meme? Sure, they're an internet phenomenon, but that doesn't automatically qualify as a meme.
- benatkin 3y agoYes, but an obscure meme. A maymay, if you will.
- hannasanarion 3y agoWhat do you think the word "meme" means, and what features does something need to qualify as one?
- dang 3y agoI've inlined 'meme' to 'iceberg' in the title above so we can hopefully not worry about this.
- Tao3300 3y agoI'd call it an image macro, but the Venn Diagram of memes and image macros is a 99.9% overlap, so we might as well not draw a distinction.
- slotrans 3y agoHeh, this is pretty great. Is it good or bad that I know almost all of these? Not sure "SERIAL is non-transactional" belongs at the bottom, I thought that was common knowledge... but that might be from years doing Oracle where sequences are more explicit.
- wrs 3y agoMany databases including PostgreSQL have a second reason “serial” sequences may have missing numbers. It’s too inefficient to write to the disk every time you get a new value from the sequence, so sequence transactions use up a batch of consecutive values, not just one. The set of values in the batch is then maintained in memory. If (when) the database crashes, the leftovers can’t be restored to the sequence (since there’s no finer-grained log of them). So on average the sequence will permanently skip half of the batch size on each crash.
- ta988 3y agoRegular reminder, icebergs don't look like that. https://joshdata.me/iceberger.html https://joshdata.me/iceberger.html
- nomilk 3y agoThis is a pretty awesome way of summarising a topic. Perhaps an advanced version could offer an ELI5 explanation on hoverover. I'm in the first or second layer of the postgres version of the meme, yet would gladly spend hours exploring layers I don't yet know about (simply reading the names of the ideas in each layer was incredibly interesting tbh).
- phamilton 3y agoWe got hit with a good bit of the postgres iceberg this week. VACUUM by default runs with the TRUNCATE option. This will, if possible and valuable, truncate the heap and return those pages to the operating system. This operation takes an AccessExclusive lock, which blocks reads and writes. It is written to be very deferential and only runs if nothing else is trying to take a competing lock. AccessExclusive is a unique lock in that it is replicated to standbys. When the writer holds the lock, reads on the standbys will be blocked. However, the writer doesn't know about them, so its conservative approach of only acquiring the lock if there's no contention is completely thwarted. Finally, if vacuum truncate is interrupted by lock contention it must repeat a scan of the heap to ensure the blocks are still empty before proceeding with the truncation. All of these details combined meant our writer got stuck in a "grab lock, scan, be interrupted, release lock, grab lock, restart scan, be interrupted, release lock... etc" loop. The replication of this lock to the reader seemed to have batched together, so the readers never got to run in between the loop iterations. The vacuum never actually succeeded because it kept re-scanning. We had to intervene manually to disable autovacuum and cancel the in-progress jobs. We've hit plenty of weird postgres issues, but this was a new one for us. Edit: of note is the somewhat rare lack of clarity in the postgres docs. "VACUUM (without FULL)" is documented as only requiring ShareUpdateExclusive, which is why autovacuum is considered safe to run. Turns out that's not true.
- winrid 3y agoI love PG, but things like this make me appreciate Mongo's compaction and replication which can run interleaved with reads and writes.
- alawrence 3y ago> so its conservative approach of only acquiring the lock if there's no contention is completely thwarted. How is contention on the standbys communicated back to the writer? I'm confused about how the writer is interrupted mid-cycle if only the readers are experiencing lock contention.
- phamilton 3y agoThe writer also has traffic that can attempt to acquire the locks. Reads and writes (though our highest volume read traffic is reader only). Contention from the standbys do not get communicated to the writer.
- ksarw 3y agoThis was super neat, really appreciate all the examples :)
- the_imp 3y agoI am left wondering how far down one must go to find row level security.