does LIMIT clause mean constant memory/storage in a PostgreSQL query? When a LIMIT clause is used in a query with a very large number of records, e.g.

SELECT *
    FROM my_tab
    ORDER BY column1
    LIMIT 1 OFFSET 9


, does PostgreSQL always use a (roughly) constant amount of memory (and/or storage) during the query (1 row in the example)?

The documentation doesn't say much about the underlying mechanism or resource consumption.
Definitely not. With an order by clause, resource usage can vary between both extremes depending on whether or not a sort needs to be done or an index can be used.

Other factors play a major or minor role too:


querying from a base table, a view or a foreign table
width of the table
data types involved
size of the table / indexes involved
whether the table(s) are partitioned or not and whether partition elimination can be applied for the query
whether a serial or a parallel plan is used (which depends on the structure of the query, the indexes, the partitioning (or not), the DBMS version and possibly other factors)
resource consumption settings in postgresql.conf, like work_mem and replacement_sort_tuples (see Postgres docs: Resource Consumption for details.)