3 ms·

Everything is denormalized into an array so we can evaluate funnel queries quickly, with no joins.

by drob 13y ago

batbomb 13y ago
Did you try Clustering with PG (aka index organized table in Oracle)
- SigmundA 13y ago
  My understanding is PostgreSQL does not have true indexed organized tables (Clustered Index in MSSQL). You can create the table as clustered on an index and it will do it one time, if new data is inserted it is not clustered, you have to manually recluster exclusively locking it. Also this does not help performance nearly as much in PG because the index used to cluster is still a normal index and used normally with indirection. The only performance benefit comes from the fact that the data being searched for might be on the same page saving some disk I/O. A true clustered index the table IS the index, the table is seeked directly. This also saves disk space and some I/O since a separate index is not built redundantly storing the data. That being said an array should be even faster than a lookup to a secondary table with real clustered index since the array data would be in the row data of the main table incurring only the offset lookup to retrieve. There are definitely cases where hierarchical data structures are superior in performance to normalized ones. You gotta watch out though for hierarchical structures that store keys inline as the add their own space and I/O overhead (See shortening key names in Mongo DB). Arrays are a good choice though, they allow multi-value data inline without key overhead.
Groxx 13y ago
Why not store them as a [user_id, <cols for whatever is in the array>] table? There's no join for pulling a user's data, just like pulling an array column. I haven't used postgres, but this seems like trying to use a write-once-optimized structure for append-heavy use. Is it actually faster? And if so, since this is used for funnel-queries, is the query-speedup worth the write-slowdown, given what I would expect are much higher write loads than read loads, and that writes probably have to happen quickly while reads do not?

Everything is denormalized into an array so we can evaluate funnel queries quickly, with no joins.

by drob 13y ago

Everything is denormalized into an array so we can evaluate funnel queries quickly, with no joins.

batbomb 13y ago

Did you try Clustering with PG (aka index organized table in Oracle)

SigmundA 13y ago
My understanding is PostgreSQL does not have true indexed organized tables (Clustered Index in MSSQL). You can create the table as clustered on an index and it will do it one time, if new data is inserted it is not clustered, you have to manually recluster exclusively locking it. Also this does not help performance nearly as much in PG because the index used to cluster is still a normal index and used normally with indirection. The only performance benefit comes from the fact that the data being searched for might be on the same page saving some disk I/O. A true clustered index the table IS the index, the table is seeked directly. This also saves disk space and some I/O since a separate index is not built redundantly storing the data. That being said an array should be even faster than a lookup to a secondary table with real clustered index since the array data would be in the row data of the main table incurring only the offset lookup to retrieve. There are definitely cases where hierarchical data structures are superior in performance to normalized ones. You gotta watch out though for hierarchical structures that store keys inline as the add their own space and I/O overhead (See shortening key names in Mongo DB). Arrays are a good choice though, they allow multi-value data inline without key overhead.

Groxx 13y ago

Why not store them as a [user_id, <cols for whatever is in the array>] table? There's no join for pulling a user's data, just like pulling an array column. I haven't used postgres, but this seems like trying to use a write-once-optimized structure for append-heavy use. Is it actually faster? And if so, since this is used for funnel-queries, is the query-speedup worth the write-slowdown, given what I would expect are much higher write loads than read loads, and that writes probably have to happen quickly while reads do not?