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

81–90 of 98 posts

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

#81

Post by ggm »

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.

"in case of disk loss, we wrote the things we were going to write to disk, to the WAL. which is also on disk"

I never entirely got it. Either your WAL is on more reliable media, or duplicated. If its just "easier" to write the WAL and faster to read off properly indexed state, ok, thats a local optimisation.

If your WAL is on the same filesystem behind a vendor specific RAID controller, you're still stuffed, if that RAID card dies.

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

#82

Post by rpmisms »

drewsberry wrote: post #59
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

Have you been saving that one for a rainy day?

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

#85

Post by drewsberry »

jayde2767 wrote: post #71

Just curious if anyone else encountered this same error from the initial "docker run" command: docker: Error response from daemon: create ./pg-data: "./pg-data" includes invalid characters for a local volume name, only "[a-zA-Z0-9][a-zA-Z0-9_.-]" are allowed. If you intended to pass a host directory, use absolute path.

It's probably complaining about the relative path, try replacing `-v ./pg-data:/var/lib/postgresql/data` with `-v "$PWD/pg-data:/var/lib/postgresql/data"`

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

#86

Post by Doxin »

ggm wrote: post #81
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.

"in case of disk loss, we wrote the things we were going to write to disk, to the WAL. which is also on disk" I never entirely got it. Either your WAL is on more reliable media, or duplicated. If its just "easier" to write the WAL and faster to read off properly indexed state, ok, thats a local optimisation. If your WAL is on the same filesystem behind a vendor specific RAID controller, you're still stuffed, if that…

the WAL is not for when your disk dies. It's for when the server crashes. First writing what you're going to do to the WAL before doing it makes it possible to recover from a crash at any point. If it crashes during writing to the WAL? Ignore the half-written WAL records and keep trucking. If it crashes during writing to the data file? Use the WAL to rerun the writes that didn't complete.

It doesn't guarantee you don't lose data written during the crash, but it does guarantee you can get the database back into a usable state.

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

#87

Post by silvestrov »

loeg wrote: post #80
silvestrov wrote: post #75

Earlier quoted context omitted.

So when using these intrinsics an Intel Core i7 can do 30 GB/s but the performance check linked above (by isosphere ) says only 300 MB/s, i.e. 1% Something is amiss here. If a CPU can do 30 GB/s then a CRC check should not have any real performance impact.

I don't know where you're getting 300 MB/s from.

Page 5 of https://www-staging.commandprompt.com/uploads/images/Command... says "This system can checksum data at about 300 MB/s per core."

It lacks page numbers. Page 5 is first page with gray box at the top of the page.

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

#88

Post by mharig »

ozim wrote: post #78
mharig wrote: post #24

> Can’t we just store some data on disk and read / write from it when we need to? (Spoiler: no.) I disagree. SQLite does a good job in uniting the 2 worlds: complex SQL queries with excellent data consistency and simple file(s). Although SQLite is for sure not the one size fits all solution.

But SQLite doesn’t do concurrency on writing you lock the file. While other db engines deal with row/table locks concurrent connections etc.

[dead]

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

#89

Post by mharig »

berkes wrote: post #66
mharig wrote: post #24

> Can’t we just store some data on disk and read / write from it when we need to? (Spoiler: no.) I disagree. SQLite does a good job in uniting the 2 worlds: complex SQL queries with excellent data consistency and simple file(s). Although SQLite is for sure not the one size fits all solution.

> SQLite is for sure not the one size fits all solution Nor is Postgres. PG is surprisingly versatile. E.g. with some extensions can be used as key-value storage (hashtable), document database, time-series db and so on. And it works quite well. Beyond "good enough" for many use cases. Added benefit, aside from having to run only one db-server, is that you can mix it: part relational, part document, etc. But the PG ve…

[dead]
Post reply on HN