PostgreSQL performance tips you have never seen before | Citus Con: An Event for Postgres 2023

[@RenzoCotta]

comming from a MSSQL background I got a job administering Potsgres databases ... I read a couple of books from Hans Jurgen Schoning that helped me a lot in my daily work ... thank you very much sir ... I enjoyed your book as much as I enjoyed this presentation

[@JohnMatthew1]

interesting, thank you for the video.

[@thienquan9040]

Thanks for share ! very nice tricks

[@greyshopleskin2315]

The hashtext function IS INTERNAL. You are not supposed to rely on it. It can change implementation and break stuff. It did happen in the past

[@phatminh2003]

Thanks for share!

[@nandocamila]

Thank you again Hans.. very simple items that normally DBA/E doesn't take in account.

[@cesarcofg]

Awesome very nice explanation

[@nmatheus]

sehr interessanter Vortrag :)

[@enrico1713]

Excellent

[@rory_o]

The initial premise of “there isn’t a go fast setting because that would be default” is already entirely wrong. Postgres defaults are massively conservative even two decades ago. Even showed as much by FDW default is just 50 rows at a time! Modern systems could easily transfer millions of rows in a batch.

[@ketaminefairy]

haven't watched the video, but this alone makes me very skeptical. I like citus, but is this con really worth the time? Assuming you've watched it... Thanks!

[@corniliusrupert5917]

Thank you for this video.

[@vineethv2786]

Loved it.

[@jules4642]

Thanks, I didn't know you could reduce table size with the order of column regarding alignement. Great vieo

[@blank001]

17:19 First time hearing this concept, Thank you

[@SimarMannSingh]

Seems very interesting. Would have to give these tricks a try for sure.... Thanks :)

[@Robert-G]

15:15 that was a waking nightmare to watch. that’s a horrible oversight on the dbms part. Not just for perf, but also wrt consistency. if the function wasn’t pure, then their way of calling it multiple times, even when every sane person would assume that it’s only called once, could create weird problems that would be hard to track down. I’m still watching, but thx A LOT for this one already!

[@dankubb]

This has bitten me before, but I think it's just a side effect of two features not interacting well together. For me the best way to think about how .* works in PostgreSQL, is to imagine it naively expanding the expression so that it is duplicated once per column. It doesn't attempt to do anything smart, it's just expands the statement *prior* to execution, like how a macro works in other languages. If the function is stable or immutable, it might be possible for postgresql to use the cached result for each column (I don't know if it applies this optimization, I'm just saying it could). If it was volatile though, it *must* execute it once per statement because it could return a different answer each time.

[@Robert-G]

@@dankubb the classic problem of leaky abstractions. if .* really is just naïvely expanding the whole expression, then that’s IMO a bug. good to know about it, to workaround. but it’s a bug nonetheless…

[@OzoneGrif]

Nice advanced tricks, I'll remind them for my database audits.

[@keenoogodlike]

Great technique

[@fayazvar3948]

Thanks

[@shoaibahmedkhan9676]

Are we sure default values doesn't take storage space for each row. ChatGPT says. Hahah. The default value of a column in PostgreSQL does take up storage space for each row where the default is used. When a default value is specified for a column, PostgreSQL doesn't store the default value itself for each row; rather, it stores the value as if it were explicitly set by an insert command. This means that even if a column has a default value, every row that uses this default will have storage allocated for that value, just as if it had been specified explicitly in the INSERT statement. The actual storage mechanism does not optimize away the storage for default values; they are treated like regular values.

[@jaikaransaini6122]

Can anyone please tell me if on video 6:25 "storing data efficiently" points has any mention on official postgresql documentation i tried it and it worked I want to learn more about it through documentation.

[@BradleyKieser]

Gold dust

[@elzabethtatcher9570]

Not very useful, but very interesting.

[@RU-qv3jl]

Well I’ve used similar techniques in SQL Server so I wouldn’t say it’s not very useful. Everything has its place. You may not need it and yet other people might. As someone who uses PG only for smaller project because work wants to keep using SQL Server, I was quite happy to learn about the FDW bucket sizes. Some of the other things I have no use for in PG but hashing text is something I have used a fair bit in SQL Server to make multi-column equality index lookups a lot faster.

[@thanatosor]

Now ppl need to seek for secret way of improving performance on a piece of software that someone else made. How desperate

[@hansschonig2472]

well, there is always a corner case in any system of this size. 99% work nicely by default - but any system will have things that can be improved over time