Improve database performance

Azeem Shaikh — Mon, 17 Nov 2025 18:26:35 +0000

You can improve database performance by introducing indexing and verifying aws logs.

Create new Database in Postgres

Azeem Shaikh — Sun, 16 Nov 2025 19:25:41 +0000

Pre-requisites

You have PostgreSQL installed in your system.
You have root privileges

Logging into psql as root

Open your terminal and Enter the following commands -
sudo -i -u postgres
Enter your password and you should login into something like this -

Enter psql and press enter. You have entered the psql terminal.

Creating the database

First, you need to have a user with which you will login to the database.
CREATE USER new_username WITH ENCRYPTED PASSWORD 'your_secure_password';
Once the user is created, we will create the database -
CREATE DATABASE database_name;
Now we will grant the required permissions -
GRANT ALL PRIVILEGES ON DATABASE database_name TO new_username;

Press \q to exit the psql terminal and exit the postgres session.

Verify login

Run the below command to verify whether you are able to login to the database -
psql -U test_user my_blog_db_test
We get an error when we run this -
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "test_user"

Fix

This error arises because postgres is set to use peer authentication by default. We need to change this to md5 in /etc/postgresql/14/main/pg_hba.conf

Open /etc/postgresql/14/main/pg_hba.conf in the editor of your choice.
Search for local all postgres peer in the file
Add this line below the postgres config - local all test_user md5
Restart the postgres service. sudo systemctl restart postgresql

Congratulations! You have successfully created your database.