Not guaranteed. The DYNAMIC (default) and COMPRESSED row formats [0] can shift larger values of variable-length columns in off-page records. In the example given, this wouldn’t occur, but it isn’t a guarantee per se. This is roughly equivalent to Postgres’ TOAST, though Postgres’ default threshold for TOAST is 1/4 of a page (2 KiB with its default 8 KiB page size), whereas InnoDB is 1/2 of a page (8 KiB with its default 16 KiB page size).
Very interesting, the more I learn about InnoDB, the more I'm impressed. I guess I haven't encountered a column value that's bigger than 8KiB in an InnoDB database yet.
I like the view into the personal journey learning new things and sharing it.
Suggested next steps would be trying out DuckDB instead. My guess is it would smoke spark etc at this scale. Always good to learn different corners of the data landscape.
I swapped out some data analytics processing that was originally implemented with Python & Postgres to DuckDB and have been immensely pleased with the outcome. Stuff that would take 6+ hours to do with Python/Postgres now takes me around half an hour to complete.
It's a great addition to an existing Postgres database as it has very tight integration with it's native Postgres extension, which allows you to do things like select data from your Postgres database, process it locally with DuckDB, then insert back into your Postgres database.
To be honest Postgres and DuckDB are on my lists to really get my hands dirty with.
One of the more interesting talks I read about was how someone used MySQL's bin log to mirror a lakehouse different versions of data and used DuckDB as the compute layer of a lakehouse. I think the talk was held in South America, so I didn't get to go.
Postgres is just useful for pretty much everything it seems like lol. Knowing they work together so well makes for a side project to learn both.
> InnoDB’s Three Guarantees
Rows store all columns together
Not guaranteed. The DYNAMIC (default) and COMPRESSED row formats [0] can shift larger values of variable-length columns in off-page records. In the example given, this wouldn’t occur, but it isn’t a guarantee per se. This is roughly equivalent to Postgres’ TOAST, though Postgres’ default threshold for TOAST is 1/4 of a page (2 KiB with its default 8 KiB page size), whereas InnoDB is 1/2 of a page (8 KiB with its default 16 KiB page size).
0: https://dev.mysql.com/doc/refman/9.7/en/innodb-row-format.ht...
Very interesting, the more I learn about InnoDB, the more I'm impressed. I guess I haven't encountered a column value that's bigger than 8KiB in an InnoDB database yet.
Here's a few reasons to be dissapointed in it: https://jepsen.io/analyses/mysql-8.0.34
Ha, I was told to avoid blobs/texts columns like the plague, I guess that explains why
Pretty much every db storage worth any salt has some sort of off row storage for lob/blob crap.
I like the view into the personal journey learning new things and sharing it.
Suggested next steps would be trying out DuckDB instead. My guess is it would smoke spark etc at this scale. Always good to learn different corners of the data landscape.
DuckDB's come up a lot since I started my little journey to be honest.
Everything I've read says you're absolutely right about the performance boost lol. It very well may be the next piece I check out, thank you.
DuckDB is amazing.
I swapped out some data analytics processing that was originally implemented with Python & Postgres to DuckDB and have been immensely pleased with the outcome. Stuff that would take 6+ hours to do with Python/Postgres now takes me around half an hour to complete.
It's a great addition to an existing Postgres database as it has very tight integration with it's native Postgres extension, which allows you to do things like select data from your Postgres database, process it locally with DuckDB, then insert back into your Postgres database.
I'd highly recommend giving it a try.
To be honest Postgres and DuckDB are on my lists to really get my hands dirty with.
One of the more interesting talks I read about was how someone used MySQL's bin log to mirror a lakehouse different versions of data and used DuckDB as the compute layer of a lakehouse. I think the talk was held in South America, so I didn't get to go.
Postgres is just useful for pretty much everything it seems like lol. Knowing they work together so well makes for a side project to learn both.