3

In our database we use a custom type to store 3D locations:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

In many examples I see INTERNALLENGTH used as a parameter with the definition of custom types. Also, the documentation tells me that "[t]he default assumption is that it is variable-length", if this parameter is omitted. Is this also true for types like the one above where the size can be calculated (3*4 bytes + 4byte padding)? So should I add 16 as internal length to the definition?

Also, are there other parameters that could improve the performance of handling many (~10,000,000) entries using this type?

Improve this question
1
  • Your biggest issues will be (a) alignment costs and (b) the relatively huge 24 byte per row overhead of Pg.
    – Craig Ringer
    Commented Jul 30, 2014 at 15:11

1 Answer 1

Reset to default
1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc.

What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the manual once more.

To optimize disk space and performance I would rather avoid using composite types due to big row overhead. Details depend on the details of your requirements.

Improve this answer
2
  • Thanks Erwin, this answers my question. What overhead can I expect when I use this location type? Due to padding I would expect 24 byte. I often run queries that find rows matching a certain bounding box (WHERE l.x > 10000 AND l.x < 12000 AND l.y > 40 AND l.y < 120.4 ...). I've got a B-Tree index on each location component.
    – tomka
    Commented Jul 30, 2014 at 18:52
  • @tomka: I suggest you ask a new question (or two, actually!). Comments are not the place. You can always add a link to this one for context to avoid redundant typing.
    – Erwin Brandstetter
    Commented Jul 30, 2014 at 18:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.