View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006620 | SymmetricDS | Improvement | public | 2024-10-15 11:03 | 2024-10-15 11:34 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | assigned | Resolution | open | ||
| Product Version | 3.15.0 | ||||
| Summary | 0006620: Turn on UNLOGGED table mode during initial load into an empty PostgreSQL table | ||||
| Description | Currently the UNLOGGED table mode is not used for initial load into PostgreSQL. The UNLOGGED mode is helpful in reducing amount of Write-Ahead Logging that PostgreSQL database is doing - because data is recoverable (in the event of failure of the target database , the initial load data can be re-sent from the source). This mode is estimated to reduce load time by about 10%, which may be significant for very large initial loads. In case either the source of target table had been already specified as UNLOGGED, this trait should be preserved. | ||||
| Steps To Reproduce | CREATE UNLOGGED TABLE source.test6620( col1key INTEGER NOT NULL ,col2nullablekey CHAR(3) NULL ,col3 INTEGER NULL ,col4 TIMESTAMPTZ NOT NULL ); alter TABLE symtest_pgcentral1.test6620 set unlogged; alter TABLE symtest_pgcentral1.test6620 set logged; -- Query current mode of a table: select n.nspname , t.relname, t.relpersistence from pg_class t join pg_namespace n on n.oid=t.relnamespace and n.nspname = 'symtest_pgcentral1' where t.relname = 'test6620'. -- The relpersistence='u' for an unlogged table -- and relpersistence='p' for a logged table. | ||||
| Additional Information | The drawback of the UNLOGGED mode is that the contents of such table are also not replicated (to standby PostgreSQL servers). This mode is specific to PostgreSQL database and is not present in ANSI/ISO specification. Oracle database has a similar table-level mode called NO LOGGING, but only applies to the insert statement with the /*+ APPEND */ hint or SQL*Loader. The parents database ARCHIVELOG / NO ARCHIVE LOG configuration is also important in Oracle, but not in PostgreSQL. | ||||
| Tags | No tags attached. | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2024-10-15 11:03 | pbelov | New Issue | |
| 2024-10-15 11:03 | pbelov | Status | new => assigned |
| 2024-10-15 11:03 | pbelov | Assigned To | => pbelov |
| 2024-10-15 11:34 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2024-10-15 11:34 | pbelov | Additional Information Updated | View Revisions |