4 ms·

My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.

barrkel 6y ago
The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)
- btilly 6y ago
  Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on Postgres.)
  - mulmen 6y ago
    VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?
    - Tostino 6y ago
      Yes, that is a thing in Postgres. They are real tables and use the same underlying storage management as the rest of the database.
    - btilly 6y ago
      Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html https://www.postgresql.org/docs/12/catalog-pg-attribute.html. If you do so faster than it can vacuum, you're in for a world of hurt.
      - mulmen 6y ago
        Huh. For a long time there was no autovacuum in Redshift and AFAIK this was never a problem for us. I wrote our VACUUM script and it would never pick up system tables. Maybe Redshift does something differently.
    - barrkel 6y ago
      AFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.

The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)

btilly 6y ago
Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on Postgres.)
- mulmen 6y ago
  VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?
  - Tostino 6y ago
    Yes, that is a thing in Postgres. They are real tables and use the same underlying storage management as the rest of the database.
  - btilly 6y ago
    Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html https://www.postgresql.org/docs/12/catalog-pg-attribute.html. If you do so faster than it can vacuum, you're in for a world of hurt.
    - mulmen 6y ago
      Huh. For a long time there was no autovacuum in Redshift and AFAIK this was never a problem for us. I wrote our VACUUM script and it would never pick up system tables. Maybe Redshift does something differently.
  - barrkel 6y ago
    AFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.