1

we're thinking about moving to Postgres from some other DB backend we're currently using. From what I've really seen, it's very reliable, stable and efficient. I realized I really like Postgres - it performs very well, compared to some other trendy things. However, the use case might be odd so I'm a bit concerned about my decision.

What we want is a simple key-value storage for hundreds of millions of rows. Each row is very different in size - from 10 kb to megabytes (but not more than 10 megabytes). Keys are strings, values are binary data.

We need only the PK and the Key index, no indexes for values.

However, since we use SSDs, I'd like to keep the disk usage as low as possible. So what is the HDD overhead in Postgres? Is there any formula to estimate it?

What would be the best storage engine for that (keeping the lowest HDD usage)? We need very fast writes but it's ok to have relatively slow reads.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Spaceman
  • 1,185
  • 4
  • 17
  • 31
  • 1
    While a completely valid question, this isn't exactly the place for it since it has nothing to do with programming. I would try http://dba.stackexchange.com/ – Blindy May 20 '14 at 14:11
  • @Blindy ouch you're probably right. If the moderators close this quesiton, I'll open it there. My bad – Spaceman May 20 '14 at 14:16
  • Even if they don't, it's really unlikely you'll get a professional response to this question here. Programmers tend to be really hand-wave'y about this level of nitty-gritty details! – Blindy May 20 '14 at 14:19
  • 1
    I think you are looking for this: http://www.postgresql.org/docs/current/static/storage-file-layout.html especially this: http://www.postgresql.org/docs/current/static/storage-page-layout.html –  May 20 '14 at 14:23
  • This one about [measuring size of rows](http://dba.stackexchange.com/questions/23879/measure-the-size-of-a-postgresql-table-row/23933#23933) and [this one](http://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes/13570853#13570853) and [this one](http://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468) about size on disk may be of help. – Erwin Brandstetter May 20 '14 at 23:39

2 Answers2

3

PostgreSQL has an overhead of 24-28 bytes per row.

It's that big because the rows contain all the MVCC transaction visibility information - there's no "redo" and "undo" logs like some other MVCC database implementations have. That has some real advantages in some workloads, and some real disadvantages in others. Yours is probably one of the disadvantages.

Not only that, but I don't see that you'll really benefit from PostgreSQL's features. It does offer very robust write safety guarantees. It has reliable transaction isolation (at a variety of levels). There are lots of good things, but many of them are less useful if you're just using it as a dumb k/v store.

I suggest that if all you need is a dumb K/V store, use a dumb k/v store. There are tons of options out there to meet different requirements with different levels of isolation/transaction support, write reliability, etc, and correspondingly different overheads and consistency guarantees.

One time where PostgreSQL can be great is when you want to mix K/V like operation with other more relational workloads. In that case support for things like hstore and json can be fantastic. They're not really ideal for your use case though.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • well, it's better to use a good tool rather then trying to write it from scratch. With a requirement of fast writes (2000\sec), I would really like to avoid reinventing the wheel – Spaceman May 20 '14 at 14:29
  • Yeah, good plan. You need to think carefully about data loss windows, consistency requirements, isolation requirements, etc. Define what you need _then_ choose your tool. – Craig Ringer May 20 '14 at 14:46
2

You want this page from the documentation which details the row formats. You have at least 24 bytes per row overhead. Life is complicated by the out-of-line TOAST compression of longer data. I would recommend building a sample database and using the system functions to measure size.

However, do test your MB sized rows - speed of transfer might be an issue for you.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51