I have a table containing 100 million point geometries. I was wondering if there is a maximum number of geometries when creating a spatial index in PostGIS/PostgreSQL? Or is it only a matter of time/resources?

  • 1
    Have a look at danbaston.com/posts/2016/11/28/… – TomazicM Apr 10 at 12:28
  • 1
    That is a good post, but, that is about the maximum size of a single geometry, and not, the number of rows in a table/index. Here are the rules for Postgres in general: wiki.postgresql.org/wiki/…. I have made spatial indexes on tables with billions of rows. There is no fixed limit that I am aware of, beyond time and resources, as you say. – John Powell Apr 10 at 12:52
  • 1
    Once you exceed 10M rows you become more vulnerable to the impact of spatial fragmentation. If the points are randomly distributed (with respect to space), you could see significant impact on medium-size queries which return via spatial index. It's been a while since I last benchmarked this, but queries on fragmented tables were an order of magnitude or two slower. This doesn't change the ability to index, just the efficiency of doing so. – Vince Apr 10 at 12:58
  • 1
    Well, yes, but the question was about whether it was technically possible, rather than performance per se. You can also use the CLUSTER command on a GIST (spatial index), see, for example, this post. – John Powell Apr 10 at 13:22
  • Thanks for all your comments! All combined they deliver great information and already think a step further then I did. If someone cares to turn them into an answer, I'll gladly accept it. – blabbath Apr 10 at 13:38

Your Answer

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

Browse other questions tagged or ask your own question.