pgloader

Örnek

Şöyle yaparız

pgloader mysql://user:pass@10.10.10.10:3306/zabbix \
         postgresql://user:pass@10.10.10.1:31320/zabbix

pg_ivm - PostgreSQL Incremental View Maintenance

Giriş

Açıklaması şöyle

an extension that transforms materialized views into self-updating, always-fresh data structures that update instantly as your base data changes.

Açıklaması şöyle

Under the Hood: How pg_ivm Works

When you create an incremental materialized view, pg_ivm automatically:

1. Installs triggers on all referenced base tables

2. Captures changes through INSERT/UPDATE/DELETE monitoring

3. Calculates deltas to determine the minimal impact

4. Updates the view with only the necessary changes

REFRESH MATERIALIZED VIEW CONCURRENTLY - The Non-Blocking Approach

Giriş

Açıklaması şöyle. 

The Good: Allows queries during refresh (requires a unique index)

The Bad: Still recomputes the entire view from scratch

The Reality: Better UX, but still expensive and slow for large datasets.

Örnek

Şöyle yaparız

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

REFRESH MATERIALIZED VIEW - Manual Refresh

Giriş

Açıklaması şöyle. Alternatif olarak REFRESH MATERIALIZED VIEW CONCURRENTLY kullanılabilir

The Good: Simple and straightforward

The Bad: Locks the entire view during refresh, blocking all queries

The Reality: Your users stare at loading spinners while the view rebuilds

Örnek

Şöyle yaparız

REFRESH MATERIALIZED VIEW sales_summary;

Soft deletes vs. Journal tables

Örnek

create_journal_table() metodu şöyledir. Bu metod EVENT TRIGGER HANDLER içindir.

CREATE OR REPLACE FUNCTION create_journal_table()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;
    schema_name text;
    table_name text;
    journal_table_name text;
BEGIN
    FOR obj IN
        SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.command_tag = 'CREATE TABLE' AND obj.object_type = 'table' THEN
            schema_name := split_part(obj.object_identity, '.', 1);
            table_name  := split_part(obj.object_identity, '.', 2);

            schema_name := trim(both '"' from schema_name);
            table_name  := trim(both '"' from table_name);

            -- Skip journal tables to avoid recursion
            IF right(table_name, 8) = '_journal' THEN
                CONTINUE;
            END IF;

            journal_table_name := format('%I.%I_journal', schema_name, table_name);

            EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
                id bigserial PRIMARY KEY,
                action text NOT NULL,
                changed_at timestamptz DEFAULT now(),
                old_data jsonb,
                new_data jsonb
            )', journal_table_name);

            EXECUTE format(
                'CREATE TRIGGER %I_to_journal
                 AFTER INSERT OR UPDATE OR DELETE ON %I.%I
                 FOR EACH ROW
                 EXECUTE FUNCTION log_to_journal()',
                table_name, schema_name, table_name
            );
        END IF;
    END LOOP;
END;
$$;  

Bu metodu register ederiz. Metod şöyledir. Bu metod CREATE EVENT TRIGGER içindir.

CREATE EVENT TRIGGER create_journal_after_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION create_journal_table();

log_to_journal() metodu şöyledir. Bu metod row level logging yapar

CREATE OR REPLACE FUNCTION log_to_journal()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    journal_table text;
BEGIN
    journal_table := format('%I.%I_journal', TG_TABLE_SCHEMA, TG_TABLE_NAME);

    IF TG_OP = 'INSERT' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, NULL, to_jsonb(NEW);

    ELSIF TG_OP = 'UPDATE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), to_jsonb(NEW);

    ELSIF TG_OP = 'DELETE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), NULL;
    END IF;

    RETURN NULL;  -- AFTER trigger
END;
$$;

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 *;