Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

I don't understand if they mean that foreign key relationships are contributing to the problem. If so is it possible to turn off foreign key constaints during a big load?

Would it be worth removing indexes during a big load?

(Currently fighting with an etl that can't get above 500 rows / second even after using copy from)



The reason the indexes are their is because the table is involved in multiple JOINs, which without the indexes are very slow. I suspect it's a straight index, not a foreign key constraint.


All indexes on a pg table need updating if any value in the row is modified, that's the write amplification. The indexes are needed for joins, they're not so important for foreign keys.

Certainly checking foreign key constraints works better with indexes, but those are usually the other way around - verifying row exists with primary key, ie only one index. Updates to primary key would benefit from index on foreign key columns, but that's much rarer.


Indices trade reads for writes (there is no free lunch); if you index every column of your database you'll be able to do any read query reasonably fast but all writes will be slow, if you index nothing then writes will be fast but every read will be a full table scan. Presumably they have their indices because they need them for their read queries.


Dropping constraints when doing ETL is common. You can create then after and rollback if problems arise.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: