This is digital content from the Postgres Conference (PostgresConf.org) series. The largest Postgres Conference series in the world. We are non-profit, volunteer driven and a U.S. 501c3. We focus on a mission of People, Postgres, Data which is an inclusive community inviting all walks of life from all cultures. We strive to help not only PostgreSQL but all of the Postgres ecosystem succeed with Postgres and related technologies.
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.
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.
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)
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!
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?
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.
