Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/754276296465719296
fix inadequate title, tags
Source Link
Erwin Brandstetter
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

Should arrays be avoided in postgres Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent postgresPostgres features (e.g. gin indexes) from being used?

Should arrays be avoided in postgres?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent postgres features (e.g. gin indexes) from being used?

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent Postgres features (e.g. gin indexes) from being used?

Source Link
Xodarap
Xodarap
  • 233
  • 2
  • 5

Should arrays be avoided in postgres?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent postgres features (e.g. gin indexes) from being used?