31 Ekim 2019 Perşembe

CREATE INDEX

CREATE INDEX CONCURRENTLY
Açıklaması şöyle
Creating an index is always a resource-intensive operation, so always use the “CONCURRENTLY” parameter so that the creation does not affect the performance of the index source table.
Örnek
Şöyle yaparız
CREATE INDEX CONCURRENTLY index_name ON table_name USING btree (column);

1. Index Tipleri
Index tipleri şöyle
- Balanced Tree (B-Tree)
- Generalized Inverted Index (GIN) : GIN INDEX yazısına taşıdım
- Generalized Inverted Search Tree (GiST)
- Space partitioned GiST (SP-GiST)
- Block Range Indexes (BRIN)

Açıklaması şöyle
You should know your indexes, although 99% of the time, you’re going to use B-Tree indexes, there’s that 1% that can make a huge difference if used right.
Balanced Tree (B-Tree)
Balanced Tree (B-Tree) yazısına taşıdım

Bu index aynı zamanda pattern_ops ile de kullanılabilir. 
- text_pattern_ops : text için
- varchar_pattern_ops : varchar için
- bpchar_pattern_ops : char için

değerlerini alabilir.

text_pattern_ops 
Ne zaman fuzzy search kullanmak gerekir?
Eğer bu iki maddeden birine takılıyorsak pg_trgm Module - Pattern Matching Full Text Search yazısına bakınız. Açıklaması şöyle
The BTree index can only search smaller strings with either direct match or prefix/suffix match with slightly better performance. But in the real world, users often misspell words, and it gets pretty hard to search for them. This is where the fuzzy search capabilities of PostgreSQL come in. They can search strings with similar words and do not have the size limitations of BTree indexes.
1. Eğer Full Text Search Yapıyorsak Takılırız
Şöyle yaparız. Burada prefix/suffix arama yapılmıyor ve index kullanılsa da yavaş olduğu görülecektir.
SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ILIKE '%winter%'
2. Eğer Index Yaratamıyorsak Takılırız
B-Tree index 'in büyüklüğü belli bir değeri aşamaz. Şöyle yaparız.
CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)
Çıktı olarak şunu alırız
index row size 4144 exceeds btree version 4 maximum 2704 for index "..."
Kullanım 
Örnek - Text Alan + LIKE
Açıklaması şöyle.
The B-Tree index is one of the simplest yet commonly used indexes in the PostgreSQL world. But when it comes to text, it cannot handle searches.
Görmek için şöyle yaparız. Sequential Scan yaptığı görülebilir.
EXPLAIN ANALYZE SELECT
  event_type
FROM
  storm_events
WHERE
  event_type LIKE '%Storm'
Index'i yaratmak için pattern_ops kullanılmalı. Şöyle yaparız
CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)
Örnek - Text Alan + REGEX
Şöyle yaparız. S veya H ile başlayan event_type'ları seçer.
SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ~ '^(S|H)'
2. Partial Index
Partial Index yazısına taşıdım



29 Ekim 2019 Salı

Array Functions and Operators

İndeks İle Erişme
Şöyle yaparız.
select (array['Cat', 'Dog', 'Horse'])[3];

24 Ekim 2019 Perşembe

pgAdmin GUI Tool

Giriş
Açıklaması şöyle
An Open Source cross-platform PostgreSQL GUI tool. 

Strengths:

- Compatible with Linux, Windows, macOS.
- Allows for working with multiple servers at a time.
- CSV file export.
- Query planning functionality.
- An ability to monitor your sessions, database locks via the dashboard.
- Shortcuts in the SQL editor for more convenient work.
- A procedural language debugger inside aimed to help in code debugging. 
- Thorough documentation and vibrant community.

Weaknesses:

- Slow and not intuitive UI compared to the paid competitors.
- Too heavy.
- Not easy to onboard.
- You need advanced skills to work with several databases at a time.
Login
Şöyle yaparız
version: '3.8'
services:
  db:
    container_name: pg_container
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_DB: test_db
    ports:
      - "5432:5432"
  pgadmin:
    container_name: pgadmin4_container
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: root
    ports:
      - "5050:80"
Açıklaması şöyle
First, access the pgadmin4 via your favorite web browser by visiting the URL http://localhost:5050/. Use the admin@admin.com as the email address and root as the password to log in.
Servers > Create > Server 
Şeklen şöyle. Yeni bir PostgreSQL sunucusu yaratır.



Database Simgesi
Restore Menüsü
Tüm bağlantıların kapalı olması gerekir. Ayrıcate veritabanı drop edilir. Açıklaması şöyle.
Before restoring a database, you need to terminate all connections to that database and prepare the backup file.

Table Simgesi
Import Menüsü
Tablo'ya dosyadan veriyi aktarmayı sağlar.

Restore Menüsü
Önce tablonun truncate edilmesi gerekir.

15 Ekim 2019 Salı

ALTER SYSTEM

max_connections
Örnek
Şöyle yaparız
ALTER SYSTEM SET max_connections = 250;
max_prepared_transactions
Örnek
Şöyle yaparız
ALTER SYSTEM SET max_connections = 250;
shared_buffer
Örnek
Veri tabanının kullanacağı cache bellek miktarını belirtmek için şöyle yaparız.
ALTER SYSTEM SET shared_buffer TO = <value>

13 Ekim 2019 Pazar

psql - PostgreSQL interactive terminal

Giriş
psql için bazı notlar

postgresql Kullanıcısı
Açıklaması şöyle. postgresql kullanıcısı psql komutunu çalıştır
There is a sub-class of non-root users that are often called "system users". Despite what the name suggests, they are ordinary users. They just happen to be created for special purposes like running a particular daemon and owning that daemon's files and directories. e.g. user lp for a printer daemon, ftp for ftpd, postgres for the postgresql database, and many more. They usually have a disabled password and their shell set to /bin/false or /usr/sbin/nologin or similar (user postgres is a notable exception because it's fairly common to su to user postgres to run psql for maintenance tasks).
Dump dosyasını geri yüklemek için şöyle yaparız
psql dbname < infile
Auto Complete
Açıklaması şöyle
Q : Is it possible to make autocomplete working in psql on Windows? As I know from Linux users, they can make use pressing TAB to autocomplete, which is quite handy, I guess.

A : psql's autocompletion comes by virtue of the readline or libedit library, whichever was configured when PostgreSQL was built from source. The Windows binaries were built without that support, most likely because there are no Windows ports of these libraries. If you port either of these libraries to Windows, you can build PostgreSQL from source and enjoy command line completion.
Çıktı
Giriş yaptıktan sonra gösterilen bilgi şöyledir
# psql -U myuser -d mydb -h 127.0.0.1 -p 5432 -W
Password:
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256,
compression: off) Type "help" for help.
Seçenekler
-c seçeneği
SQL cümlesini verir
Örnek
Şöyle yaparız.
psql mydb myuser -c "update endpoint set endpoint_group_id = 15 where mac_address='a';"
-d/--dbname seçeneği
Bağlanılacak veri tabanı ismini verir.
Örnek
Şöyle yaparız
psql -h localhost -U postgres -d mydb
-f/--file seçeneği
Çalıştırılacak SQL dosyasını belirtir.
Örnek
Şöyle yaparız
psql -U postgres -f /opt/scripts/test_script.sql
-h/--host seçeneği
Bağlanılacak host ismini belirtir. host ismi belirtilmezse localhost kullanılır.

ON_ERROR_STOP seçeneği
Örnek
Şöyle yaparız
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
Örnek
Şöyle yaparız
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  create schema test_schema;
  create table test_schema.employee(
          id  SERIAL PRIMARY KEY,
          firstname   TEXT    NOT NULL,
          lastname    TEXT    NOT NULL,
          email       TEXT    not null,
          age         INT     NOT NULL,
          salary         real,
          unique(email)
      );
EOSQL
--set seçeneği
Örnek
SSL ile bağlanmak için şöyle yaparız
psql -h <HOST_NAME> -p 5432 -U <USER_NAME> -W -d <DB_NAME> --set=sslmode=require
-U/--username seçeneği
Bağlantı için kullanılacak kullanıcı ismini belirtir.

-V seçeneği
Şöyle bir çıktı alırız
psql (PostgreSQL) 11.12
-w/--no-password seçeneği
Açıklaması şöyle
Never issue a password prompt. If the server requires password authentication and a password is not available from other sources such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.
-W/--password seçeneği
Açıklaması şöyle
Force psql to prompt for a password before connecting to a database, even if the password will not be used.
Şöyle yaparız
psql -h localhost -p 5432 -U postgres -W -d postgres

Meta Commands
Meta Commands yazısına taşıdım

PG_STAT_STATEMENTS Sistem Tablosu - İstatistikleri İçerir

Giriş
Açıklaması şöyle
The pg_stat_statements view returns statistics for all SQL queries that have run on the database since the last reset of statistics.

Note that the pg_stat_statements view is not enabled by default in Postgres. You may need to have a chat with your DevOps/DB admin team to enable it.
Kurulum
Eğer kurulu değilse kurmak için şöyle yaparız
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
calls Sütunu
Kaç defa çağrıldığını gösterir
Örnek
Şöyle yaparız. total_time milisaniyeden dakikaya çevrilir. Ortalama ise milisaniye olarak gösterilir
SELECT (total_time / 1000 / 60) as total, (total_time/calls) as avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;
Örnek
Şöyle yaparız
SELECT pss.userid, pss.dbid, pd.datname as db_name, round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time, pss.calls, round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean, round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg, pss.query FROM pg_stat_statements pss, pg_database pd WHERE pd.oid=pss.dbid ORDER BY (pss.total_exec_time + pss.total_plan_time) DESC LIMIT 100;
mean_time Sütunu
Örnek
En yavaş 20 sorguyu görmek için şöyle yaparız
SELECT query, round(total_time::numeric, 2) AS total_time, calls, round(mean_time::numeric, 2) AS mean, round(stddev_time::numeric, 2) AS std_dev, round ((100* total_time / sum (total_time::numeric) OVER ())::numeric, 2) AS percentage_overall, userid, dbid, queryid FROM pg_stat_statements WHERE mean_time > 10 ORDER BY total_time DESC LIMIT 20;
total_time Sütunu
Örnek - Query Profiling
Yavaş sorguları bulmak için kullanılabilir. Açıklaması şöyle.
This can be accomplished through their modules auto_explain and pg_stat_statements, checking pg_stat_activity for table and index activity on your server, analyzing the slow query log, or reviewing in your code.
Açıklaması şöyle
Query profiling is a technique for analyzing query performance and identifying potential bottlenecks. PostgreSQL provides support for query profiling through the use of the pg_stat_statements module, which can be used to collect and analyze query execution statistics.
Örnek
En yavaş 10 sorguyu görmek için şöyle yaparız
SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
Örnek
En yavaş 10 sorguyu görmek için şöyle yaparız
-- Identify slow-running queries SELECT query, calls, total_time, rows FROM pg_stat_statements WHERE total_time > '1 second' ORDER BY total_time DESC;
2. Query Caching
Açıklaması şöyle
Query caching is a technique for storing the results of frequently executed queries in memory for faster access. PostgreSQL provides support for query caching through the use of the shared cache, which can be configured to cache query plans and results.
Örnek
Şöyle yaparız
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all


VACUUMLO Komutu

Giriş
psql.exe gibi harici bir komuttur. Açıklaması şöyle.
PostgreSQL VACUUM is a technique to remove tuples that have been deleted or are now obsolete from their table to reclaim storage occupied by those dead tuples, also known as Bloat. VACUUM is an important process to maintain, especially for frequently-updated tables before it starts affecting your PostgreSQL performance.
VACUUM için 2 seçenek var.

1. Auto Vacuum
Açıklaması şöyle.
The most popular process for PostgreSQL VACUUM is the built-in autovacuum,... . The autovacuum daemon is optional, but highly recommended in the PostgreSQL community, at it automates both VACUUM and ANALYZE commands, continuously checking tables for deal tuples. 
Auto Vacuum'un bazı dezavantajları var. Açıklaması şöyle.
 - autovacuum may run even when turned off to deal with transaction ID wraparound.
- autovacuum is constantly running, which makes it start over every time it runs out of space, and start a new worker for each database in your cluster.
- autovacuum can cause out of memory errors.
- autovacuum may have trouble keeping up on a busy server.
- autovacuum can easily consume too much I/O capacity.
2. Manual Vacuum
Örnek
Şöyle yaparız.
vacuumlo -h <hostname> -U <username> -W -v <database_name>