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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


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
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 '14 at 9:11
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 '14 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 '14 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 '14 at 15:37
Could you provide two pieces of information please: 1) What is most important to you, insert speed or query speed? 2) What queries are the most common? – Thomas Kejser Oct 27 '14 at 22:51

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are sufficiently small.

Given the choices available, cleaner data operations and avoiding a daily vacuum is a good thing to have.

Delivering selects may be faster with option 1, though I suspect it will probably be a wash. With option 1, records with the same date and elevation are placed near each other in one big clustered index. With option 2, records with the same date and elevation are placed near each other in many smaller clustered indexes.

share|improve this answer

Your Answer


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.