Discover the game-changing integration of DuckDB with PostgreSQL, enhancing analytics capabilities. Learn how pg_duckdb addresses challenges in transaction and analytics management. Explore the intricacies of schema migration and the importance of careful upgrades. Hear insights about community contributions and how newcomers can participate in this dynamic project. Exciting discussions about optimizing query performance and the future roadmap of DuckDB and Postgres await!
01:00:19
forum Ask episode
web_stories AI Snips
view_agenda Chapters
auto_awesome Transcript
info_circle Episode notes
volunteer_activism ADVICE
Isolate Analytics Workloads Carefully
Limit analytics queries by connection limits and resource controls in Postgres and DuckDB settings.
Consider separating transactional and analytical workloads on different machines or use MotherDuck for isolation.
volunteer_activism ADVICE
Use Explain to Debug Queries
Use EXPLAIN and EXPLAIN ANALYZE commands to understand DuckDB query plans and performance.
Monitor timing and row counts to diagnose slow analytics queries.
insights INSIGHT
Current Write and Delete Support
Writing data to analytics in pg_duckdb currently supports copy to external files or inserts/deletes via MotherDuck only.
Deleting analytics data requires external tools or using MotherDuck's SQL support.
Get the Snipd Podcast app to discover more snips from this episode
The GeekNarrator memberships can be joined here: https://www.youtube.com/channel/UC_mGuY4g0mggeUGM6V1osdA/join
Membership will get you access to member only videos, exclusive notes and monthly 1:1 with me.
Here you can see all the member only videos: https://www.youtube.com/playlist?list=UUMO_mGuY4g0mggeUGM6V1osdA
------------------------------------------------------------------------------------------------------------------------------------------------------------------
About this episode:
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hey folks - In this episode we have Jelte with us, who is the main contributor to the pg_duckdb project, which is a postgres extension to add the #duckdb power to our beloved #postgresql.
We will try to understand how it works? Why is it needed and what's the future of pg_duckdb?
If you love #Postgres or #Duckdb or just understanding #database internals then this episode will give you pretty solid insights into Postgres query processing, Duckdb analytics, Postgres extension ecosystem and so on.
Basics:
pg_duckdb is a Postgres extension that embeds DuckDB's columnar-vectorized analytics engine and features into Postgres. We recommend using pg_duckdb to build high performance analytics and data-intensive applications.
Chapters:
00:00 Introduction to PG-DuckDB
03:40 Understanding the Integration of DuckDB with Postgres
06:23 Architecture of PG-DuckDB: Query Processing Explained
10:02 Configuring DuckDB for Analytics Queries
15:37 Managing Workloads: Transactional vs. Analytical
21:02 Observability and Debugging in DuckDB
25:58 Data Deletion and GDPR Compliance
30:46 Schema Management and Migration Challenges
33:14 Managing Schema Changes in Databases
35:21 Upgrading Database Extensions
36:33 Enhancing Data Reading Methods
38:33 Future Features and Improvements
45:54 Use Cases for PGDuckDB
50:03 Challenges in Building the Extension
55:25 Getting Involved with PGDuckDB
Important links:
The duckdb discord server, which has a pg_duckdb channel inside it: https://discord.duckdb.org/
repo: https://github.com/duckdb/pg_duckdb
good-first-issue issues: https://github.com/duckdb/pg_duckdb/issues?q=sort%3Aupdated-desc+is%3Aissue+is%3Aopen+label%3A%22good+first+issue%22
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Like building real stuff?
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Try out CodeCrafters and build amazing real world systems like Redis, Kafka, Sqlite. Use the link below to signup and get 40% off on paid subscription.
https://app.codecrafters.io/join?via=geeknarrator
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Link to other playlists. LIKE, SHARE and SUBSCRIBE
------------------------------------------------------------------------------------------------------------------------------------------------------------------
If you like this episode, please hit the like button and share it with your network.
Also please subscribe if you haven't yet.
Database internals series: https://youtu.be/yV_Zp0Mi3xs
Popular playlists:
Realtime streaming systems: https://www.youtube.com/playlist?list=PLL7QpTxsA4se-mAKKoVOs3VcaP71X_LA-
Software Engineering: https://www.youtube.com/playlist?list=PLL7QpTxsA4sf6By03bot5BhKoMgxDUU17
Distributed systems and databases: https://www.youtube.com/playlist?list=PLL7QpTxsA4sfLDUnjBJXJGFhhz94jDd_d
Modern databases: https://www.youtube.com/playlist?list=PLL7QpTxsA4scSeZAsCUXijtnfW5ARlrsN
Stay Curios! Keep Learning!
#sql #postgres #databasesystems
The GeekNarrator