5 ms·

> 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

> 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.

cedricd 5y ago
Yes, that's a really great point. I should emphasize that more clearly in the blog :).
- mattashii 5y ago
  A few more items I've encountered: - 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.
  - galkk 5y ago
    Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
    - mattashii 5y ago
      yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. 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.
wwweston 5y ago
What situations would make people unable to upgrade to 12+?
- cedricd 5y ago
  Maybe it doesn't get prioritized until it's important. I know PG upgrades are pretty straightforward, but sometimes people don't want to touch something running well. That said, given the performance implications, if someone wants to use PG as a warehouse upgrading to 12 is a no-brainer.
- goatinaboat 5y ago
  What situations would make people unable to upgrade to 12+? 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.
- aargh_aargh 5y ago
  One case I decided against it was when I needed to access the new postgres server (which I initially planned to be v13) from an old machine (with a legacy distro with v9.6). v13 introduces a new SCRAM-SHA-256 password hashing method by default and only libpq10 and newer supports this method. For some reason I couldn't or didn't want to neither rehash the passwords nor upgrade the client, so I remained on a lower version. Certainly not unsolvable, but I didn't have the time to spend on a proper fix.
truculent 5y ago
Yes, it is strange that "Avoid CTEs" made it into the TL;DR instead.
- cedricd 5y ago
  I updated the blog post :)
arcticfox 5y ago
Something painful I just learned today is that even Postgres >= 12 isn't that great with planning CTEs. Querying the same CTE twice seems to force it into materializing the CTE just like Postgres <12 used to do. Fortunately there's a workaround - using the `with .. as not materialized ()` hint sped up my query 100x.
- jeltz 5y ago
  Yeah, the only thing PosgtreSQL 12 did was to remove the optimization fence. There was no additional optimizer logic added.

> 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.

cedricd 5y ago

Yes, that's a really great point. I should emphasize that more clearly in the blog :).

mattashii 5y ago
A few more items I've encountered: - 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.
- galkk 5y ago
  Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
  - mattashii 5y ago
    yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. 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.

wwweston 5y ago

What situations would make people unable to upgrade to 12+?

cedricd 5y ago
Maybe it doesn't get prioritized until it's important. I know PG upgrades are pretty straightforward, but sometimes people don't want to touch something running well. That said, given the performance implications, if someone wants to use PG as a warehouse upgrading to 12 is a no-brainer.
goatinaboat 5y ago
What situations would make people unable to upgrade to 12+? 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.
aargh_aargh 5y ago
One case I decided against it was when I needed to access the new postgres server (which I initially planned to be v13) from an old machine (with a legacy distro with v9.6). v13 introduces a new SCRAM-SHA-256 password hashing method by default and only libpq10 and newer supports this method. For some reason I couldn't or didn't want to neither rehash the passwords nor upgrade the client, so I remained on a lower version. Certainly not unsolvable, but I didn't have the time to spend on a proper fix.

truculent 5y ago

Yes, it is strange that "Avoid CTEs" made it into the TL;DR instead.

cedricd 5y ago
I updated the blog post :)

arcticfox 5y ago

Something painful I just learned today is that even Postgres >= 12 isn't that great with planning CTEs. Querying the same CTE twice seems to force it into materializing the CTE just like Postgres <12 used to do. Fortunately there's a workaround - using the `with .. as not materialized ()` hint sped up my query 100x.

jeltz 5y ago
Yeah, the only thing PosgtreSQL 12 did was to remove the optimization fence. There was no additional optimizer logic added.