NEXT VALUE FOR

Örnek

Şöyle yaparız

CREATE TABLE container
(
    id bigint NOT NULL DEFAULT (NEXT VALUE FOR container_sequence),
   ...
);

REINDEX

INDEX

Açıklaması şöyle

PostgreSQL offers two types of reindexing methods: offline and online.

a. Offline Reindexing

Offline reindexing locks the table, preventing any data modifications during the process. It is suitable for smaller tables or during maintenance windows with lower database activity.

b. Online Reindexing

Online reindexing allows concurrent read and write operations on the table while rebuilding the index. It is suitable for larger tables that require continuous access.

Örnek - Index Dosyası Bozulursa

Index Dosyası Bozulursa tekrar REINDEX çalıştırılır. Şöyle yaparız. Aynı yazıda index dosyasının nasıl bulunacağı ve bozulacağı da gösteriliyor.

# REINDEX INDEX CONCURRENTLY a_idx;

Örnek - OFFLINE

Şöyle yaparız

-- Perform the reindexing
REINDEX INDEX index_name;

Örnek - OFFLINE

Şöyle yaparız

-- List the index name
SELECT indexname, tablename, schemaname
FROM pg_catalog.pg_indexes

-- Reindex by Index Name
REINDEX INDEX your_index_name;

Örnek - ONLINE

Şöyle yaparız

-- Reindex the index concurrently
REINDEX INDEX CONCURRENTLY index_name;

SCHEMA

Örnek

Şöyle yaparız

-- Reindex by Schema Name
REINDEX SCHEMA your_schema_name;

TABLE

Örnek

Şöyle yaparız

REINDEX TABLE payment;

CLUSTER - Tabloda Performans Problemi Varsa Kullanılır

Giriş

Açıklaması şöyle

Another option is to use the CLUSTER command, where the entire table will be rewritten, taking and reordering based on the created index

Google Bard Açıklaması şöyle

The CLUSTER command in PostgreSQL is used to physically reorder the data in a table based on the index information. This can improve the performance of queries that use the index, as the rows will be stored in a contiguous block on disk.

The syntax for the CLUSTER command is as follows:

CLUSTER table_name [USING index_name]

The table_name parameter specifies the name of the table to be clustered. The index_name parameter specifies the name of the index to use for clustering. If the index_name parameter is not specified, PostgreSQL will use the primary key index, if one exists.

Örnek

170 milyon satır içeren büyük bir tabloya satır ekleme işlemi uzun süren bir örnek burada. Çözüm olarak CLUSTER komutu ile tablo tekrar tekrar fiziksel olarak yaratılmış. Açıklaması şöyle

1. Created a btree index on the unique identifier column (took about 20 minutes to complete, the size of the index was about 14gb).

2. Reduced the fill factor from 100 to 85 (we do get a lot of updates on the same unique id)

3. Ran ‘CLUSTER <table> USING <id index>’ (took about 45 minutes to complete)

4. Ran ‘VACUUM FULL ANALYZE’ (we could have ran just ‘ANALYZE’ alone, but i wanted to do the check fresh).

5. Re-ran the bulk upsert command, but this time the temp table will have data sorted as per the unique id (ascending), so that the upsert are done in the sorted id format.

Örnek

Şöyle yaparız

CLUSTER table_name USING index_name

Don’t Use Double Quotes in PostgreSQL For Table and Column Names

SQL Açısından

SQL açısından her şey case insensitive. Büyük küçük harf duyarlı değil. Açıklaması şöyle

The SQL standard states that SQL queries and identifiers (e.g., table names) aren’t case sensitive.  Thus, there’s no difference between

select id, email from people;

and

SELECT ID, EMAIL FROM PEOPLE;

Ancak PostgreSQL her şeyi küçük harf haline getiriyor. Yani tablo isimleri küçük harf haline geliyor

Double Quotes 

Açıklaması şöyle

Whereas single quotes in PostgreSQL are used to create a text string, double quotes are used to name an identifier without changing its case.

Özeti şöyle

The bottom line, then, is to avoid using double quotes when creating anything.  Actually, you should avoid double quotes when retrieving things as well — otherwise, you might discover that you’re trying to retrieve a column that PostgreSQL doesn’t believe exists.

Örnek

Bir seferinde JDBC ile şöyle bir cümleyi çalıştırırken hata aldım. Çünkü tablo ismi çift tırnak ileydi ve mapping değişkeninin değerinin hepsi küçük harf değildi

"SELECT * FROM \"" + mapping + "\" LIMIT 0"

Örnek

Elimizde şöyle bir SQL olsun. Bundan sonra bu tabloya artık sadece "People" şeklinde erişebilirim

CREATE TABLE "People" (
 id SERIAL NOT NULL,
 email TEXT NOT NULL,
 PRIMARY KEY(id)
);

Şu hata verir

SELECT * FROM people;

ERROR: relation "people" does not exist

PostgreSQL İsmi Nereden Geliyor?

Açıklaması şöyle

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

CREATE CAST

Giriş

CREATE CAST ile VARCHAR ve ENUM arasında dönüşüm yapılabilir. Böylece JDBC kodları daha kolay kullanılır.

 

Örnek - ENUM

Elimizde şöyle bir PostgreSQL tablosu olsun. Burada order_status isimli yeni bir type yarattık.

CREATE TYPE order_status AS ENUM(
  'Ordered', 
  'Baking', 
  'Delivering', 
  'YummyInMyTummy');

CREATE TABLE pizza_order (
  id INT PRIMARY KEY,
  status order_status NOT NULL,
  order_time TIMESTAMP NOT NULL DEFAULT now()
);

Şu SQL çalışır, çünkü status tipi olarak CREATE TYPE ile belirtilen bir string verdik

> INSERT INTO pizza_order (id, status, order_time) 
VALUES (1, 'Ordered', now());

String ve enum arasında dönüşüm için bir cast yaratırız. 

CREATE CAST (varchar AS order_status) WITH INOUT AS IMPLICIT;

Audit Trails

Giriş

Audit Trail için iki seçenek var

1. Ayarları elle yapmak

2. pg_audit extension kullanmak

1. Ayarları elle yapmak

Şöyle yaparız

> show log_statement;
> set log_statement = 'all';

// log destination is stderr
> show log_destination;

//Show logger pattern
> show log_line_prefix;
%m [%p]

Logger pattern'ı değiştirmek için postgresql.conf dosyasında şöyle yaparız

# Locate the line log_line_prefix and change to the following
log_line_prefix = '(%m,%p,%d,%u,%r)'

Ayrıca bu dosyada tanımlanabilecek diğer bazı değişkenlerin açıklaması şöyle

log_statement_sample_rate: value between 0 and 1. This is for sampling some statements to log. Useful if you have a lot of loads.

log_destination: we can specify the file path where store the log and the format as well. I.e: jsonlog /mnt/logs/postgres.json . plain, JSON and CSV are the format supported at this moment.

2. pg_audit Extension

Bitnami ile bu extension kurulu geliyor. Şöyle yaparız

$ docker run -d \
  --name postgres-pg-audit \
  -e POSTGRESQL_PASSWORD=postgres \
  -e POSTGRESQL_PGAUDIT_LOG="read, ddl" \
  bitnami/postgresql:latest

Aslında pg_audit'in avantajı şöyle

define a logging policy per object

Örnek

Şöyle yaparız

>  create role auditor;

> set pgaudit.role = 'auditor';

> set pgaudit.log = '';

> grant select (password) on public.account to auditor;