Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How Postgres stores data on disk – this one's a page turner

drew.silcock.dev

Post reply on HN
Print view View original

51–60 of 98 posts

Re: How Postgres stores data on disk – this one's a page turner

#51

Post by ikiris »

magicalhippo wrote: post #32
speed_spread wrote: post #18

Earlier quoted context omitted.

If physical integrity is already provided by the backing filesystem, such as ZFS, wouldn't --data-checksums be redundant?

If data is served from ARC, it's primary cache, ZFS does not perform a checksum check before handing it to you, as the data was checked when it got read into the ARC. If you use ECC you're quite safe, but ECC can't detect multi-bit errors, just single and double bit errors. So if you care much about your integrity, you might want Postgres to do its checksum check as well.

If you somehow have 3+ bit errors coming out of ram on an ECC board, you have much bigger problems than trying to verify your postgres data via checksum.

Re: How Postgres stores data on disk – this one's a page turner

#52

Post by hinkley »

MattJ100 wrote: post #34
indoordin0saur wrote: post #29

This URL is blocked by my company's network because of a certain substring in the URL lol

A classic case of the Scunthorpe problem: https://en.wikipedia.org/wiki/Scunthorpe_problem In this case the substring is part of the author's name. Such names are not at all uncommon.

Alistair Cockburn is one of the signatories of the Agile Manifesto.

You may have heard of him more recently with the Hexagonal Architecture approach.

Re: How Postgres stores data on disk – this one's a page turner

#53

Post by fullstop »

Izkata wrote: post #50
fullstop wrote: post #39

Earlier quoted context omitted.

I was on a mailing list once where messages were blocked because of m sexchange being in the headers.

Any relation to ExpertsExchange?

Ah, this was before ExpertsExchange existed. I think that it was Corel that had the mail filter, and it was related to Corel Linux.

https://en.wikipedia.org/wiki/Corel_Linux

Re: How Postgres stores data on disk – this one's a page turner

#54

Post by hinkley »

forinti wrote: post #40
indoordin0saur wrote: post #29

This URL is blocked by my company's network because of a certain substring in the URL lol

I once worked for a company that blocked Cuban sites because of .cu (which is the Portuguese word for the end of your digestive system), but did not block porn sites (or so I was told ;-).

That seems like the sort of thing you check on your last day as you’re going out the door.

“The rumors are true!”

(Although less amusing, you could also just ask the IT guys and gals)

Re: How Postgres stores data on disk – this one's a page turner

#55

Post by hinkley »

Izkata wrote: post #50
fullstop wrote: post #39

Earlier quoted context omitted.

I was on a mailing list once where messages were blocked because of m sexchange being in the headers.

Any relation to ExpertsExchange?

You mean ExpertSexChange? If you’re gonna capitalize it, do it right.

Re: How Postgres stores data on disk – this one's a page turner

#56

Post by hinkley »

dfox wrote: post #13

> This process of retrieving the expected database state from the WAL is called logical decoding and Postgres stores files related to this process in here. While logical decoding is about WAL, it is not related to the recovery process. Logical decoding is a mechanism to convert the WAL entries back into the high-level operations that caused the WAL entries, for example for replication or audit.

But in a power loss the WAL is also read to restore and transactions that were in flight, right?

Re: How Postgres stores data on disk – this one's a page turner

#57

Post by dfox »

> Then, there’s a random byte like 0x25 or 0x07 followed by the column data – the rest of the columns are string types so they’re all stored in UTF-8. If you know what these inter-column bytes mean, leave a comment below! I can’t figure it out.

Next paragraph mentions TOAST and this byte is related to that. The low order bits (on little endian platforms) determine whether the value is stored inline (00, first 4 bytes are total length), is stored in TOAST table (11) or is shorter than 127 bytes (01 for even length, 10 for odd length, the total length is first byte >> 1). So for 0x25 you get 01, so length is 0x25 >> 1 = 18, which is that byte followed by "Equatorial Guinea".

Edit: the reason why endianness matters is that the same representation is also used in memory and the whole first word is interpreted as one length value. The toast tag bits have to be in first byte, which is most easily done as two highest order bits of that word on big endian. That means that it is placed in the two highest bits of the byte.

Re: How Postgres stores data on disk – this one's a page turner

#58

Post by dfox »

drewsberry wrote: post #20
dfox wrote: post #13

> This process of retrieving the expected database state from the WAL is called logical decoding and Postgres stores files related to this process in here. While logical decoding is about WAL, it is not related to the recovery process. Logical decoding is a mechanism to convert the WAL entries back into the high-level operations that caused the WAL entries, for example for replication or audit.

Very good point, I've rephrased this.

You still refer to logical decoding as part of recovery in the last section. The main point is that WAL by itself is designed only for data recovery and only contains information about what is going to be written where in terms of essentially raw disk accesses (notably, the writes do not have to be page aligned).

Logical decoding (which needs wal_level=logical which extends the WAL format with additional metadata) is about parsing the WAL for other purposes than performing the recovery (or physical replication, which is essentially the same thing as recovery, but performed on another instance of the same cluster). The name "logical decoding" is certainly intended to emphasize that there are other uses for that than logical replication, but these are not that different from logical replication on this level (get a stream of changed tuples in tables).

Re: How Postgres stores data on disk – this one's a page turner

#59

Post by drewsberry »

indoordin0saur wrote: post #29

This URL is blocked by my company's network because of a certain substring in the URL lol

Well if it isn't my arch-nemesis – my legally designated name. Maybe I should've gone for something with just my first name like drewsexpertblog.dev

Re: How Postgres stores data on disk – this one's a page turner

#60

Post by drewsberry »

MattJ100 wrote: post #34
indoordin0saur wrote: post #29

This URL is blocked by my company's network because of a certain substring in the URL lol

A classic case of the Scunthorpe problem: https://en.wikipedia.org/wiki/Scunthorpe_problem In this case the substring is part of the author's name. Such names are not at all uncommon.

There are dozens of us!
Post reply on HN