New answers tagged

0

Since Greenplum uses the Postgres database engine, reasoning applicable to a Postgres database seems appropriate. I don't see any reason not to use TRUNCATE when deleting all rows in a table with no children or foreign keys (which is your case), unless you have the ON DELETE triggers that you want to fire.


2

Delete several billion records old records (roughly 600GB) In this case it's may be more convenient to copy the rows that should remain rather than delete. Note that all 600Gb will move to your log file surely. By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction ...


0

If you have ON DELETE CASCADE triggers, they are hopefully there for a reason, and therefore should not be disabled. Another trick (still add your indices) that works for me is to create a delete function that manually deletes data starting with the tables at the end of the cascade, and works towards the main table. (This is the same as you would have to if ...


0

Have you tried deleting in batches? Maybe the SQL optimizer thinks that the FULL DELETE is too big so why using an index, instead it accesses the full table.. Try spitting in 5-10 batches, I think the explain delete will be different then


0

By deleting the data first, you are essentially making the data inaccessible. Don't you want to avoid this "downtime"? Consider loading the replacement data into a temp table, then doing IODKU to update the main data: INSERT INTO main (...) ON DUPLICATE KEY UPDATE col1 = VALUES(col1), ... SELECT ... FROM temp; If ...


0

Since you are deleting a large percentage of the table, it would be better to build a new table with the rows to keep: CREATE TABLE new LIKE real; INSERT INTO new SELECT * FROM real LEFT JOIN extract ON ... WHERE ... IS NULL; RENAME TABLE real TO old, new TO real; -- swap tables (sort of) DROP TABLE old; -- ...


Top 50 recent answers are included