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: