petergeoghegan

36 ms·

1. 151.▲
   by petergeoghegan 10y ago

   BTW, the new amcheck tool, available in Postgres 10, lets you validate that an index is consistent with its designated sort order (B-Tree operator class). At least you now have some way of detecting the kind of inconsistency you describe. I
2. 152.▲
   by petergeoghegan 10y ago

   PostgreSQL never stored strxfrm() output in indexes, but did at one time use it to build indexes (that is, strxfrm() was used to build values when sorting). However, it would be nice to be able to store strxfrm() output on disk, in indexes,
3. 153.▲
   by petergeoghegan 10y ago

   They published it as 3 books. Or, a third party did.
4. 154.▲
   by petergeoghegan 10y ago

   I agree that the quality of each implementation is very pertinent here. I wonder if the B-Tree implementation in question used the Lehman & Yao B-link technique, in order to obviate the need for "latch coupling"/"cra
5. 155.▲
   by petergeoghegan 11y ago

   VACUUM is often not the thing that cleans up most bloat in Postgres. There is also HOT pruning: https://github.com/postgres/postgres/blob/master/src/backend... Separately, certain index access metho
6. 156.▲
   PostgreSQL Parallel Aggregate(blog.2ndquadrant.com)

   262 points by petergeoghegan 11y ago|45 comments
7. 157.▲
   by petergeoghegan 11y ago

   Thanks
8. 158.▲
   by petergeoghegan 11y ago

   Thanks! Hope I was not unduly curt with the poster, but there are simply no packages available for 9.5.0 today, and won't be until Thursday. There is an official embargo observed by the -packagers group.
9. 159.▲
   by petergeoghegan 11y ago

   You've jumped the gun. Postgres 9.5 is not due to be released until January the 7th. This link is to a stamp commit for 9.5.0; a git tag REL9_5_0 will be created separately. Granted, it is true that it's been determined exactly wh
10. 160.▲
    by petergeoghegan 11y ago

    """B-Link-Trees: Lehman and Yao’s paper actually discusses an innovation they researched related to concurrency and locking when multiple threads are using the same B-Tree. Remember, Postgres’s code and algorithms need to be
11. 161.▲
    by petergeoghegan 11y ago

    Sorting will be a lot faster on PostgreSQL 9.5: http://pgeoghegan.blogspot.com/2015/01/abbreviated-keys-expl... http://pgeoghegan.blogspot.com/2015/04/abbreviated-keys-for-... I wonder w
12. 162.▲
    by petergeoghegan 11y ago

    It has race conditions. The only work-around that works (unless perhaps you have a 2-phase locking system, and are using serializable isolation level) involves subtransactions. For example: http://www.postgresql.org/docs&#x2
13. 163.▲
    by petergeoghegan 11y ago

    Avoiding reporting an error only extends to errors that the user can reasonably hope to avoid, like spurious duplicate violations (from the "arbiter unique index"), and what I call unprincipled deadlocks (deadlocks where there is
14. 164.▲
    by petergeoghegan 11y ago

    The documentation covering the new feature has now built: http://www.postgresql.org/docs/devel/static/sql-insert.html
15. 165.▲
    Exploiting locality to improve PostgreSQL's text sort performance(pgeoghegan.blogspot.com)

    2 points by petergeoghegan 12y ago|0 comments
16. 166.▲
    by petergeoghegan 12y ago

    Here is a hacked-together tool for visualizing Postgres B+Trees as GraphViz graphs: https://github.com/petergeoghegan/btree_visualization Note that it's very rough, because I never got around to polishing it (I am
17. 167.▲
    by petergeoghegan 12y ago

    The internal nodes point to leaf nodes. The leaf nodes contain pointers to the heap (table). At least with a B+Tree, the internal nodes only point to the leaf nodes in the same B+Tree structure, and not to "data proper". So the le
18. 168.▲
    by petergeoghegan 12y ago

    I'm surprised that there is no mention of the recent big improvements in the GIN infrastructure that Postgres full text search uses. The 9.4 release notes list: * Reduce GIN index size (Alexander Korotkov, Heikki Linnakangas) * Improve
19. 169.▲
    by petergeoghegan 12y ago

    PostgreSQL removed the switch to insertion sort even before NetBSD, in 2006.
20. 170.▲
    by petergeoghegan 13y ago

    Full text search is expected to benefit considerably from work in Postgres 9.4: https://www.postgresql.eu/events/sessions/pgconfeu2013/sessi... These advances within the GIN inverted index infrastructure will
21. 171.▲
    by petergeoghegan 13y ago

    Importantly, in Postgres the arrays are indexable with a GIN (inverted) index. So you can ask arbitrary questions along the lines of: show me all rows in this table that have any of the following integers: [5, 7, 9] somewhere in their arr
22. 172.▲
    by petergeoghegan 13y ago

    I'm a friend and former colleague of the author, and having read the book from cover to cover I don't think it's true that it has lots of typos. I think you might be confusing it with another title.
23. 173.▲
    by petergeoghegan 13y ago

    There is a really old O'Reilly book, I think, but there is also an O'Reilly book called "PostgreSQL: Up and Running" that came out last year that covers 9.2.
24. 174.▲
    by petergeoghegan 14y ago

    Yes, you still have to do an ALTER TABLE to change the type of a column within Postgres, because, well, that's just how it's done. This does not necessarily mean that the table will be rewritten, which I assume is what you're concerned abou