The hidden cost of PostgreSQL arrays

PostgreSQL arrays begin with deceptive simplicity. A column declared as integer[] accepts values like {1,2,3}, and suddenly your relational database appears to handle lists as gracefully as any document store. The syntax feels intuitive, the documentation suggests straightforward usage, and the temptation to embed related data directly into rows is powerful. Yet this convenience masks architectural decisions that fundamentally conflict with PostgreSQL's relational design, creating hidden costs that manifest as performance bottlenecks, data integrity issues, and maintenance nightmares.

The core tension lies in what arrays actually represent: they are document storage in disguise. When you store tag_ids in an array, you're making the same architectural choice as a NoSQL database embedding subdocuments—prioritizing data locality over normalization. This eliminates joins and simplifies reads, mapping naturally to application objects. But PostgreSQL was designed around the relational model, where foreign keys enforce referential integrity, joins connect normalized tables, and updates modify individual rows, not entire lists. Arrays give you document-model convenience while discarding relational promises. There are no foreign keys for array elements, no ON DELETE CASCADE for individual IDs. If you delete a tag entry, the orphaned ID remains in your array indefinitely. The rule of thumb is stark: if you need referential integrity, you want a link table, not an array. Arrays are for data sharing the same lifecycle as the parent row—whitelist IP addresses for a service account, not relationships spanning tables.

The syntax itself harbors gotchas that break production code. Arrays don't default to starting at 1; PostgreSQL allows arbitrary bounds. An array like [-5:-3]={10,20,30} exists, and iterating with FOR i IN 1 .. array_length(fruits, 1) will fail silently. You must use array_lower() and array_upper() in PL/pgSQL or generate_subscripts() in SQL to handle any array correctly. Dimension enforcement is equally illusory. Declaring integer[][] doesn't enforce two dimensions; it's syntactic sugar. PostgreSQL accepts {{1,2},{3,4}}, {1,2,3}, and even {{{1,2},{3,4}},{{5,6},{7,8}}} in the same column. To enforce specific dimensions, you need explicit CHECK constraints using array_ndims() and array_length(). The database does enforce uniformity—sub-arrays must match dimensions—but that's it.

Access patterns reveal more traps. Slicing behaves differently than indexing: matrix[1][1] returns a scalar, while matrix[1:1][1:1] returns {{1}}, a single-element array. Out-of-bound access returns NULL for indexing but empty arrays {} for slicing, creating inconsistent error handling. The most confusing aspect is multi-dimensional array treatment. PostgreSQL stores matrices as single structures, not arrays of arrays. ARRAY[[1,2],[3,4]][1] returns NULL because the index is incomplete—you can't extract a row directly. Even slicing {{1,2},{3,4}}[1:1] yields {{1,2}}, not {1,2}. To get a proper sub-array, you must unnest and re-aggregate: SELECT array_agg(val) FROM unnest(matrix[1:1]) val, but this requires explicit ORDER BY to guarantee sequence. For complex multi-dimensional structures where sub-arrays have independent meaning, JSONB is simply better.

Indexing arrays demands understanding GIN (Generalized Inverted Index), not B-tree. B-tree indexes treat arrays as atomic values for equality and sorting, rendering them useless for element queries. GIN indexes invert the data structure: instead of mapping rows to values, it maps values to rows. This enables set operations efficiently. Creating CREATE INDEX posts_by_tags ON posts USING GIN (tags) allows containment @> (all tags present) and overlap && (any tag present). But the ANY operator is a trap. WHERE 'feature' = ANY(tags) doesn't use GIN; it's a scalar equality inside a loop, forcing sequential scans. The correct form is WHERE tags @> ARRAY['feature']. The distinction is critical: ANY is for passing lists into queries, not for querying array columns.

Write amplification is where arrays become truly expensive. GIN indexes multiply writes: one row with N array elements creates N index entries. PostgreSQL mitigates this with "fast update," buffering new entries in a pending list merged during VACUUM. This helps INSERTs but hurts SELECTs, which must scan both the organized index and messy pending list. For read-heavy workflows, disable it: CREATE INDEX ... WITH (fastupdate = off). But the deeper problem is row immutability. PostgreSQL's MVCC means updates rewrite entire rows. UPDATE user_activity SET event_ids = event_ids || 10001 WHERE user_id = 50 copies the whole array, then the whole row. When arrays exceed ~2 KB, they move to TOAST storage, turning simple appends into decompress-modify-recompress cycles that rewrite the entire blob. PostgreSQL 14's LZ4 compression reduces CPU penalty, but the fundamental I/O cost remains.

The 2 KB threshold derives from TOAST_TUPLES_PER_PAGE (default 4), ensuring four tuples fit on an 8 KB page. Below this, arrays stay inline; above, they incur TOAST overhead. This means small arrays are cheap, but large or frequently modified arrays become performance anchors. An array of 10,000 elements written once and read-only is valid; an array of 50 elements appended on every request is a villain. Compression tests show the difference: a 10,000-element array of low-cardinality floats compresses to 15 KB with LZ4 but uses 39 KB uncompressed. The trade-off is clear—storage efficiency versus update cost.

Despite these costs, arrays excel at bulk loading. Inserting 5,000 rows via unnest is vastly faster than application loops or massive VALUES strings. One network round-trip, one query plan, and PostgreSQL handles row-by-row insertion internally. This works for UPSERTs and MERGE, making arrays ideal for transport, even if storage is problematic.

Specialized extensions unlock further performance. The intarray extension provides native functions for 32-bit integers: sort() and uniq() avoid unnest-reaggregate patterns. Its query syntax @@ simplifies boolean logic: available_days @@ '1 & (6 | 7) & !2' replaces chained && and @> operators. But it's strictly for integers; values exceeding 2 billion revert to generic arrays.

The AI era brings pgvector, which is fundamentally arrays of floats with mathematical semantics. While standard arrays care about exact matches, vectors care about distance: cosine <=> or Euclidean <->. This enables semantic search and recommendations, but the architectural decision mirrors standard arrays—storing fuzzy data directly on rows, trading strict structure for convenience. Since joins can't express similarity, you accept larger tables for proximity queries.

Arrays in PostgreSQL are neither inherently good nor bad. They are a document-model feature grafted onto a relational engine, creating friction at every layer: syntax, indexing, storage, and integrity. The hidden cost isn't just performance—it's the gradual erosion of relational guarantees. Use arrays for data that shares the parent's lifecycle, never for relationships. Use GIN indexes correctly, disable fast updates for read-heavy workloads, and prefer JSONB for complex structures. When you need referential integrity, normalization, or frequent element-level updates, reach for link tables. Arrays are powerful transport and bulk-loading tools, but their convenience demands respect for the underlying architecture.

For further exploration, see the PostgreSQL array documentation, GIN index details, and extensions like intarray and pgvector.