On 4 March 2018 at 23:05, Ants Aasma <ants.aa...@eesti.ee> wrote: > On Sat, Mar 3, 2018 at 4:53 PM, David Rowley >> It's a good job someone invented HASH partitioning then. >> >> It would be interesting to hear how your benchmarks go using current >> master + the faster partition pruning patchset [1]. Currently, HASH >> partitioning does exist in master, just there's no partition pruning >> for the non-matching partitions, which is why you need [1]. >> >> I think trying with something like 500-1000 partitions might be a good >> place to start. > > I don't think that will actually help much. 1000 partitions means each > partition gets data from ~50 vehicles. A 60 tuples per page each page > in the partitioned able will contain on average 1.2 interesting > tuples. So you still have almost one page read per row.
hmm, I missed that part about only 60 tuples per page. It may be worth an experiment with two table, one to hold the day's worth of data, and a holding table which stores about 1-2 minutes of data. Each minute or two the holding table could be flushed like: WITH del AS (DELETE FROM driver_pos_holding RETURNING *) INSERT INTO driver_pos SELECT * FROM del ORDER BY id,ts; then perhaps a manual VACUUM of driver_pos_holding... or leave it up to auto-vacuum... both tables could be inherited by a single parent to allow queries to return all rows, or be wrapped up in a UNION ALL view, although an inherited table should provide better plans than the view in some cases. Although using an inherited parent would disallow you to use partitioning if you ever wanted to partition by ts to make the job of removing old data easier. Hopefully having 60-120 seconds of driver data will in the holding table will mean that the tuples for each driver only span 2-3 pages for that 1-2 minute period in the main table You might then have not much more than 240 pages to load for a driver after a 4-hour run. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
