Hi Jeff and Martin On 18. November 2013 17:44 Jeff Janes <jeff.ja...@gmail.com> wrote: > I rather doubt that. All the bottlenecks I know about for well cached read-only workloads are around > locking for in-memory concurrency protection, and have little or nothing to do with secondary storage.
Interesting point. But I think this is only partially the case - as Stonebraker asserts [1]. While I don't see how to speed-up locking (and latching), AFAIK there is quite some room for enhancement in buffer pooling (see also [2]). Especially in GIS environments there are heavy calculations and random access operations - so buffer pool will play a role. To Martin: Stonebraker explicitly supports my hypothesis that in-memory databases become prevalent in the future and that "elephants" will be challenged if they don't adapt to new architectures, like in-memory and column stores. The specific use case here is a PostGIS query of an OpenStreetMap data of the whole world (see [3]). On 2013/11/18 Jeff Janes <jeff.ja...@gmail.com> wrote: > >> On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller <sfkel...@gmail.com> wrote: >> BTW: Having said (to Martijn) that using Postgres is probably more efficient, than programming an in-memory > database in a decent language: OpenStreetMap has a very, very large Node table which is heavily > used by other tables (like ways) - and becomes rather slow in Postgres. > > Do you know why it is slow? I'd give high odds that it would be a specific implementation detail in > the code that is suboptimal, or maybe a design decision of PostGIS, rather than some high level > architectural decision of PostgreSQL. Referring to the application is something you can always say - but shouldn't prevent on enhancing Postgres. PostGIS extension isn't involved in this use case. In this use case it's about handling a very huge table with a bigint id and two numbers representing lat/lon. As I said, an obvious solution is to access the tupels as fixed length records (which isn't a universal solution - but exploiting the fact that's in-memory). You can replicate this use case by trying to load the planet file into Postgres using osm2pgsql (see [2]). The record currently is about 20 hours(!) I think with 32GB and SSDs. --Stefan [1] Michael Stonebraker: “The Traditional RDBMS Wisdom is All Wrong”: http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ [2] Oracle Database In-Memory Option - A Preview: In-Memory Acceleration for All Applications http://www.oracle.com/us/corporate/features/database-in-memory-option/index.html [3] osm2pgsql benchmark: http://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks 2013/11/18 Jeff Janes <jeff.ja...@gmail.com> > On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller <sfkel...@gmail.com> wrote: > >> 2013/11/18 Andreas Brandl <m...@3.141592654.de> wrote: >> > What is your use-case? >> >> It's geospatial data from OpenStreetMap stored in a schema optimized for >> PostGIS extension (produced by osm2pgsql). >> >> BTW: Having said (to Martijn) that using Postgres is probably more >> efficient, than programming an in-memory database in a decent language: >> OpenStreetMap has a very, very large Node table which is heavily used by >> other tables (like ways) - and becomes rather slow in Postgres. >> > > > Do you know why it is slow? I'd give high odds that it would be a > specific implementation detail in the code that is suboptimal, or maybe a > design decision of PostGIS, rather than some high level architectural > decision of PostgreSQL. > > Cheers, > > Jeff >
