I’ve been working on databases for some time, and you win an upvote.
Sure, I can find some nits and have various opinionated color to things (learned indexes, for instance, are interesting but ultimately useless in practice under write load), but generally speaking you’ve done a great job simply highlighting (and, thankfully, not overexplaining or dismissing) many of the hard problems of databases. Double props for getting down to fsync() which, my god, don’t deal in filesystems, kids, because every part of the stack lies.
Will be linking people to this when they’re learning as a teachable moment :)
Double props for getting down to fsync() which, my god, don’t deal in filesystems, kids, because every part of the stack lies.
I’m laughing with tears in my eyes because I’ve been there… and at the time (circa linux kernel 2.4.x) it seemed that the more complex your storage provisioning (ex. SAN) the less you could trust fsync.
Holy moley, does the Halloween problem still exist? That is, will update employee set salary = salary + (salary / 10) where salary < 25000 keep updating employees until all salaries are at least 25,000?
Hi. Author here.
When I was writing that section it looked obvious to me that pg doesn’t have this issue, but now that I am re-reading that part it sure sounds misleading.
I will add a note that pg doesn’t have this issue when I’m home.
This took a while to read, but I actually did. I’ve been working in computers for almost twenty years now and it isn’t every day I learn something anymore… but these last couple days as i read through this, I actually did.
or as some people say, how “PostgreSQL used fsync incorrectly for 20 years”)
I don’t think there is a correct usage of fsync. AFAIK, Linux still marks unflushed dirty pages as clean. Crashing the application isn’t enough, you need to purge the page cache. Maybe going as far as rebooting the entire OS
It’s platform specific. I believe illumos systems have a correct implementation of fsync, for example. We inherited it from Solaris all those years ago. I expect at least FreeBSD does as well.
Postgres follows the SQL standard (with extensions), and the SQL standards splits the type composition (new structures out of base types) and the constraints (restrictions on existing types).
So you can define reusable non-nullable types with domains.
NULLs are equal in DISTINCT but unequal in UNIQUE
pg15 allows specifying the behaviour of nulls in unique indexes and constraints, via the NULLS [NOT] DISTINCT clause (distinct being the default).
So you can define reusable non-nullable types with domains.
Well…
A more subtle example is
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
The empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.
I’ve been working on databases for some time, and you win an upvote.
Sure, I can find some nits and have various opinionated color to things (learned indexes, for instance, are interesting but ultimately useless in practice under write load), but generally speaking you’ve done a great job simply highlighting (and, thankfully, not overexplaining or dismissing) many of the hard problems of databases. Double props for getting down to
fsync()which, my god, don’t deal in filesystems, kids, because every part of the stack lies.Will be linking people to this when they’re learning as a teachable moment :)
I’m laughing with tears in my eyes because I’ve been there… and at the time (circa linux kernel 2.4.x) it seemed that the more complex your storage provisioning (ex. SAN) the less you could trust fsync.
I think that’s still essentially valid, and storages didn’t get simpler over time either, as far as i’m aware
Holy moley, does the Halloween problem still exist? That is, will
update employee set salary = salary + (salary / 10) where salary < 25000keep updating employees until all salaries are at least 25,000?It doesn’t still exist in a properly functioning database, but it’s a great illustration of why you need some kind of transaction concept.
Why is it in a PostgreSQL database article then? Which parts of the article are relevant to PostgreSQL?
Hi. Author here. When I was writing that section it looked obvious to me that pg doesn’t have this issue, but now that I am re-reading that part it sure sounds misleading. I will add a note that pg doesn’t have this issue when I’m home.
Wait that was a real bug? It would just update the same row over and over?
I didn’t know Postgres had a meme. And I’m now ashamed to know I’m in the sky zone!
No shame in being one of today’s lucky 10,000.
For what it’s worth, it only came across my desk for the first time Friday too.
I saw a post about it on Lemmy, but the image didn’t federate, so I moved on. Definitely glad to see it in this better form here!
This took a while to read, but I actually did. I’ve been working in computers for almost twenty years now and it isn’t every day I learn something anymore… but these last couple days as i read through this, I actually did.
Nice work.
I don’t think there is a correct usage of fsync. AFAIK, Linux still marks unflushed dirty pages as clean. Crashing the application isn’t enough, you need to purge the page cache. Maybe going as far as rebooting the entire OS
It’s platform specific. I believe illumos systems have a correct implementation of fsync, for example. We inherited it from Solaris all those years ago. I expect at least FreeBSD does as well.
That is not quite true.
Postgres follows the SQL standard (with extensions), and the SQL standards splits the type composition (new structures out of base types) and the constraints (restrictions on existing types).
So you can define reusable non-nullable types with domains.
pg15 allows specifying the behaviour of nulls in unique indexes and constraints, via the
NULLS [NOT] DISTINCTclause (distinct being the default).Well…
Ultimately, isn’t all teaching the conveyance of memes? :-)