Re: Postgres as In-Memory Database?

Edson Richter <edsonrichter@xxxxxxxxxxx> · Sun, 17 Nov 2013 22:46:23 -0200



    Em 17/11/2013 22:02, Gavin Flower
      escreveu:

    
      On 18/11/13 12:53, Stefan Keller
        wrote:

      
        Hi Martijn
          

          2013/11/17 Martijn van Oosterhout <kleptog@xxxxxxxxx> wrote:

          
          > If your dataset fits in memory
            then the problem is trivial: any decent

            > programming language provides you with all the
            necessary tools to deal

            > with data purely in memory.  
          

          What about Atomicity, Concurrency and
            about SQL query language and the extension mechanisms of
            Postgres? To me, that's not trivial.
           

          > There are also quite a lot of
            databases that cover this area.
          

          Agreed. That's what partially
            triggered my question, It's notably Oracle TimesTen, MS SQL
            Server 2014 (project Hekaton), (distributed) "MySQL
            Cluster", SAP HANA or SQLite >3. To me this rather
            confirms that an architecture and/or configuration for
            in-memory could be an issue also in Postgres. 
          

            The actual architecture of Postgres
              assumes that memory resources are expensive and optimizes
              avoiding disk I/O. Having more memory available affects
              database design e.g. that it can optimize for a working
              set to be stored entirely in main memory. 
            

            --Stefan
            

      [...]

      
      It would allow optimised indexes that store memory pointers of
      individual records, rather than to a block & then search for
      the record - as well as other optimisations that only make sense
      when data is known to be in RAM (and RAM is plentiful).  As
      already big severs can have a TerraByte or more of RAM, that will
      become more & more common place.  I have 32GB on my
      development box.

      
      Cheers,

      Gavin

    
    Yes, those optimizations I was talking about: having database server
    store transaction log in high speed solid state disks and consider
    it done while background thread will update data in slower disks...
    

    There is no reason to wait for fsync in slow disks to guarantee
    consistency... If database server crashes, then it just need to
    "redo" log transactions from fast disk into slower data storage and
    database server is ready to go (I think this is Sybase/MS SQL
    strategy for years).

    
    Also, consider to have lazy loading (current?) or eager loading
    (perhaps, I just learned a bit about pg_warmcache).

    
    And, of course, indexes that would point to pages in disk to memory
    areas when in RAM - as you just mentioned.

    
    Regards,

    
    Edson