Syscalls always have an overhead that you pay per call. Even if the hardware is perfectly capable of doing lots of tiny reads, the syscall overhead will eat your lunch.
I dont think the term is overused. It's just what describes the concept the best. In this case, its used in very different parts of the system and it makes sense both places.
Nice one, appreciate it. Thanks a lot. The question is if the planner chooses a sequential scan doesn't it mean that all pages need to be read?The only exception I see is LIMIT clause, otherwise all pages need to be read by sequential scan.
Very interesting approach. Looks like there is also pwritev in linux kernel. I am wondering if any database take advantage of this for writing to the disk.
Hussein, I’ve followed you for around 3-4 years. Your content is amazing, but man! Ever since you decided to buy this mic, it’s become hard to listen to you. I don’t know why, but you don’t need to talk like you’re recording Backend Engineering ASMR! You used to be much, much more energetic with a clearer (and not so soft, low, and slow) voice! We’re not at a funeral, man, so don’t need t o be worry about speaking louder. Thanks for everything :)
Exactly. The only reason the performance was acceptable before was because both the OS and the drive itself were doing read ahead for the last few decades.
I am weighing a multimodal DB (ArcadeDB) with PostgreSQL & PgVector. Do you think this will this overtly effect this scenario? Either way I need to find a Redis replacement ;( (unless zip-stripping KVs out of PG is now a viable option) ;)
thank you ! I'm just trying to assume/imagine here. Also during high operations query (ORDERBY, GROUP BY, SORT) etc, only work_mem comes into picture right? Let's say my data is in shared_buffer and work_mem is not enough and lead to psql doing too many disk writes(*syscalls) which is worse than this shared_buffer part?
@@Thecommet no because one who hacks the system can read the decrypted files. Whereas one would additionally need the encryption key used by pg-3rd-party which is never stored or in an env-var locally.
@@kartik4792 I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're loged in with instead of taking the one specified after the `-u` flag. I can't recall the version I've used, things could've got better. This is what Debian showed: 16+262.pgdg120+1 but 17 is already here.
@@kartik4792 I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're logged in with instead of taking the one specified after the `-u` flag. I can't recall the version I've used, things could've got better. This is what Debian showed: 16+262.pgdg120+1 but 17 is already here.
I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're logged in with instead of taking the one specified after the `-u` flag. I can't recall the version I've used, things could've got better. This is what Debian showed: 16+262.pgdg120+1 but 17 is already here.
It's a misconception rooted in very old times. I work with both mysql and psql and I can honestly say that psql is much faster. With mysql I often come across performance issues, like dumb optimizer (not using indexes when it should), poor performance of views and complex queries, ineffective optimization of CTEs, etc. I had even cases where certain fast queries would have been impossible in mysql but could be done in psq due to more index types suited for various scenarios. Unless someone is using only very simple queries and structure, psql wins in performance hands down.
Fundamentals of Database Engineering course databases.win
I love when Hussein learns something because then we all learn something.
This is the reason why I continue being a member :)
How to become one?
why? I also got the video
@@weirdworld3734 we are getting this content after some time after released its members first content
PostgreSQL just keeps getting better, and it's set to become the top choice for any new application that needs a relational database.
Thank you, this episode answered opened questions i had.
specially the mapping of database pages to filesystem blocks
This is mostly useful for HAMR and spinning disk configs. Most NVMEs have super large i/o queues and write to the FTL in their own schedule.
Syscalls always have an overhead that you pay per call. Even if the hardware is perfectly capable of doing lots of tiny reads, the syscall overhead will eat your lunch.
But sequential I/O is also faster on SSDs, they’re just not as terrible as HDDs for small reads
Arab gulf region brother, Good work, happy to see your content. keep it up
It makes so much sense that I'm curious why aren't other databases also doing this?
They are, only postgress doesn't, until now.
I dont think the term is overused. It's just what describes the concept the best. In this case, its used in very different parts of the system and it makes sense both places.
Good video.
Nit: You might want to look into a light diffuser if you want to reduce the distracting glare in your glasses.
Thank you so much, Hussein
Please make a video about BRIN indexes (Block Range Index)
Thanks, man
Fun watch! On a similar note, you should check out tiger beetle db. It’ll make a great video (or few!
I'm surprised it didn't do this already.
Thank you, great content as always.
Nice one, appreciate it. Thanks a lot. The question is if the planner chooses a sequential scan doesn't it mean that all pages need to be read?The only exception I see is LIMIT clause, otherwise all pages need to be read by sequential scan.
Very interesting approach. Looks like there is also pwritev in linux kernel. I am wondering if any database take advantage of this for writing to the disk.
Hussein, I’ve followed you for around 3-4 years. Your content is amazing, but man! Ever since you decided to buy this mic, it’s become hard to listen to you. I don’t know why, but you don’t need to talk like you’re recording Backend Engineering ASMR! You used to be much, much more energetic with a clearer (and not so soft, low, and slow) voice! We’re not at a funeral, man, so don’t need t o be worry about speaking louder. Thanks for everything :)
Congratulations! Postrges finally discovered "multiblock reads". A thing which has been done in most enterprise scale databases since the 1990s.
Please give an example or something I can lookup to learn more about this
@@sankalpmukim1052oracle multiblock read
Exactly. The only reason the performance was acceptable before was because both the OS and the drive itself were doing read ahead for the last few decades.
Thank you
I am weighing a multimodal DB (ArcadeDB) with PostgreSQL & PgVector. Do you think this will this overtly effect this scenario? Either way I need to find a Redis replacement ;( (unless zip-stripping KVs out of PG is now a viable option) ;)
thank you !
I'm just trying to assume/imagine here.
Also during high operations query (ORDERBY, GROUP BY, SORT) etc, only work_mem comes into picture right?
Let's say my data is in shared_buffer and work_mem is not enough and lead to psql doing too many disk writes(*syscalls) which is worse than this shared_buffer part?
What's your take on YugabyteDB??
why do they name vectorized?
well explained
Postgres encrypted data at rest would be great without having to get it through a third party.
Isn't this the responsibility of the filesystem? Just put the db into an encrypted partition (e.g. LUKS)
@@Thecommet no because one who hacks the system can read the decrypted files. Whereas one would additionally need the encryption key used by pg-3rd-party which is never stored or in an env-var locally.
Make video about day in the life of software engineer
yet, their CLI is still so bad.
What features or improvements do you think could make it better?
@@kartik4792 more colors and rainbows 🌈 🎨
@@kartik4792 I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're loged in with instead of taking the one specified after the `-u` flag.
I can't recall the version I've used, things could've got better.
This is what Debian showed: 16+262.pgdg120+1
but 17 is already here.
@@kartik4792 I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're logged in with instead of taking the one specified after the `-u` flag.
I can't recall the version I've used, things could've got better.
This is what Debian showed: 16+262.pgdg120+1
but 17 is already here.
I just want it to be like MySQL in the ease of use. PG forces me to specify a DB in order to enter their prompt while there is no DB when you first install it, and it takes the user that you're logged in with instead of taking the one specified after the `-u` flag.
I can't recall the version I've used, things could've got better.
This is what Debian showed: 16+262.pgdg120+1
but 17 is already here.
As a mysql fan i always thought psql was slow and clunky. Color me surprised when they make it faster. Wow. Then again it's more about the features.
It's a misconception rooted in very old times. I work with both mysql and psql and I can honestly say that psql is much faster. With mysql I often come across performance issues, like dumb optimizer (not using indexes when it should), poor performance of views and complex queries, ineffective optimization of CTEs, etc. I had even cases where certain fast queries would have been impossible in mysql but could be done in psq due to more index types suited for various scenarios. Unless someone is using only very simple queries and structure, psql wins in performance hands down.
I like read code , how about you ???😅
To shorten this video in one sentence: Postgresql will have Hyper Threading/Prefetching
Thanks!
Very helpfull, as always
thank you very much!