How to Run PostgreSQL in Memory Only

1. Introduction to In-Memory Database
2. PostgreSQL Support Using a Database in Memory or Not

The databases we create are stored on the permanent memory of the hard disk or SSD (Solid State Drive).

However, this memory is slower than RAM (Random Access Memory). Therefore, we sometimes create and use a database in RAM called memory to make it more efficient and faster.

This article discusses how we can use a database in memory in PostgreSQL. However, first, let us study what is meant by running a database in memory and how other platforms allow it.

Introduction to In-Memory Database

An in-memory database (IMDB) is a database that is stored and run on the computer’s RAM or main memory instead of the disk drive. It is done to generate more efficient and quicker performance, as retrieving data from the main memory requires fewer instructions in the CPU.

Therefore, such databases are primarily used in real-time systems and gaming applications since these require a speedy response time.

This speed is guaranteed because it does not involve translation or encryption of the data. This way, data is kept in a form ready to use directly, resulting in a faster response time.

One drawback of using a database in memory is that RAM is volatile. Also, since RAM is not a permanent storage solution, we will lose the data if the power is interrupted or the database or server crashes.

However, technologies such as flash memory and non-volatile random access memory (NVRAM) are used to counter this volatility of in-memory databases. In addition, many SQL platforms support in-memory databases.

For example, SQL Server supports running a database in memory because it has In-Memory OLTP. In the SQL Server, you can store a table in memory by creating it WITH (MEMORY_OPTIMIZED = ON) or similar commands.

But the notable point is can you do it in PostgreSQL? This article includes the answer to this question.

PostgreSQL Support Using a Database in Memory or Not

Unfortunately, PostgreSQL does not support using a database or any of its components in memory. The main reason is that PostgreSQL is implemented in C, a compiled language.

However, running a database in memory is a beneficial quality, primarily used in unit testing to speed up the results. So how do developers do this in PostgreSQL if no in-memory database support exists?

We will look at some alternatives in this article. One way to do this is to create tablespaces. Let’s look at what these are and whether they are a good alternative or not.

Use TABLESPACE in PostgreSQL

A tablespace in PostgreSQL is a database object whose location can be defined by the database administrator during creation. The tablespace helps allocate memory locations according to the efficiency needs of the data.

For example, we can use tablespace to store frequently accessed tables in faster memory, such as SSD, and lesser needed data in slower memory, such as hard disks.

Similarly, we can use tablespace to store some data in RAM, thus allowing us the same effect as using a database in memory. The syntax for creating tablespaces is as follows:

CREATE TABLESPACE tablespace_name LOCATION '/ssd/postgresql/data';

After the LOCATION keyword, we add the symbolic link of the address where we want to create the tablespace. However, the use of tablespaces in memory in PostgreSQL is not recommended. It is even added as a warning in the official PostgreSQL documentation.

Because of the volatility of RAM, all the data in the database cluster will become unreadable if you lose your tablespace due to system crashes or accidental deletion. Therefore, using a tablespace in memory is not a good practice.

So, what options does it leave for us? First, let us look at another alternative that we can consider: creating a throwaway PostgreSQL instance for faster response times.

Create a Throwaway PostgreSQL Instance

To understand this method, let us consider the example of unit testing.

Testing a database requires fast and efficient performance speed. One way you can do this in PostgreSQL is to create a disposable PostgreSQL instance which will be deleted after use.

This method requires using the initdb command to create a new database cluster in PostgreSQL. A database cluster is a group of databases run on the same server.

The syntax of the initdb command is as follows:

initdb [option...] [ --pgdata | -D ] directory

An example of how we can use this command, create a new database cluster, as shown in the following query:

initdb --auth=trust --username=postgres -D /path/to/temp/datadir

This command helps create a temporary storage database cluster. Moreover, it grants trust to the default postgres database.

Now, you must connect to the database cluster created as the user postgres specified in the query above. After this, it is time for you to perform creation commands, such as CREATE USER or CREATE DATABASE, and then hand over your code to the unit tests.

After completing the tests, you will use the pg_ctl command to stop the server and delete the datadir data directory. This process will allow faster response times while testing, similar to running a database in memory.

Therefore, we can conclude that PostgreSQL does not support using a database in memory, unlike the SQL Server or MySQL.

We can create an in-memory tablespace by specifying its location in the RAM, but that is not recommended since it may cause severe data loss, which can take a lot of effort to reverse.

To speed up response time in PostgreSQL for testing, we may create a throwaway PostgreSQL instance and configure it accordingly. We hope we were able to clarify these concepts for you. Keep learning!