DBA TO CLOUD AND BIGDATA SOLUTION: PostgreSQL

Effective_cache_size:

Set the planners assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index. A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL’s shared buffers and the portion of the kernel’s disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache, it is used only for estimation purposes. The system also doest not assume data remains in the disk cache between queries. The default is 4 gigabytes (4GB).

The same rough of thumb that would put shared_buffers at 25% of system memory would set effective_cache_size to between 50% and 75% of RAM. To get a more accurate estimate, first observe the size of the file system cache.

The Practical impact of effective_cache_size:-

The cost of this query is 0.43.. 99923664

we set effective_cache_size to 10000 MB size then execute the query statement.

Now you can see the 0.43..109179.59

INNER JOIN

firstdb=# SELECT * from PERSON

JOIN

CAR ON person.car_id=car.id;

firstdb=#

-[ RECORD 1 ]----+-----------------

id | 1

first_name | Fernanda

last_name | Beardon

email | hello1@gmail.com

gender | Female

date_of_birth | 1952-10-02

country_of_birth | india

car_id | 10

id | 10

make | honda

model | onda

price | 12600.00

-[ RECORD 2 ]----+-----------------

id | 2

first_name | Ashok

last_name | Beardon

email | hello2@gmail.com

gender | Female

date_of_birth | 1952-10-02

LEFT JOIN

SELECT * from person;

----+------------+-----------+------------------+--------+---------------+------------------+--------

(4 rows)

(END)

select * from car;

id | make | model | price

----+------------+----------+----------

10 | honda | onda | 12600.00

11 | Land Rover | Sterling | 87665.38

12 | GMC | Acadia | 17662.69

(3 rows)

firstdb=# ^C

firstdb=#

++++++++++++++++++++

How to find out the records who don;t have foreign key constraints by using Left join, the steps are below :-

SELECT * from person WHERE car_id is NULL;

firstdb-# ----+------------+-----------+---------------+--------+---------------+------------------+--------

firstdb-# (1 row)

firstdb-#

Vimal don't have car id , he is not owning car. But when you use Left join, it will shows all records including who don;t have foreign key constraints.

The below JOIN statement will retrieve records with constraints only.

SELECT * FROM person

JOIN car ON car.id=person.car_id;

----+------------+-----------+------------------+--------+---------------+------------------+--------+----+------------+----------+----------

1 | Fernanda | Beardon | hello1@gmail.com | Female | 1952-10-02 | india | 10 | 10 | honda | onda | 12600.00

2 | Ashok | Beardon | hello2@gmail.com | Female | 1952-10-02 | india | 11 | 11 | Land Rover | Sterling | 87665.38

3 | Ashok | Beardon | hello2@gmail.com | Female | 1952-10-02 | india | 12 | 12 | GMC | Acadia | 17662.69

(3 rows)

LEFT JOIN gives all records :-

SELECT * FROM person

LEFT JOIN car ON car.id=person.car_id;

----+------------+-----------+------------------+--------+---------------+------------------+--------+----+------------+----------+----------

1 | Fernanda | Beardon | hello1@gmail.com | Female | 1952-10-02 | india | 10 | 10 | honda | onda | 12600.00

2 | Ashok | Beardon | hello2@gmail.com | Female | 1952-10-02 | india | 11 | 11 | Land Rover | Sterling | 87665.38

3 | Ashok | Beardon | hello2@gmail.com | Female | 1952-10-02 | india | 12 | 12 | GMC | Acadia | 17662.69

(4 rows)

(END)

DELETING RECORDS WHEN YOUR HAVING THE FORIGN KEY

1. you can delete the records from person who don’t have foreign key constraints

2. you can make this car id is null then you can delete the records.

firstdb=# DELETE FROM car where id=12;

ERROR: update or delete on table "car" violates foreign key constraint "person_car_id_fkey" on table "person"

DETAIL: Key (id)=(12) is still referenced from table "person".

firstdb=#

step 1:-

firstdb=# DELETE FROM person WHERE id=3;

DELETE 1

step 2:-

firstdb=# DELETE FROM car where id=12;

DELETE 1

firstdb=#

now you have deleted the records from CAR table after deleting child record from person table.

Exporting Query results in CSV FORMAT: -

SELECT * FROM person;

ashokkumar@worldpgdb:~/Documents$ mkdir -p export_rec

ashokkumar@worldpgdb:~/Documents$ chmod -R 7777 export_rec

firstdb=# \copy (SELECT * FROM person LEFT JOIN car on car_id=person.car_id) TO '/home/ashokkumar/Documents/export_rec/results.csv' DELIMITER ',' csv HEADER;

COPY 4

firstdb=#

results.csv opened in LibreOffice in ubuntu

SERIAL & SEQUENCE NUMBERS

Table "public.person"

Column | Type | Collation | Nullable | Default

------------------+------------------------+-----------+----------+------------------------------------

id | bigint | | not null | nextval('person_id_seq'::regclass)

first_name | character varying(50) | | not null |

last_name | character varying(50) | | not null |

email | character varying(150) | | |

gender | character varying(50) | | not null |

date_of_birth | date | | not null |

country_of_birth | character varying(150) | | not null |

car_id | bigint | | |

Indexes:

"person_pkey" PRIMARY KEY, btree (id)

"person_car_id_key" UNIQUE CONSTRAINT, btree (car_id)

Foreign-key constraints:

"person_car_id_fkey" FOREIGN KEY (car_id) REFERENCES car(id)

(END)

----+------------+-----------+------------------+--------+---------------+------------------+--------

(2 rows)

firstdb=# SELECT nextval('person_id_seq'::regclass);

nextval

---------

(1 row)

firstdb=# SELECT * FROM person_id_seq;

last_value | log_cnt | is_called

------------+---------+-----------

9 | 24 | t

(1 row)

firstdb=#

firstdb=# select * from car;

id | make | model | price

----+------------+----------+----------

10 | honda | onda | 12600.00

11 | Land Rover | Sterling | 87665.38

(2 rows)

firstdb=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth,car_id) values ('Vicky', 'Priya', 'vpriya@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia',11);

ERROR: duplicate key value violates unique constraint "person_car_id_key"

DETAIL: Key (car_id)=(11) already exists.

firstdb=# insert into car (id,make,model,price)

firstdb-# values

firstdb-# (16,'Honda','i 10',60000);

INSERT 0 1

firstdb=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth,car_id) values ('Vicky', 'Priya', 'vpriya@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia',16);

INSERT 0 1

firstdb=#

ALTER sequence person_id_seq RESTART WITH 10;

firstdb=# ALTER sequence person_id_seq RESTART WITH 10;

ALTER SEQUENCE

firstdb=#

firstdb=# SELECT * FROM person_id_seq;

last_value | log_cnt | is_called

------------+---------+-----------

10 | 0 | f

(1 row)

firstdb=#

firstdb=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Revathy', 'Ashok', 'reva@gmail.com', 'Female', '10/10/1989', 'Oman');

INSERT 0 1

firstdb=#

select * from person where first_name='Revathy';

firstdb=#

sequence id taken as 10.

----+------------+-----------+----------------+--------+---------------+------------------+--------

(1 row)

firstdb=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Poornima', 'Ashok', 'poorna@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia');

INSERT 0 1

----+------------+-----------+----------------------+--------+---------------+------------------+--------

(1 row)----+----------------------+--------+---------------+------------------+--------

(1 row)

----+------------+-----------+----------------------+--------+---------------+------------------+--------

(1 row)

EXTENSION#

Postgresql is designed to be easily extensible. For this reason,extension loaded into the database can function just like features that are built in.

SELECT * FROM pg_available_extenstions;

name | default_version | installed_version | comment

--------------------+-----------------+-------------------+----------------------------------------------------------------------

pgrowlocks | 1.2 | | show row-level locking information

moddatetime | 1.0 | | functions for tracking last modification time

earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth

intagg | 1.1 | | integer aggregator and enumerator (obsolete)

btree_gin | 1.3 | | support for indexing common datatypes in GIN

ltree | 1.1 | | data type for hierarchical tree-like structures

tcn | 1.0 | | Triggered change notifications

dict_int | 1.0 | | text search dictionary template for integers

dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing

btree_gist | 1.5 | | support for indexing common datatypes in GiST

pageinspect | 1.7 | | inspect the contents

UNDERSTANDING UUID datatype:

Universally Unique Identifiers (UUID)

DBA TO CLOUD AND BIGDATA SOLUTION

Saturday, September 28, 2019

Effective_Cache_size parameter Tuning

Effective_cache_size:

The Practical impact of effective_cache_size:-

Tuesday, August 20, 2019

PostgreSQL Join Query, Query Results in CSV format

Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

BLOCKROLL

Report Abuse