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
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.