postgres temporary table unlogged - ghdrako/doc_snipets GitHub Wiki

Temp buffers

This area stores temporary tables required during an operation and when more temporary result sets are needed during multiple join operations. It is also used whenever Work memory can no longer support the query operation. For example, to view the current allocation, you may issue the command below:

 postgres=# SHOW temp_buffers;
 temp_buffers
-------------
8MB
 (1 row

In PostgreSQL, each table or index is stored in one or more files. When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments.

Find unlogged tables in datababase

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name
FROM
    pg_class c
JOIN
    pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind = 'r'       -- 'r' oznacza zwykłą tabelę (relację)
    AND c.relpersistence = 'u' -- 'u' oznacza niezalogowaną ('unlogged')
ORDER BY
    schema_name, table_name;

Wyjaśnienie

• pg_class: To główny katalog systemowy, który przechowuje informacje o tabelach, indeksach, widokach i innych obiektach podobnych do tabel.
• relkind: Ta kolumna określa typ obiektu. Dla zwykłych tabel używamy wartości 'r' (relation).
• relpersistence: Ta kolumna określa trwałość obiektu:
  • 'p': Permanent (trwała, domyślna tabela logowana).
  • 'u': Unlogged (niezalogowana tabela - nie generuje WAL, tracona w przypadku awarii).
  • 't': Temporary (tabela tymczasowa).

SELECT schemaname, tablename, relpersistence
FROM pg_catalog.pg_tables
WHERE relpersistence = 'u';

Widok pg_tables

To systemowy widok, który PostgreSQL tworzy dla wygody użytkowników. W jego definicji znajdziesz mniej więcej coś takiego (uproszczona wersja):

CREATE VIEW pg_tables AS
SELECT 
    n.nspname AS schemaname,
    c.relname AS tablename,
    c.relowner AS tableowner,
    t.spcname AS tablespace,
    c.hasindexes,
    c.hasrules,
    c.hastriggers
    c.relpersistence,    -- 'p' = permanent, 'u' = unlogged, 't' = temp
    c.relkind,           -- 'r' = table, 'v' = view, 'i' = index, 'S' = sequence, ...
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'r' OR c.relkind = 'p';  -- r = regular table, p = partitioned table

Find unlogged tables with GENERATED columns - przeszkadzaja w upgrade

SELECT
    c.relname AS table_name,
    a.attname AS column_name,
    a.attidentity AS identity_type
FROM
    pg_catalog.pg_class c
    JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    a.attidentity IN ('a', 'd')  -- 'a' for GENERATED ALWAYS, 'd' for GENERATED BY DEFAULT
    AND c.relkind = 'r'  -- Only consider ordinary tables
    AND c.relpersistence = 'u'  -- Include unlogged tables
ORDER BY
    c.relname, a.attname;

Temporary table

begin work;
create temp table if not exists temp_users_transaction (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
,PRIMARY KEY( pk )
,UNIQUE ( username )
) on commit drop;

\d temp_users_transaction

commit work;

\d temp_users_transaction  # table dissapear 

Unlogged table

• https://pganalyze.com/blog/5mins-postgres-unlogged-tables

Unlogged tables are much faster than classic tables (also known as logged tables) but are not crash-safe. This means that the consistency of the data is not guaranteed in the event of a crash.

CREATE UNLOGGED TABLE staging_table ( /* table definition */ );

create unlogged table if not exists unlogged_users (
pk int GENERATED ALWAYS AS IDENTITY
,username text NOT NULL
,gecos text
,email text NOT NULL
Chapter 4
89
,PRIMARY KEY( pk )
,UNIQUE ( username )
);

When you have data in your unlogged table and you shutdown Postgres with a restart, for example, then the data will still be there.

However, when there is a crash, Postgres will truncate it. And that's in a sense a feature. The reason that it truncates is because it's not clear what the data is.

• unlogged table is automatically truncated after a crash or unclean shutdown

• Unlogged tables in Postgres are not replicated

• Unlogged tables can be written to disk because there is pressure in shared buffers - there's not enough space - and so Postgres has to write out something to disk, to make space for something else. Another case can be if there is a clean shutdown checkpoint as seen in Greg's post. This is why you may see files on disk - but unfortunately you just don't know what state that on-disk file is in. And so you shouldn't trust a crashed unlogged table on disk at all.

Unlogged to Logged

When working with unlogged tables, if you plan to make them logged later, keep in mind that transition will create a lot of Write Ahead Log (WAL) changes suddenly, which can cause a lot of resource load.

ALTER TABLE mytable SET UNLOGGED; -- cheap!

ALTER TABLE mytable SET LOGGED; -- expensive!

Index and sequence in unlogged tables

• https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED
• Any indexes created on an unlogged table are automatically unlogged as well.
• If this is specified, any sequences created together with the unlogged table (for identity or serial columns) are also created as unlogged.

Unlogged partition table

Mozna mieszac typy. Np nadrzedna unlogged a podrzedne logged i unlogged. tez nadrzedna unlogged a podrzedne logged i unlogged. Po nizej przyklad ze nie dziala zmiana typu partycji nadrzednej: https://dba.stackexchange.com/questions/245247/unable-to-alter-partition-table-to-set-logged

CREATE UNLOGGED TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text)PARTITION BY LIST(cust_country);

CREATE UNLOGGED TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');

CREATE UNLOGGED TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');

CREATE UNLOGGED table customers_def PARTITION OF customers DEFAULT;

INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');

INSERT INTO customers VALUES (4499,'Tony','Arizona','USA');

\d+ customers
checkpoint;
alter table customers set logged ;  -- not working - still unlogged
\d+ customers;  

-- workerand

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 u              | customer_ind
 u              | customer_jap
 u              | customers
 u              | customers_def
(4 rows)

-- enable logging on partition tables
ALTER TABLE customer_ind SET LOGGED;
ALTER TABLE customer_jap SET LOGGED;
ALTER TABLE customers_def SET LOGGED;
ALTER TABLE customers SET LOGGED;

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 u              | customers
 p              | customers_def
(4 rows)

-- Manually, detach/attach partitions
ALTER TABLE customers DETACH PARTITION customer_ind;
ALTER TABLE customers DETACH PARTITION customer_jap;
ALTER TABLE customers DETACH PARTITION customers_def;

DROP TABLE customers;
CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text) PARTITION BY LIST(cust_country);

ALTER TABLE customers ATTACH PARTITION customer_ind FOR VALUES IN ('ind');
ALTER TABLE customers ATTACH PARTITION customer_jap FOR VALUES IN ('jap');
ALTER TABLE customers ATTACH PARTITION customers_def DEFAULT;

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 p              | customers
 p              | customers_def
(4 rows)

disable Autovacuum

Autovacuum can also be disabled, which keeps it from consuming resources.

ALTER TABLE trip_requests_intermediate
SET (autovacuum_enabled = false)