3 ms·

Introducing the caveats of PostgreSQL arrays is informative. However, I can't help but note that this app probably would have been better served by a classic ev

Introducing the caveats of PostgreSQL arrays is informative. However, I can't help but note that this app probably would have been better served by a classic event log table. Why have each user forever carrying tens, or hundreds of thousands of events directly in their user record? If data mining is necessary, it's right there in the event log and can be manipulated by much simpler (and faster, possibly by orders of magnitude) SQL. Additionally, it's not bloating regular access of the user records. I've been seeing these designs more and more, recently. Is it a natural extension of non-DBAs taking DDL design roles? Because this data manipulation style doesn't really mix well with any RDBMS I'm familiar with.

mrits 12y ago
A lot of times its because you want want a rollup table but also need to answer cardinality questions. So instead of scanning millions of rows you can have a single rolled up array that you can perform set operations on other rolls (instead of a numeric aggregate that does not have the appropriate mathematical properties to do much with).
- bonesmoses 12y ago
  Well... generally this is solved via indices. An event log indexed by user ID would have the same effect. You get all the user info, and if you really need to grab historical or event data, it's there when necessary for parts of the app that actually need it. I own a lot of stuff, but I don't drag my house everywhere in case I might need something. :p
  - deleted 12y ago
    [deleted]
acveilleux 12y ago
If my pg-fu is accurate, as the array grows, it would eventually get farmed out to a `pg_toast` table once the row no longer fits within a page (exact threshold might be lower). So it wouldn't be bloating any query path that doesn't use/depend on the array at the expense of forcing a toast lookup on every tuple that refers to the array. That should not be construed as an endorsement. I would think the array append might get expensive.
dorianj 12y ago
Great question; I'm an engineer at Heap. This data model fit our needs at the time, however, you're absolutely right that these user rows do become quite cumbersome as they grow large. > Well... generally this is solved via indices. An event log indexed by user ID would have the same effect. You get all the user info, and if you really need to grab historical or event data, it's there when necessary for parts of the app that actually need it. Indeed! We're investigating a more traditional schema, as the topics of our more recent blog posts might suggest (http://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/ http://blog.heapanalytics.com/speeding-up-postgresql-queries...).

mrits 12y ago

A lot of times its because you want want a rollup table but also need to answer cardinality questions. So instead of scanning millions of rows you can have a single rolled up array that you can perform set operations on other rolls (instead of a numeric aggregate that does not have the appropriate mathematical properties to do much with).

bonesmoses 12y ago
Well... generally this is solved via indices. An event log indexed by user ID would have the same effect. You get all the user info, and if you really need to grab historical or event data, it's there when necessary for parts of the app that actually need it. I own a lot of stuff, but I don't drag my house everywhere in case I might need something. :p
- deleted 12y ago
  [deleted]

acveilleux 12y ago

If my pg-fu is accurate, as the array grows, it would eventually get farmed out to a `pg_toast` table once the row no longer fits within a page (exact threshold might be lower). So it wouldn't be bloating any query path that doesn't use/depend on the array at the expense of forcing a toast lookup on every tuple that refers to the array. That should not be construed as an endorsement. I would think the array append might get expensive.

dorianj 12y ago

Great question; I'm an engineer at Heap. This data model fit our needs at the time, however, you're absolutely right that these user rows do become quite cumbersome as they grow large. > Well... generally this is solved via indices. An event log indexed by user ID would have the same effect. You get all the user info, and if you really need to grab historical or event data, it's there when necessary for parts of the app that actually need it. Indeed! We're investigating a more traditional schema, as the topics of our more recent blog posts might suggest (http://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/ http://blog.heapanalytics.com/speeding-up-postgresql-queries...).