Database Tuning

COPY FREEZE

When a large amount of data is loaded into an archive table, the approach used before PostgreSQL 9.3 was to perform a subsequent VACUUM FREEZE on the relation if the records were guaranteed not to change later on.This approach had the advantage that you could possibly save a VACUUM FREEZE in production operations later on, possibly under a heavy transaction load. In any case, it removes the need for a computationally expensive additional scan of the entire table later on, which is a huge timesaver, especially for large data sets.

In PostgreSQL 9.3, you now have the option of doing this immediately while loading the data into a table in a single pass. The COPY command now has a FREEZE parameter.

To work correctly, a COPY with FREEZE requires some conditions be fulfilled. For example, the target table must be created in the same transaction or subtransaction that issues the COPY command. Also, no CURSOR can be open for the table. The following simple example

BEGIN;
CREATE TABLE t3(LIKE t2);
COPY archive_tbl FROM '/Users/bernd/tmp/archive.dat' FREEZE CSV;
COMMIT;

shows the use of COPY FREEZE with a CSV file.

Event Trigger

Users frequently requested triggers when performing certain DDL commands. Triggers can be used to track changes to the database itself – for example, with replication systems (e.g., Slony-I) that rely on being notified of changed objects.

An event trigger is produced using the CREATE TRIGGER EVENT command, which uses the same syntax as CREATE TRIGGER; that is, the trigger function must be defined in advance. Listing 5 shows an example that denies all ALTER TABLE commands in a database with an error. Besides the ddl_command_start event trigger event, there are also the ddl_command_end and SQL_DROP events. dl_command_start is executed before running the respective DDL command and ddl_command_end, accordingly, before the DDL command is terminated. Event triggers with the SQL_DROP event are executed before the ddl_command_end event trigger for all objects that are removed within the event.

01 CREATE OR REPLACE FUNCTION public.deny_alter_table()
02  RETURNS event_trigger
03  LANGUAGE plpgsql
04 AS $function$
05 BEGIN
06
07         RAISE EXCEPTION '% is disabled', TG_TAG;
08
09 END;
10 $function$
11
12 CREATE EVENT TRIGGER etg_deny_alter_table
13 ON ddl_command_start
14 WHEN tag IN ('ALTER TABLE')
15 EXECUTE PROCEDURE deny_alter_table();
16
17 ALTER TABLE test ADD COLUMN another_col integer;
18 ERROR:  ALTER TABLE is disabled

Event triggers currently can be implemented in PL/pgSQL or C. A CREATE EVENT TRIGGER command itself cannot trigger an event trigger.

Checksums

In PostgreSQL 9.3, database blocks written to disk can be checksummed to help diagnose hard disk problems by unambiguously detecting data corruption. This is interesting especially for systems running on fairly unreliable hardware. You must specify whether to use checksums when you initialize the PostgreSQL instance using initdb or the new --data-checksums command-line parameter. This feature cannot be enabled or disabled retroactively. Moreover, it is effective for all databases and objects.

The checksums are written and checked at the block level for all database objects such as tables or indexes. Confirmation of checksums cannot be deactivated for individual objects, and enabling checksums will affect the speed of the database.