1

I have an array that looks like this [[(Double,Double)]]. It's a multi-dimensional array of tuples.

This is data that I will never query on, as it doesn't need to be queried. It only makes sense if it's like that on the client side. I'm thinking of storing the entire thing as string and then parsing it back to multi array.

Would that be a good approach and would the parsing be very expensive considering I can have a max of 20 arrays with 4 max inner array each with a tuple of 2 Double?
How would I check to see which is a better approach and if storing it as multi-dimensional array in PostgreSQL is the better approach?
How would I store it?

Improve this question
8
  • "would the parsing be very expensive" - since you would do that outside of the postgres database, only a benchmark in your chosen language/framework/technology can tell. Also measure the serialisation overhead. Commented Nov 13, 2019 at 1:06
  • I can't see any reason not to store it an actual array in postgres, which will hopefully choose the optimal memory layout for it. Except if you can take a huge advantage of compression after your custom serialisation. That said, 20*4*2 is a really small array, so unless you have thousands of rows with these arrays, you probably don't need to worry anyway. Commented Nov 13, 2019 at 1:10
  • How would I store a multi array of tuples though. I can store multi array of Double but would I store (Double,Double) Commented Nov 13, 2019 at 1:26
  • Just like Erwin showed below :-) Alternatively, as a threedimensional array where the third dimension has a size of 2. Commented Nov 13, 2019 at 1:59
  • 1
    I don't think that will make any difference. The composite type might be a bit more difficult to work with, but that depends mostly on your tooling and technology. Commented Nov 13, 2019 at 2:23

1 Answer 1

Reset to default
6

To store an array of composite type (with any nesting level), you need a registered base type to work with. You could have a table defining the row type, or just create the type explicitly:

CREATE TYPE dd AS (a float8, b float8);

Here are some ways to construct that 2-dimensional array of yours:

SELECT ARRAY [['(1.23,23.4)'::dd]]
     , (ARRAY [['(1.23,23.4)']])::dd[]
     , '{{"(1.23,23.4)"}}'::dd[]
     , ARRAY[ARRAY[dd '(1.23,23.4)']]
     , ARRAY(SELECT ARRAY (SELECT dd '(1.23,23.4)'));

Related:

Note that the Postgres array type dd[] can store values with any level of nesting. See:

Whether that's more efficient than just storing the string literal as text very much depends on details of your use case.

  • Arrays types occupy an overhead of 24 bytes plus the usual storage size of element values.
  • float8 (= double precision) occupies 8 bytes. The text string '1' occupies 2 bytes on disk and 4 bytes in RAM. text '123.45678' occupies 10 bytes on disk and 12 bytes in RAM.
  • Simple text will be read and written a bit faster than an array type of equal size.
  • Large text values are compressed (automatically), which can benefit storage size (especially with repetitive patterns) - but adds compression / decompression cost.

An actual Postgres array is cleaner in any case, as Postgres does not allow illegal strings to be stored.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

What do you mean by the last line ? "An actual Postgres is cleaner in any case, as Postgres does not allow illegal strings to be stored." My use case is that when this data is queried it need to be in that array on the client as fast as possible. I am using swift on the client side
The last line means, nobody can sneak in illegal data in the database - by accident or with malicious intent.
So what would u recommend for my use case using dd type arrays or using strings. I added my use case in the comment above and BTW thanks!
@j.doe Implement them both, and put a timing on that place where it matters. Then run on real-world data.
@j.doe: The alternative approach with another array instead of the composite type at its core does not need the custom type dd, resulting in simpler syntax. Storage size and performance should be identical. Maybe a bit more expensive to translate to/from your original form.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.