In order to support a new product development I mentioned in an earlier blog post, I re-did my existing waypoint database as a PostGIS geographical database. I also added some foreign keys and some other cleanup that I’ve been meaning to do for a while. But obviously, I don’t want to support two databases, so today I’ve been converting one of my existing waypoint generator perl scripts to use the new PostGIS database instead of the MySQL database it was on before, but without any actual GIS functionality. And Houston? We have a problem. Doing a full US + Canada data load on the MySQL version takes about a minute and a half. Doing the same load on the PostGIS version takes twenty five minutes. Something tells me that I need to make some adjustments here.
One thought on “Back to the drawing board”
Comments are closed.
(a) Ensure you are not doing your load one transactional insert at a time. Wrap 1000 inserts in a transaction block. Even better, do your load with a COPY statement or at least using prepared statements.
(b) Tune your postgresql.conf file, increase your WAL buffers and other parameters off the crappy default values.
(c) If you are doing a bulk load set fsync off at the start and on at the end. It’s dangerous, but if it’s a bulk load you don’t mind re-starting the load in the unlikely event your server crashes midway through.