Обсуждение: FK from logged to unlogged table?

Hello,

 

I'm looking to improve bulk write performance on a table of about 23 million rows by setting it unlogged. If lost, the table can be re-generated from archived raw data. The unlogged table would be referenced from a different, logged, table by a sparse but very important foreign key.

 

If the unlogged table is lost, I can repair the foreign key data by re-uploading the raw data and following a different unique key. However, this would be annoying and I would rather not have to implement it if I can protect the keyed records instead.

1. Is it allowed to key from a logged table to an unlogged table?
2. What is the system behavior if the unlogged table is lost?
3. Is there a clean way to protect only the subset of records that are keyed? (Yes, I am considering periodic backups to an archive table, but there would still be some repair needed that way).

<dolan@directdemocracysolutions.com> wrote:
> Is it allowed to key from a logged table to an unlogged table?
I tried and it seems not:
create unlogged table u(i int primary key);
create table l(i int references u);
ERROR: constraints on permanent tables may reference only permanent tables

Interestingly it works in the other direction. I assume it's forbidden
because the loss of the unlogged table will break the constraint. I
expect you could simulate the foreign key with a trigger, but I'm not
sure you should.

> Is there a clean way to protect only the subset of records that are keyed?
I wonder if partitioning could help you. I believe it's possible to
have logged and unlogged partitions in the same table [1].

I'm sure you've explored other options for faster inserts [2].  Most
of the times I've wanted to use an unlogged table, I've decided it's
not worth the rescue effort.

Thanks,
Bernice

[1] https://www.postgresql.org/message-id/flat/ZiiyGFTBNkqcMQi_%40paquier.xyz
[2] https://www.postgresql.org/docs/current/populate.html