4 ms·

A big one that nobody has mentioned yet - reduce memory requirements for your db. In general, for a database to be performant, it needs to be able to keep the h

by yashap 9y ago

A big one that nobody has mentioned yet - reduce memory requirements for your db. In general, for a database to be performant, it needs to be able to keep the hot part of the dataset and REGULARLY USED INDEXES in memory, you don't want to be paging indexes in and out of memory every time you have to use them. Say you've got a giant table for of emails, and there's a bunch of indexed fields (people maybe want to filter their emails by tags, read/unread, etc.). If you keep them in one giant table, you need to keep those giant indexes fully in memory to get good performance. BUT, you know 99% of requests are for recent emails, people only occasionally look at old ones. So you partition your table by timestamp, and instead of running queries like `select * from email where status = 'unread' order by timestamp desc limit 50`, you run queries like `select * from email where status = 'unread' AND TIMESTAMP >= <pretty-recent> order by timestamp desc limit 50`. This only hits the first partition, and almost always returns the 50 emails you need - if it doesn't, you run subsequent queries to hit older partitions. But now, say you partition by month, and keep 2 years of data, almost all queries are being serviced by a partition containing 1/24th of the total data, so keeping indexes for that 1 partition in memory is WAY easier than keeping indexes in memory for the entire giant table. When you have to query old partitions, it's slower and there's still some swapping happening, but that's rare anyways. So you can get away with having way less memory for your db, without performance degradation on most queries.

LogicX 9y ago
With your example, I believe you’ve just described TimescaleDB. https://www.timescale.com/ https://www.timescale.com/ We use it to store 150M records a day, and be performant enough to have a customer facing analytics dashboard querying against it for real-time results. Pretty amazing approach.
- mfreed 9y ago
  TimescaleDB person here. Thanks for the call-out. For those who don't know, TimescaleDB is a extension to Postgres that makes it easy to use and scale for time-series data. It works with both PG9.6 and PG10. Some folks might be interested in two recent blog posts we wrote about PG10 partitioning: 1. Technical write-up about PG10 partitioning: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1 https://blog.timescale.com/scaling-partitioning-data-postgre... 2. Why TimescaleDB's partitioning is easier-to-use and more performant than PG10's native partitioning for time-series data: https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5 https://blog.timescale.com/time-series-data-postgresql-10-vs...
pg314 9y ago
Is there that much of a performance difference, though? In your example the btree of the index of the 2 years of data is probably 1 level deeper than that of an individual month. If you're mostly querying the last month, only that part of the btree will be kept in memory. The size of the hot data of the index is similar in both cases. I fail to see how that would make much of a performance difference. For bulk operations like (auto-)VACUUM, CLUSTER or dumping/restoring data it can make a big difference, though.
- jashmatthews 9y ago
  Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table. There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth. There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data.
  - pg314 9y ago
    > Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table. That makes sense. > There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth. I've actually had a similar scenario. CLUSTERing by the index solved that. But of course running CLUSTER on a huge table is very slow. If you can partition the data so you only have to cluster one of the partitions, that's a huge win. > There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data. Didn't think of that. Thanks for the clarifications.

by yashap 9y ago

LogicX 9y ago

With your example, I believe you’ve just described TimescaleDB. https://www.timescale.com/ https://www.timescale.com/ We use it to store 150M records a day, and be performant enough to have a customer facing analytics dashboard querying against it for real-time results. Pretty amazing approach.

mfreed 9y ago
TimescaleDB person here. Thanks for the call-out. For those who don't know, TimescaleDB is a extension to Postgres that makes it easy to use and scale for time-series data. It works with both PG9.6 and PG10. Some folks might be interested in two recent blog posts we wrote about PG10 partitioning: 1. Technical write-up about PG10 partitioning: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1 https://blog.timescale.com/scaling-partitioning-data-postgre... 2. Why TimescaleDB's partitioning is easier-to-use and more performant than PG10's native partitioning for time-series data: https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5 https://blog.timescale.com/time-series-data-postgresql-10-vs...

pg314 9y ago

Is there that much of a performance difference, though? In your example the btree of the index of the 2 years of data is probably 1 level deeper than that of an individual month. If you're mostly querying the last month, only that part of the btree will be kept in memory. The size of the hot data of the index is similar in both cases. I fail to see how that would make much of a performance difference. For bulk operations like (auto-)VACUUM, CLUSTER or dumping/restoring data it can make a big difference, though.

jashmatthews 9y ago
Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table. There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth. There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data.
- pg314 9y ago
  > Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table. That makes sense. > There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth. I've actually had a similar scenario. CLUSTERing by the index solved that. But of course running CLUSTER on a huge table is very slow. If you can partition the data so you only have to cluster one of the partitions, that's a huge win. > There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data. Didn't think of that. Thanks for the clarifications.