8

I have a file geodatabase containing research data that I need to load into PostGIS. The GDB contains one feature class with one billion records with three columns (the FID, a FK, and the geometry (single points)).

I'm using this to load the GDB into PG:

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5433 dbname=XXX user=XXX password=XXX" C:\XXX\XXX.gdb -lco SPATIAL_INDEX=NO --config PG_USE_COPY YES

While loading, I also disable auto-vacuum on the table.

After loading the file, I create a spatial index.

The problem I'm encountering is the first 400 million records load very quickly but performance eventually degrades to crawl. It goes from millions of points in mere seconds to taking several minutes to load 100K points. How do I address this or tune my system?

I imported smaller GDBs ranging from 1 million to 500 million records using the above steps. At various points I had over 2 billion records in various tables within the same database. No performance issues and all GDBs loaded w/o issue.

Windows 10 16GB RAM 512GB SSD hard drive, 300GB free

I tried to load a subset of the points using some options I found here, except with ogr2ogr, but performance was horrible even when selecting "FID < 10". I quickly ruled out that option.

ogrinfo out.gdb -al -sql "select * from eea_1Kgrid where fid = 1" -dialect OGRSQL

Thoughts?

update 1: The points will not be rendered all at once. Small subsets may be rendered but this is more about getting all the data into the database for analysis and summary.

  • 2
    Bit of a longshot but have you tried the OGR PGDump driver? There's an example on that page of piping direct to psql - ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ abc.shp | psql -d my_dbname -f - – user2856 May 20 '16 at 4:28
  • 3
    There is nothing simple about any opertaion involving a billion rows. You should only insert columns you intend to keep. Best practice is to avoid loading a billion of anything, period. It is particularly pointless to load a biliion points sans attributes. The best way to render those points is to merge neighboring points into multipoint objects 10,000 or more at a time, giving you 100k features which will render sprightly. – Vince May 20 '16 at 10:02
  • Sounds a bit silly but I have read about better results when first exporting to a CSV file and then load that one with COPY from within postgres. And a billion records is a lot but that's what a database is supposed to handle in the first place. – tilt May 22 '16 at 10:54
1

The best thing you could do is give it time. I, myself, have witnessed an upload of over 7 billion observations and that took over 3 days. The only other recommendation I could make is to use PSQL Console in PG Admin III for your command line scripting.

In an effort to speed up the process, consider separating out some of the information using a bulk upload structure like 'metacoding'. If you do this, push the code into transactions where you are performing multiple operations. This is best done with python or even excel. Check out the package psycopg for python if you pick this.

Ex: 1. Copy to a temporary table, on commit drop. 2. Change data to break into smaller identifying columns. 3. Copy to the final table. 4. Commit changes (temp table will disapear).

Another option would be to upload some data into separate tables and link them together in a form of a tree.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.