PostgreSQL – Are Dynamic Arrays More Wasteful Than Plain Text?

postgispostgresqlpostgresql-9.1

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to understand what is being returned. His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

My questions are: do PostgreSQL dynamic arrays use up more memory than text? And if so, is it a significant concern to drop the use of arrays on large queries?

Best Answer

His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

Evidence?

If he's arguing performance against what'd otherwise be a clarity and correctness improvement, the onus is on him to provide evidence.

Delimited text is usually the wrong way to do it - see e.g. this related question.

I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

It depends on how you're constructing the arrays. Try to create them in a single run using array_agg or an ARRAY[] constructor or similar. Iteratively building them with concatenation in a loop tends to be very slow because at the SQL level arrays are immutable, so they have to be copied to append to them.

Of course, the same is true of text at the SQL level, and the same principles apply.

My questions are: do PostgreSQL dynamic arrays use up more memory than text?

It depends on the underlying type. In general, if t1 uses more memory than t2, then an array of t1 uses more memory than an array of t2.

The internal storage of arrays depends a little on how they're created, but in general they add little or no memory overhead over the base type.

And if so, is it a significant concern to drop the use of arrays on large queries?

Yes, but no more of a concern than using delimited text.

Try to return result sets, not huge single results.

Related Solutions

Postgresql updating integer column taking more space than expected

Well, you are running this giant UPDATE statement of all rows in table B:

update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

which will more-or-less double the size of the table, since the UPDATE has to keep old row versions around. You may want to read up a bit on how PostgreSQL implements MVCC and how vacuuming works, but to answer this question:

If so could I expect the size to go down if i use VACUUM?

The space utilized by the table should go down if you run a VACUUM FULL or CLUSTER; probably a VACUUM alone will not be sufficient to immediately reclaim space.

PostgreSQL – Reuse SELECT Query by Adding Results in Array

It's a textbook case for a CTE, like @Daniel commented.
The example can be simplified some more. And you need to be aware of how LIMIT works in a UNION query.

CREATE OR REPLACE FUNCTION featured_products(valid_to_in      timestamp
                                           , taxonomy_id_in   integer
                                           , product_limit_in integer)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY
   WITH featured AS (SELECT supplier_id FROM products LIMIT 2)
   SELECT supplier_id
   FROM   featured

   UNION ALL
   (
   SELECT p.supplier_id
   FROM   products p
   LEFT   JOIN featured f USING (supplier_id)
   WHERE  f.supplier_id IS NULL
   LIMIT  product_limit_in
   )  -- parens required - or not?
END
$func$  LANGUAGE plpgsql VOLATILE;

LIMIT can only be applied once in a UNION (ALL) query, unless you enclose the leg of the query in parentheses. You may or may not want to add parentheses.
- The way I have it, a maximum of product_limit_in rows are returned in addition to the "featured" rows from the CTE.
- If you remove the parentheses you get a maximum of product_limit_in rows total - meaning that even "featured" products may be discarded.
  Related: Optimize a query on two big tables
Either way, don't ORDER BY the outer (combined) result before you LIMIT, if you can avoid it. Postgres can optimize the query very efficiently and just stop evaluating once enough rows have been returned (possibly fetching tuples from the top of a matching index). That would not be possible any more, which can make a huge difference in performance.
Using LEFT JOIN / NOT NULL to exclude featured rows from the second SELECT, which is probably faster than NOT IN and does not carry "surprises" when dealing with NULL values or empty results.
- Select rows which are not present in other table
In Postgres (as opposed to some other RDBMS), you can refer to p.supplier_id and f.supplier_id after joining with USING (supplier_id).

And yes, the CTE is only evaluated once:

A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries.

Bold emphasis mine.

Best Answer

Related Solutions

Postgresql updating integer column taking more space than expected

PostgreSQL – Reuse SELECT Query by Adding Results in Array

Related Question