does LIMIT clause mean constant memory/storage in a PostgreSQL query?
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.)