Thread: Postgres as In-Memory Database?

On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> How can Postgres be used and configured as an In-Memory Database?
>
> Does anybody know of thoughts or presentations about this "NoSQL feature" -
> beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)?
>
> Given, say 128 GB memory or more, and (read-mostly) data that fit's into
> this, what are the hints to optimize Postgres (postgresql.conf etc.)?
In this case as you are trading system safety (system will not be
crash-safe) for performance... The following parameters would be
suited:
- Improve performance by reducing the amount of data flushed:
fsync = off
synchronous_commit=off
- Reduce the size of WALs:
full_page_writes = off
- Disable the background writer:
bgwriter_lru_maxpages = 0
Regards,
--
Michael

Em 17/11/2013 10:00, Michael Paquier escreveu:
> On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> How can Postgres be used and configured as an In-Memory Database?
>>
>> Does anybody know of thoughts or presentations about this "NoSQL feature" -
>> beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)?
>>
>> Given, say 128 GB memory or more, and (read-mostly) data that fit's into
>> this, what are the hints to optimize Postgres (postgresql.conf etc.)?
> In this case as you are trading system safety (system will not be
> crash-safe) for performance... The following parameters would be
> suited:
> - Improve performance by reducing the amount of data flushed:
> fsync = off
> synchronous_commit=off
> - Reduce the size of WALs:
> full_page_writes = off
> - Disable the background writer:
> bgwriter_lru_maxpages = 0
> Regards,
One question: would you please expand your answer and explain how would
this adversely affect async replication?

Edson

On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote:
> How can Postgres be used and configured as an In-Memory Database?
>
>
> Does anybody know of thoughts or presentations about this "NoSQL
> feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon
> 2010)?
>
>
> Given, say 128 GB memory or more, and (read-mostly) data that fit's
> into this, what are the hints to optimize Postgres (postgresql.conf
> etc.)?
>
>
> -- Stefan

Not as being completely "in memory".
Back in the "good ol'days" of DMS II (written in Algol and ran on
Burroughs mainframes) and Linc II (also Burroughs) it was possible to
define certain tables as being memory resident. This was useful for low
volatile data such as salutations, street types, county or state codes,
time zones, preferred languages, etc.
It saved disk I/O twice. Firstly building your drop down lists and
secondly when the entered data hit the server and was validated.
It would be good to have a similar feature in PostgreSql.
If a table was altered by, say inserting a new street type, then the
data base engine has to refresh the cache. This is the only overhead.

Cheers,
Robert

Em 17/11/2013 12:15, rob stone escreveu:
>
> On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote:
>> How can Postgres be used and configured as an In-Memory Database?
>>
>>
>> Does anybody know of thoughts or presentations about this "NoSQL
>> feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon
>> 2010)?
>>
>>
>> Given, say 128 GB memory or more, and (read-mostly) data that fit's
>> into this, what are the hints to optimize Postgres (postgresql.conf
>> etc.)?
>>
>>
>> -- Stefan
> Not as being completely "in memory".
> Back in the "good ol'days" of DMS II (written in Algol and ran on
> Burroughs mainframes) and Linc II (also Burroughs) it was possible to
> define certain tables as being memory resident. This was useful for low
> volatile data such as salutations, street types, county or state codes,
> time zones, preferred languages, etc.
> It saved disk I/O twice. Firstly building your drop down lists and
> secondly when the entered data hit the server and was validated.
> It would be good to have a similar feature in PostgreSql.
> If a table was altered by, say inserting a new street type, then the
> data base engine has to refresh the cache. This is the only overhead.
>
> Cheers,
> Robert

For this purpose (building drop down lists, salutations, street types,
county or state codes), I keep a permanent data cache at app server side
(after all, they will be shared among all users - even on a multi tenant
application). This avoids network connection, and keep database server
memory available for database operations (like reporting and transactions).
But I agree there are lots of gaings having a "in memory" option for
tables and so. I believe PostgreSQL already does that automatically
(most used tables are kept in memory cache).

Edson.

Hi Edson

As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsoleteand interesting to discuss (but that was'nt exactly what I asked above).

Hi, Stephan,

I don't think any feature you add to database server would bring obsolescence to app server caches: app server caches have just no lag at all:

1) Don't need network connection to database server
2) Don't need to materialize results (for instance, I have in mind a Java or .Net app server running hundred thousands of objects in memory).

IMHO, no matter how much you improve database, app server caches provides additional level of speed that cannot be achieved by database.

That said, I still can see huge improvements in database server.
Having strong in memory operation would bring substantial improvements.
For instance, if you have in-memory database (tables, indexes, etc) for all sort of queries, and just **commit** to disks, then you will have unprecedent performance.
I would get benefit from this architecture, since typical customer database has < 64Gb on size (after 2 or 3 years of data recording). So, a database server with 64Gb of memory would keep everything in memory, and just commit data to disc.

In this case, commited data would be instantly available to queries (because they are all in memory) while log (changes) is recorded in a fast disk (a SSD, perhaps) and then those changes are made persistent data, written async into slow massive disks (SCSI or SAS).

This would allow also a hybrid operation (too keep as much data pages as possible in memory, with a target of 50% or more in memory).

When database server is started, it would have lazy load (data is loaded and kept in memory as it is used) or eager load (for slower startup but faster execution).

May be I'm just wondering too much, since I don't know PostgreSQL internals...


Regards,

Edson

--Stefan

 

[1] http://www.sqlite.org/inmemorydb.html

[2] http://www.postgresql.org/docs/9.1/static/non-durability.html

2013/11/17 Edson Richter <edsonrichter@hotmail.com>

Em 17/11/2013 12:15, rob stone escreveu:

On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote:

How can Postgres be used and configured as an In-Memory Database?


Does anybody know of thoughts or presentations about this "NoSQL
feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon
2010)?


Given, say 128 GB memory or more, and (read-mostly) data that fit's
into this, what are the hints to optimize Postgres (postgresql.conf
etc.)?


-- Stefan

Not as being completely "in memory".
Back in the "good ol'days" of DMS II (written in Algol and ran on
Burroughs mainframes) and Linc II (also Burroughs) it was possible to
define certain tables as being memory resident. This was useful for low
volatile data such as salutations, street types, county or state codes,
time zones, preferred languages, etc.
It saved disk I/O twice. Firstly building your drop down lists and
secondly when the entered data hit the server and was validated.
It would be good to have a similar feature in PostgreSql.
If a table was altered by, say inserting a new street type, then the
data base engine has to refresh the cache. This is the only overhead.

Cheers,
Robert

For this purpose (building drop down lists, salutations, street types, county or state codes), I keep a permanent data cache at app server side (after all, they will be shared among all users - even on a multi tenant application). This avoids network connection, and keep database server memory available for database operations (like reporting and transactions).
But I agree there are lots of gaings having a "in memory" option for tables and so. I believe PostgreSQL already does that automatically (most used tables are kept in memory cache).

Edson.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Nov 17, 2013 at 10:33:30PM +0100, Stefan Keller wrote:
> I think I have to add, that pure speed of a read-mostly database is the
> main scenario I have in mind.
> Duration, High-availability and Scaling out are perhaps additional or
> separate scenarios.
>
> So, to come back to my question: I think that Postgres could be even faster
> by magnitudes, if the assumption of writing to slow secondary storage (like
> disks) is removed (or replaced).

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.  There are also quite a lot of databases
that cover this area.

PostgreSQL excels in the area where your data is much larger than your
memory. This is a much more difficult problem and I think one worth
focussing on. Pure in memory databases are just not as interesting.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

Edson,

> Em 17/11/2013 19:26, Stefan Keller escreveu:
> > Hi Edson
> >
> > As Rob wrote: Having a feature like an in-memory table like SQLite
> > has
> > [1] would make application cahces obsoleteand interesting to
> > discuss
> > (but that was'nt exactly what I asked above).
>
> Hi, Stephan,
>
> I don't think any feature you add to database server would bring
> obsolescence to app server caches: app server caches have just no lag
> at
> all:
>
> 1) Don't need network connection to database server
> 2) Don't need to materialize results (for instance, I have in mind a
> Java or .Net app server running hundred thousands of objects in
> memory).
>
> IMHO, no matter how much you improve database, app server caches
> provides additional level of speed that cannot be achieved by
> database.
>
> That said, I still can see huge improvements in database server.
> Having strong in memory operation would bring substantial
> improvements.
> For instance, if you have in-memory database (tables, indexes, etc)
> for
> all sort of queries, and just **commit** to disks, then you will have
> unprecedent performance.
> I would get benefit from this architecture, since typical customer
> database has < 64Gb on size (after 2 or 3 years of data recording).
> So,
> a database server with 64Gb of memory would keep everything in
> memory,
> and just commit data to disc.
>
> In this case, commited data would be instantly available to queries
> (because they are all in memory) while log (changes) is recorded in a
> fast disk (a SSD, perhaps) and then those changes are made persistent
> data, written async into slow massive disks (SCSI or SAS).
>
> This would allow also a hybrid operation (too keep as much data pages
> as
> possible in memory, with a target of 50% or more in memory).
>
> When database server is started, it would have lazy load (data is
> loaded
> and kept in memory as it is used) or eager load (for slower startup
> but
> faster execution).

not sure I fully understand your point. Isn't this the typical mode-of-operation plus added cache warming?

Anyways, just wanted to point you to [1] which gives a good overview of cache warming techniques.

Regards,
Andreas

[1] http://raghavt.blogspot.fr/2012/04/caching-in-postgresql.html

Em 17/11/2013 20:46, Andreas Brandl escreveu:
> Edson,
>
>> Em 17/11/2013 19:26, Stefan Keller escreveu:
>>> Hi Edson
>>>
>>> As Rob wrote: Having a feature like an in-memory table like SQLite
>>> has
>>> [1] would make application cahces obsoleteand interesting to
>>> discuss
>>> (but that was'nt exactly what I asked above).
>> Hi, Stephan,
>>
>> I don't think any feature you add to database server would bring
>> obsolescence to app server caches: app server caches have just no lag
>> at
>> all:
>>
>> 1) Don't need network connection to database server
>> 2) Don't need to materialize results (for instance, I have in mind a
>> Java or .Net app server running hundred thousands of objects in
>> memory).
>>
>> IMHO, no matter how much you improve database, app server caches
>> provides additional level of speed that cannot be achieved by
>> database.
>>
>> That said, I still can see huge improvements in database server.
>> Having strong in memory operation would bring substantial
>> improvements.
>> For instance, if you have in-memory database (tables, indexes, etc)
>> for
>> all sort of queries, and just **commit** to disks, then you will have
>> unprecedent performance.
>> I would get benefit from this architecture, since typical customer
>> database has < 64Gb on size (after 2 or 3 years of data recording).
>> So,
>> a database server with 64Gb of memory would keep everything in
>> memory,
>> and just commit data to disc.
>>
>> In this case, commited data would be instantly available to queries
>> (because they are all in memory) while log (changes) is recorded in a
>> fast disk (a SSD, perhaps) and then those changes are made persistent
>> data, written async into slow massive disks (SCSI or SAS).
>>
>> This would allow also a hybrid operation (too keep as much data pages
>> as
>> possible in memory, with a target of 50% or more in memory).
>>
>> When database server is started, it would have lazy load (data is
>> loaded
>> and kept in memory as it is used) or eager load (for slower startup
>> but
>> faster execution).
> not sure I fully understand your point. Isn't this the typical mode-of-operation plus added cache warming?
>
> Anyways, just wanted to point you to [1] which gives a good overview of cache warming techniques.
>
> Regards,
> Andreas
>
> [1] http://raghavt.blogspot.fr/2012/04/caching-in-postgresql.html
>
>
Worndeful, never knew about it.
I'm ready ASAP.

Regards

Edson

Hi Stefan,

> How can Postgres be used and configured as an In-Memory Database?
>

we've put the data directory on our buildserver directly on a ramdisk (e.g. /dev/shm) to improve build times.

Obviously you then don't care too much about durability here, so one can switch off all related settings (as has
alreadybeen pointed out). The only thing to do on a server reboot would be to re-create a fresh data directory on the
ramdisk.

So if you're able to start from scratch relatively cheap (i.e. on a server reboot), don't care about durability/crash
safetyat all and your database fits into ram that solution is easy to handle.  

I've also tried having only a separate tablespace on ramdisk but abandoned the idea because postgres seemed too
surprisedto see the tablespace empty after a reboot (all tables gone). 

Overall the above solution works and improves our build times but I think there are better ways to have
in-memory/applicationcaches than using a postgres.  

What is your use-case?

Regards
Andreas

Hi Martijn

2013/11/17 Martijn van Oosterhout <kleptog@svana.org> 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

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

Hi Martijn

2013/11/17 Martijn van Oosterhout <kleptog@svana.org> 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

On 11/17/2013 4:46 PM, Edson Richter wrote:
>
> 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).

you need to fsync that slower disk before you can purge the older WAL or
redo log, whatever, from your fast storage.   this fsync can, of course,
be quite a ways behind the current commit status.




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Tue, Nov 19, 2013 at 02:39:17AM +0100, Stefan Keller wrote:
> Referring to the application is something you can always say - but
> shouldn't prevent on enhancing Postgres.

With respect, that sounds like a sideways version of, "You should
optimise for $usecase".  You could be right, but I think the judgement
of the Postgres developers has generally been that special cases are
not the mainline case.  And indeed, given the specifics of the use
case you're outlining, it's as much a demonstration of that evaluation
as a repudiation of it.

I don't think there's any evidence that the Postgres developers ignore
useful optimisations.  What you're arguing is that the optimisation
you have in mind isn't covered.  What you need is an argument that it
is generally useful.  Otherwise, the right thing to do is get a
specialised tool (which might be a special optimisation of the
Postgres code).

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 11/17/2013 07:02 PM, Stefan Keller wrote:
> 2013/11/18 Andreas Brandl <ml@3.141592654.de
> <mailto: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. Since it's of fixed length I'm looking at
> file_fixed_length_record_fdw extension [1][2] (which is in-memory) to
> get the best of both worlds.
>
> --Stefan
>
> [1]
> http://wiki.postgresql.org/wiki/Foreign_data_wrappers#file_fixed_length_record_fdw
> [2] https://github.com/adunstan/file_fixed_length_record_fdw


First. please don't top-post on the PostgreSQL lists. See
<http://idallen.com/topposting.html>

Second, what the heck makes you think that this is in any sense
in-memory? You can process a multi-terabyte fixed length file. It's not
held in memory.

cheers

andrew

On Sun, Nov 17, 2013 at 4:46 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

 

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).

Using a nonvolatile write cache for pg_xlog is certainly possible and often done with PostgreSQL.  It is not important that the nonvolatile write cache is fronting for SSD, fronting for HDD is fine as the write cache turns the xlog into pure sequential writes and HDD should not have a problem keeping up.

Cheers,

Jeff

Hum... I agree about the tecnology (SSD x HDD, etc) - but may be I misunderstood, but I have read that to keep always safe data, I must use fsync, and as result every transaction must wait for data to be written in disk before returning as success.
By using the approach I've described you will have fsync (and data will be 100% safe), but transaction is considered success once written in the transaction log that is pure sequencial (and even pre-allocated space, without need to ask OS for new files or new space) - and also no need to wait for slow operations to write data in data pages.

Am I wrong?

Edson

On Tuesday, November 19, 2013, Edson Richter wrote:

Em 19/11/2013 22:29, Jeff Janes escreveu:

On Sun, Nov 17, 2013 at 4:46 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

 

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).

Using a nonvolatile write cache for pg_xlog is certainly possible and often done with PostgreSQL.  It is not important that the nonvolatile write cache is fronting for SSD, fronting for HDD is fine as the write cache turns the xlog into pure sequential writes and HDD should not have a problem keeping up.

Cheers,

Jeff

Hum... I agree about the tecnology (SSD x HDD, etc) - but may be I misunderstood, but I have read that to keep always safe data, I must use fsync, and as result every transaction must wait for data to be written in disk before returning as success.

By using the approach I've described you will have fsync (and data will be 100% safe), but transaction is considered success once written in the transaction log that is pure sequencial (and even pre-allocated space, without need to ask OS for new files or new space) - and also no need to wait for slow operations to write data in data pages.

Am I wrong?

On Tuesday, November 19, 2013, Edson Richter wrote:

Em 19/11/2013 22:29, Jeff Janes escreveu:

On Sun, Nov 17, 2013 at 4:46 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

 

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).

Using a nonvolatile write cache for pg_xlog is certainly possible and often done with PostgreSQL.  It is not important that the nonvolatile write cache is fronting for SSD, fronting for HDD is fine as the write cache turns the xlog into pure sequential writes and HDD should not have a problem keeping up.

Cheers,

Jeff

Hum... I agree about the tecnology (SSD x HDD, etc) - but may be I misunderstood, but I have read that to keep always safe data, I must use fsync, and as result every transaction must wait for data to be written in disk before returning as success.

A transaction must wait for the *xlog* to fsynced to "disk", but non-volatile write cache counts as disk.  It does not need to wait for the ordinary data files to be fsynced.  Checkpoints do need to wait for the ordinary data files to be fsynced, but the checkpoint process is a background process and it can wait for that without impeding user processes.

If the checkpointer falls far enough behind, then things do start to fall apart, but I think that this is true of any system. So you can't just get get a BBU for the xlog and ignore all other IO entirely--eventually the other data does need to reach disk, and if it gets dirtied faster than it gets cleaned for a prolonged period then things will freeze up.

 

By using the approach I've described you will have fsync (and data will be 100% safe), but transaction is considered success once written in the transaction log that is pure sequencial (and even pre-allocated space, without need to ask OS for new files or new space) - and also no need to wait for slow operations to write data in data pages.

Am I wrong?

No user-facing process needs to wait for the data pages to fsync, unless things have really gotten fouled up.

Cheers,

Jeff

Ok, I still have one doubt (I'm learning a lot, tkx!):

What happens, then, if data has been commited (so it is in xlog), but it is not in data pages yet, and it doesn't fit in memory buffers anymore: how would PostgreSQL query data without having to wait for checkpoint happend and data be available in data pages?

Regards,

Edson

On Sun, Nov 17, 2013 at 09:00:05PM +0900, Michael Paquier wrote:
> On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> > How can Postgres be used and configured as an In-Memory Database?
> >
> > Does anybody know of thoughts or presentations about this "NoSQL feature" -
> > beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)?
> >
> > Given, say 128 GB memory or more, and (read-mostly) data that fit's into
> > this, what are the hints to optimize Postgres (postgresql.conf etc.)?
> In this case as you are trading system safety (system will not be
> crash-safe) for performance... The following parameters would be
> suited:
> - Improve performance by reducing the amount of data flushed:
> fsync = off
> synchronous_commit=off
> - Reduce the size of WALs:
> full_page_writes = off
> - Disable the background writer:
> bgwriter_lru_maxpages = 0
> Regards,

FYI, the Postgres manual covers non-durability settings:

    http://www.postgresql.org/docs/9.3/static/non-durability.html

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

On Sunday, March 30, 2014, Stefan Keller <sfkeller@gmail.com> wrote:

Hi Jeff

2013/11/20 Jeff Janes <jeff.janes@gmail.com>

I don't know what you mean about enhancements in the buffer pool.  For an in-memory database, there shouldn't be a buffer pool in the first place, as it is *all* in memory.  

You are right: In-memory DBs are making buffer-pooling obsolete - except for making data persistent (see below).

> 

> 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.

Postgres has been enhanced.  Now we need to change osm2pgsql to take advantage of them.  It defines indexes on the tables that are going to be bulk loaded with COPY, which defeats some recent optimizations made to COPY.  The creation of the indexes should be delayed until after the bulk load is done.  

A further enhancement to Postgres would be would be to automatically defer creation of the indexes when a table is truncated or created within a transaction, so that users get the benefit of the improvement

These enhancements to osm2pgsql seem to be reasonable to me. I hope somebody has time to care about.

In the meantime I discussed with HANA users an thought about what makes in-memory dbs special and how to configure Postgres to be an in-memory db.

There seem to be two main things which make in-memory dbs special: 

1. Index: Having all data in memory there is no need for a default index. A full-table scan "suddenly" becomes the default.

2. Persistence: All updates/changes to the db are streamed to disk.

3. An increase of data simply needs to be compensated with more memory (since memory became cheap).

AFAIK one can configure Postgres for all these properties: One can force the use of full-table scan(?) and replicate to disk(?).

What do you think about this?

Are there any show cases out there?

On 01 Apr 2014, at 4:20, Jeff Janes <jeff.janes@gmail.com> wrote:

> On Sunday, March 30, 2014, Stefan Keller <sfkeller@gmail.com> wrote:
> There seem to be two main things which make in-memory dbs special:
> 1. Index: Having all data in memory there is no need for a default index. A full-table scan "suddenly" becomes the
default.
>
> Surely not.  I would say that full table scans are *already* the default, deviated from only if it thinks an index
seemsto be better.  If you don't make an index, it can't seem to be better.  And I don't know of any higher-level
in-memorylanguage which fails to provide a way to do efficient searching into an in-memory structure, usually in the
formof hash tables or balanced trees.  If "let's seq scan everything as long as it is already in memory" is a good
idea,why would Perl, Java, Python, etc. (not to mention C libraries and the source code of PostgreSQL itself) provide
waysto do efficient searches in memory? 
>
> The caveat here is you can't make certain constraints without an index.  In theory you *could* have an unique
constraintwithout an index to support it, but if it were implemented you would probably rapidly learn that you don't
actuallywant to do that. 

That’s what I thought initially reading that line as well, but that’s not necessarily true for an in-memory database.

The keyword here is “default index”. The reasoning is probably along the lines that if all your data is in-memory, then
keepingit all sorted on the primary column(s) is relatively cheap (compared to doing so on disk). You could even split
offthe primary column(s) from the rest of the records and link back to them using pointers, which makes it easier to
keepthem in a (somewhat) balanced btree. 

When you do that, the data effectively becomes the primary key index and would theoretically also be usable as the
primarykey constraint. So you _do_ have an index, but it’s not a separate one; it’s part of your data. That frees up
memorythat you do not need to preserve for an index, which is probably rather a gain for an in-memory database. 

Apparently this works for HANA, but for an MVCC database like Postgres that’s a little more involved.

Of course, with databases like that you want them replicated on different hardware and kernels to reduce data-loss
risks.And that brings us closer to what ISTR Stonebreaker is doing these days. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Sunday, March 30, 2014, Stefan Keller <sfkeller@gmail.com> wrote:

Hi Jeff

2013/11/20 Jeff Janes <jeff.janes@gmail.com>

I don't know what you mean about enhancements in the buffer pool.  For an in-memory database, there shouldn't be a buffer pool in the first place, as it is *all* in memory.  

You are right: In-memory DBs are making buffer-pooling obsolete - except for making data persistent (see below).

I would be very reluctant to use any database engine which considered disk access obsolete.

The disk is not obsolete but something called 'anti-caching' is used:
http://www.vldb.org/pvldb/vol6/p1942-debrabant.pdf

 

Are there any show cases out there?

What did the HANA users have to say?  Seems like they would be in the best position to provide the test cases.

This paper provides some insights into the research behind HANA http://www.sigmod09.org/images/sigmod1ktp-plattner.pdf

regards
Yeb

On 04/02/2014 12:32 AM, Stefan Keller wrote:

> It also mentions an insert-only technique: "This approach has been
> adopted before in POSTGRES [21] in 1987 and was called "time-travel".
> I would be interested what "time-travel" is and if this is still used by
> Postgres.

Back in the old days, PostgreSQL never deleted any tuples.  Rows were
deleted by writing the deletion time into a column.  As a result, you
could go back to old data just by telling PostgreSQL to report rows
which where visible at a given time.

Obviously, this approach precluded use of PostgreSQL in many scenarios.
  For example, you wouldn't want to use it as your web application
session store.

--
Florian Weimer / Red Hat Product Security Team

On Mon, Apr 07, 2014 at 10:43:58PM +0200, Stefan Keller wrote:
> running out-of-memory [1][2] - i.e. pretty much what has been discussed
> here - although little bit reluctantly :-)

It is just possible that some of the reluctance is because (1) this
has been discussed many times in the past, partly with the arguments
you've already seen in this thread, and with much the same results;
(2) nobody seems to be saying, "I have $n to spend on this effort and
$thesepatches to contribute towards this end along with $thisdesign,"
but instead to be saying, "It'd be nice if someone else did this
work;" and (3) there _are_ several in-memory-only databases on the
market, including free-software ones, so it isn't clear what Postgres
would contribute, especially since its basic design isn't obviously
amenable to this sort of use.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Tue, Apr 08, 2014 at 12:46:37AM +0200, Stefan Keller wrote:
> Hi Andrew
>
> 2014-04-07 23:37 GMT+02:00 Andrew Sullivan wrote:
> > (1) this has been discussed many times in the past (...)
>
> Can you point me to one of these discussions?
>
> Actually, I browsed once again the mailing list and this is one of the few
> posts I found:
> "In-Memory Columnar Store" 9.12.13 by knizhnik.

I think you have to go back in time further than that.  I recall
in-memory table pinning being a recurrent topic during the 8.x series.
I also seem to recall it being mostly on the hackers list.

> I said, that I'd like to discuss things before I code.

Ok.  I think in the past what has been successful is some basic design
combined with a POC or some such, generally discussed on -hackers
since that's where all the people who really know the back end hang
out.  It seems to me that most of the "built in replication" stuff
that ended up happening worked that way, and that seems to be roughly
similar size of work to this, but I haven't thought about it too much.

> To me it's unclear why design of Postgres should prevent implementation of
> "in-memory tables" e.g. as foreign data wrappers (see e.g. white papers
> for SQL Server mentioned before).

I don't think it does.  But new code in the back end isn't free: it
presents a future maintenance burden that others may not be willing to
pay.  These things always have to be traded off.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca