PostgreSQL Compression – Options for Storing Large Text

Infrastructure/Database Services

You should probably read this for an overview of a high-volume site that runs on AWS with EBS. They've moved to Ephemeral storage but have had to create some redundancy in being able to (re)store the data.

http://blog.reddit.com/2012/01/january-2012-state-of-servers.html

Data Warehouse/ETL

I've used Pentaho in the past. Not directly with postgres, but I've found it to be a good solution for both OLAP (Mondrian) and ETL (Kettle)

http://www.pentaho.com/

edit: "Community Editions" can be found here

http://mondrian.pentaho.com/

http://kettle.pentaho.com/

Connection

These folks seem to really like pgbouncer. https://stackoverflow.com/questions/1125504/django-persistent-database-connection

I have no experience with it, though. Apparently, Disqus uses it.

SubQuestion 1: I don't know of any way to get rid of the 23 byte header. There is also a 24 byte header on the page. http://www.postgresql.org/docs/8.3/static/storage-page-layout.html

SubQuestion 2: This guide should help. As suggested, increasing maintenance_work_mem is advised, but also increasing checkpoint_segments should help (to reduce the frequency of checkpoints). You've set your work_mem at 64GB, which seems really high. I would suggest keeping it under 2GB even on a system like yours. work_mem is for doing sorts and operations of that nature. http://www.postgresql.org/docs/current/interactive/populate.html

SubQuestion 3: I am not able to find any information on the physical layout of indexes within the pages. I would create a temporary table, fill it with test data (1 million rows?), create a temporary index, get the size of the index from the system, then divide by the number of rows to get the average. I can't show you an SQLFiddle version because it doesn't allow access to system functions. Try these techniques: http://postgresql.cc/postgres-index-size

PS - 25 billion rows? That's more than I've ever dealt with. I'm in the 4 billion range for one particular table ... For comparison, other database servers, like SQL Server 2008+, have a minimum 9 byte header for each row, so you can't entirely get rid of it but I've always thought that PostgreSQL's 23 byte header was a bit excessive.