pgaddict

18 ms·

151.
by pgaddict 8y ago
Generally speaking yes, but it's not clear when exactly that will happen (if at all). Firstly, at this point JIT requires LLVM, with may or may not be available when building (so it depends on the packager). We might add other JIT prov
152.
by pgaddict 8y ago
https://www.reddit.com/r/ProgrammerHumor/comments/9gtq70/lad...
153.
by pgaddict 8y ago
Or just use pg_upgrade. Or use logical replication to reduce the downtime even more.
154.
by pgaddict 8y ago
That really depends. You're right UPDATEs may be an issue (because we handle them essentially as DELETE+INSERT). Generally speaking, row churn in the table alone is not an major issue - it's easy to clean up by vacuum, and it will
155.
by pgaddict 8y ago
I'm not sure I understand the "open source" part - it's not as if we do it for free. I'd say for most of the senior PostgreSQL engineers / contributors it's part of their paid jobs. We either work for comp
156.
by pgaddict 8y ago
Good question. I'm contributing to postgres for quite a few years and I still don't have a clear answer to that. It certainly is not a single discrete reason, but IMHO a combination of various factors: 1) careful step-by-step engi
157.
by pgaddict 8y ago
I agree - got one, quite happy with it. But it's targeted for home use, not sure how well it fits into larger networks (small offices). MikroTik seems like a better fit for that, not sure. One major advantage of Turris Omnia is that CZ
158.
by pgaddict 8y ago
https://www.youtube.com/watch?v=uvPbj9NX0zc
159.
by pgaddict 8y ago
I don't think any of the extensions actually provides the type of auto-tuning / balancing you're asking for, actually. For example pg_partman helps with setting up some partitioning schemes (by time, by ID), but does not reba
160.
by pgaddict 8y ago
I'm not really sure what is your point. I was not suggesting partitioning alone is a load-balancing solution, but that partitioning in combination with other features is a powerful scalability feature. Assuming there is a (1) way to ke
161.
by pgaddict 8y ago
That is what was mentioned as "global indexes" in this discussion. The problem with this approach is twofold - firstly it negates many of the partitioning benefits (e.g. removing data is not merely a DROP PARTITION but you have to
162.
by pgaddict 8y ago
Possibly, but considering how much easier is the second case to implement (compared to global indexes), I'd expect that to get in first. I'm not sure why the bloom filter would require good spatial locality?
163.
by pgaddict 8y ago
No, that's not true. When combined with parallelism, ability to place partitions to other hosts and partition-aware algorithms (partition-wise joins/aggregation etc.) it can be a powerful scalability feature.
164.
by pgaddict 8y ago
I think eventually we'll go with the second option, and do stuff to eliminate the scalability issues with many partitions. For example we could maintain a global bloom index (still global, but tiny compared to the sidetable), which sho
165.
by pgaddict 8y ago
Unfortunately not in PG11, but not because we don't want it - it simply didn't get ready in time. https://github.com/postgres/postgres/commit/3de241dba86f3dd0... The relevant limitation is described
166.
by pgaddict 8y ago
I'm not sure what you mean by "move to a new host" - all the built-in partitioning features are single-host for now, so this does not make much sense. There are plans to support remote (foreign) partitions, but that is in the
167.
by pgaddict 8y ago
The downside of using ORM this way is that it doesn't really solve the interesting cases, or more subtle differences in behaviour. By "interesting cases" I mean applications that leverage the more unique features in different
168.
by pgaddict 8y ago
Keep in mind there are limitations - the unique index / constraint has to include the partition keys, and foreign keys are possible in one direction only. This may be improved in the future, of course.
169.
by pgaddict 9y ago
I don't recall the exact discussion from P2D2, but AFAIK the reasoning was more along the lines "There are other bottlenecks that we need to address first." That is, issues that would either limit the JIT gains or issues with
170.
by pgaddict 9y ago
I'm not familiar with merge tables, but it sounds quite like a view for UNION of all the tables.
171.
by pgaddict 9y ago
I agree pg_partman is awesome, and it's a great testament to the extensibility baked into PostgreSQL.
172.
by pgaddict 9y ago
Except that the removal of data from tables is fairly expensive process - you have to delete the data, which means running queries (which have to scan the data, write a lot of WAL and modified blocks, etc) and then do cleanup (which means v
173.
by pgaddict 9y ago
Partitioning by date is a common solution to efficient archiving - the partitions may be dumped independently, and instead of DELETE, which requires expensive cleanup after the fact, you can simply drop the partition (which does not require
174.
by pgaddict 9y ago
There's a patch adding this capability in the current commitfest. See: https://commitfest.postgresql.org/16/1452/ If everything goes well, it might be in PostgreSQL 11.
175.
by pgaddict 9y ago
I don't know who recommends random_page_cost=1, but IMNSHO it's a bit silly. Even SSDs handle sequential I/O better than random I/O. Values between 1.5 and 2.0 are more appropriate. I wouldn't really recommend 1.0 e
176.
by pgaddict 9y ago
You can rebuild it with smaller pages, including 4kB, which may be beneficial for various reasons. The packages however stick to 8kB. And yes, the database has it's own cache (aka shared buffers), on top of page cache (filesystem cache
177.
by pgaddict 9y ago
But you don't know which blocks you'll need at planning time, so you can't really check that. You could of course check if the total database size is within RAM, but it's much more common to have database much larger tha
178.
by pgaddict 9y ago
Not entirely. The documentation says you can interpret it that way, not that it's how the numbers were determined. AFAIK it's much more "We're using those numbers as defaults because they seem to be working well,"
179.
by pgaddict 9y ago
The idea is that up until some number of relations (8 by default, IIRC) the join tree is searched exhaustively, then it switches to the genetic algorithm. So it's kinda automatic.
180.
by pgaddict 9y ago
Unfortunately the author does not say some pretty basic things - which PostgreSQL version, how much data, how much of it fits into RAM, what storage (and hardware in general) ... If I understand it correctly, PostgreSQL was using the defaul

More ›