tag:blogger.com,1999:blog-20038672.post8194514980762048791..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: Big IdeasRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger77125tag:blogger.com,1999:blog-20038672.post-13628434849821991692010-11-14T07:45:17.573-05:002010-11-14T07:45:17.573-05:00Hello,
I have a message for the webmaster/admin...Hello,<br /> <br /> I have a message for the webmaster/admin here at rhaas.blogspot.com.<br /><br />Can I use part of the information from this blog post above if I provide a link back to this website?<br /><br />Thanks,<br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-47371621953581600592010-09-28T22:58:55.575-04:002010-09-28T22:58:55.575-04:00@Anonymous - Which link is not working for you?@Anonymous - Which link is not working for you?Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-49700443868445860082010-09-25T13:29:19.420-04:002010-09-25T13:29:19.420-04:00Thanks for sharing this link, but unfortunately i...Thanks for sharing this link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please reply to my post if you do!<br /><br />I would appreciate if a staff member here at rhaas.blogspot.com could post it.<br /><br />Thanks,<br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-3894233010173743582010-09-20T09:10:50.545-04:002010-09-20T09:10:50.545-04:00Thanks for sharing the link, but unfortunately it...Thanks for sharing the link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please answer to my post if you do!<br /><br />I would appreciate if a staff member here at rhaas.blogspot.com could post it.<br /><br />Thanks,<br />WilliamAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-12744611251161252862010-05-24T20:14:22.038-04:002010-05-24T20:14:22.038-04:00I'll add another one for "more informatio...I'll add another one for "more information from the database". This could be:<br /><br /> * More built in queries/views for determining index/table sizes, which indexes are getting used and which aren't, when things are getting bloated, etc. I've been devouring some of the pgCon talks about these things but the information sometimes seems so difficult to get to unless someone posts some helpful system catalog queries to build that info.<br /> * More options for logging useful information (into separate files that can be rotated/handled differently) that can later be parsed and analyzed in useful ways. Especially if some contrib modules were added to do that analysis.<br /> * Anything to help me make more sense out of EXPLAIN ANALYZE. My company's main product is maturing to the point where I can go back to some of our problem queries to optimize, but I often don't know where to start. Identifying problems and hinting at potential solutions would help me immensely. Documentation, tools, GUIs, I don't care, I just need some help.<br /> * Some way to sanity check various configuration/tuning options. So many of the optimization recommendations and best practices are outdated and confusing. I'd like a simple way to determine either what my best settings are or be able to create test suites to benchmark various settings. Really, even an updated best practices documentation with each release would be awesome.<br /><br />Simple and easy things that would make my life easier:<br /><br /> * Being able to modify ENUMs without having to rebuild my table would be super dandy.<br /> * Some kind of an "If 0 results on UPDATE, do an INSERT instead" functionality. Currently I implement this in application logic (which makes sense), but being able to fire a single statement at the DB instead would be faster and more efficient code-wise.<br /><br />Beyond that, next-gen pie-in-the-sky type features I'd like to see include:<br /> * Materialized views would be helpful in some circumstances. I'd actually been investigating rolling my own here without knowing about this generalized functionality.<br /> * Better partitioning support. Being able to split my data up without all the caveats and manual setup would be dandy.<br /> * Multi-master clusters, when combined with that partitioning support would really enhance scalability.<br /><br />I've been using PostgreSQL since 7.0/7.1 and I have to say the progress that's been made has been incredible. That so many things have dropped off my wishlist since those days is very encouraging and I really look forward to seeing what the future holds.Gregorynoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-41251480047003620242010-05-12T05:51:31.596-04:002010-05-12T05:51:31.596-04:00More monitoring features. We want to know which co...More monitoring features. We want to know <i>which</i> component does a query realy slow down: parser? executer? IO-Time (read/write)? For us log_statement_stats, log_parser_stats, log_planner_stats, log_executor_stats are a little bit too much, but log_min_duration_statement(0) with the duration-time is not enough to drill down the what's realy going on inside of PG.Berndnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-10003374044164315692010-05-11T01:24:19.142-04:002010-05-11T01:24:19.142-04:00I would like to see new features in table partitio...I would like to see new features in table partitioning: <br /><br />a) Horizontal partitioning. Right now there is some very useful Horizontal partitioning. It would be nice to have support at GUI level.<br />It would be nice to improve the current Horizontal partitioning. Version 8.2.x needed some triggers and some manual stuff. <br />I think it would be nice to create a table and define the Horizontal partition with DDL and let postgresql to solve the implementation details.<br /><br />b) Alter a Horizontal partitioned table. Meaning alter using DDL the partition scheme.<br /><br /><br /><br />c) Vertical Partitined Table. Same thing: It would be nice to have support at GUI and DDL level. <br />Perhaps assigned columns or groups of columns to different tablespaces. Same indexes.<br /><br />d) Alter a vertical partitioned table.<br /><br />:Dpabloahttps://www.blogger.com/profile/16133252584307890598noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-42061919899754311422010-05-07T11:08:39.714-04:002010-05-07T11:08:39.714-04:00Personally I'd like to see more "enterpri...Personally I'd like to see more "enterprisey" stuff:<br /><br />1. Auditing capabilities similar to oracle. PG is getting pushed aside at my shop because of lack of SOX compliance auditing.<br /><br />2. Triggers on "create" and "alter" SQL commands. I think this could potentially make replication much much easier to administer (you could trigger the replication system to replicate the change). Triggers on login/logout, startup/shutdown, and on errors (especially privilege errors for auditing) would be extremely useful.<br /><br />3. More monitoring metrics to measure and tune PG parameters, such as autovac and bgwriter.<br /><br />4. Read/Write capability while re-indexing. I have a 24x7x365 system and reindexing never happens.<br /><br />5. Separate logs for connection logs, SQL logs, and system notice/warning/error logs.Uncommon Sensehttps://www.blogger.com/profile/06152462583343281653noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-39755906787177705562010-05-07T08:07:22.284-04:002010-05-07T08:07:22.284-04:00Top 10 mentioned features so far:
1. Materialized...Top 10 mentioned features so far:<br /><br />1. Materialized views<br />2. Multiple CPUs/parallel query<br />3. MERGE<br />4. Automatically clustered indexes (index-only scans?)<br />5. Improved partitioning<br />6. Multimaster replication<br />7. Easier configuration/administration<br />8. Temporal features<br />9. "Real" procedures with transaction handling<br />10. Locale per column or query<br /><br />Which is up to small variations almost exactly what http://postgresql.uservoice.com/forums/21853-general says.Anonymoushttps://www.blogger.com/profile/02849480732923051923noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-56929303307721595692010-05-07T05:13:42.558-04:002010-05-07T05:13:42.558-04:00Although I'm happy with WAL shipping changes i...Although I'm happy with WAL shipping changes in 9.0 I'd still like to see full-blown multi-master replication.awe_czhttps://www.blogger.com/profile/00300333989305963703noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-10636592676734463752010-05-07T02:47:27.632-04:002010-05-07T02:47:27.632-04:00First of all - shame on me for not having sent you...First of all - shame on me for not having sent you guys a contribution yet. I will remedy this.<br /><br />I've been using postgres for a small personal project: web-based (but socket driven) mmo space strategy game - infinite time / space, no "winner". the client is in silverlight of all things.<br /><br />anyway. this is my first experience with postgres and i like it! since i'm in a continuous, heavy dev cycle and i'm still in early beta - i haven't learned much about the diagnostics and monitoring...<br /><br />one thing i have used a lot is pg_dump... trying to automate beta deployments. it would be nice if -c supported "if exists, drop" and if -T also filtered out sequences. other than that...<br /><br />YAY.<br /><br />(oh also - if you want to play... i'll make you a beta account)DSquireshttp://www.werulethestars.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-52347909328258119832010-05-07T01:16:23.383-04:002010-05-07T01:16:23.383-04:00Postgres has been kind to me, but I still have two...Postgres has been kind to me, but I still have two large problems with Postgres:<br /><br />1) Partitioning<br /><br />2) Clustered Indexes<br /><br />The data sets I deal with are time ordered sets of continuous data 24/7. This naturally leads me to want to both cluster on timestamp and partition by time. <br /><br />After realizing how much server side code had to be written to support partitioning (building up the next partition at the correct time, dropping old partitions, replacing triggers etc) I was actually a bit shocked. I suspect that many users besides myself are looking to parition by time ranges, so I think that support for automated paritioning based on some simple rules (per day, per 1,000,000 records) would be very useful.<br /><br />Additionally, the lack of automatic clustering significantly hurts reporting on my latest partition that is still receiving incoming data. It is relatively easy for me to get our "historical" partitions clustered, but the current partition is not (and can't reasonably be) routinely clustered. Right now there is no real solution besides making the clustered range a bit smaller to limit the size of the non clustered data set, but making too many paritions seems to have a significant impact on insert performance. I suspect that this has improved in 9.0, but I haven't yet had time to test.Unknownhttps://www.blogger.com/profile/02605202809822064675noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-63754242644447748332010-05-07T00:24:13.998-04:002010-05-07T00:24:13.998-04:00+1 for better JDBC drivers. The ability to use pos...+1 for better JDBC drivers. The ability to use postgresql to its full potential with Hibernate and JPA in general would be huge. It's my understanding that it doesn't yet support fully JDBC 4.0.Unknownhttps://www.blogger.com/profile/07905498427479774240noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-37926922706222887532010-05-06T18:31:43.913-04:002010-05-06T18:31:43.913-04:00MERGE or a simpler non-standard upsert-y statement...MERGE or a simpler non-standard upsert-y statement.fdrhttps://www.blogger.com/profile/17355808021372259590noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-59249035287240769202010-05-06T16:15:58.548-04:002010-05-06T16:15:58.548-04:00Being able to alter column position in a way that ...Being able to alter column position in a way that is better than what is listed at <a href="http://wiki.postgresql.org/wiki/Alter_column_position" rel="nofollow">http://wiki.postgresql.org/wiki/Alter_column_position</a> would be appreciated.Unknownhttps://www.blogger.com/profile/09192357465428063312noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-182867809842800942010-05-06T16:15:04.824-04:002010-05-06T16:15:04.824-04:00I would love to see better integration in .NET. A ...I would love to see better integration in .NET. A good LINQ to Postgres implementation would be really useful.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-9222575144294749472010-05-06T15:05:50.985-04:002010-05-06T15:05:50.985-04:00The killer database feature nobody seems to have b...The killer database feature nobody seems to have been able to implement yet is good temporal support. Read "Temporal Data and the Relational Model by Date (no flame wars ;)) and Darwen, the stuff in there would save *tons* of application development time in lots of areas (accounting, contracts, scheduling) and improve application correctness and performance as a bonus.<br /><br />Server-side support for graph algorithms would also be great. DFS, BFS, all-pairs shortest paths, single source shortest paths, connected components, minimum spanning trees, etc all have lots of applications but sometimes require access to huge amounts of data to produce quite small result sets.Douglas McCleanhttps://www.blogger.com/profile/06662331850663558712noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-52060675051628494172010-05-06T14:23:51.776-04:002010-05-06T14:23:51.776-04:00Fun post Robert! Here's a PostGIS take!Fun post Robert! Here's a <a href="http://blog.opengeo.org/2010/05/06/postgresql-big-ideas/" rel="nofollow">PostGIS take</a>!Paul Ramseyhttps://www.blogger.com/profile/04056244920940087995noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-1503620276096891522010-05-06T13:52:05.383-04:002010-05-06T13:52:05.383-04:00I Vote for:
Parallel Query Optimizer (From Greem...I Vote for: <br /><br />Parallel Query Optimizer (From Greemplum)<br />Converting SQL or MapReduce into a physical execution plan.<br />Using a cost-based optimization algorithm in which it evaluates a vast number of potential plans<br />and selects the one that it believes will lead to the most efficient query execution.<br />Take a global view of execution across the cluster, and factors in the cost of moving data between nodes<br />in any candidate plan. <br /><br />Polymorphic Data Storage (From Greemplum)<br />Customers can tune the storage types and compression settings of different partitions within the same table.<br />A single partitioned table could (for example) <br />have older data stored as 'column-oriented with deep/archival compression',<br />more recent data as 'column-oriented with fast/light compression',<br />and the most recent data as 'read/write optimized' to support fast updates and deletes.<br /><br /><br />BITMAP INDEX like Oracle 9i Does<br /><br />create bitmap index<br />idx_product_local<br />on<br />pos_product_local(ag_prod_cod_barra, ag_gdet_local)<br />from<br />pos_product_local ag ,pos_product a, pos_local pg<br />where<br />ag.ag_prod_cod_barra = a.prod_cod_barra and ag.ag_gdet_local = pg.gon_codigo;<br /><br /><br />MATERIALIZED VIEWS <br />Need of Built-in materialized views and updateables.<br /><br />CONFIGURATION PARAMETER<br />The use multiple cores to execute a single CPU-heavy transaction.Hugo R. L.http://www.datacentersystems.orgnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-56411057722909283672010-05-06T12:22:10.160-04:002010-05-06T12:22:10.160-04:00Can you tackle a big problem?
Distributed databas...Can you tackle a big problem?<br /><br />Distributed database with automated load-balancing, optimized querying, data replication, et cetera.<br /><br />While temporal databases, graph-database-like interaction, data-cubes, and the multitude of RDBMS standards are all very nice to have and support...<br /><br />RDBMSes are inherently based on relational algebra. Relational algebra ought to be distributable. I would like to be able to access my database as though it were a single server. But, when I need additional performance, add a few systems to a cloud and call it good. I don't want to worry that if one of those systems goes down, the data is lost. I don't want to be limited in my data capacity to that amount of storage available on the smallest machine. This might require that I have a separate disk performing asynchronous saves like git change-sets to a central data store. Each system would have to be ACID compliant with its operations including a commit to the queue on the persistent data store manager.<br /><br />That's a big task, eh?jmcentirehttps://www.blogger.com/profile/07185404395432206163noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-68970357941530121852010-05-06T12:08:41.235-04:002010-05-06T12:08:41.235-04:001. autoconfiguration: I would love postgres to sug...1. autoconfiguration: I would love postgres to suggest or set its best guess at the optimal settings for shared_buffers, work_mem etc given my hardware.<br /><br />2. query optimization with stored procedures: while the query optimizer is great for plain SQL queries it is not able to dig down and take into account the queries within stored procedures. That is, I have a SQL query that has a subquery with a call to PL/PGSQL stored procedure. Expand the ultimate query from all the calls and optimize that.<br /><br />3. documentation: yes, the technical docs are good but what about books for newbs: "postgres for dummies", "postgres in 24 hours" etc. These are important in lowering the barrier to entry to non-DBAs. That your average Barnes & Noble / Borders etc. has 10+ books on MySQL, none on postgres I am sure drives traffic to mysql.<br /><br />4. schema support: schema are not yet first class concepts with full support. E.g. in 8.3 there is not schema option for log_line_prefix and I cannot specify a schema in psql -cAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-49562163112779347732010-05-06T12:07:06.187-04:002010-05-06T12:07:06.187-04:00live materialized views would be massively useful ...live materialized views would be massively useful for web apps.<br /><br />at the moment a postgres user is forced to make tradeoffs between good design (normalization, referential integrity) and performance - basically it's too slow to do all the joins, and you're better off duplicating the data.<br /><br />a live materialized view would give the best of both worlds - create a well structured DB, but then use materialized views to cache the overhead of flattening the tables.<br /><br />web sites frequently run at 10,000-1 or higher read/write ratio, so it really doesn't matter a bit if there is significant cost to maintaining the view (as long as it's not a complete rebuild on every change..)Adamhttps://www.blogger.com/profile/15295240411610633742noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-57630763033419338362010-05-06T12:04:25.214-04:002010-05-06T12:04:25.214-04:00Materialized Views.
They're the only thing I ...Materialized Views.<br /><br />They're the only thing I really miss from my days using commercial database software.Unknownhttps://www.blogger.com/profile/15705358858600924144noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-4925442345920790462010-05-06T12:03:19.501-04:002010-05-06T12:03:19.501-04:00I would love to see SSI (serializable snapshot iso...I would love to see SSI (serializable snapshot isolation) for truly serializable transactions.<br />Then you would know for sure there's no isolation anomalies despite having very complex transactions.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-58786495108175681972010-05-06T11:46:00.746-04:002010-05-06T11:46:00.746-04:00I'm no database guy, but my business associate...I'm no database guy, but my business associate won't use postgres because it doesn't have true record level locking for serial transactions. I know for most uses mvcc and concurrent transactions are faster, but some people have to use the serial transaction features and would really like true record level locking.<br /><br />Parallel queries would also be nice.Anonymousnoreply@blogger.com