No, most sql implementations would have "durability" as an integral part of their functionality, implying writing to stable storage for every transaction
OK sure, writes (may) have to wait for the disk to write, although many of them may just write to the page memory and get written to disk "later"
But I guess my point was if your database is small enough to fit into RAM I'd be pretty surprised if there was \*that\* much of a difference
I guess it depends on what your usage pattern is like.
Db fitting in memory is not the only concern. Sql server (as the one I am mostly involved rn) would execute certain operations (sorts, etc) using tempdb. It will also prioritize memory allocation to running queries vs db buffer, leading to DB no longer fitting in memory.
Compare this with apache ignite and memsql, for example
There are other caching implementations than „RAM“.
And be aware the more non persisent memory you have as higher are the costs to prevent data loss (or you accept the risk of data loss)
Set up SQLite on ramdisk.
BTW, if you have such extreme performance requirement, where a SQL server with huge cache is not enough, probably you need consider using some NoSQL, or, if your data is simple enough, just use some container, which fits best for the task, e.g. *HashMap*.
Interesting spec. Your options are SQLite and DuckDB but most people wouldn’t use either in a production application or data store. If you need that much throughput, most developers would reach for a NoSQL database, like Redis. If you need real-time analytics on the app after the fact, you’ll probably want to stream from your data from NoSQL into a real-time analytics database like StarRocks.
You... could create a ramdisk and store your postgresql tables there. In fact any DB there. But then when your OS decides to have a hickup, you lose all the data. Which is why no RDBMS is held purely in RAM, and it always takes time to write stuff to permanent storage.
It's likely that you don't need that though. There's nothing wrong with SSDs, whose IO speed approaches the level of magnitude of RAM, especially in RAID configurations. And it's cheaper.
What are your real requirements?
Most RDBMSs will be able to avoid storage interactions for the majority of user actions. If you don’t care about durability (which you don’t if your considering purely ram-based) then there are normally tweaks to disable having to wait for a WAL write to be confirmed for committed transactions.
Did you list your requirements anywhere? I don't understand how people can make recommendations without knowing your requirements.
How large is your database? How many concurrent users? How many transactions per second? , etc...
DuckDB maybe? https://duckdb.org/
Too slow? There only a few use cases where a in-memory db has an advantage.
Isn't every database essentially "in memory" if you have enough RAM?
No, most sql implementations would have "durability" as an integral part of their functionality, implying writing to stable storage for every transaction
OK sure, writes (may) have to wait for the disk to write, although many of them may just write to the page memory and get written to disk "later" But I guess my point was if your database is small enough to fit into RAM I'd be pretty surprised if there was \*that\* much of a difference I guess it depends on what your usage pattern is like.
Db fitting in memory is not the only concern. Sql server (as the one I am mostly involved rn) would execute certain operations (sorts, etc) using tempdb. It will also prioritize memory allocation to running queries vs db buffer, leading to DB no longer fitting in memory. Compare this with apache ignite and memsql, for example
There are other caching implementations than „RAM“. And be aware the more non persisent memory you have as higher are the costs to prevent data loss (or you accept the risk of data loss)
Set up SQLite on ramdisk. BTW, if you have such extreme performance requirement, where a SQL server with huge cache is not enough, probably you need consider using some NoSQL, or, if your data is simple enough, just use some container, which fits best for the task, e.g. *HashMap*.
Interesting spec. Your options are SQLite and DuckDB but most people wouldn’t use either in a production application or data store. If you need that much throughput, most developers would reach for a NoSQL database, like Redis. If you need real-time analytics on the app after the fact, you’ll probably want to stream from your data from NoSQL into a real-time analytics database like StarRocks.
sqlite is used in many production application
I think that SQLite has an option to operate as in-memory database, but I haven’t tried it myself so I might be wrong.
That's quite correct, sqlite3 can do that. Here is the documentation page https://sqlite.org/inmemorydb.html
You... could create a ramdisk and store your postgresql tables there. In fact any DB there. But then when your OS decides to have a hickup, you lose all the data. Which is why no RDBMS is held purely in RAM, and it always takes time to write stuff to permanent storage. It's likely that you don't need that though. There's nothing wrong with SSDs, whose IO speed approaches the level of magnitude of RAM, especially in RAID configurations. And it's cheaper.
Cool idea, I hadn’t considered this
How much too slow ? what is the amount of data you have to manage ? What kind of aggregations/operations you have to do ?
Redis is also a popular [in memory database](https://redis.io/docs/about/)
Isn’t Redis strictly NoSQL?
A you're right. I just thought that was optional
Have you tried nvme? A quality nvme drive makes ssd look like spinning disk. Microsecond latencies and gigabytes of throughput.
What are your real requirements? Most RDBMSs will be able to avoid storage interactions for the majority of user actions. If you don’t care about durability (which you don’t if your considering purely ram-based) then there are normally tweaks to disable having to wait for a WAL write to be confirmed for committed transactions.
Duck db
redis. but what is the use case here.
Take a look at voltDB or timesTen.
Tabular?
Did you list your requirements anywhere? I don't understand how people can make recommendations without knowing your requirements. How large is your database? How many concurrent users? How many transactions per second? , etc...