Postgres Indexes for Newbies
blog.crunchydata.com
Postgres Indexes for Newbies
1–10 of 43 posts
Re: Postgres Indexes for Newbies
#2I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like.
I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
Re: Postgres Indexes for Newbies
#3Creating indexes without understanding how they actually arrange the underlying data is at best a mild performance hit and at worst a catastrophic performance sink.
Each type handles a different access pattern and query work load. But “I have an index” doesn’t mean anything if the column order or operator doesn’t match the actual task.
If you want to really make your database experience shine, then take the time to understand how each type of index actually lays out your data. What it means to perform a range operation (e.g. key > X) v.s. an equality (e.g. key = or contains X). Otherwise you’re adding overhead to every data modification with no long term gain.
Re: Postgres Indexes for Newbies
#4I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like. I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
BRIN indexes don't affect the storage layer, so you need to make sure your table is appropriately sorted on disk through other means. CLUSTER can do this as a one-off job (pg_repack if you need to keep the table available for writes), but won't sort new data as it's written.
Re: Postgres Indexes for Newbies
#5I always first try to teach the intuition that an index is a different sorting of the data (with the full table scan being insertion order/sort by rowid). Everything that's faster with data sorted in this manner is faster in the database. That's for 2nd semester students who should have heard of sorted-list merging and binary search.
That overlooks hash indexes or index-only queries, of course.
Love the article but I'm not sure that the indirection of the index being an additional data structure really helps understanding it better.
I think you basically only need to understand that once we have some degenerated indexes with bad selectivity where a full table scan would be better. Here the additional index access is neck breaking.
Of course, this sorting-intuition doesn't help in understanding the different kind of indexes. Is that a topic for newbies though?
Re: Postgres Indexes for Newbies
#6I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like. I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date.
You are correct that they can be used for analytical workloads (but not exclusively, you still have other index types available and transactions, etc). They have very little performance overhead for the write path and small memory footprint compared to B-tree indexes.
However, they are only effective for improving read performance when filtering a table with a selective predicate, and even then- only if there’s some temporal relationship between the indexed column values and the time that the rows are inserted. Auto incrementing id and created_at columns work great at this.
So to your second point, no- I don’t believe there’s any specific storage layer awareness of them. However, Postgres inserts are typically at the “tail” of the table as a consequence of the MVCC implementation (although, tables with frequent deletes and updates can be confounding to the effectiveness of BRINs).
Remember though that the correlation needn’t be perfect to be very effective. If you can limit what would otherwise be a full table scan + filter evaluation to a scan + filter of a handful of pages that “might” contain matching rows, that can be a huge performance boost with a tiny cost of maintaining the index.
Re: Postgres Indexes for Newbies
#7I always first try to teach the intuition that an index is a different sorting of the data (with the full table scan being insertion order/sort by rowid). Everything that's faster with data sorted in this manner is faster in the database. That's for 2nd semester students who should have heard of sorted-list merging and binary search. That overlooks hash indexes or index-only queries, of course. Love the article but I…
> with the full table scan being insertion order/sort by rowid
I'm not sure that makes sense. As far as I'm aware selecting without sorting has no guaranteed order across vendors at all.
Re: Postgres Indexes for Newbies
#8I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like. I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date. You are correct that they can be used for analytical workloads (but not exclusively, you still have other index types available and transactions, etc). They have very little performance overhead for the write path and small memory footprint compared to B-tree indexes. Howeve…
Interesting, thanks for sharing.
I am comparing this mentally to Redshift's sort keys. It sounds like a BRIN index might approximate that behavior for the time dimension but not for a locale dimension (integer value, low cardinality, high volume).
Do BRIN indices support compound keys? Sounds like that may not even be desirable even if they do.
With a Postgres analytic table that has a time and locale dimension it sounds like a hybrid of a BRIN index on the time dimension and then partition on the locale, which probably manifests as an inherited table.
But that would require some contortions in the ETL layer to re-write the partitions in order when they are updated, and to create and drop the new partitions as necessary.
Re: Postgres Indexes for Newbies
#9I’m loving all the Postgres submissions on HN over the last couple days. My professional experience is mostly with Redshift which is only Postgres-like. I was not aware of BRIN indexes. Am I correct in assuming those are suitable for analytical workloads? Is the data implicitly sorted in a BRIN index or is there a mechanism to optimize the storage layer for BRIN indexed data?
BRIN indexes don't affect the storage layer, so you need to make sure your table is appropriately sorted on disk through other means. CLUSTER can do this as a one-off job (pg_repack if you need to keep the table available for writes), but won't sort new data as it's written. https://www.postgresql.org/docs/current/sql-cluster.html https://reorg.github.io/pg_repack/
Very cool, thanks for sharing. I'm curious how far vanilla Postgres can be taken for analytics before more exotic columnar solutions like Redshift start to make sense.
Re: Postgres Indexes for Newbies
#10One thing I struggle with when figuring out the best indexe is creating enough junk in the database to force it to use an index vs seq scan. Anyone have tips on good data generation tools that works with foreign keys and creates a good range of data? I've looked into using queries and generate_series but it doesn't work well with UUID keys (I think?)
Right now I have a bash script that creates a bunch of entries through my app API...