Date: 2022-09-22
Time: 17:05–17:55
Room: The Innovation Space
Level: Intermediate
Feedback: Leave feedback
Cursors in PostgreSQL can do more than just iterate through result sets! In fact, they are a powerful tool for data transfer and access control. This talk will focus on how we’ve used server-side cursors as “data portals”, to dispatch large datasets between applications in a way that’s both performant and secure.
We’ll begin by motivating the problem and describing other potential solutions, and briefly discussing some of their shortcomings.
Next, we’ll look at how PostgreSQL server-side cursors can help with data transfer and access control. This section will include Python code samples using the psycopg2 library.
This section will also include an in-depth look at the performance characteristics of this solution, with an emphasis on real performance benchmarks of different solutions across datasets of different sizes. There are performance tradeoffs associated with this solution, and we’ll see where those occur, and what application workloads they’re acceptable for.
Finally, we’ll discuss how the solution described so far fits into database application design. This section will include Python code samples using the contextlib library, plus some analysis of which application architectures are well suited to make use of the solution, and which are not.