Video

Harris Jeffrey Clark Sharon Garcia Paul

Newbie GROUP BY question: is there an easy formula to use GROUP BY to simulate Javascript's .groupBy() or Google Sheet's table Group By? That is, I want to take one column as a "category" and list all the rows for each value of the category column, suppressing the category in the tabular output. I've gotten close I think with something like SELECT cat, UNNEST( ARRAY_AGG( STRUCT( supc AS supc, descr AS descr ) ) ) FROM mytable GROUP BY (cat) ORDER BY (cat, descr); Am I on the right track? Am I hunting for the wrong Y for the X? The real task I'm trying to do is use psql as a scripting language to read in a CSV file, do a few edits I'd normally do manually in Excel or Google Sheets, and write out a new CSV file with some columns rearranged or suppressed, ideally in a specific format with an extra column holding "C", for a new category row or "P" for a product row.

this dude is so funny

thank you for the amazing series

It is a very useful site i always recommend this site. Thank you!

great video.... thanks a lot

i do love some optimisations :)

Good presentation

Such a delightful talk. Thanks for this detailed information. It was a delight to go through the video. The audio quality was bit difficult to understand sometimes but overall it was very informative.

Good job

There isn't the slides of 2021 year in that page: wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

this guy is a hero!

25:10 thought that rust has now a default! macro... but its only defaulting the SQL and not the rust code... Nevertheless a great demo of the power of pgx (now pgrx)

My first time to say first.

Sadly the screen is not totally invisible

Tough crowd. I liked the joke in the beginning.

This! This video should be an example of how all technical videos should be. Presenter was clear, precise, easy to understand, and had great command on the topic he was presenting. Bravo! Standing ovation!

can this be used with AWRD RDS Postgresql/Aurora Postgresql?

Just get bitten by some of the issues Alvaro shared about mongodb. Have a terrible time when the app was open to the public. I knew PG from way back but I fall for the Mongodb merchandise and wanted to have an app build only with NoSQL by doing a full project. Sadly the transactions killed the app performance and catastrophic eventual consistency issues, I reached to mongodb for consultancy, but a bill of almost 5000 USD for 3 days of 4 hours each session was the tipping point, now working on migrating to old and trusty relational databases.

🎯 Key Takeaways for quick navigation: 00:00 🎤 *Introduction to the webinar and speaker's background.* - Introduction of the webinar topic and speaker. - Tong Zhuang's role as co-founder and chief scientist at ScaleFlux. 01:03 🧑‍🏫 *Explanation of Postgres data compression without performance loss.* - Postgres's inability to compress table data. - Reliance on underlying storage hierarchy for data compression. 02:09 💾 *File system compression issues and solutions.* - The inefficiencies of file system compression methods. - Potential solutions to improve compression ratio and performance. 03:12 ⚖️ *Trade-offs between compression ratio and performance.* - The fundamental trade-off in compression technologies. - The impact of these trade-offs on database performance. 04:03 🤔 *Limitations of popular file systems in handling compression.* - Inherent constraints in file system-level compression. - The popularity of journaling file systems and their limitations. 05:11 🗃️ *Alternatives for supporting data compression.* - Other methods to implement data compression in databases. - The trade-offs involved in these alternative methods. 06:05 🛠️ *Introduction of hardware-based compression solutions.* - The role of hardware in improving data compression. - Benefits of hardware-based compression for storage and performance. 07:08 🌐 *Transition to heterogeneous computing and computational storage.* - Shift in computing infrastructure. - Introduction of computational storage drives for offloading tasks. 08:15 📊 *Benefits of computational storage drives in database applications.* - The impact of computational storage drives on database performance. - How these drives complement the CPU in a heterogeneous environment. 09:22 📈 *Comparison of computational storage drives with traditional methods.* - Advantages of computational storage drives over traditional storage. - Improved compression ratios and performance. 10:03 🚀 *Potential improvements in Postgres with computational storage drives.* - The impact of computational storage drives on Postgres performance. - Suggestions for integrating these drives into Postgres systems. 11:50 📝 *Technical specifics of computational storage drive architecture.* - The architecture and functionality of computational storage drives. - How these drives differ from traditional storage solutions. 13:09 📊 *Performance benchmarks and comparisons.* - Benchmarks showing the effectiveness of computational storage drives. - Comparisons with mainstream compression libraries. 14:03 💻 *Real-world testing scenarios and results.* - Testing scenarios for computational storage drives. - Impact on I/O workload and performance. 15:36 📋 *Case study: Postgres performance with computational storage.* - Performance of Postgres using computational storage drives. - Storage cost reduction and performance improvement details. 17:26 🤔 *Analysis of different workloads and their impact.* - Examination of various workloads on computational storage drives. - Performance comparisons under different conditions. 18:57 🔄 *Further advantages and usage scenarios of computational storage.* - Additional benefits of using computational storage in databases. - Scenarios where computational storage can enhance database performance. 20:15 📐 *Adjusting Postgres parameters for optimized performance.* - The effect of modifying Postgres parameters. - Balancing database performance with storage costs. 21:47 📈 *Benchmark results with different Postgres configurations.* - Results from benchmark testing on different configurations. - Performance improvements and storage space implications. 23:17 💼 *Potential for Postgres to leverage computational storage more effectively.* - How Postgres can benefit further from computational storage. - Suggestions for deeper integration with computational storage technologies. 24:21 🌍 *Broader implications and future directions in storage technology.* - The growing trend of hardware-based transparent compression. - The potential impact on the database community and cloud environments. 25:54 🏭 *Industry trends and commercialization of transparent compression.* - The adoption of transparent compression in industry products. - The role of cloud vendors in implementing hardware compression. 27:12 🚀 *Future integration ideas for Postgres and computational storage.* - Innovative ideas for integrating Postgres with new storage technologies. - Potential benefits and areas for exploration. 29:12 💡 *Proposals for enhancing Postgres performance and reliability.* - Suggestions for reducing I/O traffic and enhancing database reliability. - Leveraging transparent compression for more efficient database operations. 31:25 📊 *Conclusion and summary of key points.* - Overview of the benefits of computational storage for Postgres. - Invitation for collaboration and future development in this field. 33:42 ❓ *Q&A session and audience engagement.* - Addressing audience questions about distributed databases and Postgres. - Discussing opportunities for proof of concept and third-party validation. Made with HARPA AI

Having trouble finding "bitner heap scan" on the internet, I'm somewhat new to postgres and wondering more about this, or is this a typo in the chapter's section and possibly meant "Bitmap Heap Scan"?

Hai,I got good understanding through this video.I created one function declare a volatile, parallel unsafe(the function contain only select query).In sometimes the function resulted nothing,after some attempts show a data..This was the cause because of using parallel unsafe??

I created a Function default set as parallel unsafe (the function contain only select queries).For sometimes the function return nothing on that after some attempts the function return data.This was the cause because of parallel unsafe?

Thanks!, it was very helpful, however, the audio was a bit low!

😇Thanks for posting

Can we find other 5 sessions online also?

Do you mean the system can extract csv file for 1 Billion Row per second?

128 GB of ram is a bit too much, isn't it? The usual sizes even with Postgres 15 are 16 and 32 GBs of RAM. Or were you talking about SSD?

this is pure gold

Summary of this presentation is "mongodb users, doing csv stuff without knowing it". I bet doing fopen & fs lock on csv is more acid than mongodbing.

This is great, thank you!

Good to see the comparison

I made it to an hour:20 the UM AH UM AAHH makes this un-listenable

Thanks for the content! I'm here because of lack of documentation for PostgreSQL 9.4 + BDR 1.00.05 regarding node_state = 'c'.

where can i get bar table in this video ? can i get this?

show don't tell applies here. pages after pages of powerpoint presentation is awful. should have types the commands and shown what he means.

Try to recompile your video in advance with someone who knows not only PG internal, but presentation skills. To "polish" it and make it more dynamic, compact and handy to learn.

Hi! Could you share presentation. It's diffult to read. Thanks!

Congrats 👏

PostgreSQL has a very good (debatable?) community (@35:00). First comment :D

Best video out there explaining pgbouncer. It would be great if questions were bit more audible.

torodb/stampede is last updated 3yrs ago. am i on correct repo?

That's an amazing time saver! Good work!

Wow.

Also good to shart presentation with steps and commands you use

Sound quality not good, hard to listen