4 ms·
Assuming you're doing bulk inserts, and can restart the insert on any failure, one tactic is to disable all consistency and durability storage features during t
by vii 6y ago
Assuming you're doing bulk inserts, and can restart the insert on any failure, one tactic is to disable all consistency and durability storage features during the insert. This heavily reduces the IO operations needed and the need to wait for them to complete. That's assuming that iops are the resource that you are constrained on.
Steps:
- use ext2 or even tmpfs as the filesystem, this disables journaling and COW features of ZFS; mount options async,nobarrier
- set tables to be UNLOGGED to disable Postgres WAL
This got things fast enough for my last use-case. But next ideas, as at some point you then become CPU/memory bound
- you could try sharding by partitioning the table
- another trick is to use SQLite as a backing store for inserts (it is quite fast if you turn off all logging) and then query with Postgres via a FDW https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw
- koeng 6y agoHow fast did it go for your last use-case? Disabling all the consistency and durability storage features is dreadful, to say the least, but I'd definitely be open to trying it if it means I can get it done quicker. The algorithm I'm working with uses single-cores but is linear-time and in my experience is at least 10x-100x faster than the IO. Memory is actually the biggest problem with the method, which is why I need to use a database to back it to disk.
- prpl 6y agoIt's relatively safe to do so for a one-time bulk load, especially if you have a way to verify consistency what you've loaded for integrity reasons (foreign keys, for example, might be a single query and a join). If it's not a one-time load, then you are right, it's dreadful. If, for example, you get a 3-5x speedup without checks (not unheard of), you can do it twice, verify the copies are the same, and get a 2x speedup.