8 ms·

Author here. Feel free to ask me any questions. By the way, the 10 million number in the title is outdated. It's now about 30 million indexes. Also, I've been

Author here. Feel free to ask me any questions. By the way, the 10 million number in the title is outdated. It's now about 30 million indexes. Also, I've been publishing one blog post a weekday on my personal blog about one Postgres feature or component. If you liked this post, you should check it out: http://malisper.me http://malisper.me

jdelaney 9y ago
Great article! Thanks for taking the time to write it up. What sort of performance impact do these indexes have on writes? I'm guessing PostgreSQL has to look at evaluate each index predicate for the inserted data to see if the index needs to have an entry added.
- malisper 9y ago
  > What sort of performance impact do these indexes have on writes? I'm guessing PostgreSQL has to look at evaluate each index predicate for the inserted data to see if the index needs to have an entry added. When we first deployed partial indexes as our primary indexing strategy, we did notice significantly higher CPU. At the time our assumption was that evaluating the partial index predicates was the cause of the high CPU. I later wound up investigating the high CPU use and found the time wasn't spent on evaluating the partial index predicates, but on actually parsing them! I recently wrote up another blog post on how I found this out: https://blog.heapanalytics.com/basic-performance-analysis-saved-us-millions/ https://blog.heapanalytics.com/basic-performance-analysis-sa...
lalwanivikas 9y ago
Hey Michael, not directly related to your post, but I wanted to know how a noob(like myself) can be good at Postgres? I am a frontend developer who wants to start making(and deploying) full stack apps. I have heard that Postgres is one of the most reliable relational databases out there and I would love to use it in my apps. Any suggestions on how to get started with Postgres? Or relational dbs in general? Books/courses/making projects/anything else? Thanks in advance!
- kornish 9y ago
  Craig Kerstiens has a phenomenal set of blog posts about a few different Postgres topics to get you thinking: http://www.craigkerstiens.com/content/ http://www.craigkerstiens.com/content/ It's really worth checking out all his stuff - his explanations are concise and easy to follow: http://www.craigkerstiens.com/categories/postgresql/ http://www.craigkerstiens.com/categories/postgresql/
- skrebbel 9y ago
  I'm not Michael, and I'm not sure if this is the answer you want to hear, but if you're a frontend developer the database is likely going to be the least of your worries when you start. Making and deploying the backend is going to take much more of your time than doing Postgres well. There's a lot of blog posts on HN about bending databases like Postgres to pretty insane workloads. Heap's workload is a nice example of that. they're interesting blog posts but don't let them distract you. Most databases really do work pretty well by default, if you just use them the simplest way possible. Most apps don't have insane workloads, and especially when you start out they won't. If you just make a table for every "thing" in your system and link them together with foreign keys you're pretty much set. Most popular frameworks (Rails, Django, etc) do this for you without even touching SQL. This approach is fine for nearly every case. I bet even Heap started like that. Famously, Twitter started like that as well, and the only reason that got them in trouble (remember the failwhale?) was because they were growing faster than nearly any platform ever before. Most sites don't 10x their user count every half year, and if you do, lucky you. Scaling problems are luxury problems. Half of HN is jealous of people who have scaling problems. I sure am. The other half, well, they won't have scaling problems because they're not shipping fast enough. Their backends would scale though. They read all the books on Postgres and doing it right from the start!
  - StavrosK 9y ago
    This is exactly right. Install Postgres, add some indices on the columns you're querying, and you're probably good for the next ten years in that respect. Try to focus on making a product people want and making them aware of it instead. No company[1] has ever died because their tech stack wasn't good enough. [1]: Approximately.
deleted 9y ago
[deleted]
mkesper 9y ago
After the Heap snippet is added to a website, Heap will automatically capture every event that users do on that website. That means every click, pageview, form submission, etc, are all captured and logged into Heap. From there, the owner of the website can use Heap to answer questions about their users. Would it be possible to filter out only interesting events?
- malisper 9y ago
  What do you mean by "filter out only interesting events"? The Heap philosophy is to capture all of the data up front and then make it easy for our customers to specify and query the subsets of the data they care about. That way our customers never have to write any tracking code and they never have to worry about forgetting to track something.
3pt14159 9y ago
I have so many questions. I've made a number of internal dashboards for companies over the years, so I've always been curious about how a real analytics company handles some of the challenges where n > 1: 1. How good is Citrus really? It seems almost too good to be true but the line on their product page: "[Citrus is not a good fit for] free form SQL by analysts. Because of the distributed nature of Citus some SQL primitives do not map seamlessly. If you have data scientists or end customers that are looking to run free-form SQL, not controlled by an application, then Citus is not an ideal fit for you." Gives me pause. I'm seriously thinking of migrating to them for a project we have. 2. Do you use the cloud or the enterprise pricing for Citrus? If enterprise, how much are we looking at here? I assume it's at least five digits in a setup fee, but enterprise pricing is so hard to predict. 3. Before moving to Citrus what were some Postgres performance tactics that you used other than partial indexes that made a real difference on performance? 4. When did a single Postgres instance on a single server stop scaling? Was it a problem of writes per second or querying? How long did it take to migrate to Citrus? 5. What is your primary application written in? Do you split it off by endpoint and use something higher level for the low throughput endpoints or do you just write everything in, say, Java or Go? 6. How do you deal with cyber attack? Even normal sites get targeted, but breaking into a analytics company seems like a cyber criminals dream. How do you handle real vs fake traffic on your clients website? I could imagine spammers exploiting this or is that not a primary problem? I feel tempted to ask more, but I'm afraid I'd be exploiting your offer to the point of being a chore.
- barrkel 9y ago
  It's Citus Data and CitusDB, not Citrus. I had a look at Citus for analytics workload (typically using full table scan queries), comparing it with Greenplum and Impala (which uses HDFS and Hive metastore, but not much more from Hadoop). I found Citus to not be very competitive with Greenplum; Greenplum executed table scans quite a bit faster, but more importantly Citus didn't have column stores without adding the cstore extension, and column stores massively increase table scan performance (like, multiple orders of magnitude); and cstore_fdw isn't in the box, you need to add it manually. Greenplum in turn didn't perform as well as Impala as data scaled up; also, Impala could deal with thousands of columns, whereas Greenplum and Citus inherit Postgres limits. Greenplum is also tied together with ssh and scripting duct tape, operationally it's a lot less flexible than HDFS. Postgres on its own isn't very good at analytics queries, it's far too slow for similar reasons to why Citus without cstore was too slow for us, only you get even less I/O parallelization.
daliwali 9y ago
This may be a stupid question but I glanced over the marketing website and didn't really understand how logs are stored. - Are you storing each log event as a row in Postgres? - How do you rotate logs, what happens when logs get indefinitely large? - Can logs be exported to some common format for offline analysis?
- malisper 9y ago
  We create one row per event logged in Heap. We currently have a default 3-year retention policy. We will keep your old data for up to three years. We have a redshift integration which you can use to export your data from Heap into redshift.
orf 9y ago
Hey malisper, Have you investigated BRIN[1] indexes? Have you considered or benchmarked those? For naturally ordered data it seems to be pretty efficient. 1. https://www.postgresql.org/docs/9.5/static/brin-intro.html https://www.postgresql.org/docs/9.5/static/brin-intro.html
- malisper 9y ago
  While our data is ordered, a BRIN index doesn't support our use case. We need to perform non-zero amount of deletions/updates to existing data. This leaves holes in our table which will be filled in by new data, which makes a BRIN index unusable since the data is no longer ordered.
joncrocks 9y ago
Did you consider creating new tables (with appropriate, more generic indexes) vs. creating indexes for everything? You've got to consider that indexes can be viewed as cut-down tables that you can't directly address. I would have thought that you are going to start leaning heavily on the query optimiser/index updaters, which may well not be well designed for this kind of approach. With the caveat that it obviously seems to working now :-)
- malisper 9y ago
  One of the nice advantages of partial indexes is that Postgres will automatically fallback to the next best possible plan. If we created separate tables for each event, either we would have to handle the logic for falling back ourselves, or the data would be momentarily non queryable. I have come up with a schema/set of indexes that we think would allow us to have a fixed set of indexes. The downside is we would have to do a lot of the work Postgres is currently doing ourselves.
olavgg 9y ago
That is a great blog! We recently started to play around with recursive queries in PostgreSQL. We have a gigantic tree with several thousand nodes. With recursive queries, query time was reduced from 30 seconds to 120ms! And with this we also found new ways to improve workflows in our application.
artumi-richard 9y ago
I would have thought a single multi-column index would have been more appropriate. Did you consider these? They have been in PostgreSQL since 9.2 https://www.postgresql.org/docs/9.2/static/indexes-multicolumn.html https://www.postgresql.org/docs/9.2/static/indexes-multicolu...
- malisper 9y ago
  A single multi-column index would only work for the one specific query in the example. We have many queries that contain filters that are not easy to index such as regular expression filters. Additionally, a multi-column index would be significantly larger than the corresponding partial index.
koolba 9y ago
I've been reading your blog posts and they're very well done. Kudos!
netcraft 9y ago
Does the predicate in your queries have to match the index exactly or can you add more conditions and have it still use the index? (assuming AND not OR)
- malisper 9y ago
  You can add more conditions and Postgres will use the index to first find the rows that match the partial index condition and then use the additional condition to postfilter the rows.
  - netcraft 9y ago
    excellent - thanks for the information!
bogomipz 9y ago
Your blog looks great, thanks for sharing!
kbaker 9y ago
Just wondering if you looked into using GiN or GiST indexes on the JSON in the table?
- malisper 9y ago
  Yes, we have. The problem a lot of our keys and values are numeric. GIN/GiST only support equality for JSON fields. Additionally the GIN/GiST index winds up being significantly larger and is a bit slower than partial indexes.
alexbanks 9y ago
Please keep writing. I very much enjoy Postgres and also your blog about it.

jdelaney 9y ago

Great article! Thanks for taking the time to write it up. What sort of performance impact do these indexes have on writes? I'm guessing PostgreSQL has to look at evaluate each index predicate for the inserted data to see if the index needs to have an entry added.

malisper 9y ago
> What sort of performance impact do these indexes have on writes? I'm guessing PostgreSQL has to look at evaluate each index predicate for the inserted data to see if the index needs to have an entry added. When we first deployed partial indexes as our primary indexing strategy, we did notice significantly higher CPU. At the time our assumption was that evaluating the partial index predicates was the cause of the high CPU. I later wound up investigating the high CPU use and found the time wasn't spent on evaluating the partial index predicates, but on actually parsing them! I recently wrote up another blog post on how I found this out: https://blog.heapanalytics.com/basic-performance-analysis-saved-us-millions/ https://blog.heapanalytics.com/basic-performance-analysis-sa...

lalwanivikas 9y ago

Hey Michael, not directly related to your post, but I wanted to know how a noob(like myself) can be good at Postgres? I am a frontend developer who wants to start making(and deploying) full stack apps. I have heard that Postgres is one of the most reliable relational databases out there and I would love to use it in my apps. Any suggestions on how to get started with Postgres? Or relational dbs in general? Books/courses/making projects/anything else? Thanks in advance!

kornish 9y ago
Craig Kerstiens has a phenomenal set of blog posts about a few different Postgres topics to get you thinking: http://www.craigkerstiens.com/content/ http://www.craigkerstiens.com/content/ It's really worth checking out all his stuff - his explanations are concise and easy to follow: http://www.craigkerstiens.com/categories/postgresql/ http://www.craigkerstiens.com/categories/postgresql/
skrebbel 9y ago
I'm not Michael, and I'm not sure if this is the answer you want to hear, but if you're a frontend developer the database is likely going to be the least of your worries when you start. Making and deploying the backend is going to take much more of your time than doing Postgres well. There's a lot of blog posts on HN about bending databases like Postgres to pretty insane workloads. Heap's workload is a nice example of that. they're interesting blog posts but don't let them distract you. Most databases really do work pretty well by default, if you just use them the simplest way possible. Most apps don't have insane workloads, and especially when you start out they won't. If you just make a table for every "thing" in your system and link them together with foreign keys you're pretty much set. Most popular frameworks (Rails, Django, etc) do this for you without even touching SQL. This approach is fine for nearly every case. I bet even Heap started like that. Famously, Twitter started like that as well, and the only reason that got them in trouble (remember the failwhale?) was because they were growing faster than nearly any platform ever before. Most sites don't 10x their user count every half year, and if you do, lucky you. Scaling problems are luxury problems. Half of HN is jealous of people who have scaling problems. I sure am. The other half, well, they won't have scaling problems because they're not shipping fast enough. Their backends would scale though. They read all the books on Postgres and doing it right from the start!
- StavrosK 9y ago
  This is exactly right. Install Postgres, add some indices on the columns you're querying, and you're probably good for the next ten years in that respect. Try to focus on making a product people want and making them aware of it instead. No company[1] has ever died because their tech stack wasn't good enough. [1]: Approximately.

deleted 9y ago

[deleted]

mkesper 9y ago

After the Heap snippet is added to a website, Heap will automatically capture every event that users do on that website. That means every click, pageview, form submission, etc, are all captured and logged into Heap. From there, the owner of the website can use Heap to answer questions about their users. Would it be possible to filter out only interesting events?

malisper 9y ago
What do you mean by "filter out only interesting events"? The Heap philosophy is to capture all of the data up front and then make it easy for our customers to specify and query the subsets of the data they care about. That way our customers never have to write any tracking code and they never have to worry about forgetting to track something.

3pt14159 9y ago

I have so many questions. I've made a number of internal dashboards for companies over the years, so I've always been curious about how a real analytics company handles some of the challenges where n > 1: 1. How good is Citrus really? It seems almost too good to be true but the line on their product page: "[Citrus is not a good fit for] free form SQL by analysts. Because of the distributed nature of Citus some SQL primitives do not map seamlessly. If you have data scientists or end customers that are looking to run free-form SQL, not controlled by an application, then Citus is not an ideal fit for you." Gives me pause. I'm seriously thinking of migrating to them for a project we have. 2. Do you use the cloud or the enterprise pricing for Citrus? If enterprise, how much are we looking at here? I assume it's at least five digits in a setup fee, but enterprise pricing is so hard to predict. 3. Before moving to Citrus what were some Postgres performance tactics that you used other than partial indexes that made a real difference on performance? 4. When did a single Postgres instance on a single server stop scaling? Was it a problem of writes per second or querying? How long did it take to migrate to Citrus? 5. What is your primary application written in? Do you split it off by endpoint and use something higher level for the low throughput endpoints or do you just write everything in, say, Java or Go? 6. How do you deal with cyber attack? Even normal sites get targeted, but breaking into a analytics company seems like a cyber criminals dream. How do you handle real vs fake traffic on your clients website? I could imagine spammers exploiting this or is that not a primary problem? I feel tempted to ask more, but I'm afraid I'd be exploiting your offer to the point of being a chore.

barrkel 9y ago
It's Citus Data and CitusDB, not Citrus. I had a look at Citus for analytics workload (typically using full table scan queries), comparing it with Greenplum and Impala (which uses HDFS and Hive metastore, but not much more from Hadoop). I found Citus to not be very competitive with Greenplum; Greenplum executed table scans quite a bit faster, but more importantly Citus didn't have column stores without adding the cstore extension, and column stores massively increase table scan performance (like, multiple orders of magnitude); and cstore_fdw isn't in the box, you need to add it manually. Greenplum in turn didn't perform as well as Impala as data scaled up; also, Impala could deal with thousands of columns, whereas Greenplum and Citus inherit Postgres limits. Greenplum is also tied together with ssh and scripting duct tape, operationally it's a lot less flexible than HDFS. Postgres on its own isn't very good at analytics queries, it's far too slow for similar reasons to why Citus without cstore was too slow for us, only you get even less I/O parallelization.

daliwali 9y ago

This may be a stupid question but I glanced over the marketing website and didn't really understand how logs are stored. - Are you storing each log event as a row in Postgres? - How do you rotate logs, what happens when logs get indefinitely large? - Can logs be exported to some common format for offline analysis?

malisper 9y ago
We create one row per event logged in Heap. We currently have a default 3-year retention policy. We will keep your old data for up to three years. We have a redshift integration which you can use to export your data from Heap into redshift.

orf 9y ago

Hey malisper, Have you investigated BRIN[1] indexes? Have you considered or benchmarked those? For naturally ordered data it seems to be pretty efficient. 1. https://www.postgresql.org/docs/9.5/static/brin-intro.html https://www.postgresql.org/docs/9.5/static/brin-intro.html

malisper 9y ago
While our data is ordered, a BRIN index doesn't support our use case. We need to perform non-zero amount of deletions/updates to existing data. This leaves holes in our table which will be filled in by new data, which makes a BRIN index unusable since the data is no longer ordered.

joncrocks 9y ago

Did you consider creating new tables (with appropriate, more generic indexes) vs. creating indexes for everything? You've got to consider that indexes can be viewed as cut-down tables that you can't directly address. I would have thought that you are going to start leaning heavily on the query optimiser/index updaters, which may well not be well designed for this kind of approach. With the caveat that it obviously seems to working now :-)

malisper 9y ago
One of the nice advantages of partial indexes is that Postgres will automatically fallback to the next best possible plan. If we created separate tables for each event, either we would have to handle the logic for falling back ourselves, or the data would be momentarily non queryable. I have come up with a schema/set of indexes that we think would allow us to have a fixed set of indexes. The downside is we would have to do a lot of the work Postgres is currently doing ourselves.

olavgg 9y ago

That is a great blog! We recently started to play around with recursive queries in PostgreSQL. We have a gigantic tree with several thousand nodes. With recursive queries, query time was reduced from 30 seconds to 120ms! And with this we also found new ways to improve workflows in our application.

artumi-richard 9y ago

I would have thought a single multi-column index would have been more appropriate. Did you consider these? They have been in PostgreSQL since 9.2 https://www.postgresql.org/docs/9.2/static/indexes-multicolumn.html https://www.postgresql.org/docs/9.2/static/indexes-multicolu...

malisper 9y ago
A single multi-column index would only work for the one specific query in the example. We have many queries that contain filters that are not easy to index such as regular expression filters. Additionally, a multi-column index would be significantly larger than the corresponding partial index.

koolba 9y ago

I've been reading your blog posts and they're very well done. Kudos!

netcraft 9y ago

Does the predicate in your queries have to match the index exactly or can you add more conditions and have it still use the index? (assuming AND not OR)

malisper 9y ago
You can add more conditions and Postgres will use the index to first find the rows that match the partial index condition and then use the additional condition to postfilter the rows.
- netcraft 9y ago
  excellent - thanks for the information!

bogomipz 9y ago

Your blog looks great, thanks for sharing!

kbaker 9y ago

Just wondering if you looked into using GiN or GiST indexes on the JSON in the table?

malisper 9y ago
Yes, we have. The problem a lot of our keys and values are numeric. GIN/GiST only support equality for JSON fields. Additionally the GIN/GiST index winds up being significantly larger and is a bit slower than partial indexes.

alexbanks 9y ago

Please keep writing. I very much enjoy Postgres and also your blog about it.