{"id":110087,"date":"2026-05-29T12:00:00","date_gmt":"2026-05-29T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110087"},"modified":"2026-05-13T16:03:17","modified_gmt":"2026-05-13T16:03:17","slug":"how-data-scientists-work-with-postgresql-from-python-and-how-to-write-queries-that-dont-break-at-scale","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/how-data-scientists-work-with-postgresql-from-python-and-how-to-write-queries-that-dont-break-at-scale\/","title":{"rendered":"How data scientists work with PostgreSQL from Python (and how to write queries that don\u2019t break at scale)"},"content":{"rendered":"\n

In this article,<\/strong> learn how PostgreSQL powers data science workflows, including query execution, performance optimization, indexing, data retrieval, and more.<\/strong><\/p>\n\n\n\n

There\u2019s a part of the data science stack that rarely gets discussed. Not because it\u2019s unimportant, but because it\u2019s already been decided long before you arrive. Somewhere upstream, engineers chose a relational database<\/a>. In many cases, they chose PostgreSQL<\/a>. And, since it often becomes a ‘background’ system, it’s very easy to underestimate its impact.<\/p>\n\n\n\n

It’s seen as just a place to pull data from before the \u201creal work\u201d begins in Python<\/a>. It sits beneath notebooks, pipelines, and dashboards, rarely drawing attention unless anything breaks. That perception, however, is misleading. Yes, PostgreSQL stores data. Yet it also decides how<\/em> that data is accessed, how<\/em> it\u2019s shaped, and in many cases, how<\/em> it\u2019s computed.<\/p>\n\n\n\n

PostgreSQL is not designed to optimize for shortcuts. It prioritizes correctness: validating data strictly, enforcing constraints<\/a>, and adhering closely to SQL<\/a> standards – even when doing so introduces additional overhead. That can make it appear slower in trivial cases, but it’s precisely this design that allows it to behave predictably under load.<\/p>\n\n\n\n

The dual-purpose backbone of PostgreSQL<\/h2>\n\n\n\n

PostgreSQL occupies a unique position in the data ecosystem because it supports both transactional (OLTP) and analytical (OLAP) workloads<\/a> within the same system. This hybrid capability (HTAP<\/a>) means that the same database handling user transactions is also responsible for reporting, feature generation, and analytical queries<\/a>.<\/p>\n\n\n\n

That dual role introduces constraints, since analytical queries compete with production workloads instead of running in isolation. A poorly written aggregation<\/a> or join<\/a> can impact application latency just as much as inefficient transactional logic. It’s exactly why indexing strategy and execution planning<\/a> are crucial to responsible system design.<\/p>\n\n\n\n

\"An
PostgreSQL supports both OLTP and OLAP workloads<\/em><\/figcaption><\/figure>\n\n\n\n

The data scientist\u2019s entry point<\/h2>\n\n\n\n

Many workflows still begin with comma-separated values (CSV)<\/a> exports. While convenient during early experimentation, this approach introduces long-term problems. Data becomes stale, transformation logic is lost outside version control<\/a>, and reproducibility depends on manual steps that are difficult to track.<\/p>\n\n\n\n

Explaining the PostgreSQL direct query mandate<\/h2>\n\n\n\n

Direct querying of PostgreSQL changes this dynamic substantially: the SQL query becomes a reproducible, executable definition of how data is constructed rather than just a retrieval mechanism. Data scientists operate on the source of truth, retrieving exactly<\/em> the required subset of data, exactly<\/em> when needed.<\/p>\n\n\n\n

In production environments, this extends to feature generation pipelines<\/a>, dashboards, and inference systems. They all depend on live queries so, at that point, the database is no longer upstream of the system – it’s actively participating in computation.<\/p>\n\n\n\n

The risk of breakage at scale<\/h2>\n\n\n\n

A script that works well with ten thousand rows can completely fail with one hundred million, simply as queries that used to return results quickly start to slow down as the planner selects inefficient paths. Python processes can run out of memory after retrieving more data than they can handle. Connections build up until the database simply can’t take on any more. <\/p>\n\n\n\n

Large results accumulate the network, making data transfer the main expense. None of these problems show up early – they are, in fact, often found (too) late. Hence, recognizing how<\/em> these failures occur is the first step to preventing them.<\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

Python drivers & execution, explained<\/h2>\n\n\n\n

Python interacts with PostgreSQL through a small set of well-established drivers, each operating at a different level of abstraction. Psycopg2<\/a> offers stability and direct control, Psycopg3<\/a> introduces improved type handling and async capabilities, and SQLAlchemy<\/a> provides abstraction and connection pooling<\/a>.<\/p>\n\n\n\n

The choice reflects workload characteristics. Batch pipelines prioritize simplicity and determinism, while high-concurrency systems benefit from asynchronous execution and pooling.<\/p>\n\n\n\n

The execution lifecycle<\/h3>\n\n\n\n

Regardless of the driver, execution follows the same path once a query is issued. Calling cursor.execute()<\/code> triggers a multi-stage process inside PostgreSQL.<\/p>\n\n\n\n

The database parses the SQL, the planner selects an execution strategy, and the executor runs that plan while streaming results back to the client. The planning phase is where PostgreSQL decides how<\/em> to run the query, whether to use an index, and in what order operations should occur.<\/p>\n\n\n\n

The planner is the critical piece, as its decisions depend on how accurately it can estimate data distribution. If statistics are outdated, the planner may choose inefficient strategies such as scanning entire tables instead of using indexes, or selecting the wrong join algorithm. <\/p>\n\n\n\n

Hence, the same query can behave differently across environments, depending on how accurate the planner\u2019s assumptions are.<\/p>\n\n\n\n

\"An
Result returned to client<\/em><\/figcaption><\/figure>\n\n\n\n

Parameterized queries in PostgreSQL – what are they?<\/h3>\n\n\n\n

Constructing SQL queries through string interpolation<\/a> introduces both security risks and<\/em> performance inefficiencies. Parameterized queries prevent SQL injection<\/a> and allow PostgreSQL to reuse execution plans. For high-frequency queries, this saves real latency.<\/p>\n\n\n\n

Efficient retrieval patterns (streaming vs. loading) – which is best?<\/h3>\n\n\n\n

As datasets grow, retrieval strategy becomes critical. Client-side cursors that load entire result sets into memory quickly become infeasible. Server-side cursors enable streaming, allowing rows to be processed incrementally.<\/p>\n\n\n\n

This shift changes how pipelines are designed. Instead of loading data and then processing it, systems operate on streams of results. Memory usage becomes predictable and pipelines can handle datasets that exceed available resources. This pattern is particularly useful in ETL (extract, transform, load)<\/a> pipelines and long-running analytical jobs.<\/p>\n\n\n\n

How to avoid the ‘notebook trap’<\/h2>\n\n\n\n

Many pipelines start life inside a notebook. Data is loaded into memory, transformed using Python, and iterated on quickly. This works well for small datasets but introduces inefficiencies at scale.<\/p>\n\n\n\n

The most common issue is over-fetching<\/a>. Queries such as SELECT<\/code> * retrieve entire tables, shifting filtering and aggregation into Python. This not only increases memory usage but also bypasses the database\u2019s optimized execution engine.<\/p>\n\n\n\n

Production-grade query design<\/h3>\n\n\n\n

Scaling requires shifting relevant computation back into SQL. The database has access to indexes, optimized join strategies, and buffered I\/O (input\/output)<\/a>. Queries should be written to return only<\/em> the necessary data, already transformed and aggregated.<\/p>\n\n\n\n

More importantly, performance must be approached systematically. Bottlenecks can’t be reliably predicted – they must be measured – and fixing one bottleneck often reveals another. Plus, assumptions about performance frequently prove incorrect under load.<\/p>\n\n\n\n

Effective systems follow a disciplined approach:<\/p>\n\n\n

\n