3

I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.

The point is: from 50 mb plain data (~700,000 lines), this results in 600 mb db size. This is a factor 12! I am sure I am doing something wrong here. Could you help me to reduze the size of my db? Source of database size is the web postgres admin interface.

Here's the insert:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');       ## max lenght of enum names ~15

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT UNIQUE,                     ## max 51 char String
   ATTRIBUTE_THREE TEXT UNIQUE,                     ## max 52 char String
   ATTRIBUTE_FOUR  TEXT UNIQUE,                     ## max 64 char String
   ATTRIBUTE_FIFE  TEXT UNIQUE,                     ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

I don't really need that enum since I can insert it without, too. Does a enum have an effect on the database size?

Is there a way to reduce the size? Is it possible to reach factor x4 (instead of x12)? If not, I could delete some of the columns, if necessary.

Maybe there are other Postgres data types for character data?

After feedback here, my updated table looks now like this:

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT,                            ## max 51 char String
   ATTRIBUTE_THREE TEXT,                            ## max 52 char String
   ATTRIBUTE_FOUR  TEXT,                            ## max 64 char String
   ATTRIBUTE_FIFE  TEXT,                            ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

Before: 12x

Now: 7x :)

Are there any more possible optimizations? (Except deleting columns?) Maybe other data types using less space?

  • 1
    The UNIQUE on every column doesn't really make sense to me, but: each unique constraint is implemented through a unique index. So you have 6 indexes on that table, one for each column (except the customer_type). – a_horse_with_no_name Dec 14 '17 at 13:10
  • The link http://localhost:8051/#/details-pg/pg10 isn't helpful - my computer has no application listening on port 8051 – a_horse_with_no_name Dec 14 '17 at 13:10
  • Thanks. I used the indexes to be able to search fast. But I indeed do not need every index. Are there other ways? The link is just you know from where I got the size information (web postgres admin interface) since I dont know if rightclick on the db folder reflect the actual size. – A.c Dec 14 '17 at 13:18
  • Ok, deleted all indexes execpt PK on CUSTOMER_ONE and unique on ATTRIBUTE_ONE. --> 330 MB (factor 7). WAY BETTER :) I edited my post. Maybe we can optimize a little bit more. – A.c Dec 14 '17 at 13:55
  • BTW, this is column backwards CUSTOMER_TYPE PRIVATEKEYTYPE. CUSTOMER_TYPE is the type name, guess you reversed that. Reveals that you altered the table definition manually for the question, which may have introduced errors in translation. – Erwin Brandstetter Dec 14 '17 at 15:09
4

text is the best type for plain text. Text values alone occupy about the same internally (depends on encoding details, too of course: UTF-8?). A small overhead of 1 byte per field in your case. See:

The enum column contributes very little to the total size. 4 bytes per row (an integer quantity internally). The length of enum names is practically irrelevant, stored once. Minimal overhead for the type declaration. So the enum column actually occupies less space inside the DB than in your text file. There are various types of overhead in Postgres storage, it has to be like that. Some of them one-time costs, some other scale with the number of rows or relations etc. There are catalog tables (system data), indexes, relationships, table statistics, bookkeeping data etc.

12x the size seems more than average for 50 MB of raw text data. But that really depends on details. Many small rows have a lot of overhead, few large rows not so much, can even be compressed and smaller. a_horse already pointed to a major reason: 6 indexes - some of which obviously unnecessary, too.

Each row in a table, as well as each index tuple in a btree index has a tuple header occupying 23 bytes (plus alignment padding, typically 24 bytes), and alignment padding between tuples, 4 bytes for the item pointer, some overhead per data page (typically 8kb page size). Roughly ~ 35 bytes per row in your case.

About as much for every index tuple. 6 indexes, ~ 200 bytes per table row. Plus every text column is saved twice, 1x table, 1x index. And btree indexes start with a FILLFACTOR of 90, so ~ + 10 % on everything.

Your average row size is ~ 75 bytes (50MB / 700000 - assuming "lines" correspond to rows). Your table and indexes occupy about 6 times the size of the raw text. The rest is other overhead mentioned above.

That's all assuming no table bloat (yet). Once you work with the DB (update, delete, transactions rolled back etc.) there will be dead rows and such, possibly multiplying total space. That very much depends on write patterns.

You can save a lot by removing unnecessary indexes.

And a couple of bytes per row if you move the enum column to the top of the table: less alignment padding, should average at 1.5 bytes per row, roughly 1 MB total, hardly worth it. See:

  • Thanks for your details answer! I upvoted it! + I edited my post to show the new create statement. Now the overhead is about 7x (which is way better then before). Attribute_One is calculated from Customer_One. Customer_One is a primary key, maybe I can use INDEX instead of UNIQUE for Attribute_One? Because is Customer_One is unique, I KNOW that Attribute_One is unique, too. I need to search AT LEAST fast in Customer_One and Attribute_One (thats why I created unique). Maybe there is another option like index, which is also fast, but does require less space? – A.c Dec 14 '17 at 14:19
  • @A.c. UNIQUE or just INDEX are both implemented with the same index, basically the same size on disk. But if Attribute_One is calculated from Customer_One there is more potential: don't store the functionally dependent column redundantly at all. Calculate it on the fly. You can have an expression index if you need it for look-ups ... – Erwin Brandstetter Dec 14 '17 at 14:34
  • If I dont store the calculated result of Costumer_One in Attribute_One, I need to calculate the value on the fly (as you said) to be able to search for both. Since Postgres uses some kind of search algorithm on indexed columns, to calculate on the fly would take much longer (search algorithm with very vew steps VS calculate every row). For every line, I would have to calculate the Attribute_One. Is my assumption correct? I dont understand what you mean with expression index. Could you please write a suitable expression index? :) – A.c Dec 14 '17 at 15:07
  • @A.c: Basically: no. Please start a new question for the new question providing exact details. Comments are not the place. – Erwin Brandstetter Dec 14 '17 at 15:10
  • Thank you very much. For now: I managed to go from 12x to 7x to 4.3x :). This is quite good for now. :) – A.c Dec 14 '17 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.