Re: Postgres as In-Memory Database?

Hi Jeff and Martin

On 18. November 2013 17:44 Jeff Janes <jeff(dot)janes(at)gmail(dot)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(dot)janes(at)gmail(dot)com> wrote:

> >> On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller <sfkeller(at)gmail(dot)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(dot)janes(at)gmail(dot)com>

> On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>
>> 2013/11/18 Andreas Brandl <ml(at)3(dot)141592654(dot)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
>