3 ms·
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 temporar
by 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 agoVACUUM 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 agoYes, 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 agoYes, 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 agoHuh. 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 agoAFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.