petergeoghegan

13 ms·

91.
by petergeoghegan 6y ago
https://www.postgresql.org/docs/13/release-13.html
92.
by petergeoghegan 6y ago
Actually, Postgres does not add any performance features to point releases at all. Point releases are only for bug fixes. You may be thinking of MySQL. Occasionally, there are cases that could be argued to be exceptions to the general rule.
93.
by petergeoghegan 6y ago
How does the introduction of new features negatively impact your use of Postgres?
94.
by petergeoghegan 6y ago
Why would you assume that the author must have a formal model? It's a short blog post. Perhaps the author really is deserving of derision to some degree - who can say? It should not be based on a straw man that nobody could possibly
95.
by petergeoghegan 6y ago
I agree with your points about undefined behavior. I'm pleasantly surprised that you didn't defend C's terrible strict aliasing rules. Your interpretation seems uncharitable. I imagine that you've interpreted the author&
96.
by petergeoghegan 6y ago
I read the article as a criticism of the strict aliasing rules in C. I think that you're attributing something to the author that they couldn't possibly believe.
97.
by petergeoghegan 6y ago
GIN is optimized for things like full text search and jsonb indexing. GIN's internal B-Tree/entry tree consists of datums that are decomposed from the data type you're actually indexing rather than the data type itself (e.g.
98.
by petergeoghegan 6y ago
There is no overhead for reads, since deduplication doesn't compress the data as such. Rather, deduplication creates a representation that removes duplicates but is otherwise identical to the existing representation.
99.
by petergeoghegan 6y ago
You will need to REINDEX after upgrading using pg_upgrade. Otherwise deduplication won't be applied in the old indexes at all. These days it's possible to use the CONCURRENTLY option with REINDEX, which is much less disruptive.
100.
by petergeoghegan 6y ago
B-Tree deduplication can also help to control duplicate index tuples caused by MVCC "version churn" -- including in unique indexes. See the paragraph that begins with "B-Tree indexes are not directly aware that under MVCC...&
101.
by petergeoghegan 6y ago
It's hard to give a simple answer, but it's certainly possible for it to help performance significantly. Both for queries, and garbage collection by VACUUM. It's especially helpful with writes + low cardinality indexes.
102.
PostgreSQL B-Tree index deduplication(blog.rustprooflabs.com)
186 points by petergeoghegan 6y ago|25 comments
103.
by petergeoghegan 6y ago
I hear that one from time to time. It's a consequence of the fact that sequences are generally not transactional, and the fact that the underlying "speculative insertion" infrastructure really does have to do most of the work
104.
by petergeoghegan 6y ago
I am the primary author of INSERT ... ON CONFLICT in Postgres. I extensively studied similar features from other database systems as part of that project. MERGE simply doesn't provide the guarantees that most users want -- not in theor
105.
How to Scram in Postgres with PgBouncer(info.crunchydata.com)
6 points by petergeoghegan 6y ago|0 comments
106.
by petergeoghegan 6y ago
> In a sense, SQL is just not designed to be easy to optimize — it's too broad, and there are too many equivalent-ish ways of doing the same thing. This is why even after incredibly smart people cumulatively spent engineer-millennia
107.
by petergeoghegan 6y ago
The ecosystem of preexisting C extensions to generalize from, for one
108.
by petergeoghegan 6y ago
Most of that stuff depends on explicit CREATE STATISTICS commands being run in order to work around column correlations and stuff like that. The general assumption of independence among columns/attributes is pretty universal (as the pa
109.
by petergeoghegan 6y ago
> The core idea of the project is to get us the convenience of specifying the "what question I want answered," but without the inconvenience of "how is the answer computed / with which specific set of queries / w
110.
by petergeoghegan 6y ago
That may be true, but that doesn't seem like the important takeaway to me. The important takeaway is "In contrast to cardinality estimation, the contribution of the cost model to the overall query performance is limited". Act
111.
by petergeoghegan 6y ago
> Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS! Is it really obvious, though? In my experience, many people consistently fail to grasp this point. It seems likely t
112.
by petergeoghegan 6y ago
> It’s not a myth. In the trite examples provided with tiny datasets they scale. The point of the article is that in general nested loop joins based on index scans/iteration scale in the sense that the amount of I/O incurred gr
113.
Pg_hexedit: Open PostgreSQL relation files in a hex editor with annotations(github.com)
4 points by petergeoghegan 6y ago|0 comments
114.
by petergeoghegan 6y ago
Here is the thesis itself: https://ses.library.usyd.edu.au/bitstream/handle/2123/5353/m... I do remember thinking that it was unusually well argued and presented. It's much more accessible to the pr
115.
by petergeoghegan 6y ago
I used to work with Kevin, back when I was with VMware. I can confirm that it's an interesting story!
116.
by petergeoghegan 6y ago
I believe that you're thinking of Michael J. Cahill: https://courses.cs.washington.edu/courses/cse444/08au/544M/R... This became the basis of PostgreSQL's serializable snapshot isolation featur
117.
Control Runaway Postgres Queries with Statement Timeout(info.crunchydata.com)
8 points by petergeoghegan 6y ago|0 comments
118.
by petergeoghegan 6y ago
The default isolation level is read committed mode, whereas the bug in question only affected applications that use serializable mode. You have to ask for serializable mode explicitly; if you're not, then you cannot possibly be affecte
119.
by petergeoghegan 6y ago
Technically it does. You can ask for read uncommitted mode, though you'll just get read committed mode. This is correct because you're getting the minimal guarantees that you asked for. The SQL standard allows this.
120.
by petergeoghegan 6y ago
The exact recording in question was about 125MB, and that was after I materialized it using "rr pack". I'd say that the storage overhead is unlikely to be a concern in almost all cases. It's just something that you need

More ›