PostgreSQL and mySQL database size question - Mailing list pgsql-general

From Fred Ingham
Subject PostgreSQL and mySQL database size question
Date
Msg-id PNEJKLGAKBJECOKMAOPHIEDDCPAA.ingham@erols.com
Whole thread Raw
Responses Re: PostgreSQL and mySQL database size question
Re: PostgreSQL and mySQL database size question
List pgsql-general
To all,

Before I begin, this is NOT a PostgreSQL verses mySQL post.  I have used
both databases and have found both of these products to be excellent choices
depending on the specific requirements for a  given project.   That said, I
am trying to understand the database size differences between PostgreSQL and
mySQL for a current project I am working on.  I have attached the schemas
for the PostgreSQL and mySQL databases below (minus three tables that are
not important for this discussion and have no data in them).

I created the database as defined in the schemas below in both PostgreSQL
v7.1 (on Windows 2000 via cygwin) and mySQL 3.23.37 (on Windows 2000 native)
and populated them both with the same data: PIN table with 1,740 tuples and
the PINNDX table with 92,488 tuples.  Looking at the number of files and
size of the files, I get the following (after doing a vacuumdb with
PostgreSQL):

PostgreSQL
  Files  109
  Size   60.7MB

mySQL
  Files  24 (3 x 8 tables)
  Size   11.1MB

Why is there such a discrepancy in the database sizes?  I know that
PostgreSQL has additional capabilities such as logging however, without
being able to ascribe a function to each of the numbered files in the
PostgreSQL database, it is unclear to me what is taking up so much space.
Performance and capabilities (and the cost of disk space) aside, this is a
major problem for me.  If anyone can shed some light on this I would
appreciate it.

Thanks, Fred

---- PostgreSQL schema ----

CREATE SEQUENCE pins_seq;

CREATE TABLE pins (
  pinnum    INTEGER   NOT NULL DEFAULT nextval('pins_seq') PRIMARY KEY,
  modified  TIMESTAMP NOT NULL DEFAULT now(),
  indexed   CHAR(1)   NOT NULL DEFAULT 'N',
  sindexed  CHAR(1)   NOT NULL DEFAULT 'N',
  pin       TEXT      NOT NULL
)
;

CREATE INDEX indexed_ndx  ON pins (indexed);
CREATE INDEX sindexed_ndx ON pins (sindexed);

CREATE SEQUENCE pinndx_seq;

CREATE TABLE pinndx (
  pinndxnum INTEGER NOT NULL DEFAULT nextval('pinndx_seq') PRIMARY KEY,
  parent    INTEGER NOT NULL,
  tagpath   INTEGER NOT NULL,
  tagtype   CHAR(1)          NOT NULL,
  tagname   INTEGER NOT NULL,
  atrname   INTEGER NOT NULL,
  pinnum    INTEGER NOT NULL,
  nvalue    FLOAT4,
  value     TEXT
)
;

CREATE INDEX parent_ndx  ON pinndx (parent);
CREATE INDEX tagpath_ndx ON pinndx (tagpath);
CREATE INDEX tagname_ndx ON pinndx (tagname);
CREATE INDEX atrname_ndx ON pinndx (atrname);
CREATE INDEX pinnum_ndx  ON pinndx (pinnum);
CREATE INDEX nvalue_ndx  ON pinndx (nvalue);
CREATE INDEX value_ndx   ON pinndx (value);

CREATE SEQUENCE tagpath_seq;

CREATE TABLE tagpathtbl (
  vkey   INTEGER NOT NULL DEFAULT nextval('tagpath_seq') PRIMARY KEY,
  value VARCHAR(255) NOT NULL
);

CREATE INDEX tagpathtbl_ndx ON tagpathtbl (value);

CREATE SEQUENCE tagname_seq;

CREATE TABLE tagnametbl (
  vkey  INTEGER NOT NULL DEFAULT nextval('tagname_seq') PRIMARY KEY,
  value VARCHAR(255) NOT NULL
);

CREATE INDEX tagnametbl_ndx ON tagnametbl (value);

CREATE SEQUENCE atrname_seq;

CREATE TABLE atrnametbl (
  vkey  INTEGER NOT NULL DEFAULT nextval('atrname_seq') PRIMARY KEY,
  value VARCHAR(255) NOT NULL
);

CREATE INDEX atrnametbl_ndx ON atrnametbl (value);

---- mySQL schema ----

CREATE TABLE pins (
  pinnum    INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  modified  DATETIME         NOT NULL,
  indexed   CHAR(1)          NOT NULL DEFAULT 'N',
  INDEX indexedndx (indexed),
  sindexed  CHAR(1)          NOT NULL DEFAULT 'N',
  INDEX sindexedndx (sindexed),
  pin       MEDIUMTEXT       NOT NULL
)
AVG_ROW_LENGTH=5000
MAX_ROWS=315360000
PACK_KEYS=1
ROW_FORMAT=compressed
;

CREATE TABLE pinndx (
  pinndxnum INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent    INTEGER UNSIGNED NOT NULL,
  INDEX parentndx (parent),
  tagpath   INTEGER UNSIGNED NOT NULL,
  INDEX tagpathndx (tagpath),
  tagtype   CHAR(1)          NOT NULL,
  tagname   INTEGER UNSIGNED NOT NULL,
  INDEX tagnamendx (tagname),
  atrname   INTEGER UNSIGNED NOT NULL,
  INDEX atrnamendx (atrname),
  pinnum    INTEGER UNSIGNED NOT NULL,
  INDEX pinnumndx (pinnum),
  nvalue    DOUBLE,
  INDEX nvaluendx (nvalue),
  value     MEDIUMTEXT,
  INDEX valuendx (value(30))
)
AVG_ROW_LENGTH=500
MAX_ROWS=3153600000
PACK_KEYS=1
;

CREATE TABLE tagpathtbl (
  vkey   INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(255) NOT NULL,
  INDEX tagpathndx (value(30))
);

CREATE TABLE tagnametbl (
  vkey   INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(255) NOT NULL,
  INDEX tagnamendx (value(30))
);

CREATE TABLE atrnametbl (
  vkey   INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(255) NOT NULL,
  INDEX atrnamendx (value(30))
);


pgsql-general by date:

Previous
From: John Pagakis
Date:
Subject: ON DELETE Behavior after the fact
Next
From: Vince Vielhaber
Date:
Subject: While we're on the subject of searches...