Developer Advocate at Redgate
State College, Pennsylvania, United States
Ryan is an Advocate at Redgate focusing on PostgreSQL. Ryan has been working as a PostgreSQL advocate, developer, DBA and product manager for more than 20 years, primarily working with time-series data on PostgreSQL and the Microsoft Data Platform.
Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server before returning to PostgreSQL full-time in 2018. He’s at the top of his game when he's learning something new about the data platform or teaching others about the technology he loves.
Area of Expertise
Arrays are an essential part of software development. Among modern databases, however, only PostgreSQL has dedicated datatypes, functions, and indexes built to efficiently work with arrays. In fact, I think arrays are a hidden superpower of PostgreSQL, fully utilized as a data type, usable in pl/pgSQL, and essential for a myriad of built-in functions to process data more efficiently.
In this session I’ll discuss the array datatype in PostgreSQL along with things to consider before using it in a schema model. After laying the foundation for how arrays can be created, queried, and modified, I’ll demonstrate a handful of functions that can be used to process strings, JSON, numerics… and even Wordle emoji’s! The session will finish by demonstrating how using arrays in dynamic SQL can create more efficient multi-value inserts in some circumstances.
By the end of this session, you will understand how arrays work in PostgreSQL and how powerful they can be as a datatype, logic variable, and in processing other data.
Exploring new features in PostgreSQL or reproducing an unusual query plan can be tricky without representative data to utilize. While there are a plethora of sources for sample data and tools to import it, you can end up spending too much time finding representative data to work with.
Although using real application data would be ideal, PostgreSQL provides the generate_series() function which makes it easy to create a representative time-series dataset using varying cardinalities and different lengths of time.
In this talk we'll introduce generate_series() and demonstrate how to use it to create realistic-looking time-series data of all shapes and sizes, using custom PostgreSQL user defined functions. Once we've mastered the basics, we'll dial it up a notch by incorporating PostgreSQL math functions and relational data to create realistic time-series patterns of data for various use cases like sales or web site visits.
Security is an essential, yet often misunderstood, part of effectively managing a PostgreSQL cluster. As the popularity and adoption of PostgreSQL continues to grow, the interaction of roles, privileges, and object ownership is a recurring theme of confusion in forums and mailing lists.
In this session, I’ll start by defining the Principle of Least Privileges and how this philosophy influences roles and privileges in PostgreSQL. Next, I’ll demonstrate the importance of object ownership in PostgreSQL, how group and user roles can work in unison to effectively manage permissions, multiple ways to manage default privileges, and which privileges should always be modified in any new PostgreSQL database. Finally, I’ll discuss how recent releases are laying a foundation for more flexible and robust security management in the years ahead.
By the end of this session, you will understand how roles work in PostgreSQL, how they impact your daily work, and how to effectively communicate security best practices with others on your team. You’ll leave with a solid information to start creating roles that effectively manage access to your cluster and data.
Since December 2015, Eric Wastl has been creating 25 daily coding puzzles each year known as The Advent of Code. In 2022, more than 250,000 developers from around the world worked each day to solve the challenges in a language of their choice, including some members of the PostgreSQL community.
In this talk I’ll discuss 10 things I learned working through the daily challenges, using only PostgreSQL and SQL, without the use of pl/pgsql. The talk will start with an overview of the extract, load, and transform (ELT) pattern using built-in functions to work with data, regardless of the form. Once loaded, I’ll show multiple ways to chain together PostgreSQL functions and SQL commands to analyze the data and solve puzzles. I’ll demonstrate how CTEs, recursive CTEs, arrays, LATERAL CROSS JOINS, string functions, window functions, CASE statements, and more can work together without any procedural code to transform and query unique datasets.
By the end of this session, you will understand how to approach data challenges differently, using the power of PostgreSQL and SQL to work more effectively. Whether you’re a seasoned professional or still learning your way around PostgreSQL, you’ll leave with at least one new tip to help in your next data puzzle.
Do you know which queries are acting abnormally today vs. yesterday? Which queries are fast but running 100,000 times per hour? Are there certain times per day that performance lags unexpectedly?
The pg_stat_statements extension is our most valuable tool for understanding the current state of query workloads within your PostgreSQL cluster. Unfortunately, all of the tracked metrics are cumulative until they are reset (either manually or with a restart), making it difficult to use for point-in-time tuning and observability.
In this talk, I'll review the metrics that pg_stat_statements provides and then demonstrate how to save the data to a table periodically for better visibility into your queries' performance and resource usage over time, including sample Grafana dashboards. We'll conclude the talk by discussing the additional benefits of storing this data in a TimescaleDB hyptertable, which provides native compression (store more data longer) and automatic data retention policies.
Time-series data is the backbone of many applications, tracking measurements from devices, financial transactions, and users at high frequencies. In nearly every application there is a need to frequently retrieve the most recent data point for each tracked item. As data grows, however, querying this information efficiently can become a bottleneck, even with properly tuned and indexed tables.
In this talk, I'll discuss the reason querying "last point" data in PostgreSQL can be problematic and demonstrate five approaches to keep your queries responsive. The size and scope of your data, as well as the development expertise on your team, will influence which approach is right for your situation. You'll leave the talk ready to evaluate your schema, indexes, and queries to keep your application running at peak performance.
PostgreSQL has risen to be the dominant choice for new projects and cost-saving migrations in an increasingly cloud-first, price-conscious world. Additionally, all three major cloud providers have invested heavily in PostgreSQL and their own forks, signaling a continued shift in the DBMS market. This increases the likelihood that you will be asked to manage an alternative RDBMS like PostgreSQL in the next 3-5 years.
In this session, I'll briefly introduce the history of PostgreSQL and what has allowed it to shine in this modern age. Next, we'll look at 10 areas of PostgreSQL including community, tooling, configuration, data types, and SQL differences. As a former SQL Server developer, I'll demonstrate how to transfer the knowledge you already have to how PostgreSQL works so that you can succeed faster on your next project.
By the end of the session, you will have enough information to connect to PostgreSQL with free tooling, modify common SQL to run correctly, get information about the state of the database, and help with basic query tuning.
Whether you are the DBA for an Agile, Sprint-based team, or a developer with DBA responsibilities, you may feel like the development bottleneck at every turn. Managing migrations with your ORM of choice is easy but often error-prone and hard to manage as the project grows. Or worse yet, maybe your schema and change management is a folder full of poorly commented SQL scripts saved to a shared folder or local drive.
Surely there's a better, SQL-centric way to manage databases as code, validate changes, and deploy updates - right? Yes, there is!
This talk will explore what database DevOps is and how open-source and paid tools like Flyway can help you manage database schema and code changes while incorporating agile CI/CD practices.
By the end of the session, you'll be ready to tame your rogue application databases, managing changes and migrations with ease, and become the envy of the development team.
Developer Advocate at Redgate
State College, Pennsylvania, United States