5 ms·

> You can get a concurrent rebuild by creating a new index concurrently, then transactionally drop the old one and rename the new one. It's error prone though.

by merb 7y ago

> You can get a concurrent rebuild by creating a new index concurrently, then transactionally drop the old one and rename the new one. It's error prone though. actually you can't since you can't create/drop indexes concurrently inside a transaction. btw. my #1 request would be a better materialized view (which supports indexes) and also makes the performane of some stuff really better. unfortunatly you can't refresh it without a full seq scan. it would be great if postgres would have an auto updating materialized view.

paulddraper 7y ago
You can certainly drop, or at least rename, inside a transacion. Only the rename part needs to transactional. > it would be great if postgres would have an auto updating materialized view. 100%. Check out Incremental View Maintainence https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... There's been some work done on it, but it's a pretty difficult problem. If/when it comes to PostgreSQL it'll come for only very simple queries.
- jeltz 7y ago
  I wouldn't recommend dropping large indexes within a transaction. Use DROP INDEX CONCURRENTLY instead, Without CONCURRENTLY it has to lock the table which can cause issues in high traffic databases. But this is mostly a moot point since 12 has REINDEX CONCURRENTLY.
  - paulddraper 7y ago
    > it has to lock the table which can cause issues in high traffic databases In my case, I'm okay with a seconds-long lock while an index is dropped. An hours-long lock while an index is created is the real show stopper. > But this is mostly a moot point since 12 has REINDEX CONCURRENTLY. Fantastic.
- aidos 7y ago
  Thanks for the interesting link on IVM - I hadn’t heard about that work. Crikey, it’s a humbling one to solve generally reading through that page. I have a case of this where I do the incremental updates myself. In my case, figuring out which rows have changed isn’t that tough, I’ve got time stamps to lean on that simplify the process. More complex is that even plucking the data for a single row is slow if you’re not careful. I have a number of large subqueries that Postgres doesn’t optimise well if I just restrict the rows once in the outer query. Instead I need to be very specific with my restrictions in a number of duplicated locations throughout the query to get fast incremental updates. It works well now it’s up and running but it’s a little clumsy to set up.
  - paulddraper 7y ago
    It gets much easier if you are willing to perform "extra" query work to update the view. IVM is taking a sophisticated approach that results in a minimum of required work, even in cases of (some) aggregations.

by merb 7y ago

paulddraper 7y ago

You can certainly drop, or at least rename, inside a transacion. Only the rename part needs to transactional. > it would be great if postgres would have an auto updating materialized view. 100%. Check out Incremental View Maintainence https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... There's been some work done on it, but it's a pretty difficult problem. If/when it comes to PostgreSQL it'll come for only very simple queries.

jeltz 7y ago
I wouldn't recommend dropping large indexes within a transaction. Use DROP INDEX CONCURRENTLY instead, Without CONCURRENTLY it has to lock the table which can cause issues in high traffic databases. But this is mostly a moot point since 12 has REINDEX CONCURRENTLY.
- paulddraper 7y ago
  > it has to lock the table which can cause issues in high traffic databases In my case, I'm okay with a seconds-long lock while an index is dropped. An hours-long lock while an index is created is the real show stopper. > But this is mostly a moot point since 12 has REINDEX CONCURRENTLY. Fantastic.
aidos 7y ago
Thanks for the interesting link on IVM - I hadn’t heard about that work. Crikey, it’s a humbling one to solve generally reading through that page. I have a case of this where I do the incremental updates myself. In my case, figuring out which rows have changed isn’t that tough, I’ve got time stamps to lean on that simplify the process. More complex is that even plucking the data for a single row is slow if you’re not careful. I have a number of large subqueries that Postgres doesn’t optimise well if I just restrict the rows once in the outer query. Instead I need to be very specific with my restrictions in a number of duplicated locations throughout the query to get fast incremental updates. It works well now it’s up and running but it’s a little clumsy to set up.
- paulddraper 7y ago
  It gets much easier if you are willing to perform "extra" query work to update the view. IVM is taking a sophisticated approach that results in a minimum of required work, even in cases of (some) aggregations.