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

71–80 of 98 posts

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

#71

Post by jayde2767 »

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.

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

#72

Post by forinti »

CodesInChaos wrote: post #68
forinti wrote: post #40

Earlier quoted context omitted.

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

Are you sure it wasn't because of the US embargo on Cuba? Companies outside the US often participate(d) as well, because they want to do business with the US and US companies.

The proxy replied with a message stating that the site was blocked because it was pornographic.

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

#73

Post by ikiris »

magicalhippo wrote: post #65
ikiris wrote: post #51

Earlier quoted context omitted.

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.

AFAIK RowHammer attacks can cause multiple bits to flip in a row[1], no? But sure, it's not for the vast majority of folks. [1]: https://www.vusec.net/projects/eccploit/

Rowhammer would qualify as a bigger problem, yes.

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

#75

Post by silvestrov »

loeg wrote: post #64
Joe_Cool wrote: post #46

Earlier quoted context omitted.

Interesting. I guess M1 doesn't have the 'crc32' "acceleration" that is included in SSE4.2.

M1 has CRC32 acceleration intrinsics. https://dougallj.wordpress.com/2022/05/22/faster-crc32-on-th... https://github.com/corsix/fast-crc32?tab=readme-ov-file#appl...

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.

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

#76

Post by nemothekid »

When I started my dev career, NoSQL was the rage and I remember reading about BigTable, Cassandra, Dynamo, and most importantly LSMs. They made a big deal about how the data on stored on disk was sorted. I never knew why this was a big deal but always kept it in mind, but I never bothered to understand how it was done previously.

>Something really important about tables which isn’t obvious at first is that, even though they might have sequential primary keys, tables are not ordered.

This was very surprising to read.

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

#77

Post by Izkata »

nemothekid wrote: post #76

When I started my dev career, NoSQL was the rage and I remember reading about BigTable, Cassandra, Dynamo, and most importantly LSMs. They made a big deal about how the data on stored on disk was sorted. I never knew why this was a big deal but always kept it in mind, but I never bothered to understand how it was done previously. > Something really important about tables which isn’t obvious at first is that, even tho…

I'mma pop up again with this, since it's not mentioned - there's a CLUSTER command that lets you reorder the table data to match an index. It's a one-off so you'll need to run it regularly from a crontab or something, but it's important to be aware of because postgres keeps a "correlation" statistic between the indexes and the order on disk. It affects the query planner, biasing it against random disk access and towards sequential disk access. It's one of the possible reasons postgres might not use an index that otherwise makes sense - the random disk access penalty is too high.

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

#78

Post by ozim »

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.

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

#79

Post by cedws »

nraynaud wrote: post #12

A bit of curiosity: how did Postgres choose 8k pages? shouldn’t it be the FS page size to help with atomicity?

I guess it's a tradeoff. Ideally you'd want a small enough page size such that pages are unlikely to be split across multiple LBAs, but the performance wouldn't be good. Standard filesystems don't really guarantee LBA alignment anyway.

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

#80

Post by loeg »

silvestrov wrote: post #75
loeg wrote: post #64

Earlier quoted context omitted.

M1 has CRC32 acceleration intrinsics. https://dougallj.wordpress.com/2022/05/22/faster-crc32-on-th... https://github.com/corsix/fast-crc32?tab=readme-ov-file#appl...

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.
Post reply on HN