Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using PostgreSQL.

I have site which is sized to handle large number of data and traffic. The infrastructure will be built using on amazon (AWS) using EC2 instances and EBS volumes.

The design should have two databases, a main transactional database and a data warehouse to handle analysis and reporting.

Main transactional database

will be used for live website, the site is built on multiple nodes to scale up concurrent users. Mainly we require the database for this case to be extremely fast in reading operations, we expect >100GB data with 30% annual growth. At this point, we are planning to use two EC2 servers (and add more later as we need).

my question, what is the recommended setup for the above requirements? Plus, is there a way to manage the table and volume partitioning? is there recommendations for using AWS setup?

Data warehouse database

Will be used mainly for capturing all the data from main transactional database in time dimension. so, even deleted records from main database will be captured in the DWH. Therefore, the data will be very large and growth will be even bigger. We will also use couple EC2 instances or more if required.

What is the recommended setup in this case? this will require fast writing operation because of the constant writing (ETL). Can we build OLAP cubes in PostgreSQL? if yes, has anyone out there gave it a try?

Connecting to database

The web servers will be connecting to the main database to query and write. We are currently developing an application using django which uses native library for connecting. Is it recommended to use the same basic method? or should we configure pgpool?

Data warehouse (ETL)

What is recommended way for building ETL processes to read from main and load to data warehouse? Any tools? methodology to follow? does PostgreSQL offer any helpful functions/tools in building ETL processes?

share|improve this question

migrated from serverfault.com Jan 25 '12 at 20:31

This question came from our site for professional system and network administrators.

    
Regarding scaling, you might want to read this: stackoverflow.com/questions/10256923/… –  a_horse_with_no_name Apr 26 '12 at 6:57

2 Answers 2

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. http://stackoverflow.com/questions/1125504/django-persistent-database-connection

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

share|improve this answer

Your setup resembles one that I developed for a university. The database wasn't huge, but fairly large, about 300GB in size and the largest table contained about 500 million records. And still growing.

For the purpose two really beefy servers (real iron, not virtualized), one dedicated for handling the data from a web site and the other used for statistical calculations and analyses, were used. Data was replicated in both directions using Slony. The OLTP data was replicated to the OLAP server continuously and some schemas and single tables were replicated from the OLAP-server to the OLTP. In this way heavy calculations could be performed on the analysis server without impacting the OLTP-server. Nowadays, there are some alternatives to Slony for replicating data: http://www.postgresql.org/docs/9.2/static/different-replication-solutions.html

Slony was good and fast for our concern but it may be harsh teacher.

As the OLAP-server will grow steadily you should consider using some kind of partioning if applicable.

If have the possibility, use connection pooling. I've only used PgPool and it worked flawlessly. PgBouncer is another option. Besides reducing the init latency it also reduces the session startup and session management. http://momjian.us/main/blogs/pgblog/2012.html#April_25_2012

Another benefit of using a connection pool is that you got a single point where you can easily redirect your traffic (this might of course also be a risk).

I haven't used any readymade ETL for loading data into the OLAP server. I wrote my own script in Python as some of the data was delivered in huge text files with a peculiar format.

The structure of the database needs to be carefully considered. Using schemas is good for gathering and ease the handling of objects. It may seem cumbersome to start with to use schemas but as the number of objects grow you will thank yourself. Knowing that you have to explicitly prefix objects with their schema you know exactly which objects you operate on. http://momjian.us/main/blogs/pgblog/2012.html#April_27_2012

For the daring ones PostgreSQL XC be an interesting alternative or just an oversized costume http://postgres-xc.sourceforge.net/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.