The Real Python Podcast
Leveraging the Features of Your Database With Postgres and Python
Episode guests
Podcast summary created with Snipd AI
Quick takeaways
- Postgres offers rich data types, robust indexing options, and full-text search to enhance Python projects.
- Python developers can leverage Postgres' unique data types, JSON support, and PostGIS extension for advanced geospatial capabilities.
- Customizing the psql command-line interface and utilizing Postgres extensions can improve the development experience and performance tuning of Python projects.
Deep dives
Advantages of Using Postgres
Postgres offers numerous advantages, such as transactional DDL, rich data types, including range types for enforcing constraints and geospatial data capabilities. It also provides robust indexing options and full-text search. JSON support allows flexible data storage, and extensions offer customization options. Postgres has evolved in the last decade, becoming more user-friendly and feature-rich.
The Exploration of Data Types
Postgres offers unique data types that are advantageous for Python developers. For example, range types allow the enforcement of constraints for time intervals or numeric ranges. JSON support enables storing and querying complex nested data structures. Postgres, with the PostGIS extension, competes with proprietary databases for advanced geospatial capabilities. Python developers are encouraged to explore these data types and leverage them to enhance their applications.
Tools and Tips for Working with Postgres
The psql command-line interface is a powerful tool for interacting with Postgres. By customizing the psqlrc file, developers can enhance their experience by enabling features like timing for query execution, automatic formatting of query output, and using their preferred text editor for editing queries. Query history can also be saved, providing a convenient way to store and reuse commonly used queries. Blogs and newsletters like Postgres Weekly and Crunchy Data's blog offer additional resources for learning and staying up to date with Postgres.
Using Extensions with PostGIS
PostGIS offers a range of extensions that can be added to the database. These extensions, found in the contrib library, provide additional functionality and features. One key extension is PG-stat statements, which records and parameterizes queries that run against the database. It is immensely useful for performance tuning and debugging. Enabling extensions in PostGIS is as simple as running 'create extension'. Some managed service providers may have limitations on available extensions, while self-managed instances allow more flexibility in adding extensions.
Python and Postgres
Python developers can take advantage of various features provided by Postgres and the Django ORM. Besides utilizing the ORM's data types, it is recommended to not shy away from raw SQL queries. The use of raw SQL can be easily integrated with the ORM and allows for more flexibility in data manipulation and report generation. Writing SQL code with proper formatting and documentation, akin to writing Python, can greatly improve readability and maintainability. Leveraging techniques such as common table expressions and organising code with thoughtful whitespace can make SQL more approachable and intuitive.
Are you getting the most out of your Postgres database? What features could you leverage to improve your Python project? This week on the show, Craig Kerstiens from Crunchy Data is here to discuss getting the most out of Postgres.
Craig shares his years of PostgreSQL expertise with advice on getting more from the platform. We talk about rich data types for describing ranges, geospatial data, and JSON.
Craig shares tools for accessing performance statistics from the command line and strategies for optimizing your terminal settings for SQL searches. He discusses Postgres extensions for customizing the database to your needs. Craig also provides multiple resources for learning more and an online tool for practicing within a playground environment.
Course Spotlight: Creating Web Maps From Your Data With Python Folium
You’ll learn how to create web maps from data using Folium. The package combines Python’s data-wrangling strengths with the data-visualization power of the JavaScript library Leaflet. In this video course, you’ll create and style a choropleth world map showing the ecological footprint per country.
Topics:
- 00:00:00 – Introduction
- 00:02:36 – What are reasons for considering Postgres?
- 00:07:41 – Timeline of recent features
- 00:11:06 – Unique data types
- 00:16:34 – Storing JSON information
- 00:20:19 – Video Course Spotlight
- 00:21:50 – Storing geographic information
- 00:25:16 – What’s the process for adding extensions?
- 00:31:33 – Advice for Python developers using Postgres
- 00:33:31 – Advice on writing SQL
- 00:38:06 – Command-line tools and customizations
- 00:48:18 – Django as an entry to Python
- 00:51:13 – Resources for learning and practicing with Postgres
- 00:53:45 – What are you excited about in the world of Python?
- 00:55:55 – What do you want to learn next?
- 00:58:34 – How can people follow your work online?
- 00:59:20 – Thanks and goodbye
Show Links:
- Craig Kerstiens - Blog
- Trusted Open Source PostgreSQL & Commercial Support for the Enterprise - Crunchy Data
- Why Postgres? - Crunchy Data
- Why Postgres - Craig Kerstiens - YouTube
- A hands on experience with complex SQL - Craig Kerstiens - YouTube
- Ingres (database) - Wikipedia
- PostgreSQL specific model fields - Django documentation
- Psqlrc - PostgreSQL wiki
- The most useful Postgres extension - pg_stat_statements
- High-compression Metrics Storage with Postgres Hyperloglog
- Postgres Playground and Tutorials - Crunchy Data
- PostgreSQL Blog - Crunchy Data
- It’s at least once a week I talk with someone that “loves Postgres” but isn’t sure why… Craig Kerstiens on Twitter:
- The Wok Book: Recipes and Techniques by J. Kenji Lopez-Alt
Level up your Python skills with our expert-led courses:
Remember Everything You Learn from Podcasts
