[postgis-users] some questions about PostGIS internals

[Paul Ramsey]

On Wednesday, February 4, 2004, at 02:57 AM, bartvde at xs4all.nl wrote:

> 1) somewhere I read that the GiST indexes in PostgreSQL could only deal
> with 1 concurrent user. Is this correct? And did PostGIS make a new
> implementation of GiST differing from the PostgreSQL one to overcome 
> this?
> How does PostGIS make sure it gets used in the Explain Plan etc. from
> PostgreSQL?

This is a similar question to 3. PostGIS spatial indexes are build on 
PostgreSQL GiST indexes.  PostgreSQL GiST indexes support table-level 
locking only. So if you have two nearly simultaneous updates/inserts, 
one will block until the other is finished. It just means that spatial 
tables will not scale as well as non-spatial tables under concurrent 
update/insert load.

It is worth noting that the GiST maintainers are currently working on 
more atomic locking code, which may end up in the 7.5 release. If you 
have an enterprise need for that functionality, you should consider 
funding them, to ensure they get it done and soon.

> 2) is the geometry column in PostGIS a real database column? Does it 
> make
> use of the Spatial types in PostgreSQL?

What does this mean, "real"? It is a real type, like any other 
PostgreSQL type. PostgreSQL supports full custom types and functions, 
so an add-on like PostGIS can add functionality to the system. Types 
and functions from PostGIS are as real as the built in types and 
functions.

No, we do not use the PostgreSQL spatial types. You can read some of 
the early justifications in the early postings on the PostGIS list. 
Basically, their semantics were not rich enough to support GIS (no 
holes, no aggregates).

> 3) is there only table level locking or also row level locking (like it
> says in the TODO list)? Or did this already get implemented in the
> database because of the recent Geoserver development?

As above, the locking for tables with a spatial index is table level. 
If there is no spatial index, then the locking could be row-level. 
Locking limitations really only slow you down if your have concurrent 
inserts/updates.

GeoServer development is different, because GeoServer WFS locking 
semantics are slightly different from DBMS semantics. GeoServer 
supports locking of rows, for long transactions, using a special 
locking table. This does not improve true concurrency at all, because 
the underlying tables and locks are still PostgreSQL, so they still 
have the same limitations. It does allow user A to edit rows 9-10 while 
user B has rows 15-16 locked, because the locking mechanism is not the 
internal database locks. The GeoServer locking is "concurrent" in that 
you can have write operations on some rows while others are locked.  It 
is not "concurrent" in that truly simultaneous operations (simultaneous 
in the time axis) will block one operation until the other clears, due 
to the PostgreSQL backend locking. Clear as mud? It should be. It would 
take about a 20 minute presentation to explain the semantics of this to 
someone without the background knowledge.

      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632