Re: Postgres as In-Memory Database?
| От | Stefan Keller |
|---|---|
| Тема | Re: Postgres as In-Memory Database? |
| Дата | |
| Msg-id | CAFcOn29MYEWCzsifmfQUKNMsCZZE8Ko5xHye1UWjbJCMH7nsYg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Postgres as In-Memory Database? (Jeff Janes <jeff.janes@gmail.com>) |
| Ответы |
Re: Postgres as In-Memory Database?
Re: Postgres as In-Memory Database? |
| Список | pgsql-general |
Hi Jeff and Martin
On 18. November 2013 17:44 Jeff Janes <jeff.janes@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]).
>> 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/
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
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.janes@gmail.com>
On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller <sfkeller@gmail.com> wrote:2013/11/18 Andreas Brandl <ml@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
В списке pgsql-general по дате отправления: