PostgreSQL in use

What is in this article in summary?
+ Install postgreSQL on centos 7
+ Secure your database connection via ssh tunnel
+ Some basic guide on psql prompt (How to Switching Over to the postgres Account on server, exit postgres back to linux command prompt, Create a new role, Set password for postgres account, Create a new database, list databases, switch database, show connections … )
+ SELECT / INSERT / UPDATE / DELETE query in postgreSQL
+ Other matters: index, unique index, primary key, check value exist, ON CONFLICT, etc
+ A practical case on select index and non index field
+ Others again: EXPLAIN ANALYZE, performance, Comparison of other database system
+ Handle error

Install Postgre SQL on centos 7

Follow these command step by
# install
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum list postgresql*

# begin install
sudo yum install postgresql11-server -y

#Create a new PostgreSQL database cluster with initdb:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
Use Postgre SQL basic

# Enable PostgreSQL and start service
sudo systemctl start postgresql-11
sudo systemctl enable postgresql-11

Now you ar Done, good job.
You can now check the service by typing
systemctl status postgresql-11

In order to restart postgres service
service postgresql-11 restart

Or
sudo systemctl restart postgresql-11

or
systemctl restart postgresql

Secure your database connection via ssh tunnel

To protect your database, you should stop expose it to the world wide. Disable the default port 5432 and only access via localhost
In detail:
+ Disable remote connection : disable port 5432 with firewall
+ Allow localhost only, access via ssh tunnel

How to Switching Over to the postgres Account on server

use psql command directly :
psql -U user -p port -h IP database_name
Note: -U (capitalized U), not -u (invalid option)

Or Follow the instruction

#Switch over to the postgres account on your server by typing:
sudo -i -u postgres

# You can now access a Postgres prompt immediately by typing:
psql

Result

Alternative method, we can use this simple command
sudo -u postgres psql

To exit postgres back to linux command prompt

Follow the instruction
#Exit out of the PostgreSQL prompt by typing:
\q

#This will bring you back to the postgres Linux command prompt. Now return to your original sudo account with the following:

exit

Result

Create a new role

# Use the below command
sudo -u postgres createuser –interactive
Result:

Set password for postgres account

sudo -u postgres psql
\password

sudo -u test psql
\password

To check how many users on the server, you can run
cat /etc/passwd

Create a new database

sudo -u postgres createdb testdb
Eg
sudo -u postgrestest createdb postgrestest

Or use the following method:
sudo -i -u postges
#psql
createdb testdb

Note that:
To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

How to list databases postgreSQL

Use this command

\l

Result:

How to switch database in postgreSQL

Use the command below
\c database_name

How to create a new table in postgreSQL

CREATE TABLE leads (id INTEGER PRIMARY KEY, name VARCHAR);

eg

CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in (‘north’, ‘south’, ‘west’, ‘east’, ‘northeast’, ‘southeast’, ‘southwest’, ‘northwest’)),
install_date date
);

or

CREATE TABLE courses(
course_id serial primary key,
course_name VARCHAR(255) NOT NULL,
description VARCHAR(500),
published_date date
);

How to show table list

\dt

Result

How to show column in a table

\dS table_name

How to list connection on postgreSQL

SELECT * FROM pg_stat_activity;
Result

If you want to list only active connection, not include idle connection, or on a specific database, use command like this

SELECT * FROM pg_stat_activity WHERE datname = ‘dbname’ and state = ‘active’;

How to insert a row in postgreSQL

Eg:
INSERT INTO playground (type, color, location, install_date) VALUES (‘slide’, ‘blue’, ‘south’, ‘2017-04-28’);
INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2018-08-16’);
INSERT INTO playground (type, color, location, install_date) VALUES (‘test’, ‘yellow’, ‘northwest’, ‘2018-08-16’);

How to UPDATE a row in postgreSQL

UPDATE table_name
SET column1 = value1,
column2 = value2,
…
WHERE condition;

Eg:
UPDATE playground SET location = ‘slide_update’, install_date = ‘2021-01-01’ WHERE type = ‘slide’;
=> ERROR: new row for relation “playground” violates check constraint “playground_location_check”

UPDATE playground SET location = ‘west’, install_date = ‘2021-01-01’ WHERE type = ‘test’;

UPDATE playground SET location = ‘slide_update’, install_date = ‘2021-01-01’ WHERE type = ‘test’;

UPDATE playground SET location = ‘slide_update’ WHERE type = ‘test’;

How to DELETE a row in postgreSQL

Eg
DELETE FROM playground WHERE type = ‘slide’;

SELECT a row in postgreSQL

Eg
SELECT * FROM playground;

About Index in postgreSQL

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

PostgreSQL provides several index types: B-tree, Hash, GiST and GIN. Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:

<
<= = >=
>Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.
The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE ‘foo%’ or col ~ ‘^foo’, but not col LIKE ‘%bar’. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.9 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.

B-tree indexes can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful.

Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator. The following command is used to create a hash index:

CREATE INDEX name ON table USING hash (column);
Caution
Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged.

GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:

<<
&< &>
>>
<<|
&<| |&>
|>>
@>
<@
~=
&&(See Section 9.11 for the meaning of these operators.) Many other GiST operator classes are available in the contrib collection or as separate projects. For more information see Chapter 53.
GiST indexes are also capable of optimizing “nearest-neighbor” searches, such as

SELECT * FROM places ORDER BY location point ‘(101,456)’ LIMIT 10;
which finds the ten places closest to a given target point. The ability to do this is again dependent on the particular operator class being used.

GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like GiST, GIN can support many different user-defined indexing strategies and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:

<@ @>
=
&&

Note: a practical case
We have a postgres database containt a table users with 5064799 row, and 4 GB data in storage
select count(*) from users return result 5064799
This data is big enough for our example to have a valuable result

* Now, we try to select a field indexed :
explain analyze
select * from users u
where id = ‘9552275’

Result:

Index Scan using users_pkey on users u  (cost=0.43..8.45 rows=1 width=275) (actual time=0.069..0.070 rows=1 loops=1)
  Index Cond: (id = 9552275)
Planning time: 0.416 ms
Execution time: 0.164 ms

So it take 0.164 ms

or another try on another indexed field
explain analyze
select * from users u
where username = ‘tranviethuy01’
Result

Index Scan using users_username_key on users u  (cost=0.43..8.45 rows=1 width=275) (actual time=0.315..0.319 rows=1 loops=1)
  Index Cond: ((username)::text = 'tranviethuy01'::text)
Planning time: 0.244 ms
Execution time: 0.375 ms

So it take 0.375 ms this time

* Select a field not indexed :
explain analyze
select * from users u
where phone_number = ‘948769559’

Result :

Seq Scan on users u  (cost=0.00..308698.33 rows=93 width=275) (actual time=7469.160..17117.082 rows=2 loops=1)
  Filter: ((phone_number)::text = '948769559'::text)
  Rows Removed by Filter: 5064797
Planning time: 0.396 ms
Execution time: 17117.191 ms

So it take : 17117.191 ms

So, the above example lead us to a conclusion
Query database on indexed field and not indexed field
Planning time: might be not different
Execution time: not indexed field take 17117.191/0.375, 17117.191/0.164 or 50.000 , 100.000 times

Explain analyze

This is a very powerful tool in postgres
Explain analyze
select * from earth where type = ‘sea’
Order by id desc;

PostgreSQL UNIQUE index

The PostgreSQL UNIQUE index enforces the uniqueness of values in one or multiple columns. To create a UNIQUE index, you can use the following syntax:

CREATE UNIQUE INDEX index_name
ON table_name(column_name, […]);

Use Transactions in postgreSQL

PostgreSQL and other database management system

You can have a point of view here

Resource:
https://www.altexsoft.com/blog/business/comparing-database-management-systems-mysql-postgresql-mssql-server-mongodb-elasticsearch-and-others/

Finally, handle error

Handle error FATAL: Ident authentication failed for user “postgres”

Edit pg_hba.conf config:
Change connection to “trust”

vim /var/lib/pgsql/11/data/pg_hba.conf
If you are using another version of postgresql instead of v11, you can use this command to find where this config locate
find / -name “pg_hba.conf”

Note:
Restart postgresql service after done editing
sudo systemctl restart postgresql-11

Fix sudo command not found

On centos
yum install sudo
On ubuntu
apt-get install sudo

Show pg activity and tune

1. Using pg_stat_activity
pg_stat_activity is a table that stores PostgreSQL connection & activity stats. You can get a count of active connections by simply running the following SQL query in PostgreSQL.

postgres#- SELECT * FROM pg_stat_activity;
The above query will show connection stats information about all databases on your system. If you want to see open connections to a specific database, use the following query, which specifies the database name for which you want to see active connections. Replace dbname below with the name of your database.

postgres#- SELECT * FROM pg_stat_activity where datname=’dbname’;

2. Using pgtop
pgtop is a utility that works lip top command but shows activity of PostgreSQL. You can install it with the following command.

$ sudo apt-get install ptop
Once it is installed, you can run it with the following command.

$ sudo pg_top

3. Using pgadmin
You can also use pgAdmin tool to monitor activity of PostgreSQL. Install it with the following command.

$ sudo apt-get install pgadmin4 pgadmin4-apache2
# type in password and use default url
$ pgadmin4
pgAdmin provides a web-based interface & dashboard to help you monitor active connections and other PostgreSQL stats in real-time. Here is a sample snapshot of the pgAdmin4 dashboard.

Reference:
https://www.postgresql.org/docs/

How to List Active Connections in PostgreSQL

Bài viết khác

Build for global scale: AFK scale cube and basic rule to build an application for global scale

REF https://akfpartners.com/growth-blog/scale-cube

Đọc thêm

PostgreSQL : subquery, CTE

What is subquery in PostgreSQL? In PostgreSQL, a subquery is a query that is nested inside another query. The subquery is executed first, and its results are used as input to the outer query. Subqueries can be used in various contexts, such as in the SELECT, WHERE, and HAVING clauses of a query. For example, […]

Đọc thêm

Optimize SQL : rule and todo list

Some rule and todo list to Optimize SQL REF https://www.pgmustard.com/blog/indexing-best-practices-postgresql

Đọc thêm

PostgreSQL Compound indexes

What is Compound indexes in PostgreSQL? A compound index (also known as a composite index or a multi-column index) refers to an index that is created on two or more columns of a table. It allows PostgreSQL to quickly find rows that match a query condition based on the values in multiple columns, which can […]

Đọc thêm

Use AWS to deploy your applications and services

Amazon Web Services (AWS) is a cloud computing platform that provides a wide range of services to help businesses and individuals build and deploy applications in the cloud. AWS offers a variety of services such as compute, storage, databases, networking, security, and more. In this guide, we will walk through the steps to get started […]

Đọc thêm

Use docker to run go project

Docker is a powerful tool that enables developers to create, deploy and run applications in a containerized environment. Using Docker to run Go projects has many advantages, including the ability to isolate your application from the underlying operating system, simplifying the deployment process, and allowing for greater scalability and flexibility. In this guide, we will […]

Đọc thêm