> Avoid Common Table Expressions (or upgrade to 12)
Upgrading to 12 (or 13) seems like the better option here, whenever you're able to do so. The improvements are very much worth it.
Upgrading to 12 (or 13) seems like the better option here, whenever you're able to do so. The improvements are very much worth it.
Fortunately there's a workaround - using the `with .. as not materialized ()` hint sped up my query 100x.
That said, given the performance implications, if someone wants to use PG as a warehouse upgrading to 12 is a no-brainer.
In my personal experience: the organisation doesn't invest in a storage solution that offers snapshots/COW (e.g. ZFS or SAN or whatever). Then they wait to upgrade until their disks reach a usage that the upgrade has to be done in-place. Then they become like rabbits in the headlights and never upgrade.
- Citus data, not Citrus data.
- In tables, column order matters. Order const-width non-null columns before all other columns (best so that there's no unnecessary alignment padding). Then maybe some low null fraction fixed-width columns, then ordered by query access frequency. A lot of time can be spent extracting tuple values, and you can save a lot of time using the offset caches by correctly tetris-ing your columns. Note that dropped columns are set to null, so a table rewrite (by redefining your table) may be in order for 100% performance.
Adding or removing a column in this system does not require postgres to rewrite the whole table, which means that old data can stay in the table effectively forever, as long as there are no other rewrite-required DDLs performed. Additionally, columns can be updated to SET NOT NULL / DROP NOT NULL, further complicating the whole system you're trying to optimize.
Eventually postgresql might support some form of table column reordering that permanently optimizes out deleted columns, but I think it's unlikely to happen anytime soon. There are lower hanging fruits on the tree; altering existing table definitions in a backwards-incompatible manner is a lot of effort for likely very little gain.
As for column packing: Maybe this can be implemented for CREATE TABLE with an option, but in the current transactional DDL framework this cannot be implemented for ADD COLUMN, because we can't reorder columns.