4 ms·

Seems like bulk loading might be a good use case here.

ellisv 4y ago
It’s more common to use TEMP tables but you can use UNLOGGED instead which can be helpful if you want to keep the same schema.
antupis 4y ago
We did use it ETL processing.
avianlyric 4y ago
Probably not, unless you don’t want to keep the data long term. The initial load might be faster, but you’ll end up repaying that time saving when you turn on logging back on, and Postgres is then forced to read the contents of the table and push it into the WAL anyway. Thereby throwing away any saving you originally made by avoiding the WAL with an unlogged table.
- ndriscoll 4y ago
  It can be useful to bulk load data into a temporary table, do validations (which may involve joins to your permanent tables so it's convenient to be able to do in the database vs the application) there, and then once everything is validated, copy into your real tables from the temporary table. If you have a process like that, it could make sense to have the temporary table be unlogged (assuming the copy to real tables is still safe if you do that).
- orlp 4y ago
  I am not saying it does actually work this way, but there is no reason a bulk import needs to touch the WAL at all during import. As long as a lock was held the entire time during an import on the table, the import procedure can simply write to the WAL that this table is consistent as it is on-disk after flushing, flush the WAL, then release the lock on the table.
  - avianlyric 4y ago
    No it can’t, because doing that would break live backups and replication, which both depend on capturing the WAL and assume it’s a total representation of all data managed by the DB. Any data that isn’t written to the WAL in its entirety can’t be replicated, and can’t be guaranteed to be backed up correctly. If you’re willing to throw away that functionality, then maybe you could avoid writing the rows to the WAL completely (but it seems Postgres doesn’t support that). But personally I would always be a little suspect of data stored in Postgres that never transited the WAL. It’s a core part of how Postgres provides it durability guarantees, and lots of tools and functionality assume that everything worth protecting gets written to it at least once.
BFLpL0QNek 4y ago
I’ve used it for bulk loading on time series data partitioned by date. It’s a few years ago so details are blurry. We got daily snapshots and on ingest we created an unlogged temporary table then when complete used INSERT FROM to the destination table if I remember right. As long as the application handles failure on bulk ingest and you copy the data to a logged table or enable logging on the table once ingested it worked really well.

It’s more common to use TEMP tables but you can use UNLOGGED instead which can be helpful if you want to keep the same schema.

antupis 4y ago

We did use it ETL processing.

avianlyric 4y ago

Probably not, unless you don’t want to keep the data long term. The initial load might be faster, but you’ll end up repaying that time saving when you turn on logging back on, and Postgres is then forced to read the contents of the table and push it into the WAL anyway. Thereby throwing away any saving you originally made by avoiding the WAL with an unlogged table.

ndriscoll 4y ago
It can be useful to bulk load data into a temporary table, do validations (which may involve joins to your permanent tables so it's convenient to be able to do in the database vs the application) there, and then once everything is validated, copy into your real tables from the temporary table. If you have a process like that, it could make sense to have the temporary table be unlogged (assuming the copy to real tables is still safe if you do that).
orlp 4y ago
I am not saying it does actually work this way, but there is no reason a bulk import needs to touch the WAL at all during import. As long as a lock was held the entire time during an import on the table, the import procedure can simply write to the WAL that this table is consistent as it is on-disk after flushing, flush the WAL, then release the lock on the table.
- avianlyric 4y ago
  No it can’t, because doing that would break live backups and replication, which both depend on capturing the WAL and assume it’s a total representation of all data managed by the DB. Any data that isn’t written to the WAL in its entirety can’t be replicated, and can’t be guaranteed to be backed up correctly. If you’re willing to throw away that functionality, then maybe you could avoid writing the rows to the WAL completely (but it seems Postgres doesn’t support that). But personally I would always be a little suspect of data stored in Postgres that never transited the WAL. It’s a core part of how Postgres provides it durability guarantees, and lots of tools and functionality assume that everything worth protecting gets written to it at least once.

BFLpL0QNek 4y ago

I’ve used it for bulk loading on time series data partitioned by date. It’s a few years ago so details are blurry. We got daily snapshots and on ingest we created an unlogged temporary table then when complete used INSERT FROM to the destination table if I remember right. As long as the application handles failure on bulk ingest and you copy the data to a logged table or enable logging on the table once ingested it worked really well.