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.

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a different variety of weather data (temp, dew point, wind, etc.). Each table will have 6-7 columns: latitude, longitude, point geometry, elevation, date-time the model is relevant for, and 1-2 data values of interest. The data will be primarily queried for a bounding box by time and elevation. There will be approximately 145,757,360 rows per table (data older than now is no longer relevant will be deleted). I roughly estimate the size of the tables to be about 10 GB each without indexes. (That's 52 bytes of data plus 23 bytes of overhead per row). The data will be regularly updated/inserted as new model data becomes available. Note: new data will include overwriting existing values in the table.

So I'm looking at these two plans:

Simply index and cluster by (datetime, elevation) with an additional index for the point geometry. Run a regular cron job that deletes old rows, runs vacuum/analyze, and re-clusters.

or

Partition by datetime and then cluster and index by elevation per table with an index on geometry. Run a regular cron job to add new tables going forward and drops old tables.

So, I know that dropping a table is much more efficient and deleting and vacuuming. But would I see a performance boost otherwise?

Are partitions appropriate when the all of the tables will be evenly updated and selected on until deleted as irrelevant (the documentation indicated that partitions worked best when only a few of them would be selected on)?

When delivering data will the selects be any faster than the clustered index? Does the answer change if multiple requests are being made at once?

Thank you. I hope I put up all the needed data. If not let me know and I'll add it.

share|improve this question
1  
Ouch, these narrow rows are where PostgreSQL's large row headers start to really hurt. Pity there's not really much that can be removed; it's not like we can lose xmin or xmax, etc. There's a feature that might make it into 9.4 that will probably excite you, called minmax indexes, that will make things like this a great deal more convenient. –  Craig Ringer Jan 27 at 9:11
1  
Is the following combination repetitive: "latitude, longitude, point geometry, elevation". If yes, normalizing it into another table may save some of space. –  A-K Jan 28 at 14:42
    
Only marginally. A PostGIS geometry is a binary array and not human readable. I could derive those values on output, but then I couldn't cluster on them. I could use a GeoHash to cluster, but that isn't anymore readable than the lat lon would be. But either way space isn't the issue. They offered as many terrabytes as I can fill. The problem is I can't query terrabytes at speed. The database itself will be largely non-transactional. Only two scripts will have write access at all. Everything else is strictly read-only. –  bshender Jan 29 at 15:30
    
Craig: They do seem intriguing I look forward to experimenting with them when they come out. Any thoughts about my setup in 9.3 though? –  bshender Jan 29 at 15:37

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.