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

Video of a conference talk about some PostgreSQL trickery which is possible but most people are not aware of, delivered at Citus Con: An Event for Postgres 2023 by Hans-Jürgen Schönig. In PostgreSQL, a lot can be done to speed up your SQL queries. However, many of the tricks are not very well known and need some more attention. Learn how to unleash a lot of hidden performance potential in PostgreSQL.
Hans-Jürgen Schönig has been a PostgreSQL professional for 20 years and helps customers around the globe to work with PostgreSQL. He has written a dozen books on PostgreSQL.
► Video bookmarks:
⏩ 00:00 Introduction
⏩ 02:25 Connecting to Postgres
⏩ 06:25 Storing data efficiently
⏩ 10:48 Considering indexes
⏩ 14:10 Composite type trickery
⏩ 17:07 Using Foreign Data Wrappers
⏩ 19:49 Full Text Indexes
✅ Learn more:
Watch more Citus Con 2023 talks: aka.ms/cituscon-playlist
📕 Everything you need to know about Citus Con: An Event for Postgres can be found at: aka.ms/cituscon
📌 Let’s connect:  
Twitter - @CitusCon, / cituscon
#CitusCon #PostgreSQL #Performance

[@nandocamila]

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

[@JohnMatthew1]

interesting, thank you for the video.

[@blank001]

17:19 First time hearing this concept,
Thank you

[@thienquan9040]

Thanks for share ! very nice tricks

[@phatminh2003]

Thanks for share!

[@nmatheus]

sehr interessanter Vortrag :)

[@SimarMannSingh]

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

[@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!

[@OzoneGrif]

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

[@jules4642]

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

[@fayazvar3948]

Thanks

[@keenoogodlike]

Great technique

[@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…

[@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