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

Рет қаралды 26,621

Күн бұрын

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

Пікірлер: 25

@greyshopleskin2315 14 күн бұрын

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

@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 2 ай бұрын

17:19 First time hearing this concept, Thank you

@thienquan9040 8 ай бұрын

Thanks for share ! very nice tricks

@phatminh2003 5 ай бұрын

Thanks for share!

@SimarMannSingh 10 ай бұрын

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

@nmatheus 8 ай бұрын

sehr interessanter Vortrag :)

@jules4642 10 ай бұрын

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

@OzoneGrif 10 ай бұрын

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

@enrico1713 4 ай бұрын

Excellent

@rory_o 7 ай бұрын

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 6 ай бұрын

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!

@keenoogodlike 10 ай бұрын

Great technique

@shoaibahmedkhan9676 2 күн бұрын

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.

@fayazvar3948 8 ай бұрын

Thanks

@Robert-G 9 ай бұрын

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 9 ай бұрын

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 9 ай бұрын

@@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 10 ай бұрын

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 2 ай бұрын

Gold dust

@elzabethtatcher9570 10 ай бұрын

Not very useful, but very interesting.

@RU-qv3jl 8 ай бұрын

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 8 ай бұрын

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

@hansschonig2472 5 ай бұрын

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