tag:blogger.com,1999:blog-20038672.post239545896629570125..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: PostgreSQL Past, Present, and Future: Moving The GoalpostsRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger79125tag:blogger.com,1999:blog-20038672.post-21233856655530330452017-05-14T07:31:06.587-04:002017-05-14T07:31:06.587-04:00PosgreSQL is proving i's growing , maturing an...PosgreSQL is proving i's growing , maturing and its strong is enoug against existing giant, Oralce or any. I have a chance bidding a project proposing 9.5 against Oracle where users 's very impressive the ability our cloud app with PosgreSQL can offer instead of Oracle. This is a huge challenge in Thailand.<br />Keep the good work PosgreSQL.Isn't Manhttps://www.blogger.com/profile/06604588267733590036noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-46187363572454587402016-02-09T13:13:51.232-05:002016-02-09T13:13:51.232-05:00I would realy like to have a option to hint postgr...I would realy like to have a option to hint postgres that a table should be hold in memory. I mean not temporary tables. If a update occur the data should be written to disc. Would be realy nice to have such a feature.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-9119619321168706362016-01-21T19:15:17.592-05:002016-01-21T19:15:17.592-05:00Create index with parallel processes! We want to u...Create index with parallel processes! We want to use Postgres for some very large BI warehouses, but the index creation is seriously hurting. 9.5 was a huge win for performance, but parallelism is what we really need.Nathan Thomhttps://www.blogger.com/profile/02050581159674678345noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-68101051108375434932016-01-18T21:22:40.787-05:002016-01-18T21:22:40.787-05:00Then the correct thing to do is to tune the client...Then the correct thing to do is to tune the client side pools to close down idle connections instead of aggressively keeping so many unused connections open for long periods of time. I suppose it might help to enforce this on the server side. Maybe the DBAs know better than the application developers :-)<br /><br />Regarding pooling, it might be useful to something LISTEN/NOTIFYish with expiring client side caches that have a volatility of stable. That, I would love to see.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-9640185242493065462016-01-18T21:10:36.783-05:002016-01-18T21:10:36.783-05:00With a hot standby on its own I/O (not part of a S...With a hot standby on its own I/O (not part of a SAN, perhaps local RAID), you can back up daily without affecting production, so you would not have to accumulate that many WAL files. But optimizing for faster restores is obviously a plus. Even with improvements to that, it is nicer to have a time delayed hot stand by, which you can do now. Even with the best restore technology, something that is "more online" is a better fall back plan.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-65469234758385411972016-01-18T13:00:17.363-05:002016-01-18T13:00:17.363-05:00Full support for RANGE within WINDOW FUNCTIONS wou...Full support for RANGE within WINDOW FUNCTIONS would be really helpful. There is no elegant and efficient way in PostgreSQL to write a query that references a window spaced by defined periods of time. <br /><br />PostgreSQL documentation states that the "frame_clause" in "window_definition" has the following limitation: "The value PRECEDING and value FOLLOWING cases are currently only allowed in ROWS mode".<br /><br />That is supported by many other DBMS e.g. in Oracle the following works: <br /><br />SELECT amount * 100 / AVG(amount) OVER (PARTITION BY client_id ORDER BY purchase_date RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR PRECEDING)<br />FROM purchases<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-65419739887634846972016-01-17T10:55:19.356-05:002016-01-17T10:55:19.356-05:00For me (us) the incremental backup done properly t...For me (us) the incremental backup done properly translates in the way leading commercial RDMS vendor is doing it (at block level). Our typical data processing looks like this:<br />- Full backup on Saturday (non-working day)<br />- from Monday to Friday our Oracle db is generating ~150GB of redo log over the day. At night we take incremental daily backup, which is just ~3-5GB (changed database blocks). <br /><br />If we need to recover db on Friday, we take full backup from Saturday + ~20GB of incremental backups + some redo logs from Friday. If we would use PG, we would need to apply 600GB of WAL files. In addition to that if we found out that we need to do point in time recovery due to some logical error to some previous time, let's say as of current time - 2 days, this is easier/faster done with the help from incremental backups. As you can see, it's not about replacing WAL logs altogether, also I don't see how standby can truly replace incremental backup (for example allowing faster PITR?).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-39622629324183916602016-01-17T08:23:31.131-05:002016-01-17T08:23:31.131-05:00I woluld like to see sql hints. I konow that PG sh...I woluld like to see sql hints. I konow that PG should choose best query plan based on available stats. Sadly, we do not want (or could) make such stats because of high data volume we manage. There should be a way to manually improve queries when needed. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-78123187890289482492016-01-15T21:42:04.865-05:002016-01-15T21:42:04.865-05:00I agree that server side pools are inferior to cli...I agree that server side pools are inferior to client side pools; but that doesn't mean server side pools are useless. For example, suppose there are several - perhaps even many - different applications accessing the same database server. Even though each application may have its own connection pool, the total number of connections to the server across all of those pools may be very high. If the server can handle that without becoming overwhelmed, that is better.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-6663104813504128122016-01-15T20:34:52.771-05:002016-01-15T20:34:52.771-05:00Replacing WAL logs with incremental backups sounds...Replacing WAL logs with incremental backups sounds like replacing two very similar things. Your backup solution would have to be doing something intelligent with its own incremental backups to have an advantage (such as merging replaced blocks, which sounds like a hot standby). Otherwise, WAL logs are not functionally different than your own incremental backups. I have heard of taking backups from the standby server before. Maybe this is why. The standby is acting as an incremental backup solution. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-18516300876882329982016-01-15T20:11:31.256-05:002016-01-15T20:11:31.256-05:00So basically set shared buffers higher and add an ...So basically set shared buffers higher and add an feature to make a temp like table error out instead of flushing to disk when it gets too big.<br /><br />Someone has asked about using /dev/shm for a tablespace before, but the problem there is that the schema / table definition needs to live separately from the tuples for that to not cause issues for the rest of the cluster. Also, tuples take up room in both /dev/shm and shared buffers, so you need double the memory. /dev/shm might not be the best use case for disposable table spaces.<br /><br />Having ephemeral storage that is not /dev/shm could still be useful for other things. Maybe temp spaces that are not RAIDed or something, and the process that needs that space can be re-run without too much pain.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-88540030866793067832016-01-15T19:55:52.469-05:002016-01-15T19:55:52.469-05:00That sounds a lot like ZFS's send receive.That sounds a lot like ZFS's send receive.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-7813937932577610042016-01-15T19:54:58.226-05:002016-01-15T19:54:58.226-05:00Sorry, I somehow replied to the wrong comment.
So...Sorry, I somehow replied to the wrong comment.<br /><br />So in other words, return cursors instead of sets. I too have this issue with larger data sets. I have worked around it in the past with views, and putting any required PL/pgSQL functionality into functions that are called from the WHERE clause to filter rows that cannot be filtered via the view's SQL. I also think that cursors also might help reduce memory when both the client and server cannot hold the whole resultset.<br /><br />PostgreSQL's query rewriter is nice. Views in PostgreSQL work much better than in other database software. I wish that it also applied to CTEs.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-25688832076275697302016-01-15T19:42:25.391-05:002016-01-15T19:42:25.391-05:00#3: Connection pooling on the server side is a ha...#3: Connection pooling on the server side is a hack. Clients should always do their own pooling because even if the server has a pool, the TCP handshake is an unavoidable delay. Client based pools skip the TCP handshake when a connection is reused. Therefore, server based pools are always inferior to client pools. No hacks please.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-34121803201691630352016-01-15T19:41:38.759-05:002016-01-15T19:41:38.759-05:00That sounds a lot like ZFS's send receive.That sounds a lot like ZFS's send receive.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-9995621131504588212016-01-15T19:29:33.062-05:002016-01-15T19:29:33.062-05:00It allows you to take another hot backup of the fi...It allows you to take another hot backup of the file system of only the changed files or blocks. You can then stop keeping the WAL files for that span of time.Bruce Momjianhttps://www.blogger.com/profile/07875088787463864011noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-5880304057042481372016-01-15T19:27:34.046-05:002016-01-15T19:27:34.046-05:00One feature I'd love to have for cstore_fdw is...One feature I'd love to have for cstore_fdw is support for indexes. cstore currently uses built-in min/max indexes to skip over unrelated data segments. This helps, but being able to leverage PostgreSQL's indexing subsystem would be huge and help cstore support many more use-cases.<br /><br />For newcomers to FDWs, I think having a tutorial-like documentation that gets people started out with the simple APIs and gradually teaches them new ones, would help. Also, most FDW writers copy and paste pieces from other FDWs. Having a reference FDW that has a modular design could help developers in getting started.Ozgun Erdogannoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-73696745804339703412016-01-15T19:27:03.205-05:002016-01-15T19:27:03.205-05:00Just curious, how would a good incremental backup ...Just curious, how would a good incremental backup be different than a hot standby?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-34602391511736462212016-01-15T19:23:39.055-05:002016-01-15T19:23:39.055-05:00Yes, I was aware of that. But for smaller setups,...Yes, I was aware of that. But for smaller setups, setting up something dedicated like that is not as convenient, and they are the ones who probably need it the most. The syntax, I imagine, would be like a truncate, but that it would accept a trans ID. Also, maybe instead of reverting the table, it could optionally put the rows into a new table, or maybe you could query it like the old time travel functionality, but with trans IDs. Anyway, I am sure that it would be a hit with the devs and DBAs who sometimes forget to use transactions and WHERE clauses at the same time!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-48963826764241938552016-01-15T18:23:23.256-05:002016-01-15T18:23:23.256-05:00Starting with 9.4, you can set up a standby that l...Starting with 9.4, you can set up a standby that lags the master by a certain time interval using recovery_min_apply_delay. That's not exactly the same thing, but it might help.<br /><br />http://www.postgresql.org/docs/current/static/standby-settings.htmlRobert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-14568465939129872052016-01-15T18:19:10.745-05:002016-01-15T18:19:10.745-05:00Loosening some of the conditions in https://wiki.p...Loosening some of the conditions in https://wiki.postgresql.org/wiki/Inlining_of_SQL_functionsRobertnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-75590909462576893662016-01-15T17:09:23.253-05:002016-01-15T17:09:23.253-05:00Configure the server to retain a snapshot of the d...Configure the server to retain a snapshot of the database that is at least x minutes old (like a rolling replication slot) and offer the ability to revert a table (or set of tables) back to a specific trans ID.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-70875937089281768922016-01-15T13:35:44.911-05:002016-01-15T13:35:44.911-05:00Indeed. We are using `refresh materialized view co...Indeed. We are using `refresh materialized view concurrently`, but it seems that the refresh operation is always blocked by some reader lock on the view. So, yes, possibly auto-refresh of materialized views.Anonymoushttps://www.blogger.com/profile/17547191877175861235noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-81580738613717469992016-01-15T12:24:23.711-05:002016-01-15T12:24:23.711-05:00Yes, such recalculations are certainly possible, t...Yes, such recalculations are certainly possible, though with with Postgres's plug-in aggregates, we could only do it with built-in aggregates that support this approach. I don't know anyone working on this, however.Bruce Momjianhttps://www.blogger.com/profile/07875088787463864011noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-69643297206128822822016-01-15T12:07:34.366-05:002016-01-15T12:07:34.366-05:00Yes I would expect aggregates to require recalcula...Yes I would expect aggregates to require recalculation, if any their inputs had changed, but if one structures multiple views hierarchically, such as daily totals then monthly totals then yearly, the heavy lifting would be done at the detail level, where the most benefit from incremental refresh would be obtained, re calculating months and years based on daily totals would be relatively trivial.<br />Also very useful I think for storing transitive closure of large graphs, only recalculating nodes where a sub node has changed is obviously much more efficient than recalculating all nodes - something I currently do which enables lightning fast graph traversal queries.Gary Clarkehttps://www.blogger.com/profile/07110129973127537209noreply@blogger.com