Geek asks and answers

Postgres Arrays

We use the django-dbarray module to use arrays in postgres. I did some research on postgres arrays, and some developers said they did not recommend using a postgres array storing X values. Sometimes it's ten, and I heard up to thirty. Is there any consensus on how many values ​​can or should be stored in an array before performance starts to decline?

For reference, the above database is mainly a read-only database.

We are trying to decide where we should use the staging tables and where we should use the postgres array.

One additional related question: when creating an index on a column in the table where the values ​​of the array are stored in this column (say, bigint []). I understand that the values ​​stored in the array will not be indexed, but only the array itself (I assume this is something like a C pointer). How effective is this compared to a simple staging table?

We may need to create joins with respect to the values, or have some of the specific values ​​in the where clause, and I am worried that some of the characteristics may degrade, and we may be better off having an intermediate table when we might need to create a join.

, , dbarray, ORM django (, )?

+3
1

PostgreSQL GIN GiST intarrays, :

SELECT  *
FROM    mytable
WHERE   myarray @> ARRAY[1, 2]
-- returns arrays which contain 1 AND 2

:

SELECT  *
FROM    mytable
WHERE   myarray && ARRAY[1, 2]
-- returns arrays which contain 1 OR 2

.

.

+3

More articles:


All Articles