Rendered at 12:27:36 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
sgarland 1 days ago [-]
> 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).
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.
Ha, I was told to avoid blobs/texts columns like the plague, I guess that explains why
hilariously 20 hours ago [-]
Pretty much every db storage worth any salt has some sort of off row storage for lob/blob crap.
wood_spirit 1 days ago [-]
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.
marcbpaul 21 hours ago [-]
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.
pak9rabid 19 hours ago [-]
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.
marcbpaul 15 hours ago [-]
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.
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...
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.
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.
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.
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.