Thread: Memory
Hi community,
I am reading a big dataset using code similar to this:
query = '''
SELECT timestamp, data_source, tag, agg_value
FROM my_table
'''I
batch_size = 10_000_000
with psycopg.connect(cs, cursor_factory=psycopg.ClientrCursor) as conn:
with conn.cursor('my_table') as cur:
cur = cur.execute(query)
while True:
rows = cur.fetchmany(batch_size)
SELECT timestamp, data_source, tag, agg_value
FROM my_table
'''I
batch_size = 10_000_000
with psycopg.connect(cs, cursor_factory=psycopg.ClientrCursor) as conn:
with conn.cursor('my_table') as cur:
cur = cur.execute(query)
while True:
rows = cur.fetchmany(batch_size)
# ...
if not rows:
break
if not rows:
break
The code is executed on a Databricks node, if that matters. The library version is the latest.
I found that despite fetching in batches, memory consumption grows continuously throughout the loop iterations and eventually the node goes OOM. My code does not save any references, so it might be something internal to the library.
If I change the factory to ServerCursor, the issue fixes, memory does not grow after the first iteration.
I looked the documentation, but did not find specifics related to performance differences between Server and Client cursors.
I am fine with ServerCursor, but I need to ask, is it by design that with ClientCursor the result set is copied into memory despite fetchmany() limit? ClientCursor is the default class, so may be worth documenting the difference (sorry, if I missed that).
Thank you.
On 12/21/24 02:45, Vladimir Ryabtsev wrote:
> Hi community,
>
> I am reading a big dataset using code similar to this:
>
> query = '''
> SELECT timestamp, data_source, tag, agg_value
> FROM my_table
> '''I
> batch_size = 10_000_000
>
> with psycopg.connect(cs, cursor_factory=psycopg.ClientrCursor) as conn:
FYI, ClientCursor.
>
> I looked the documentation, but did not find specifics related to
> performance differences between Server and Client cursors.
>
> I am fine with ServerCursor, but I need to ask, is it by design that
> with ClientCursor the result set is copied into memory despite
> fetchmany() limit? ClientCursor is the default class, so may be worth
> documenting the difference (sorry, if I missed that).
Client side cursor
https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#client-side-cursors
"In such querying pattern, after a cursor sends a query to the server
(usually calling execute()), the server replies transferring to the
client the whole set of results requested, which is stored in the state
of the same cursor and from where it can be read from Python code (using
methods such as fetchone() and siblings)."
https://www.psycopg.org/psycopg3/docs/api/cursors.html#psycopg.Cursor.fetchmany
"fetchmany(size: int = 0) → list[+Row]
Return the next size records from the current recordset.
size default to self.arraysize if not specified.
Return type:
Sequence[Row], with Row defined by row_factory
"
Server side cursor
https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#server-side-cursors
"PostgreSQL has its own concept of cursor too (sometimes also called
portal). When a database cursor is created, the query is not necessarily
completely processed: the server might be able to produce results only
as they are needed. Only the results requested are transmitted to the
client: if the query result is very large but the client only needs the
first few records it is possible to transmit only them.
The downside is that the server needs to keep track of the partially
processed results, so it uses more memory and resources on the server."
>
> Thank you.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Now I see, the doc is already great.
Thanks for pointing out Adrian.
On Sat, Dec 21, 2024 at 8:34 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/21/24 02:45, Vladimir Ryabtsev wrote:
> Hi community,
>
> I am reading a big dataset using code similar to this:
>
> query = '''
> SELECT timestamp, data_source, tag, agg_value
> FROM my_table
> '''I
> batch_size = 10_000_000
>
> with psycopg.connect(cs, cursor_factory=psycopg.ClientrCursor) as conn:
FYI, ClientCursor.
>
> I looked the documentation, but did not find specifics related to
> performance differences between Server and Client cursors.
>
> I am fine with ServerCursor, but I need to ask, is it by design that
> with ClientCursor the result set is copied into memory despite
> fetchmany() limit? ClientCursor is the default class, so may be worth
> documenting the difference (sorry, if I missed that).
Client side cursor
https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#client-side-cursors
"In such querying pattern, after a cursor sends a query to the server
(usually calling execute()), the server replies transferring to the
client the whole set of results requested, which is stored in the state
of the same cursor and from where it can be read from Python code (using
methods such as fetchone() and siblings)."
https://www.psycopg.org/psycopg3/docs/api/cursors.html#psycopg.Cursor.fetchmany
"fetchmany(size: int = 0) → list[+Row]
Return the next size records from the current recordset.
size default to self.arraysize if not specified.
Return type:
Sequence[Row], with Row defined by row_factory
"
Server side cursor
https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#server-side-cursors
"PostgreSQL has its own concept of cursor too (sometimes also called
portal). When a database cursor is created, the query is not necessarily
completely processed: the server might be able to produce results only
as they are needed. Only the results requested are transmitted to the
client: if the query result is very large but the client only needs the
first few records it is possible to transmit only them.
The downside is that the server needs to keep track of the partially
processed results, so it uses more memory and resources on the server."
>
> Thank you.
>
--
Adrian Klaver
adrian.klaver@aklaver.com