10 ms·

Postgres Unlogged Tables

85 points by plaur782 4y ago

chuckledog 4y ago
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.
tremon 4y ago
should someone literally pull the plug on your Postgres database server, your data is 100% safe, except for unlogged tables, in which your data is 100% gone - by design! This omits a crucial fact: your data is not gone until Postgresql has gone through its startup recovery phase. If you really need to recover unlogged table data (to which every proper database administrator will rightly say "then WhyTH did you make it unlogged"), you should capture the table files before starting the database server again. And then pay through the nose for a data recovery specialist. However, a crash will truncate it. So this isn't exactly true. A crash recovery will truncate it.
- apnew 4y ago
  Interesting, I thought unlogged tables meant they dont get get WAL-ed and stored on FS at all. Any further reading you can suggest?
  - _a_a_a_ 4y ago
    I thought the article was pretty clear.
  - tremon 4y ago
    A quick scan of the postgresql manual turns up nothing. I would have expected the steps performed during startup recovery to be documented there. Neither is the manual explicit about data file usage for an unlogged table, there's just these two snippets: from https://www.postgresql.org/docs/15/sql-createtable.html#SQL-CREATETABLE-UNLOGGED https://www.postgresql.org/docs/15/sql-createtable.html#SQL-...: > Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables and from https://www.postgresql.org/docs/15/glossary.html#GLOSSARY-UNLOGGED https://www.postgresql.org/docs/15/glossary.html#GLOSSARY-UN...: > The property of certain relations that the changes to them are not reflected in the WAL. This disables replication and crash recovery for these relations. Which both say nothing about the normal data files underlying unlogged tables, so none of what I wrote can be found in the official docs (or maybe it can, just not by me ;) However, there is also this page from the postgres developer wiki: https://wiki.postgresql.org/wiki/Future_of_storage https://wiki.postgresql.org/wiki/Future_of_storage which does say that pure in-memory tables are not supported by Postgres: > it would be nice to have in-memory tables if they would perform faster. Somebody could object that PostgreSQL is on-disk database which shouldn't utilize in-memory storage. But users would be interested in such storage engine if it would give serious performance advantages.
jonstewart 4y ago
Does COPY FROM sidestep the WAL? My (perhaps incorrect) understanding is that pg writes such data to a table file then uses a small transaction to make it live.
- lfittl 4y ago
  It does not, except if you're COPYing into an unlogged table - for regular tables data that gets COPYed in gets WAL logged and is crash safe. In my understanding, as compared to a multi-row insert, a COPY statement is (1) a lot more efficient to parse/process, (2) uses a special bulk write strategy that avoids churn and large amount of dirty pages in shared buffers. As compared to multiple single row inserts another benefit is that you're writing a single transaction ID, instead of a lot of individual transaction IDs (causing less freezing work having to be done by autovacuum, etc.)
  - anarazel 4y ago
    We also batch the insertion into the table and the WAL logging, if possible. There's cases where that optimization has to be disabled though (presence of some types of triggers, for example).
BeefySwain 4y ago
Very well written and detailed article, with the caveat that they never mention a use case they consider legitimate. Does anyone here have any uses? I could imagine some sort of ETL type tasks which are transient could make sense. Thoughts?
- anarazel 4y ago
  Session state is a very common one, where the journaling overhead also can be particularly pronounced (much easier to hide in batch workloads).
- jpalomaki 4y ago
  I've been using these for some larger analytical queries that run in batch fashion (typically something that's done like once or couple of times per day). I find it hard to get good performance from large queries with CTEs. Often, it's much easier (and uglier) to just split them to multiple steps, create intermediate tables for each step and add necessary indexes. Temporary tables are then of course even better, since usually you don't want to have these left around. But temporary tables are also unlogged.
  - avianlyric 4y ago
    Which versions of Postgres have you been running your large CTE based queries with? Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes. OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.
    - RandomBK 4y ago
      I can't speak to Parent's situation, but temp tables are common practice in OLAP workloads across many database engines (personal experience with Postgres, Presto, Spark, Netezza). At a certain level of query complexity, no query planner is able to accurately predict the characteristics of sub-queries or CTE, resulting in query plans that are ill-suited to the problem. More often than not, materializing CTEs inside a large query into 2-3 temporary tables results in order-magnitude performance as the database now knows exactly how many rows its dealing with, stats on # of nulls, etc.
lazyant 4y ago
can we have "best of both worlds" for inserting a lot of data quickly, to have an unlogged table for performance and a trigger to copy data to another regular table for permanence?
- usrusr 4y ago
  I'd think so, if there is some asynchronous processing involved that requires lots of intermediate bouncing to disk: do the input queue on logged tables, the WIP states on unlogged ones and when they are done push to output queue (again logged) and remove from the input in the same transaction.
sebazzz 4y ago
So similar use cases as MSSQL in-memory tables?

85 points by plaur782 4y ago

chuckledog 4y ago

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.

tremon 4y ago

should someone literally pull the plug on your Postgres database server, your data is 100% safe, except for unlogged tables, in which your data is 100% gone - by design! This omits a crucial fact: your data is not gone until Postgresql has gone through its startup recovery phase. If you really need to recover unlogged table data (to which every proper database administrator will rightly say "then WhyTH did you make it unlogged"), you should capture the table files before starting the database server again. And then pay through the nose for a data recovery specialist. However, a crash will truncate it. So this isn't exactly true. A crash recovery will truncate it.

apnew 4y ago
Interesting, I thought unlogged tables meant they dont get get WAL-ed and stored on FS at all. Any further reading you can suggest?
- _a_a_a_ 4y ago
  I thought the article was pretty clear.
- tremon 4y ago
  A quick scan of the postgresql manual turns up nothing. I would have expected the steps performed during startup recovery to be documented there. Neither is the manual explicit about data file usage for an unlogged table, there's just these two snippets: from https://www.postgresql.org/docs/15/sql-createtable.html#SQL-CREATETABLE-UNLOGGED https://www.postgresql.org/docs/15/sql-createtable.html#SQL-...: > Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables and from https://www.postgresql.org/docs/15/glossary.html#GLOSSARY-UNLOGGED https://www.postgresql.org/docs/15/glossary.html#GLOSSARY-UN...: > The property of certain relations that the changes to them are not reflected in the WAL. This disables replication and crash recovery for these relations. Which both say nothing about the normal data files underlying unlogged tables, so none of what I wrote can be found in the official docs (or maybe it can, just not by me ;) However, there is also this page from the postgres developer wiki: https://wiki.postgresql.org/wiki/Future_of_storage https://wiki.postgresql.org/wiki/Future_of_storage which does say that pure in-memory tables are not supported by Postgres: > it would be nice to have in-memory tables if they would perform faster. Somebody could object that PostgreSQL is on-disk database which shouldn't utilize in-memory storage. But users would be interested in such storage engine if it would give serious performance advantages.

jonstewart 4y ago

Does COPY FROM sidestep the WAL? My (perhaps incorrect) understanding is that pg writes such data to a table file then uses a small transaction to make it live.

lfittl 4y ago
It does not, except if you're COPYing into an unlogged table - for regular tables data that gets COPYed in gets WAL logged and is crash safe. In my understanding, as compared to a multi-row insert, a COPY statement is (1) a lot more efficient to parse/process, (2) uses a special bulk write strategy that avoids churn and large amount of dirty pages in shared buffers. As compared to multiple single row inserts another benefit is that you're writing a single transaction ID, instead of a lot of individual transaction IDs (causing less freezing work having to be done by autovacuum, etc.)
- anarazel 4y ago
  We also batch the insertion into the table and the WAL logging, if possible. There's cases where that optimization has to be disabled though (presence of some types of triggers, for example).

BeefySwain 4y ago

Very well written and detailed article, with the caveat that they never mention a use case they consider legitimate. Does anyone here have any uses? I could imagine some sort of ETL type tasks which are transient could make sense. Thoughts?

anarazel 4y ago
Session state is a very common one, where the journaling overhead also can be particularly pronounced (much easier to hide in batch workloads).
jpalomaki 4y ago
I've been using these for some larger analytical queries that run in batch fashion (typically something that's done like once or couple of times per day). I find it hard to get good performance from large queries with CTEs. Often, it's much easier (and uglier) to just split them to multiple steps, create intermediate tables for each step and add necessary indexes. Temporary tables are then of course even better, since usually you don't want to have these left around. But temporary tables are also unlogged.
- avianlyric 4y ago
  Which versions of Postgres have you been running your large CTE based queries with? Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes. OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.
  - RandomBK 4y ago
    I can't speak to Parent's situation, but temp tables are common practice in OLAP workloads across many database engines (personal experience with Postgres, Presto, Spark, Netezza). At a certain level of query complexity, no query planner is able to accurately predict the characteristics of sub-queries or CTE, resulting in query plans that are ill-suited to the problem. More often than not, materializing CTEs inside a large query into 2-3 temporary tables results in order-magnitude performance as the database now knows exactly how many rows its dealing with, stats on # of nulls, etc.

lazyant 4y ago

can we have "best of both worlds" for inserting a lot of data quickly, to have an unlogged table for performance and a trigger to copy data to another regular table for permanence?

usrusr 4y ago
I'd think so, if there is some asynchronous processing involved that requires lots of intermediate bouncing to disk: do the input queue on logged tables, the WIP states on unlogged ones and when they are done push to output queue (again logged) and remove from the input in the same transaction.

sebazzz 4y ago

So similar use cases as MSSQL in-memory tables?