JSON_TABLE

Giriş

Açıklaması şöyle

Use JSON_TABLE() to transform JSON data into tabular format.

Örnek

Şöyle yaparız

SELECT *
FROM JSON_TABLE(
  '[{"name":"Alice","age":30},{"name":"Bob","age":25}]',
  '$[*]'
  COLUMNS(name TEXT PATH '$.name', age INT PATH '$.age')
) AS jt;

MERGE ... RETURNING - Postgres 17 İle Geliyor

Giriş

Açıklaması şöyle

You can now use it MERGE ... RETURNING to retrieve rows that were inserted, updated, or deleted—all in one go.

Örnek

Şöyle yaparız

MERGE INTO employees AS e
USING new_data AS d
ON e.id = d.id
WHEN MATCHED THEN
  UPDATE SET salary = d.salary
WHEN NOT MATCHED THEN
  INSERT (id, salary) VALUES (d.id, d.salary)
RETURNING *;

postgresql.conf Dosyası - Log Ayarları

log_min_duration_statement

Açıklaması şöyle

log_min_duration_statement: just logging the statements whose duration is more than this value in milliseconds. This is very useful for detecting slow queries.

Açıklaması şöyle

It sets the minimum execution time in milliseconds (ms) above which all statements will be logged.

The default value for the log_min_duration_statement parameter is -1, which disables logging statements.

Setting the PostgreSQL parameter log_min_duration_statement to 0 will print all statements' durations.

Örnek

Şöyle yaparız

# set log_min_duration_statement=1;
SET

# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 1ms
(1 row)

PostgreSQL log dosyalarındaki statement log cümleleri şöyledir

LOG:  duration: 5.477 ms  statement: insert into abc values(1);
LOG:  duration: 8.656 ms  statement: insert into abc values(2);

log_statement

ALL, MOD,DDL değerlerini alabilir

Açıklaması şöyle

Most fintech companies, who are required to log all user and application activities, set the most verbose option log_statement=all. This is the easiest way to bloat the log files and invite storage issues if storage consumption is not monitored. Pg_audit can be a smarter way of logging user activities, where you can specify which class of activities you want to log, such as READ, WRITE, DDL, or FUNCTION. 

log_statement_sample_rate

Açıklama yaz

log_destination

Bu alanlar için Audit Trails yazısına bakabilirsiniz

log_destination Alanı

PostgreSQL 15 için açıklama şöyle

PostgreSQL supports several methods for logging server messages, including stderr (default), csvlog, and syslog. With this release, jsonlog is also supported which is convenient for exporting logs and debugging. In order to enable this, add the jsonlog under the log_destination inside the postgresql.conf file.

This will be a nice feature to have with exports to logging and monitoring tools like HoneyComb and DataDog etc.

Historical Table

Giriş

Yedek yani backup amaçlı değildir. Sadece eski veriyi farklı bir tabloya taşır

Örnek

Şöyle yaparız

> create table main_table_historical_data
as select * from main_table where create_date < '01-Jan-2020';
> delete from main_table where create_date < '01-Jan-2020';