Comparison of data filling of Firebird 5 and PostgreSQL 17 databases

This material is sponsored and created with the sponsorship and support of IBSurgeon https://www.ib-aid.com, vendor of HQbird (advanced distribution of Firebird) and supplier of performance optimization, migration and technical support services for Firebird. The material is licensed under Public Documentation License https://www.firebirdsql.org/file/documentation/html/en/licenses/pdl/public-documentation-license.html

Preface

Task: create identical tables with certain data types, fill them with identical data in the amount of 1 million rows, compare the table and database sizes.

Create two Firebird and PostgreSQL databases in UTF8 encoding.

For comparison, select the following data types:

boolean
char
varchar
date
integer
numeric
float
time
time with timezone
timestamp
timestamp with timezone

For char and varchar, select the length of 50 and 200 characters, numeric length of 15 and precision of 10. Create tables with a width of 12 fields + surrogate key integer.

1. Create tables and fill them with data

1.1. Data type `boolean`

CREATE TABLE T_BOOL_12 (
    ID         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_BOOL_1   BOOLEAN,
    T_BOOL_2   BOOLEAN,
    T_BOOL_3   BOOLEAN,
    T_BOOL_4   BOOLEAN,
    T_BOOL_5   BOOLEAN,
    T_BOOL_6   BOOLEAN,
    T_BOOL_7   BOOLEAN,
    T_BOOL_8   BOOLEAN,
    T_BOOL_9   BOOLEAN,
    T_BOOL_10  BOOLEAN,
    T_BOOL_11  BOOLEAN,
    T_BOOL_12  BOOLEAN
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_BOOL_12 (T_BOOL_1, T_BOOL_2, T_BOOL_3, T_BOOL_4, T_BOOL_5, T_BOOL_6, T_BOOL_7, T_BOOL_8, T_BOOL_9, T_BOOL_10, T_BOOL_11, T_BOOL_12)
VALUES (TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE);

INSERT INTO T_BOOL_12 (T_BOOL_1, T_BOOL_2, T_BOOL_3, T_BOOL_4, T_BOOL_5, T_BOOL_6, T_BOOL_7, T_BOOL_8, T_BOOL_9, T_BOOL_10, T_BOOL_11, T_BOOL_12)
VALUES (FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE);

1.2. Data type `char(200)`

CREATE TABLE T_CHAR_12_200 (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    CHAR_1   CHAR(200),
    CHAR_2   CHAR(200),
    CHAR_3   CHAR(200),
    CHAR_4   CHAR(200),
    CHAR_5   CHAR(200),
    CHAR_6   CHAR(200),
    CHAR_7   CHAR(200),
    CHAR_8   CHAR(200),
    CHAR_9   CHAR(200),
    CHAR_10  CHAR(200),
    CHAR_11  CHAR(200),
    CHAR_12  CHAR(200)
);

Data is filled with a query until 1 million rows are reached (query is shortened).

INSERT INTO T_CHAR_12_200 (CHAR_1, CHAR_2, CHAR_3, CHAR_4, CHAR_5, CHAR_6, CHAR_7, CHAR_8, CHAR_9, CHAR_10, CHAR_11, CHAR_12)
VALUES ('C++ is a horrible language.It''s made more horrible by the fact that a lot of substandard programmers use it,to the point where it''s much much easier to generate total and utter crap with it.Torvalds.',...);

1.3. Data type `char(50)`

CREATE TABLE T_CHAR_12_50 (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    CHAR_1   CHAR(50),
    CHAR_2   CHAR(50),
    CHAR_3   CHAR(50),
    CHAR_4   CHAR(50),
    CHAR_5   CHAR(50),
    CHAR_6   CHAR(50),
    CHAR_7   CHAR(50),
    CHAR_8   CHAR(50),
    CHAR_9   CHAR(50),
    CHAR_10  CHAR(50),
    CHAR_11  CHAR(50),
    CHAR_12  CHAR(50)
);

Data is filled with a query until 1 million rows are reached (query is shortened).

INSERT INTO T_CHAR_12_50 (CHAR_1, CHAR_2, CHAR_3, CHAR_4, CHAR_5, CHAR_6, CHAR_7, CHAR_8, CHAR_9, CHAR_10, CHAR_11, CHAR_12)
VALUES ( 'Talk is cheap . Show me the code. Linus Torvalds.', ...);

1.4. Data type `date`

CREATE TABLE T_DATE_12 (
    ID         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_DATE_1   DATE,
    T_DATE_2   DATE,
    T_DATE_3   DATE,
    T_DATE_4   DATE,
    T_DATE_5   DATE,
    T_DATE_6   DATE,
    T_DATE_7   DATE,
    T_DATE_8   DATE,
    T_DATE_9   DATE,
    T_DATE_10  DATE,
    T_DATE_11  DATE,
    T_DATE_12  DATE
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_DATE_12 (IT_DATE_1, T_DATE_2, T_DATE_3, T_DATE_4, T_DATE_5, T_DATE_6, T_DATE_7, T_DATE_8, T_DATE_9, T_DATE_10, T_DATE_11, T_DATE_12)
VALUES ('1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001', '1-JAN-2001');

1.5. Data type `integer`

CREATE TABLE T_INT12 (
    ID      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    INT_1   INTEGER,
    INT_2   INTEGER,
    INT_3   INTEGER,
    INT_4   INTEGER,
    INT_5   INTEGER,
    INT_6   INTEGER,
    INT_7   INTEGER,
    INT_8   INTEGER,
    INT_9   INTEGER,
    INT_10  INTEGER,
    INT_11  INTEGER,
    INT_12  INTEGER
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_INT12 (INT_1, INT_2, INT_3, INT_4, INT_5, INT_6, INT_7, INT_8, INT_9, INT_10, INT_11, INT_12)
VALUES (314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265, 314159265);

1.6. Data type `numeric`

CREATE TABLE T_NUM_12 (
    ID        INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_NUM_1   NUMERIC(15,10),
    T_NUM_2   NUMERIC(15,10),
    T_NUM_3   NUMERIC(15,10),
    T_NUM_4   NUMERIC(15,10),
    T_NUM_5   NUMERIC(15,10),
    T_NUM_6   NUMERIC(15,10),
    T_NUM_7   NUMERIC(15,10),
    T_NUM_8   NUMERIC(15,10),
    T_NUM_9   NUMERIC(15,10),
    T_NUM_10  NUMERIC(15,10),
    T_NUM_11  NUMERIC(15,10),
    T_NUM_12  NUMERIC(15,10)
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_NUM_12 (T_NUM_1, T_NUM_2, T_NUM_3, T_NUM_4, T_NUM_5, T_NUM_6, T_NUM_7, T_NUM_8, T_NUM_9, T_NUM_10, T_NUM_11, T_NUM_12)
VALUES (3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535);

1.7. Data type `real` (`float`)

CREATE TABLE T_REAL_12 (
    ID         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_REAL_1   FLOAT,
    T_REAL_2   FLOAT,
    T_REAL_3   FLOAT,
    T_REAL_4   FLOAT,
    T_REAL_5   FLOAT,
    T_REAL_6   FLOAT,
    T_REAL_7   FLOAT,
    T_REAL_8   FLOAT,
    T_REAL_9   FLOAT,
    T_REAL_10  FLOAT,
    T_REAL_11  FLOAT,
    T_REAL_12  FLOAT
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_REAL_12 (T_REAL_1, T_REAL_2, T_REAL_3, T_REAL_4, T_REAL_5, T_REAL_6, T_REAL_7, T_REAL_8, T_REAL_9, T_REAL_10, T_REAL_11, T_REAL_12)
VALUES (3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257, 3.14159274101257);

1.8. Data type `time`

CREATE TABLE T_TIME_12 (
    ID         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_TIME_1   TIME,
    T_TIME_2   TIME,
    T_TIME_3   TIME,
    T_TIME_4   TIME,
    T_TIME_5   TIME,
    T_TIME_6   TIME,
    T_TIME_7   TIME,
    T_TIME_8   TIME,
    T_TIME_9   TIME,
    T_TIME_10  TIME,
    T_TIME_11  TIME,
    T_TIME_12  TIME
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_TIME_12 (ID, T_TIME_1, T_TIME_2, T_TIME_3, T_TIME_4, T_TIME_5, T_TIME_6, T_TIME_7, T_TIME_8, T_TIME_9, T_TIME_10, T_TIME_11, T_TIME_12)
VALUES (1, '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05');

1.9. Data type `time with time zone`

CREATE TABLE T_TIME_WTZ_12 (
    ID             INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_TIME_WTZ_1   TIME WITH TIME ZONE,
    T_TIME_WTZ_2   TIME WITH TIME ZONE,
    T_TIME_WTZ_3   TIME WITH TIME ZONE,
    T_TIME_WTZ_4   TIME WITH TIME ZONE,
    T_TIME_WTZ_5   TIME WITH TIME ZONE,
    T_TIME_WTZ_6   TIME WITH TIME ZONE,
    T_TIME_WTZ_7   TIME WITH TIME ZONE,
    T_TIME_WTZ_8   TIME WITH TIME ZONE,
    T_TIME_WTZ_9   TIME WITH TIME ZONE,
    T_TIME_WTZ_10  TIME WITH TIME ZONE,
    T_TIME_WTZ_11  TIME WITH TIME ZONE,
    T_TIME_WTZ_12  TIME WITH TIME ZONE
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_TIME_WTZ_12 (ID, T_TIME_1, T_TIME_2, T_TIME_3, T_TIME_4, T_TIME_5, T_TIME_6, T_TIME_7, T_TIME_8, T_TIME_9, T_TIME_10, T_TIME_11, T_TIME_12)
VALUES (1, '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05', '5:05:05');

1.10. Data type `timestamp`

CREATE TABLE T_TSTAMP_12 (
    ID           INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_TSTAMP_1   TIMESTAMP,
    T_TSTAMP_2   TIMESTAMP,
    T_TSTAMP_3   TIMESTAMP,
    T_TSTAMP_4   TIMESTAMP,
    T_TSTAMP_5   TIMESTAMP,
    T_TSTAMP_6   TIMESTAMP,
    T_TSTAMP_7   TIMESTAMP,
    T_TSTAMP_8   TIMESTAMP,
    T_TSTAMP_9   TIMESTAMP,
    T_TSTAMP_10  TIMESTAMP,
    T_TSTAMP_11  TIMESTAMP,
    T_TSTAMP_12  TIMESTAMP
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_TSTAMP_12 (T_TSTAMP_1, T_TSTAMP_2, T_TSTAMP_3, T_TSTAMP_4, T_TSTAMP_5, T_TSTAMP_6, T_TSTAMP_7, T_TSTAMP_8, T_TSTAMP_9, T_TSTAMP_10, T_TSTAMP_11, T_TSTAMP_12)
VALUES ('1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00', '1-JAN-2001 00:00:00');

1.11. Data type `timestamp with time zone`

CREATE TABLE T_TSTAMP_WTZ_12 (
    ID               INTEGER GENERATED BY DEFAULT AS IDENTITY,
    T_TSTAMP_WTZ_1   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_2   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_3   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_4   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_5   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_6   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_7   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_8   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_9   TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_10  TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_11  TIMESTAMP WITH TIME ZONE,
    T_TSTAMP_WTZ_12  TIMESTAMP WITH TIME ZONE
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_TSTAMP_WTZ_12 (T_TSTAMP_WTZ_1, T_TSTAMP_WTZ_2, T_TSTAMP_WTZ_3, T_TSTAMP_WTZ_4, T_TSTAMP_WTZ_5, T_TSTAMP_WTZ_6, T_TSTAMP_WTZ_7, T_TSTAMP_WTZ_8, T_TSTAMP_WTZ_9, T_TSTAMP_WTZ_10, T_TSTAMP_WTZ_11, T_TSTAMP_WTZ_12)
VALUES ('1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05', '1-JAN-2001 05:05:05');

1.12. Data type `varchar(200)`

CREATE TABLE T_VARCHAR_12_200 (
    ID          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    VARCHAR_1   VARCHAR(200),
    VARCHAR_2   VARCHAR(200),
    VARCHAR_3   VARCHAR(200),
    VARCHAR_4   VARCHAR(200),
    VARCHAR_5   VARCHAR(200),
    VARCHAR_6   VARCHAR(200),
    VARCHAR_7   VARCHAR(200),
    VARCHAR_8   VARCHAR(200),
    VARCHAR_9   VARCHAR(200),
    VARCHAR_10  VARCHAR(200),
    VARCHAR_11  VARCHAR(200),
    VARCHAR_12  VARCHAR(200)
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_VARCHAR_12_200 (ID, VARCHAR_1, VARCHAR_2, VARCHAR_3, VARCHAR_4, VARCHAR_5, VARCHAR_6, VARCHAR_7, VARCHAR_8, VARCHAR_9, VARCHAR_10, VARCHAR_11, VARCHAR_12)
VALUES (1, 'C++ is a horrible language.It''s made more horrible by the fact that a lot of substandard programmers use it,to the point where it''s much much easier to generate total and utter crap with it.Torvalds. ', ...'')

1.13. Data type `varchar(50)`

CREATE TABLE T_VARCHAR_12_50 (
    ID          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    VARCHAR_1   VARCHAR(50),
    VARCHAR_2   VARCHAR(50),
    VARCHAR_3   VARCHAR(50),
    VARCHAR_4   VARCHAR(50),
    VARCHAR_5   VARCHAR(50),
    VARCHAR_6   VARCHAR(50),
    VARCHAR_7   VARCHAR(50),
    VARCHAR_8   VARCHAR(50),
    VARCHAR_9   VARCHAR(50),
    VARCHAR_10  VARCHAR(50),
    VARCHAR_11  VARCHAR(50),
    VARCHAR_12  VARCHAR(50)
);

Data is filled with queries until 1 million rows are reached.

INSERT INTO T_VARCHAR_12_50 (ID, VARCHAR_1, VARCHAR_2, VARCHAR_3, VARCHAR_4, VARCHAR_5, VARCHAR_6, VARCHAR_7, VARCHAR_8, VARCHAR_9, VARCHAR_10, VARCHAR_11, VARCHAR_12)
VALUES (1, 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ', 'Talk is cheap . Show me the code. Linus Torvalds. ');

As a result, we get two databases filled with the same data. Let’s compare the size of the databases. To get the size of the Firebird database, we will use the isql utility, for PostgreSQL — the psql utility.

Размер базы данных Firebird

Database: 'localhost:c:\DBASE\FB5\ETL3.FDB', User: SYSDBA
SQL> select (MON$PAGE_SIZE * MON$PAGES)/1048576 from MON$DATABASE;
DIVIDE
=============================================
7863

Размер базы данных PostgreSQL:

Table 1. Список баз данных
Name	Owner	Charset	Locale provider	LC_COLLATE	LC_CTYPE	Size
ETL3	postgres	UTF8	libc	C	C	7864 MB

The size is the same. Let’s check the size of tables for different data types. To get the size of a Firebird table, we’ll use the gstat utility, and to get the size of a PostgreSQL table, we’ll use the psql utility. We’ll consolidate the data into a single table:

Table 2. Table sizes by data type
Table	PostgreSQL (MB)	Firebird (MB)
t_bool_12	42	53.63
t_char_12_200	2728	2604.25
t_char_12_50	651	679.38
t_date_12	81	99.63
t_int12	81	99.63
t_num_12	159	146.13
t_real_12	128	99.63
t_time_12	128	99.63
t_time_wtz_12	223	146.13
t_tstamp_12	128	146.13
t_tstamp_wtz_12	128	233.25
t_varchar_12_200	2728	2604.25
t_varchar_12_50	651	710.25

The table shows that PostgreSQL tables storing data in integer types take up less space, while Firebird tables storing data in fixed and floating point and character types take up less space.

2. Comparison of data filling of Firebird 5 and PostgreSQL 17 databases after updating 25% and 50% of all tables

Task: update data in previously created tables with queries affecting 25% and 50% of rows. Compare two previously created databases.

For Firebird: Database size before data update 7863 MB Let’s run the script with the following queries:

update T_VARCHAR_12_200 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID < 250001;

update T_VARCHAR_12_50 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID < 250001;

update T_CHAR_12_200 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID < 250001;

update T_CHAR_12_50 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID < 250001;

update T_INT12 set
    INT_1 = (INT_1-1000),INT_2 = INT_2-1000,INT_3 = INT_3-1000,INT_4 = INT_4-1000,
    INT_5 = INT_5-1000,INT_6 = INT_6-1000,INT_7 = INT_7-1000,INT_8 = INT_8-1000,
    INT_9 = INT_9-1000,INT_10 = INT_10-1000,INT_11 = INT_11-1000,INT_12 = INT_12-1000
where ID < 250001;

update T_DATE_12 set
    T_DATE_1 = DATEADD(365 day to T_DATE_1),T_DATE_2 = DATEADD(365 day to T_DATE_2),T_DATE_3 = DATEADD(365 day to T_DATE_3),
    T_DATE_4 = DATEADD(365 day to T_DATE_4),T_DATE_5 = DATEADD(365 day to T_DATE_5),T_DATE_6 = DATEADD(365 day to T_DATE_6),
    T_DATE_7 = DATEADD(365 day to T_DATE_7),T_DATE_8 = DATEADD(365 day to T_DATE_8),T_DATE_9 = DATEADD(365 day to T_DATE_9),
    T_DATE_10 = DATEADD(365 day to T_DATE_10),T_DATE_11 = DATEADD(365 day to T_DATE_11),T_DATE_12 = DATEADD(365 day to T_DATE_12)
where ID < 250001;

update T_BOOL_12 set
    T_BOOL_1 = T_BOOL_2,T_BOOL_2 = T_BOOL_1,T_BOOL_3 = T_BOOL_2,T_BOOL_4 = T_BOOL_1,T_BOOL_5 = T_BOOL_2,T_BOOL_6 = T_BOOL_1,
    T_BOOL_7 = T_BOOL_2,T_BOOL_8 = T_BOOL_1,T_BOOL_9 = T_BOOL_2,T_BOOL_10 = T_BOOL_1,T_BOOL_11 = T_BOOL_2,T_BOOL_12 = T_BOOL_1
where ID < 250001;

update T_REAL_12 set
    T_REAL_1 = T_REAL_1 - 10000,T_REAL_2 = T_REAL_2 - 10000,T_REAL_3 = T_REAL_2 - 10000,T_REAL_4 = T_REAL_4 - 10000,
    T_REAL_5 = T_REAL_5 - 10000,T_REAL_6 = T_REAL_6 - 10000,T_REAL_7 = T_REAL_7 - 10000,T_REAL_8 = T_REAL_8 - 10000,
    T_REAL_9 = T_REAL_9 - 10000,T_REAL_10 = T_REAL_10 - 10000,T_REAL_11 = T_REAL_11 - 10000,T_REAL_12 = T_REAL_12 - 10000
where ID < 250001;

update T_NUM_12 set
    T_NUM_1 = T_NUM_1 - 10000,T_NUM_2 = T_NUM_2 - 10000,T_NUM_3 = T_NUM_2 - 10000,T_NUM_4 = T_NUM_4 - 10000,
    T_NUM_5 = T_NUM_5 - 10000,T_NUM_6 = T_NUM_6 - 10000,T_NUM_7 = T_NUM_7 - 10000,T_NUM_8 = T_NUM_8 - 10000,
    T_NUM_9 = T_NUM_9 - 10000,T_NUM_10 = T_NUM_10 - 10000,T_NUM_11 = T_NUM_11 - 10000,T_NUM_12 = T_NUM_12 - 10000
where ID < 250001;

update T_TIME_12 set
   T_TIME_1 = DATEADD(minute,60, T_TIME_1),T_TIME_2 = DATEADD(minute,60, T_TIME_2),T_TIME_3 = DATEADD(minute,60, T_TIME_3),
   T_TIME_4 = DATEADD(minute,60, T_TIME_4),T_TIME_5 = DATEADD(minute,60, T_TIME_5),T_TIME_6 = DATEADD(minute,60, T_TIME_6),
   T_TIME_7 = DATEADD(minute,60, T_TIME_7),T_TIME_8 = DATEADD(minute,60, T_TIME_8),T_TIME_9 = DATEADD(minute,60, T_TIME_9),
   T_TIME_10 = DATEADD(minute,60, T_TIME_10),T_TIME_11 = DATEADD(minute,60, T_TIME_11),T_TIME_12 = DATEADD(minute,60, T_TIME_12)
where ID < 250001;

update T_TIME_WTZ_12 set
    T_TIME_WTZ_1 = DATEADD(minute,60, T_TIME_WTZ_1),T_TIME_WTZ_2 = DATEADD(minute,60, T_TIME_WTZ_2),T_TIME_WTZ_3 = DATEADD(minute,60, T_TIME_WTZ_3),
    T_TIME_WTZ_4 = DATEADD(minute,60, T_TIME_WTZ_4),T_TIME_WTZ_5 = DATEADD(minute,60, T_TIME_WTZ_5),T_TIME_WTZ_6 = DATEADD(minute,60, T_TIME_WTZ_6),
    T_TIME_WTZ_7 = DATEADD(minute,60, T_TIME_WTZ_7),T_TIME_WTZ_8 = DATEADD(minute,60, T_TIME_WTZ_8),T_TIME_WTZ_9 = DATEADD(minute,60, T_TIME_WTZ_9),
    T_TIME_WTZ_10 = DATEADD(minute,60, T_TIME_WTZ_10),T_TIME_WTZ_11 = DATEADD(minute,60, T_TIME_WTZ_11),T_TIME_WTZ_12 = DATEADD(minute,60, T_TIME_WTZ_12)
where ID < 250001;

update T_TSTAMP_12 set
    T_TSTAMP_1 = DATEADD(minute,60, T_TSTAMP_1),T_TSTAMP_2 = DATEADD(minute,60, T_TSTAMP_2),T_TSTAMP_3 = DATEADD(minute,60, T_TSTAMP_3),
    T_TSTAMP_4 = DATEADD(minute,60, T_TSTAMP_4),T_TSTAMP_5 = DATEADD(minute,60, T_TSTAMP_5),T_TSTAMP_6 = DATEADD(minute,60, T_TSTAMP_6),
    T_TSTAMP_7 = DATEADD(minute,60, T_TSTAMP_7),T_TSTAMP_8 = DATEADD(minute,60, T_TSTAMP_8),T_TSTAMP_9 = DATEADD(minute,60, T_TSTAMP_9),
    T_TSTAMP_10 = DATEADD(minute,60, T_TSTAMP_10),T_TSTAMP_11 = DATEADD(minute,60, T_TSTAMP_11),T_TSTAMP_12 = DATEADD(minute,60, T_TSTAMP_12)
where ID < 250001;

update T_TSTAMP_WTZ_12 set
    T_TSTAMP_WTZ_1 = DATEADD(minute,60, T_TSTAMP_WTZ_1),T_TSTAMP_WTZ_2 = DATEADD(minute,60, T_TSTAMP_WTZ_2),T_TSTAMP_WTZ_3 = DATEADD(minute,60, T_TSTAMP_WTZ_3),
    T_TSTAMP_WTZ_4 = DATEADD(minute,60, T_TSTAMP_WTZ_4),T_TSTAMP_WTZ_5 = DATEADD(minute,60, T_TSTAMP_WTZ_5),T_TSTAMP_WTZ_6 = DATEADD(minute,60, T_TSTAMP_WTZ_6),
    T_TSTAMP_WTZ_7 = DATEADD(minute,60, T_TSTAMP_WTZ_7),T_TSTAMP_WTZ_8 = DATEADD(minute,60, T_TSTAMP_WTZ_8),T_TSTAMP_WTZ_9 = DATEADD(minute,60, T_TSTAMP_WTZ_9),
    T_TSTAMP_WTZ_10 = DATEADD(minute,60, T_TSTAMP_WTZ_10),T_TSTAMP_WTZ_11 = DATEADD(minute,60, T_TSTAMP_WTZ_11),T_TSTAMP_WTZ_12 = DATEADD(minute,60, T_TSTAMP_WTZ_12)
where ID < 250001;

commit;

Firebird database size after updating 25% of all tables 8504 MB

For PostgreSQL: Database size before updating data 7863 MB Let’s run the script with the following queries:

update T_VARCHAR_12_200 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID < 250001;

update T_VARCHAR_12_50 set VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID < 250001;

update T_CHAR_12_200 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID < 250001;

update T_CHAR_12_50 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID < 250001;

update T_INT12 set
    INT_1 = (INT_1-1000),INT_2 = INT_2-1000,INT_3 = INT_3-1000,INT_4 = INT_4-1000,
    INT_5 = INT_5-1000,INT_6 = INT_6-1000,INT_7 = INT_7-1000,INT_8 = INT_8-1000,
    INT_9 = INT_9-1000,INT_10 = INT_10-1000,INT_11 = INT_11-1000,INT_12 = INT_12-1000
where ID < 250001;

update T_DATE_12 set
    T_DATE_1 = T_DATE_1 + INTERVAL '365 day',T_DATE_2 = T_DATE_2 + INTERVAL '365 day',T_DATE_3 = T_DATE_3 + INTERVAL '365 day',
    T_DATE_4 = T_DATE_4 + INTERVAL '365 day',T_DATE_5 = T_DATE_5 + INTERVAL '365 day',T_DATE_6 = T_DATE_6 + INTERVAL '365 day',
    T_DATE_7 = T_DATE_7 + INTERVAL '365 day',T_DATE_8 = T_DATE_8 + INTERVAL '365 day',T_DATE_9 = T_DATE_9 + INTERVAL '365 day',
    T_DATE_10 = T_DATE_10 + INTERVAL '365 day',T_DATE_11 = T_DATE_11 + INTERVAL '365 day',T_DATE_12 = T_DATE_12 + INTERVAL '365 day'
where ID < 250001;

update T_BOOL_12 set
    T_BOOL_1 = T_BOOL_2,T_BOOL_2 = T_BOOL_1,T_BOOL_3 = T_BOOL_2,T_BOOL_4 = T_BOOL_1,
    T_BOOL_5 = T_BOOL_2,T_BOOL_6 = T_BOOL_1,T_BOOL_7 = T_BOOL_2,T_BOOL_8 = T_BOOL_1,
    T_BOOL_9 = T_BOOL_2,T_BOOL_10 = T_BOOL_1,T_BOOL_11 = T_BOOL_2,T_BOOL_12 = T_BOOL_1
where ID < 250001;

update T_REAL_12 set
    T_REAL_1 = T_REAL_1 - 10000,T_REAL_2 = T_REAL_2 - 10000,T_REAL_3 = T_REAL_2 - 10000,T_REAL_4 = T_REAL_4 - 10000,
    T_REAL_5 = T_REAL_5 - 10000,T_REAL_6 = T_REAL_6 - 10000,T_REAL_7 = T_REAL_7 - 10000,T_REAL_8 = T_REAL_8 - 10000,
    T_REAL_9 = T_REAL_9 - 10000,T_REAL_10 = T_REAL_10 - 10000,T_REAL_11 = T_REAL_11 - 10000,T_REAL_12 = T_REAL_12 - 10000
where ID < 250001;

update T_NUM_12 set
    T_NUM_1 = T_NUM_1 - 10000,T_NUM_2 = T_NUM_2 - 10000,T_NUM_3 = T_NUM_2 - 10000,T_NUM_4 = T_NUM_4 - 10000,
    T_NUM_5 = T_NUM_5 - 10000,T_NUM_6 = T_NUM_6 - 10000,T_NUM_7 = T_NUM_7 - 10000,T_NUM_8 = T_NUM_8 - 10000,
    T_NUM_9 = T_NUM_9 - 10000,T_NUM_10 = T_NUM_10 - 10000,T_NUM_11 = T_NUM_11 - 10000,T_NUM_12 = T_NUM_12 - 10000
where ID < 250001;

update T_TIME_12 set
    T_TIME_1 = T_TIME_1 + INTERVAL '60 hour',T_TIME_2 = T_TIME_2 + INTERVAL '60 hour',
    T_TIME_3 = T_TIME_3 + INTERVAL '60 hour',T_TIME_4 = T_TIME_4 + INTERVAL '60 hour',
    T_TIME_5 = T_TIME_5 + INTERVAL '60 hour',T_TIME_6 = T_TIME_6 + INTERVAL '60 hour',
    T_TIME_7 = T_TIME_7 + INTERVAL '60 hour',T_TIME_8 = T_TIME_8 + INTERVAL '60 hour',
    T_TIME_9 = T_TIME_9 + INTERVAL '60 hour',T_TIME_10 = T_TIME_10 + INTERVAL '60 hour',
    T_TIME_11 = T_TIME_11 + INTERVAL '60 hour',T_TIME_12 = T_TIME_12 + INTERVAL '60 hour'
where ID < 250001;

update T_TIME_WTZ_12 set
    T_TIME_WTZ_1 = T_TIME_WTZ_1 + INTERVAL '60 hour',T_TIME_WTZ_2 = T_TIME_WTZ_2 + INTERVAL '60 hour',
    T_TIME_WTZ_3 = T_TIME_WTZ_3 + INTERVAL '60 hour',T_TIME_WTZ_4 = T_TIME_WTZ_4 + INTERVAL '60 hour',
    T_TIME_WTZ_5 = T_TIME_WTZ_5 + INTERVAL '60 hour',T_TIME_WTZ_6 = T_TIME_WTZ_6 + INTERVAL '60 hour',
    T_TIME_WTZ_7 = T_TIME_WTZ_7 + INTERVAL '60 hour',T_TIME_WTZ_8 = T_TIME_WTZ_8 + INTERVAL '60 hour',
    T_TIME_WTZ_9 = T_TIME_WTZ_9 + INTERVAL '60 hour',T_TIME_WTZ_10 = T_TIME_WTZ_10 + INTERVAL '60 hour',
    T_TIME_WTZ_11 = T_TIME_WTZ_11 + INTERVAL '60 hour',T_TIME_WTZ_12 = T_TIME_WTZ_12 + INTERVAL '60 hour'
where ID < 250001;

update T_TSTAMP_12 set
    T_TSTAMP_1 = T_TSTAMP_1 + INTERVAL '60 hour',T_TSTAMP_2 = T_TSTAMP_2 + INTERVAL '60 hour',
    T_TSTAMP_3 = T_TSTAMP_3 + INTERVAL '60 hour',T_TSTAMP_4 = T_TSTAMP_4 + INTERVAL '60 hour',
    T_TSTAMP_5 = T_TSTAMP_5 + INTERVAL '60 hour',T_TSTAMP_6 = T_TSTAMP_6 + INTERVAL '60 hour',
    T_TSTAMP_7 = T_TSTAMP_7 + INTERVAL '60 hour',T_TSTAMP_8 = T_TSTAMP_8 + INTERVAL '60 hour',
    T_TSTAMP_9 = T_TSTAMP_9 + INTERVAL '60 hour',T_TSTAMP_10 = T_TSTAMP_10 + INTERVAL '60 hour',
    T_TSTAMP_11 = T_TSTAMP_11 + INTERVAL '60 hour',T_TSTAMP_12 = T_TSTAMP_12 + INTERVAL '60 hour'
where ID < 250001;

update T_TSTAMP_WTZ_12  set
    T_TSTAMP_WTZ_1 =  T_TSTAMP_WTZ_1 + INTERVAL '60 hour',T_TSTAMP_WTZ_2 =  T_TSTAMP_WTZ_2 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_3 =  T_TSTAMP_WTZ_3 + INTERVAL '60 hour',T_TSTAMP_WTZ_4 =  T_TSTAMP_WTZ_4 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_5 =  T_TSTAMP_WTZ_5 + INTERVAL '60 hour',T_TSTAMP_WTZ_6 =  T_TSTAMP_WTZ_6 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_7 =  T_TSTAMP_WTZ_7 + INTERVAL '60 hour',T_TSTAMP_WTZ_8 =  T_TSTAMP_WTZ_8 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_9 =  T_TSTAMP_WTZ_9 + INTERVAL '60 hour',T_TSTAMP_WTZ_10 =  T_TSTAMP_WTZ_10 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_11 =  T_TSTAMP_WTZ_11 + INTERVAL '60 hour',T_TSTAMP_WTZ_12 =  T_TSTAMP_WTZ_12 + INTERVAL '60 hour'
where ID < 250001;

The size of the PostgreSQL database after updating 25% of all tables is 9862 MB.

Let’s check the size of the tables and summarize the results in a table:

Table 3. Table sizes by data type
Table	PostgreSQL (MB)	Firebird (MB)
t_bool_12	53 MB	53.63 MB
t_char_12_200	3411 MB	2604.25 MB
t_char_12_50	814 MB	679.38 MB
t_date_12	101 MB	99.63 MB
t_int12	101 MB	99.63 MB
t_num_12	199 MB	146.13 MB
t_real_12	160 MB	99.63 MB
t_time_12	160 MB	99.63 MB
t_time_wtz_12	279 MB	146.13 MB
t_tstamp_12	160 MB	146.13 MB
t_tstamp_wtz_12	160 MB	233.25 MB
t_varchar_12_200	3442 MB	2604.25 MB
t_varchar_12_50	814 MB	710.25 MB

Let’s update the data again, up to 50% of all data (more precisely the next 25%)

Firebird Let’s execute the script with the following queries:

update T_VARCHAR_12_200 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID between 250001 and 500000;

update T_VARCHAR_12_50 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID between 250001 and 500000;

update T_CHAR_12_20 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID between 250001 and 500000;

update T_CHAR_12_50 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID between 250001 and 500000;

update T_INT12 set
    INT_1 = (INT_1-1000),INT_2 = INT_2-1000,INT_3 = INT_3-1000,INT_4 = INT_4-1000,
    INT_5 = INT_5-1000,INT_6 = INT_6-1000,INT_7 = INT_7-1000,INT_8 = INT_8-1000,
    INT_9 = INT_9-1000,INT_10 = INT_10-1000,INT_11 = INT_11-1000,INT_12 = INT_12-1000
where ID between 250001 and 500000;

update T_DATE_12  set
    T_DATE_1 = DATEADD(365 day to T_DATE_1),T_DATE_2 = DATEADD(365 day to T_DATE_2),T_DATE_3 = DATEADD(365 day to T_DATE_3),
    T_DATE_4 = DATEADD(365 day to T_DATE_4),T_DATE_5 = DATEADD(365 day to T_DATE_5),T_DATE_6 = DATEADD(365 day to T_DATE_6),
    T_DATE_7 = DATEADD(365 day to T_DATE_7),T_DATE_8 = DATEADD(365 day to T_DATE_8),T_DATE_9 = DATEADD(365 day to T_DATE_9),
    T_DATE_10 = DATEADD(365 day to T_DATE_10),T_DATE_11 = DATEADD(365 day to T_DATE_11),T_DATE_12 = DATEADD(365 day to T_DATE_12)
where ID between 250001 and 500000;

update T_BOOL_12 set
    T_BOOL_1 = T_BOOL_2,T_BOOL_2 = T_BOOL_1,T_BOOL_3 = T_BOOL_2,T_BOOL_4 = T_BOOL_1,T_BOOL_5 = T_BOOL_2,T_BOOL_6 = T_BOOL_1,
    T_BOOL_7 = T_BOOL_2,T_BOOL_8 = T_BOOL_1,T_BOOL_9 = T_BOOL_2,T_BOOL_10 = T_BOOL_1,T_BOOL_11 = T_BOOL_2,T_BOOL_12 = T_BOOL_1
where ID between 250001 and 500000;

update T_REAL_12 set
    T_REAL_1 = T_REAL_1 - 10000,T_REAL_2 = T_REAL_2 - 10000,T_REAL_3 = T_REAL_2 - 10000,T_REAL_4 = T_REAL_4 - 10000,
    T_REAL_5 = T_REAL_5 - 10000,T_REAL_6 = T_REAL_6 - 10000,T_REAL_7 = T_REAL_7 - 10000,T_REAL_8 = T_REAL_8 - 10000,
    T_REAL_9 = T_REAL_9 - 10000,T_REAL_10 = T_REAL_10 - 10000,T_REAL_11 = T_REAL_11 - 10000,T_REAL_12 = T_REAL_12 - 10000
where ID between 250001 and 500000;

update T_NUM_12 set
    T_NUM_1 = T_NUM_1 - 10000,T_NUM_2 = T_NUM_2 - 10000,T_NUM_3 = T_NUM_2 - 10000,T_NUM_4 = T_NUM_4 - 10000,
    T_NUM_5 = T_NUM_5 - 10000,T_NUM_6 = T_NUM_6 - 10000,T_NUM_7 = T_NUM_7 - 10000,T_NUM_8 = T_NUM_8 - 10000,
    T_NUM_9 = T_NUM_9 - 10000,T_NUM_10 = T_NUM_10 - 10000,T_NUM_11 = T_NUM_11 - 10000,T_NUM_12 = T_NUM_12 - 10000
where ID between 250001 and 500000;

update T_TIME_12 set
   T_TIME_1 = DATEADD(minute,60, T_TIME_1),T_TIME_2 = DATEADD(minute,60, T_TIME_2),T_TIME_3 = DATEADD(minute,60, T_TIME_3),
   T_TIME_4 = DATEADD(minute,60, T_TIME_4),T_TIME_5 = DATEADD(minute,60, T_TIME_5),T_TIME_6 = DATEADD(minute,60, T_TIME_6),
   T_TIME_7 = DATEADD(minute,60, T_TIME_7),T_TIME_8 = DATEADD(minute,60, T_TIME_8),T_TIME_9 = DATEADD(minute,60, T_TIME_9),
   T_TIME_10 = DATEADD(minute,60, T_TIME_10),T_TIME_11 = DATEADD(minute,60, T_TIME_11),T_TIME_12 = DATEADD(minute,60, T_TIME_12)
where ID between 250001 and 500000;

update T_TIME_WTZ_12 set
    T_TIME_WTZ_1 = DATEADD(minute,60, T_TIME_WTZ_1),T_TIME_WTZ_2 = DATEADD(minute,60, T_TIME_WTZ_2),T_TIME_WTZ_3 = DATEADD(minute,60, T_TIME_WTZ_3),
    T_TIME_WTZ_4 = DATEADD(minute,60, T_TIME_WTZ_4),T_TIME_WTZ_5 = DATEADD(minute,60, T_TIME_WTZ_5),T_TIME_WTZ_6 = DATEADD(minute,60, T_TIME_WTZ_6),
    T_TIME_WTZ_7 = DATEADD(minute,60, T_TIME_WTZ_7),T_TIME_WTZ_8 = DATEADD(minute,60, T_TIME_WTZ_8),T_TIME_WTZ_9 = DATEADD(minute,60, T_TIME_WTZ_9),
    T_TIME_WTZ_10 = DATEADD(minute,60, T_TIME_WTZ_10),T_TIME_WTZ_11 = DATEADD(minute,60, T_TIME_WTZ_11),T_TIME_WTZ_12 = DATEADD(minute,60, T_TIME_WTZ_12)
where ID between 250001 and 500000;

update T_TSTAMP_12 set
    T_TSTAMP_1 = DATEADD(minute,60, T_TSTAMP_1),T_TSTAMP_2 = DATEADD(minute,60, T_TSTAMP_2),T_TSTAMP_3 = DATEADD(minute,60, T_TSTAMP_3),
    T_TSTAMP_4 = DATEADD(minute,60, T_TSTAMP_4),T_TSTAMP_5 = DATEADD(minute,60, T_TSTAMP_5),T_TSTAMP_6 = DATEADD(minute,60, T_TSTAMP_6),
    T_TSTAMP_7 = DATEADD(minute,60, T_TSTAMP_7),T_TSTAMP_8 = DATEADD(minute,60, T_TSTAMP_8),T_TSTAMP_9 = DATEADD(minute,60, T_TSTAMP_9),
    T_TSTAMP_10 = DATEADD(minute,60, T_TSTAMP_10),T_TSTAMP_11 = DATEADD(minute,60, T_TSTAMP_11),T_TSTAMP_12 = DATEADD(minute,60, T_TSTAMP_12)
where ID between 250001 and 500000;

update T_TSTAMP_WTZ_12 set
    T_TSTAMP_WTZ_1 = DATEADD(minute,60, T_TSTAMP_WTZ_1),T_TSTAMP_WTZ_2 = DATEADD(minute,60, T_TSTAMP_WTZ_2),T_TSTAMP_WTZ_3 = DATEADD(minute,60, T_TSTAMP_WTZ_3),
    T_TSTAMP_WTZ_4 = DATEADD(minute,60, T_TSTAMP_WTZ_4),T_TSTAMP_WTZ_5 = DATEADD(minute,60, T_TSTAMP_WTZ_5),T_TSTAMP_WTZ_6 = DATEADD(minute,60, T_TSTAMP_WTZ_6),
    T_TSTAMP_WTZ_7 = DATEADD(minute,60, T_TSTAMP_WTZ_7),T_TSTAMP_WTZ_8 = DATEADD(minute,60, T_TSTAMP_WTZ_8),T_TSTAMP_WTZ_9 = DATEADD(minute,60, T_TSTAMP_WTZ_9),
    T_TSTAMP_WTZ_10 = DATEADD(minute,60, T_TSTAMP_WTZ_10),T_TSTAMP_WTZ_11 = DATEADD(minute,60, T_TSTAMP_WTZ_11),T_TSTAMP_WTZ_12 = DATEADD(minute,60, T_TSTAMP_WTZ_12)
where ID between 250001 and 500000;

commit;

Firebird database size after updating 50% of all tables 9528 MB

For PostgreSQL: Let’s run the script with the following queries:

update T_VARCHAR_12_200 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID between 250001 and 500000;

update T_VARCHAR_12_50 set
    VARCHAR_1 = reverse(VARCHAR_1),VARCHAR_2 = reverse(VARCHAR_2),VARCHAR_3 = reverse(VARCHAR_3),
    VARCHAR_4 = reverse(VARCHAR_4),VARCHAR_5 = reverse(VARCHAR_5),VARCHAR_6 = reverse(VARCHAR_6),
    VARCHAR_7 = reverse(VARCHAR_7),VARCHAR_8 = reverse(VARCHAR_8),VARCHAR_9 = reverse(VARCHAR_9),
    VARCHAR_10 = reverse(VARCHAR_10),VARCHAR_11 = reverse(VARCHAR_11),VARCHAR_12 = reverse(VARCHAR_12)
where ID between 250001 and 500000;

update T_CHAR_12_200 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID between 250001 and 500000;

update T_CHAR_12_50 set
    CHAR_1 = reverse(CHAR_1),CHAR_2 = reverse(CHAR_2),CHAR_3 = reverse(CHAR_3),
    CHAR_4 = reverse(CHAR_4),CHAR_5 = reverse(CHAR_5),CHAR_6 = reverse(CHAR_6),
    CHAR_7 = reverse(CHAR_7),CHAR_8 = reverse(CHAR_8),CHAR_9 = reverse(CHAR_9),
    CHAR_10 = reverse(CHAR_10),CHAR_11 = reverse(CHAR_11),CHAR_12 = reverse(CHAR_12)
where ID between 250001 and 500000;

update T_INT12 set
    INT_1 = (INT_1-1000),INT_2 = INT_2-1000,INT_3 = INT_3-1000,INT_4 = INT_4-1000,
    INT_5 = INT_5-1000,INT_6 = INT_6-1000,INT_7 = INT_7-1000,INT_8 = INT_8-1000,
    INT_9 = INT_9-1000,INT_10 = INT_10-1000,INT_11 = INT_11-1000,INT_12 = INT_12-1000
where ID between 250001 and 500000;

update T_DATE_12 set
    T_DATE_1 = T_DATE_1 + INTERVAL '365 day',T_DATE_2 = T_DATE_2 + INTERVAL '365 day',T_DATE_3 = T_DATE_3 + INTERVAL '365 day',
    T_DATE_4 = T_DATE_4 + INTERVAL '365 day',T_DATE_5 = T_DATE_5 + INTERVAL '365 day',T_DATE_6 = T_DATE_6 + INTERVAL '365 day',
    T_DATE_7 = T_DATE_7 + INTERVAL '365 day',T_DATE_8 = T_DATE_8 + INTERVAL '365 day',T_DATE_9 = T_DATE_9 + INTERVAL '365 day',
    T_DATE_10 = T_DATE_10 + INTERVAL '365 day',T_DATE_11 = T_DATE_11 + INTERVAL '365 day',T_DATE_12 = T_DATE_12 + INTERVAL '365 day'
where ID between 250001 and 500000;

update T_BOOL_12 set
    T_BOOL_1 = T_BOOL_2,T_BOOL_2 = T_BOOL_1,T_BOOL_3 = T_BOOL_2,T_BOOL_4 = T_BOOL_1,
    T_BOOL_5 = T_BOOL_2,T_BOOL_6 = T_BOOL_1,T_BOOL_7 = T_BOOL_2,T_BOOL_8 = T_BOOL_1,
    T_BOOL_9 = T_BOOL_2,T_BOOL_10 = T_BOOL_1,T_BOOL_11 = T_BOOL_2,T_BOOL_12 = T_BOOL_1
where ID between 250001 and 500000;

update T_REAL_12 set
    T_REAL_1 = T_REAL_1 - 10000,T_REAL_2 = T_REAL_2 - 10000,T_REAL_3 = T_REAL_2 - 10000,T_REAL_4 = T_REAL_4 - 10000,
    T_REAL_5 = T_REAL_5 - 10000,T_REAL_6 = T_REAL_6 - 10000,T_REAL_7 = T_REAL_7 - 10000,T_REAL_8 = T_REAL_8 - 10000,
    T_REAL_9 = T_REAL_9 - 10000,T_REAL_10 = T_REAL_10 - 10000,T_REAL_11 = T_REAL_11 - 10000,T_REAL_12 = T_REAL_12 - 10000
where ID between 250001 and 500000;

update T_NUM_12 set
    T_NUM_1 = T_NUM_1 - 10000,T_NUM_2 = T_NUM_2 - 10000,T_NUM_3 = T_NUM_2 - 10000,T_NUM_4 = T_NUM_4 - 10000,
    T_NUM_5 = T_NUM_5 - 10000,T_NUM_6 = T_NUM_6 - 10000,T_NUM_7 = T_NUM_7 - 10000,T_NUM_8 = T_NUM_8 - 10000,
    T_NUM_9 = T_NUM_9 - 10000,T_NUM_10 = T_NUM_10 - 10000,T_NUM_11 = T_NUM_11 - 10000,T_NUM_12 = T_NUM_12 - 10000
where ID between 250001 and 500000;

update T_TIME_12 set
    T_TIME_1 = T_TIME_1 + INTERVAL '60 hour',T_TIME_2 = T_TIME_2 + INTERVAL '60 hour',
    T_TIME_3 = T_TIME_3 + INTERVAL '60 hour',T_TIME_4 = T_TIME_4 + INTERVAL '60 hour',
    T_TIME_5 = T_TIME_5 + INTERVAL '60 hour',T_TIME_6 = T_TIME_6 + INTERVAL '60 hour',
    T_TIME_7 = T_TIME_7 + INTERVAL '60 hour',T_TIME_8 = T_TIME_8 + INTERVAL '60 hour',
    T_TIME_9 = T_TIME_9 + INTERVAL '60 hour',T_TIME_10 = T_TIME_10 + INTERVAL '60 hour',
    T_TIME_11 = T_TIME_11 + INTERVAL '60 hour',T_TIME_12 = T_TIME_12 + INTERVAL '60 hour'
where ID between 250001 and 500000;

update T_TIME_WTZ_12 set
    T_TIME_WTZ_1 = T_TIME_WTZ_1 + INTERVAL '60 hour',T_TIME_WTZ_2 = T_TIME_WTZ_2 + INTERVAL '60 hour',
    T_TIME_WTZ_3 = T_TIME_WTZ_3 + INTERVAL '60 hour',T_TIME_WTZ_4 = T_TIME_WTZ_4 + INTERVAL '60 hour',
    T_TIME_WTZ_5 = T_TIME_WTZ_5 + INTERVAL '60 hour',T_TIME_WTZ_6 = T_TIME_WTZ_6 + INTERVAL '60 hour',
    T_TIME_WTZ_7 = T_TIME_WTZ_7 + INTERVAL '60 hour',T_TIME_WTZ_8 = T_TIME_WTZ_8 + INTERVAL '60 hour',
    T_TIME_WTZ_9 = T_TIME_WTZ_9 + INTERVAL '60 hour',T_TIME_WTZ_10 = T_TIME_WTZ_10 + INTERVAL '60 hour',
    T_TIME_WTZ_11 = T_TIME_WTZ_11 + INTERVAL '60 hour',T_TIME_WTZ_12 = T_TIME_WTZ_12 + INTERVAL '60 hour'
where ID between 250001 and 500000;

update T_TSTAMP_12 set
    T_TSTAMP_1 = T_TSTAMP_1 + INTERVAL '60 hour',T_TSTAMP_2 = T_TSTAMP_2 + INTERVAL '60 hour',
    T_TSTAMP_3 = T_TSTAMP_3 + INTERVAL '60 hour',T_TSTAMP_4 = T_TSTAMP_4 + INTERVAL '60 hour',
    T_TSTAMP_5 = T_TSTAMP_5 + INTERVAL '60 hour',T_TSTAMP_6 = T_TSTAMP_6 + INTERVAL '60 hour',
    T_TSTAMP_7 = T_TSTAMP_7 + INTERVAL '60 hour',T_TSTAMP_8 = T_TSTAMP_8 + INTERVAL '60 hour',
    T_TSTAMP_9 = T_TSTAMP_9 + INTERVAL '60 hour',T_TSTAMP_10 = T_TSTAMP_10 + INTERVAL '60 hour',
    T_TSTAMP_11 = T_TSTAMP_11 + INTERVAL '60 hour',T_TSTAMP_12 = T_TSTAMP_12 + INTERVAL '60 hour'
where ID between 250001 and 500000;

update T_TSTAMP_WTZ_12 set
    T_TSTAMP_WTZ_1 =  T_TSTAMP_WTZ_1 + INTERVAL '60 hour',T_TSTAMP_WTZ_2 =  T_TSTAMP_WTZ_2 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_3 =  T_TSTAMP_WTZ_3 + INTERVAL '60 hour',T_TSTAMP_WTZ_4 =  T_TSTAMP_WTZ_4 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_5 =  T_TSTAMP_WTZ_5 + INTERVAL '60 hour',T_TSTAMP_WTZ_6 =  T_TSTAMP_WTZ_6 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_7 =  T_TSTAMP_WTZ_7 + INTERVAL '60 hour',T_TSTAMP_WTZ_8 =  T_TSTAMP_WTZ_8 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_9 =  T_TSTAMP_WTZ_9 + INTERVAL '60 hour',T_TSTAMP_WTZ_10 =  T_TSTAMP_WTZ_10 + INTERVAL '60 hour',
    T_TSTAMP_WTZ_11 =  T_TSTAMP_WTZ_11 + INTERVAL '60 hour',T_TSTAMP_WTZ_12 =  T_TSTAMP_WTZ_12 + INTERVAL '60 hour'
where ID between 250001 and 500000;

PostgreSQL database size after updating 50% of all tables 9894 MB Let’s summarize the results in a table

Table 4. Table sizes by data type
Table	PostgreSQL (MB)	Firebird (MB)
t_bool_12	53	53.63
t_char_12_200	3427	3255.25
t_char_12_50	814	835.13
t_date_12	101	109.13
t_int12	101	109.13
t_num_12	199	167.13
t_real_12	160	109.13
t_time_12	160	109.13
t_time_wtz_12	279	158.50
t_tstamp_12	160	158.50
t_tstamp_wtz_12	160	245.88
t_varchar_12_200	3458	3255.25
t_varchar_12_50	814	865.63

3. Conclusions

Having analyzed the presented data on storing information in PostgreSQL 17 and Firebird 5, we can draw the following conclusions about the differences between these database management systems - when initially filling the databases with the same data sets (1 million rows), the total size of the databases was almost identical - about 7863-7864 MB, which indicates comparable overall storage efficiency. However, upon detailed consideration by data types, the following differences are observed:

Numeric data types:
- PostgreSQL stores integer types more efficiently — 81 MB versus 99.63 MB in Firebird
- Firebird stores floating point types more efficiently — 99.63 MB versus 128 MB and 146.13 MB versus 159 MB
Character data types:
- Firebird stores char and varchar data more efficiently, especially with large field sizes (200 characters) — 2604.25 MB versus 2728 MB in PostgreSQL
Temporal data types:
- PostgreSQL and Firebird are approximately equally efficient at storing simple temporary data
- When using time zones (time with timezone, timestamp with timezone), the systems demonstrate a different approach: PostgreSQL uses more space for time with timezone (223 MB versus 146.13 MB), but less for timestamp with timezone (128 MB vs. 233.25 MB)

3.1. Updating data

There are significant differences in the approaches to space management when updating data in the databases (25% and 50% of all rows):

When updating 25% of the data:
- PostgreSQL: the size increased to 9862 MB (by 1999 MB or ~25%)
- Firebird: the size increased to 8504 MB (by 641 MB or ~8%)
When updating 50% of the data:
- PostgreSQL: the size slightly increased to 9894 MB (by 32 MB relative to the 25% update)
- Firebird: the size increased to 9528 MB (by another 1024 MB or ~12%)
The overall difference after updating 50% of the data:
- PostgreSQL: an increase of 2031 MB or ~26% of the original size
- Firebird: an increase of 1665 MB or ~21% of original size

3.2. Key findings

Storage model:
- PostgreSQL creates more additional space immediately on update (significant growth on the first update)
- Firebird shows more linear growth in size with successive updates
Efficiency with changes:
- Firebird is more economical with space when data changes frequently, especially when working with character data types
- PostgreSQL requires more additional space during updates, which can be related to its MVCC model (multi-version concurrency control)
Data type specialization:
- PostgreSQL is more efficient when working with integer and some time data types
- Firebird shows an advantage when working with character data and floating-point numbers
Impact of data type on performance:
- The choice of data type has a significant impact on storage efficiency in both DBMSs
- Using data types with time zones requires additional resources in both systems, but is implemented differently.

These differences should be taken into account when designing databases, especially if frequent updates of information or work with certain types of data are assumed. Depending on the specifics of the application, one of the DBMSs may provide significant advantages in terms of storage efficiency.

Comparison of data filling of Firebird 5 and PostgreSQL 17 databases

Preface

1. Create tables and fill them with data

1.1. Data type boolean

1.2. Data type char(200)

1.3. Data type char(50)

1.4. Data type date

1.5. Data type integer

1.6. Data type numeric

1.7. Data type real (float)

1.8. Data type time

1.9. Data type time with time zone

1.10. Data type timestamp

1.11. Data type timestamp with time zone

1.12. Data type varchar(200)

1.13. Data type varchar(50)

2. Comparison of data filling of Firebird 5 and PostgreSQL 17 databases after updating 25% and 50% of all tables

3. Conclusions

3.1. Updating data

3.2. Key findings

1.1. Data type `boolean`

1.2. Data type `char(200)`

1.3. Data type `char(50)`

1.4. Data type `date`

1.5. Data type `integer`

1.6. Data type `numeric`

1.7. Data type `real` (`float`)

1.8. Data type `time`

1.9. Data type `time with time zone`

1.10. Data type `timestamp`

1.11. Data type `timestamp with time zone`

1.12. Data type `varchar(200)`

1.13. Data type `varchar(50)`