We have an ETL process that starts with loading data into PostgresSQL 16.2 databases (Azure Database for PostgreSQL flexible). We load 100,000 records at a time using COPY FROM STDIN. This is staging data that doesn't need to be recovered in case of a crash. Because of this, we decided to give unlogged tables a try to reduce costs and increase performance. My issue is that with all other things being equal, simply adding 'UNLOGGED' to the create table statements increased load times by 30-35% in testing (~21 minutes up to ~28 minutes in one instance, ~31 minutes up to ~41 minutes in another). This doesn't make much sense given what I've read about unlogged tables. Indexes are only created after the data has been loaded into the tables. Overall IOPS to disk and bandwidth consumption are both down when using UNLOGGED. My write throughput (bytes/sec) is much lower with unlogged tables, but I'm not sure if that's a result of having fewer writes in the first place due to not hitting the WAL. Everything I find online simply talks about "use unlogged tables for faster writes", but there is essentially zero information about what could cause using unlogged tables to result in slower writes. Are there any recommendations for optimizing the performance of bulk loading into unlogged tables? I realize I haven't provided a ton of specifics, but given the lack of information on what can cause this decrease in performance, I don't really know what specific information would be helpful at this point. Happy to provide any addition information that I can. Any guidance is greatly appreciated!

I need to create a table for caching in the postgresql database. Transactions should be as fast as possible, so i thought of creating unlogged table and have a RAM disk as it's tablespace. There is a warning in the official postgresql documentation : > Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster. I know that unlogged tables are not replicate to the standby and it is okay for me to store unpersistent data in that table. So in case of crash i'm fine with losing it. Here is a questions: 1. Is it safe after all to place a tablespace on the RAM disk in this configuration? (unlogged + RAM disk tablespace + unpersistent data) 2. If it is a safe option should I create ram disk on a replica too, since creation of tablespace will be stored somewhere in wal? I'm using EDB 13.9.13 with streaming replication. If this will workout I will do the same for Postgresql v14.7

I use UNLOGGED tables for a few very large tables in a data warehouse style application. Until recently, I understood UNLOGGED to mean “won’t write to the WAL” - which in turn means that *recent* changes maybe lost on a process crash / unclean termination, and that there will be no replication Maybe I’m misunderstanding the language in the documentation (or maybe I’m not) but when I read the documentation recently, I understood it to mean the *entire* table will be truncated on an unclean exit. Is that right? The question is, **_on an unclean exit will the entire table will be TRUNCATEd (per the meaning of TRUNCATE in PostgreSQL) or does it mean truncated as in the everyday use- the end will abruptly terminate, only the most recent rows lost, due to there being no WAL_** Surely it myst be the latter? Being completely TRUNCATEd makes no sense to me; if I have an UNLOGGED table with a year’s worth of data, and today the postgres process goes down, why would it destroy the entire contents of the table? Or am I misunderstanding and it’s only recent changes (that would normally be restorable via WAL or similarly, by a spare) that would be lost? The blurb from the official PostgreSQL documentation is as follows, the bit about truncation us what I’m referring to: > If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. I’m not sure how I never noticed this language but it has me somewhat alarmed. Losing the most recent few days worth of data isn’t a problem for my use, but to have to restore the entire table (~60GB worth of data per-table) from the backups I keep (non-WAL backups, obviously) due to a simple process crash is alarming

I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it. Steps: pg_dump --format custom --verbose --file "potential_users.backup" --table "public.potential_users" productiondb pg_restore -a -t potential_users potential_users.backup I see the data prompted in the terminal and when it finishes shows: PostgreSQL database dump complete But after a SELECT count(*) FROM potential_users; 0 is returned.

To make updates faster, I am using: ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; What are the drawbacks of this command? What will happen if system crashes during the update? Is all the data present in the table deleted? Or only the updates which are being done will be lost?

I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command. Afterwards, I ran an alter table set logged on the parent table but the table still shows as UNLOGGED along with all its children. Is this broken? You can execute these commands to see the issue:

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 ;
\d+ customers;

Throughout the display of the table details the UNLOGGED status doesn't change when running the ALTER TABLE command against the parent table. But even when all the child tables have been SET LOGGED, I'm still not able to change this on the parent table. Tools like pg_partman will look to the parent table to create the child tables with the "correct" properties.

I have created an empty UNLOGGED table to faster copy a large amount of data (over 1 Billion rows). Loading the data took around 4 hours. Now I want to set the table to LOGGED to make it safe for unexpected shutdowns and crashes. This process takes a long time. In fact it takes longer than loading the data. Is this normal, or is there a way to speed it up?