13

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:

  1. 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.
  2. 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.

Further,

  • 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.

6
  • 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. Jan 27, 2014 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, 2014 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, 2014 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, 2014 at 15:37
  • 1
    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? Oct 27, 2014 at 22:51

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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