Re: PostgreSQL and mySQL database size question

Did you see the FAQ items on estimating database sizes? Does MySQL have
less overhead per row?

[ Charset ISO-8859-1 unsupported, converting... ]
> 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))
> );
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026