Proper storage size estimation technique for PostgreSQL

Question

We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we are doing research, reading manuals and using our general information technology knowledge to achieve this.

We have actual data to migrate to this database and have some rough estimations of growth. For the sake of the example, let's say we have a estimation of growth of 50% per year.

The point is: what's the general proper technique for doing a good size estimation?

We are estimating the storage usage by the following rules. Topics where we need advice are marked with bold text. Feedback on the whole process is more than welcome:

1. Estimate the size of each table
   1. Discover the actual size of each row.
      • For fields with a fixed size (like bigint, char, etc) we used the sizes described in the documentation
      • For fields with a dynamic size (like text) we estimated the string length and used the function select pg_column_size('expected text here'::text)
      • We added 4 more bytes for the OID that PostgreSQL uses internally
   2. Multiply the size of each row by the number of estimated rows
   3. Do I need to consider any overhead here, like row or table metadata?
2. Estimate the size of each table index
   • Don't know how to estimate this, need advice here
3. Estimate the size of the transaction log
   • Don't know how to estimate this, need advice here
4. Estimate the size of the backups (full and incremental)
   • Don't know how to estimate this, need advice here

5. Sum all the estimates for the actual minimum size

6. Apply a factor of 1.5x (the 50% growth) to the sum of the estimates 1, 2 and 4 for the minimum size after 1 year

7. Apply an overall factor of 1.2 ~ 1.4 (20% to 40% more) to estimates 5 and 6 for a good safety margin

I know the rules got pretty extensive. Let me know if examples are necessary for a better understanding.

Answer 1

For point 1), you need to read the Storage Page Layout chapter of the documentation and in particular consider the HeapTupleHeaderData Layout table for the metadata at the row level.

The 4-bytes per-row OID is obsolete for user tables. PostgreSQL no longer have them by default since 8.1. This is now controlled by the default_with_oids config parameter or the WITH(OIDS) clause of CREATE TABLE.

When a significant part of the data is live (gets frequently updated), estimating the disk size is harder because UPDATEs are equivalent to INSERT new version followed by DELETE old version, followed hopefully by reuse of the unused space whenever possible. There is also the fillfactor storage option for tables that come into play here (see the storage parameters section in CREATE TABLE)

And there is bloat at the index level, too. For write-heavy tables, it's not uncommon for an index to be several times bigger than its optimal size.

You may need to learn a bit about VACUUM to figure out how much you may be affected by table and index bloat in your specific usage.

The size of the transaction logs entirely depends on the amount of writes to the database.

Answer 2

The general proper technique for doing a good size estimation is to mock up the schema and fill a table with fake data. The generate_series function can be useful for that. Just to give an idea how error filled the process of doing it manually is, for the (1) you started to flesh you you are missing about 24 bytes of header information per row, as you suspected in (3), and there are no OIDs on regular tables anymore.

Estimating index size is even harder. How much they compact relative to the data depends on how much variation in that data there is. And they don't even grow linearly due to how B-tree indexing works. And you'll get much bigger indexes over time under a workload heavy on UPDATE and DELETE than you will on a mostly INSERT one. Putting numbers on that is really hard to do unless you simulate a workload and let it chew away at some test data for a while.

Transaction log size in the main database directory is bounded by the checkpoint_segments parameters. The documentation on that gives some formulas for estimating the size, worse case it's about 16MB * 3 * checkpoint_segment in size. It's fixed once it grows to that size and start pruning itself, and is normally trivial compared to everything else in a larger database.

If you do a binary backup of the database, it will come out to be the same size as the database. Incremental data is normally stored as a series of WAL files, and those are the important transaction log things to consider sizing. The WAL format is too complicated to size manually though, and it depends heavily on like checkpoint_segments, among other things. Again, only good way to estimate it is to run a simulation and see how many WAL files spit out during that run.

It's also possible to do a logical, text-based backup. How large those will be depends on what data types you're using. The text format is going to be larger, but numeric formats grow a lot more than string ones, as the simplest example. Easiest way to estimate, again, is to simulate some data, use pg_dump to save it to plain text, and try to extrapolate.