15 ms·

PostgreSQL 10 Beta 1 Released

507 points by ahachete 9y ago

paukiatwee 9y ago
Important features: Native Table Partitioning - https://www.keithf4.com/postgresql-10-built-in-partitioning/ https://www.keithf4.com/postgresql-10-built-in-partitioning/ Logical Replication - https://blog.2ndquadrant.com/logical-replication-postgresql-10/ https://blog.2ndquadrant.com/logical-replication-postgresql-... or what is new in PG 10 https://wiki.postgresql.org/wiki/New_in_postgres_10 https://wiki.postgresql.org/wiki/New_in_postgres_10 Postgresql really awesome!
jph 9y ago
Congratulations to the team. The replication/partition improvements are significant and much appreciated. My favorite improvements are full text search of JSON & JSONB; this makes pg a full replacement for Mongo for my use cases.
- zachmax 9y ago
  I feel like this feature replaces almost all of Mongo's use cases!
  - htsh 9y ago
    I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?
    - byefruit 9y ago
      At my previous company we made heavy use of its lossy compression feature.
      - deleted 9y ago
        [deleted]
      - X86BSD 9y ago
        You would get compression with Postgres running on ZFS.
        kinghajj 9y ago
        Lossy compression, not lossless.
        crypto5 9y ago
        In random cloud provider you may not get FS with compression on your machine..
        sqeaky 9y ago
        Database plus Copy-On-Write file systems sound like a bad idea. I am imagining a modest 100gb database being re-written for every change. I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
        X86BSD 9y ago
        You might be interested in reading this paper: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
        sqeaky 9y ago
        I am interested and I appreciate the thought and link, however these appear to be the slides to a talk without the actual talk. If so they are of limited use because the slides never have all the information the presenter has, and that information is often just a summary. Is this the correct talk: https://youtu.be/dwMQXLOXUco?t=5380 https://youtu.be/dwMQXLOXUco?t=5380 ?
        X86BSD 9y ago
        Yes, sorry about that, that is the corresponding talk to the slides. Thanks for pointing that out.
      - pkhagah 9y ago
        What lossy compression? Were you guys throwing bits into /dev/null?
      - infogulch 9y ago
        Is that related to the cool "hash compression" technology I hear about? Apparently it can compress an arbitrarily large file into just a few bytes, amazing!
    - fnayr 9y ago
      I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much better than if I was forced to make a bunch of tables. This aides in understanding the representation of our game's data, and I believe (but haven't tested) it will be faster than a relational db for my use case, but that's an ancillary benefit anyway.
      - ssijak 9y ago
        But the posters above you said that JSON and JSONB types in Postgres, and functionality around them, eliminated the need to use other databases for document type data. What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.
        fnayr 9y ago
        My claim wasn't that it didn't fulfill their needs, it was that it doesn't fulfill all needs (gamedev is one example that I'm familiar with). Postgres storing JSON types != All mongo functionality I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
        jerrysievert 9y ago
        https://www.github.com/JerrySievert/MongoLike https://www.github.com/JerrySievert/MongoLike that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
        threeseed 9y ago
  - jonknee 9y ago
    As soon as Postgres adds the "random data loss" feature they will have a full superset of Mongo.
    - colanderman 9y ago
      You can always turn off fsyncs…
      - knodi 9y ago
        Then you might as well use pg.
        lima 9y ago
        I think he meant that you can turn off fsyncs in pg in order to add random data loss :-)
        X86BSD 9y ago
        You might be interested in this paper where the author touches on fsync. https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
- Thaxll 9y ago
  Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?
  - gshulegaard 9y ago
    I would say that it isn't configured to scale like Mongo out of the box...but that doesn't mean it can't. You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there. You can also roll your own (if you really want)...and it is relatively approachable to do so using built-in features like partitioning. And, of course, with the 10 Beta it would seem that logical replication is being brought into core which sets the foundation for future replication features such as BDR to also be brought into core. I would also point out that since Mongo's BI connector fiasco, it would seem more and more Mongo users are finding more reasons to just use Postgres (where relational interfaces are desirable for BI): https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...
    - threeseed 9y ago
      None of what you posted is built in and thus supported by the vendor. That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products. The point still remains that after all these year PostgreSQL's scalability story is still a mess.
      - gshulegaard 9y ago
        I think you might be confused somewhat...PostgreSQL doesn't have "a vendor". The history of Postgres starts at UC Berkeley and now has the PostgreSQL Global Development Group which is a mixture of contributors both community and corporate sponsored: https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/ Of that group, both 2nd Quadrant and CitusDB are represented...so in a way you could say their support is "by the vendor". Not to mention EnterpriseDB which also has support options. > we can't use CitusDB when it is not supported by other vendors for use with their products. CitusDB is no longer a fork, it's an extension...this means so long as your other vendors products support Postgres, they support CitusDB. More over, CitusDB itself will sell you an Enterprise package. > The point still remains that after all these year PostgreSQL's scalability story is still a mess. If by mess you mean specifically there is no knob and dial arrangement in the core of Postgres I would agree. But within the core of PostgreSQL there are primitives which make scaling approachable (I myself am working on a data ingestion process that utilizes table partitioning and hand rolled sharding for PostgreSQL). And there are now a plethora of extensions and tools provided by core contributors such as 2nd Quadrant and CitusDB to offer somewhat out of the box solutions and they even come with support. PostgreSQL is not the right tool for every job and the replication/clustering area has been a a sore spot for Postgres in the past. But it certainly isn't bereft of options now...and the inclusion of logical replication in this beta is only the first step in bringing these options closer/into the core.
- _Codemonkeyism 9y ago
  Using MySQL/Mongo for some years, major problems with MongoDB Cloud Manager (several hours site down due to Cloud Manager removing the mongo binary), for some time now I use pg for new projects and think it's great. Two gripes: 1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields) 2. Why is the Postgres JSON syntax so different? Why not just support SELECT document.field.field instead of (inconsistent) document->'field'. Imho pg JSON syntax is hard to read and new to learn.
  - luhn 9y ago
    Re 2: That syntax is already in use by `SELECT schema.column`. I do agree that the syntax is a bit cumbersome and harder to learn, but I'm not sure if they could have done much better while being consistent with SQL.
    - _Codemonkeyism 9y ago
      Why can't pg same the same syntax? I'm sure it could detect if it's a table column or a document field - or am I missing something? Why not handle documents and columns the same, with documents a a kind of hierarchical columns.
zitterbewegung 9y ago
The new features related to clustering looks exciting. Also SCRAM support and Full text search for JSON and JSONB . Postgres is cementing itself as not only the safe choice for Databases but also the Swiss Army knife .
alfalfasprout 9y ago
The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases. Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...
- jdubs 9y ago
  Every time I see a job post mentioning mysql I realize they just haven't discovered postgres, or they have some really gross problem. :/
  - ubertaco 9y ago
    Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'
    - synotna 9y ago
      WHERE email ILIKE 'foo@example.com' https://www.postgresql.org/docs/9.6/static/functions-matching.html https://www.postgresql.org/docs/9.6/static/functions-matchin...
    - fphilipe 9y ago
      Does MySQL do that on varchar by default? Can't you just do this in PostgreSQL? SELECT id FROM users WHERE email = lower('Foo@example.com')
    - sametmax 9y ago
      Just lowercase everything. Not that hard.
      - irrational 9y ago
cygned 9y ago
I wished they would implement more from SQL:2011. I have a lot of applications that would benefit from system versioned tables. This is a good sum up of useful modern SQL features: https://www.slideshare.net/MarkusWinand/modern-sql https://www.slideshare.net/MarkusWinand/modern-sql
- Tostino 9y ago
  Get involved in the mailing list and start discussion on these features and how they would help you.
  - cygned 9y ago
    I fear I cannot contribute a lot in my extremely limited time. Is there a 'getting started' guide somewhere?
    - Tostino 9y ago
      Honestly you don't even have to submit a single patch to contribute. Here is the developer's FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ https://wiki.postgresql.org/wiki/Developer_FAQ I know the feeling of having extremely limited time, i'm in the same boat. But I use Postgres every day with my startup, and we lean very heavy on the database (not by volume of data, just by complexity of the problems). I've reviewed a few patches that I wanted to see get into Postgres, and contributed to discussions on the mailing list. That's about the extent I am able to do at the moment, but it's better than nothing. More reviewers are never a bad thing.
- saurik 9y ago
  I was expecting that presentation to be stuff you were missing, but the presentation is saying that essentially every feature they describe is implemented in PostgreSQL. Of the SQL:2011 ones, only one wasn't (temporal tables), and even the SQL:2016 features had partial support (with the summary slide at the end of other features they didn't do in detail having stuff that looks familiar in a PostgreSQL context). Do you have a reference of features not in PostgreSQL you are hoping to use?
qeternity 9y ago
As someone with little to no Postgres experience, it seems like they are heading in the direction of providing the type of massively parallel, scale out features that Citus provides. Would love to hear thoughts from someone with real expertise.
- dhd415 9y ago
  Citus is building functionality on PostgreSQL to provide sharding for data sets too large for a single machine. There aren't really any PG 10 features that duplicate that. Some of the new PG features such as increased query parallelism allow for better utilization of single-machine resources. Other features such as logical replication may allow for some horizontal scaling by splitting read workloads across replicas, but none of them provide any sharding capabilities.
  - bjt 9y ago
    Aggregate push down to foreign servers seems to overlap with Citus. It's not as transparent, but you could have a dataset sharded across several PG instances, mount them all into a master with foreign data wrappers, and UNION them (for some queries) to aggregate across the set.
    - amitlan 9y ago
      BTW, you can also create partitions as foreign tables: https://www.postgresql.org/docs/10.0/static/sql-createforeigntable.html https://www.postgresql.org/docs/10.0/static/sql-createforeig...
  - qeternity 9y ago
    Many of them seem like pre-requisites however. I understand what Citus does vs Postgres. But that gap is narrowing.
- mslot 9y ago
  Biased view of a Citus engineer here :) Any enhancement to PostgreSQL is also an enhancement to Citus, or rather, the PostgreSQL ecosystem as a whole. For example, PostgreSQL 10's declarative partitioning feature will help enable sharding+partitioning in Citus, which is one of the most frequently requested features. PostgreSQL 10 also gives you the possibility of setting up a partitioned table in which the partitions are postgres_fdw tables, which allows a basic form of manual sharding without Citus. However, as we've learned over the years, there's a huge difference between the ability to distribute a table across multiple servers and addressing a use case. A sweet spot for Citus is multi-tenant (SaaS) workloads, in which all queries and transactions are specific to a particular tenant. In that case, you can typically distribute most of your tables by tenant ID, and use (replicated) reference tables for data that is shared across tenants. Citus makes sure that data for the same tenant is automatically co-located and as long as your query filters by a particular tenant and joins by tenant, you get full SQL pushdown (with parallelism in PG10), and ACID transactions. At the same time, you can perform parallel DDL commands across all tenants to enable migrations, bulk load data through COPY, perform parallel rollups or transformations through INSERT..SELECT, and run parallel analytical queries. Overall, the combination of these features and the trade-offs that Citus makes ensure that if you need to scale out a multi-tenant app, sharding through Citus solves it. In many cases, the only changes you need to make in your app are adding a tenant_id column to your tables [1], being explicit about the tenant in your queries or ORM [2], and adding create_distributed_table calls. None of the Citus features that allow you to scale out a multi-tenant app are available if you do sharding through partitioning+postgres_fdw so far. I also wouldn't expect core postgres to make aggressive trade-offs to optimise for specific use cases. The PostgreSQL way is to make everything pluggable and let extensions specialise. [1] https://www.citusdata.com/blog/2016/08/10/sharding-for-a-multi-tenant-app-with-postgres/ https://www.citusdata.com/blog/2016/08/10/sharding-for-a-mul... [2] https://www.citusdata.com/blog/2017/01/05/easily-scale-out-multi-tenant-apps/ https://www.citusdata.com/blog/2017/01/05/easily-scale-out-m...
milquetoastaf 9y ago
Postgres is awesome and thankfully is much easier to get set up with high availability and replication baked-in (remember pgpool anyone?). AWS Cloud Formation Docker Swarm stack + postgres primary/replica compose file + datadog and you're good to go.
pilif 9y ago
While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-statistics-examples.html https://www.postgresql.org/docs/10.0/static/multivariate-sta...) is the one single knob I wanted to be able to turn so many times in the past now (especially the n-distinct counts). Most of the time, the planner does an ok job, but sometimes you have tables in a peculiar shape and if you're unlucky, the planner will opt into horribly bad plans. Whenever this happened for me, it was due to one of the two things CREATE STATISTICS allows me to tune in the future. Thank you, thank you, thank you to whoever gave us this wonderful feature
- colanderman 9y ago
  Has anything actually changed with the ROW syntax for UPDATE? Maybe I'm missing something but it looks like the functionality has always been there, but now you're allowed to type the word "ROW". E.g. compare [1] and [2]. [1] https://www.postgresql.org/docs/9.6/static/sql-update.html https://www.postgresql.org/docs/9.6/static/sql-update.html [2] https://www.postgresql.org/docs/10.0/static/sql-update.html https://www.postgresql.org/docs/10.0/static/sql-update.html
  - deleted 9y ago
    [deleted]
- walrus 9y ago
  What's the significance of the `update .. set (..) = row (..) ..` syntax? update comment set modified = now(), body = 'edited comment' where id = 123; vs update comment set (modified, body) = row (now(), 'edited comment') where id = 123; It doesn't seem to provide any new functionality, just a minor difference in syntax.
  - colanderman 9y ago
    EDIT: Turns out the below is not true, though you can achieve the same effect with the sub-select syntax. The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.
ilanco 9y ago
PostgreSQL is an amazing project. A no-nonsense database that delivers what it promises. I'm amazed at what a talented group of people can accomplish when they are driven and put their mind to it. Thanks for a wonderful product.
- _jal 9y ago
  Absolutely agreed. A friend of mine was doing an MBA and was planning a paper on open source collaboration models. She asked me for my opinion on well-run projects and Postgres was the first thing that crossed my mind. They have an excellent combination of goals, structure and expertise, and have created a remarkably low-drama, functional community around delivering excellent tools. I really think there's a lot to emulate there.
  - wmu 9y ago
    My short story regarding community. A few years ago I wanted to add a feature to PL/pgSQL that was on their TODO list. I asked about the feature on a public list and while I was unknown to the community I immediately got a private email from a developer who contributed to that part of code. We discussed the required changes and I prepared a patch. He reviewed it and later was supporting during formal review.
- irrational 9y ago
  Plus their documentation is top notch. After using Oracle for 15 years, I nearly cried when I moved to Postgres recently and saw how wonderful their documentation is.
  - swasheck 9y ago
    Completely agree! I've often said that I didn't learn English grammar until I studied German, I didn't understand Linux until I read the Arch documentation, and I didn't comprehend relational data until I read the Postgres documentation.
Tostino 9y ago
So glad that GiST indexes now support UUID and ENUM data types. That was a big wart for me due to needing exclusion constraints.
- 15155 9y ago
  I always just cast to text in this scenario.
  - Tostino 9y ago
    The issue with that is performance and index size. At first that was my solution, but as more data got into the tables that just wasn't working. For UUID's I had to move to casting to bytea using the uuid_send(uuid) function. For enums, I had to create a function to cast an enum to a unique integer, and used that in my exclusion constraints. It's hacky as hell and not something I like, but it's worked for the past 3 years. I'll be really really happy to get rid of those hacks come PG 10 though.
- pjungwir 9y ago
  I wrote the initial patch for the UUID support! It's my only Postgres contribution so far (except for docs), and I really only write C a few times a year. I made a few revisions but as I fell behind others picked it up and got it over the finish line. I'm pretty proud to say I participated, and maybe others can be encouraged to get involved too. Most of my day I write Ruby and Javascript. :-)
crgwbr 9y ago
Will having logical replication make doing a DB version upgrade in production easier? We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.
- webrobots 9y ago
  Also interested in this topic. Upgrade with downtime is kind of crappy option for us.
- anarazel 9y ago
  > Will having logical replication make doing a DB version upgrade in production easier? Yes, that's one of the major goals. > We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime. Unfortunately on RDS your options are a bit more limited than on plain postgres. Otherwise you could use londiste, pglogical, .. to keep the time to switch over to something very small.
- endymi0n 9y ago
  I guess I remember 9.3 -> 9.4 on RDS sucked big time for us (no migration option whatsoever except pg_dump/pg_restore), but since 9.4 they had it pretty much nailed. Update worked flawlessly during the small failover maintenance window you have every now and then anyway. (You do run a HA setup, right?) YMMV of course, better have your fingers crossed. If you don't trust it, do it the hard way: Switch to write-only mode (easiest: remove write rights for all DB users), notify your users, make a snapshot with pg_dump, restore to a fresh new DB copy and switch your services over. Worked for us, different story if you can't take any write downtime of course.
- deleted 9y ago
  [deleted]
- cryptonector 9y ago
  The most important (to me) thing about logical replication is that with it I can have additional schema elements in replicas. This is important for some use cases where one uses another group's DB and needs to add schema elements that the other DB's admins do not want to host (e.g., because they might think them risky) or where you don't want to have to be constrained by the other DB's maintenance schedules (e.g., for updating your schema elements).
snuxoll 9y ago
I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being 32-bits severely limits the usefulness of the feature without using convoluted workarounds (multiple databases).
- anarazel 9y ago
  Postgres dev here. > but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. What sizes of files and such are you interested in? What kind of read/write patterns? I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches. It'd be cool if you could comment on the pgsql-hackers list.
  - snuxoll 9y ago
    Small files, 25-50K on average (images of patient charts), and they're all they're all write-once read-many.
    - anarazel 9y ago
      Hm, in that case, what stops you from just using a bytea column?
      - snuxoll 9y ago
        My major issue with bytea is two fold, the same oid issue that you have with LOB's (we have 10's of billions of pages) so you're forced to less than optimal solutions like table inheritance for large numbers of records, plus it severely bloats the size of the heap file making a VACCUM FULL take a century if needed.
jakobegger 9y ago
The biggest news for me is ICU support for collations (text sorting). Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt). Now, you can optionally use ICU for collations, which gives you reliable, versioned collations. This is a big step forward! ICU collations are not the default, you need to add them with CREATE COLLATION. You have a lot more collations available to choose from, but I think it's not yet possible to change any of the advanced settings that ICU provides. (Also, when I tried it, it seems that the ICU collations are case insensitive -- but I think case insensitive collations aren't fully supported yet.)
- petereisentraut 9y ago
  ICU collations are prepopulated; see <https://www.postgresql.org/docs/devel/static/collation.html#collation-managing> https://www.postgresql.org/docs/devel/static/collation.html#.... Also, ICU collations are case sensitive, just like libc locales.
  - anarazel 9y ago
    s/insensitive/sensitive/?
    - petereisentraut 9y ago
      fixed, thanks
  - jakobegger 9y ago
    Hm, I didn't get it to work with the prepopulated collations - I'll have to try again tomorrow when I'm back at my development machine. Anyway, thanks a lot for this patch! It looks like it was a lot of work, and I'm very happy this made it into PostgreSQL 10.
- wmu 9y ago
  There's a wiki page: https://wiki.postgresql.org/wiki/Todo:ICU https://wiki.postgresql.org/wiki/Todo:ICU
jakobegger 9y ago
If you're looking for a quick way to try PostgreSQL 10 on a Macbook, we made a special build of Postgres.app that includes PostgreSQL 10beta1. You can download it from Github: https://github.com/PostgresApp/PostgresApp/releases https://github.com/PostgresApp/PostgresApp/releases
- petereisentraut 9y ago
  Or if you use Homebrew you might like: brew install --devel petere/postgresql/postgresql@10
gigatexal 9y ago
unrelated to this particular release, but since we're talking about postgres... how do people find the ability to write stored procedures and functions in languages other than just SQL? I'm coming from a MSSQL shop and curious how writing a query in python for example has benefited anyone if at all -- did you / can you use modules like numpy or pandas in a postgres python procedure?
- zie 9y ago
  Python under PG can do anything python can do.. but that said, just because you CAN import numpy/pandas and go to town, doesn't mean you SHOULD. In fact you probably really shouldn't :) That said, we use python inside of PG, and it's awesome. It's not really any more powerful than PG/SQL(their built-in language) i.e. in terms of what you can do in the DB itself. Our main app is also python, so we have a continuity of language from front-end to back-end. We do have some DB functions that take advantage of python's flexibilities, but like I said, we try to keep external modules and craziness out of the PG backend if possible. Sometimes there is no help for it, and you do what you must, and it's very handy that you can just go do it.
  - gigatexal 9y ago
    Sometimes I'd rather do python magic than self join correlated sub-queries or write window functions and have it all become a convoluted mess. But I see your point. edit: i'm still trying to train myself to think in a relational way.
    - zie 9y ago
      relations are awesome, but it def. takes training! I'd suggest avoiding window functions for pagination.. see http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset as one method.
combatentropy 9y ago
So it looks like PostgreSQL will finally be web scale ;) (ducks and runs)
- deadliftpro 9y ago
  That, i suppose, was meant to be a joke. see https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs and just replace mysql with pg.
  - combatentropy 9y ago
    Yes, it was a reference to that. My comment is currently sitting at -2. I'm still getting the hang of Hacker News' sense of humor. On Slashdot, I would be at +5. Ho hum. Anyway, just to make my intentions clear, I love Postgres, have used it for over a decade, and have yet to use MongoDB or any other NoSQL database.
    - grzm 9y ago
      In general, strive for substantive and constructive comments on HN. If you've got that, a little humor added in can be appreciated. Comments that are submitted only for humor value (which you knew yours was, given your parenthetical addendum) are likely to be less appreciated on HN than on other sites. Here's a recent thread where this has been discussed: https://news.ycombinator.com/item?id=13760333 https://news.ycombinator.com/item?id=13760333 There are likely others that describe it better, but I don't have them at hand. Edit to add: Of course there are. Here's some comments by 'dang on the topic: https://hn.algolia.com/?query=author:dang%20humor&sort=byPopularity&prefix&page=0&dateRange=all&type=comment https://hn.algolia.com/?query=author:dang%20humor&sort=byPop...
      - combatentropy 9y ago
        So what I want to know is how the members of Hacker News act as one. Presumably these are the same people who appreciate humor on other sites, upvote it, and participate in it. But on Hacker News they somehow all know to downvote it. It's eerie.
alixaxel 9y ago
Wasn't primary-primary replication from 2ndQuadrant BDR tool supposed to make GA with PostgreSQL 10?
- petereisentraut 9y ago
  The new logical replication feature is based on the experiences from BDR and pglogical, but it isn't multimaster yet.
noobsquared 9y ago
What is a good resource for learning databases in general (besides just queries) and then focus on PostgreSql?
- pgaddict 9y ago
  So you want something that covers all types of database systems? I don't think any such book exists, but maybe look at the database-oriented courses at CMU, Berkeley, Stanford, MIT ... Those are typically comprehensive and include links to source books/papers. In particular, look at Joseph M. Hellerstein at CMU: db.cs.berkeley.edu/jmh/ (doesn't load for me at the moment, not sure what's wrong). Also, the Redbook (prepared by Hellerstein, Bailis and Stonebraker) is great: http://www.redbook.io/ http://www.redbook.io/ but it's more an update on various topics than introduction.

507 points by ahachete 9y ago

Important features: Native Table Partitioning - https://www.keithf4.com/postgresql-10-built-in-partitioning/ https://www.keithf4.com/postgresql-10-built-in-partitioning/ Logical Replication - https://blog.2ndquadrant.com/logical-replication-postgresql-10/ https://blog.2ndquadrant.com/logical-replication-postgresql-... or what is new in PG 10 https://wiki.postgresql.org/wiki/New_in_postgres_10 https://wiki.postgresql.org/wiki/New_in_postgres_10 Postgresql really awesome!

jph 9y ago

Congratulations to the team. The replication/partition improvements are significant and much appreciated. My favorite improvements are full text search of JSON & JSONB; this makes pg a full replacement for Mongo for my use cases.

zachmax 9y ago
I feel like this feature replaces almost all of Mongo's use cases!
- htsh 9y ago
  I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?
  - byefruit 9y ago
    At my previous company we made heavy use of its lossy compression feature.
    - deleted 9y ago
      [deleted]
    - X86BSD 9y ago
      You would get compression with Postgres running on ZFS.
      - kinghajj 9y ago
        Lossy compression, not lossless.
      - crypto5 9y ago
        In random cloud provider you may not get FS with compression on your machine..
      - sqeaky 9y ago
        Database plus Copy-On-Write file systems sound like a bad idea. I am imagining a modest 100gb database being re-written for every change. I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
        X86BSD 9y ago
        You might be interested in reading this paper: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
        sqeaky 9y ago
        I am interested and I appreciate the thought and link, however these appear to be the slides to a talk without the actual talk. If so they are of limited use because the slides never have all the information the presenter has, and that information is often just a summary. Is this the correct talk: https://youtu.be/dwMQXLOXUco?t=5380 https://youtu.be/dwMQXLOXUco?t=5380 ?
        X86BSD 9y ago
        Yes, sorry about that, that is the corresponding talk to the slides. Thanks for pointing that out.
    - pkhagah 9y ago
      What lossy compression? Were you guys throwing bits into /dev/null?
    - infogulch 9y ago
      Is that related to the cool "hash compression" technology I hear about? Apparently it can compress an arbitrarily large file into just a few bytes, amazing!
  - fnayr 9y ago
    I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much better than if I was forced to make a bunch of tables. This aides in understanding the representation of our game's data, and I believe (but haven't tested) it will be faster than a relational db for my use case, but that's an ancillary benefit anyway.
    - ssijak 9y ago
      But the posters above you said that JSON and JSONB types in Postgres, and functionality around them, eliminated the need to use other databases for document type data. What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.
      - fnayr 9y ago
        My claim wasn't that it didn't fulfill their needs, it was that it doesn't fulfill all needs (gamedev is one example that I'm familiar with). Postgres storing JSON types != All mongo functionality I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
        jerrysievert 9y ago
        https://www.github.com/JerrySievert/MongoLike https://www.github.com/JerrySievert/MongoLike that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
        threeseed 9y ago
- jonknee 9y ago
  As soon as Postgres adds the "random data loss" feature they will have a full superset of Mongo.
  - colanderman 9y ago
    You can always turn off fsyncs…
    - knodi 9y ago
      Then you might as well use pg.
      - lima 9y ago
        I think he meant that you can turn off fsyncs in pg in order to add random data loss :-)
        X86BSD 9y ago
        You might be interested in this paper where the author touches on fsync. https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
Thaxll 9y ago
Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?
- gshulegaard 9y ago
  I would say that it isn't configured to scale like Mongo out of the box...but that doesn't mean it can't. You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there. You can also roll your own (if you really want)...and it is relatively approachable to do so using built-in features like partitioning. And, of course, with the 10 Beta it would seem that logical replication is being brought into core which sets the foundation for future replication features such as BDR to also be brought into core. I would also point out that since Mongo's BI connector fiasco, it would seem more and more Mongo users are finding more reasons to just use Postgres (where relational interfaces are desirable for BI): https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...
  - threeseed 9y ago
    None of what you posted is built in and thus supported by the vendor. That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products. The point still remains that after all these year PostgreSQL's scalability story is still a mess.
    - gshulegaard 9y ago
      I think you might be confused somewhat...PostgreSQL doesn't have "a vendor". The history of Postgres starts at UC Berkeley and now has the PostgreSQL Global Development Group which is a mixture of contributors both community and corporate sponsored: https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/ Of that group, both 2nd Quadrant and CitusDB are represented...so in a way you could say their support is "by the vendor". Not to mention EnterpriseDB which also has support options. > we can't use CitusDB when it is not supported by other vendors for use with their products. CitusDB is no longer a fork, it's an extension...this means so long as your other vendors products support Postgres, they support CitusDB. More over, CitusDB itself will sell you an Enterprise package. > The point still remains that after all these year PostgreSQL's scalability story is still a mess. If by mess you mean specifically there is no knob and dial arrangement in the core of Postgres I would agree. But within the core of PostgreSQL there are primitives which make scaling approachable (I myself am working on a data ingestion process that utilizes table partitioning and hand rolled sharding for PostgreSQL). And there are now a plethora of extensions and tools provided by core contributors such as 2nd Quadrant and CitusDB to offer somewhat out of the box solutions and they even come with support. PostgreSQL is not the right tool for every job and the replication/clustering area has been a a sore spot for Postgres in the past. But it certainly isn't bereft of options now...and the inclusion of logical replication in this beta is only the first step in bringing these options closer/into the core.
_Codemonkeyism 9y ago
Using MySQL/Mongo for some years, major problems with MongoDB Cloud Manager (several hours site down due to Cloud Manager removing the mongo binary), for some time now I use pg for new projects and think it's great. Two gripes: 1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields) 2. Why is the Postgres JSON syntax so different? Why not just support SELECT document.field.field instead of (inconsistent) document->'field'. Imho pg JSON syntax is hard to read and new to learn.
- luhn 9y ago
  Re 2: That syntax is already in use by `SELECT schema.column`. I do agree that the syntax is a bit cumbersome and harder to learn, but I'm not sure if they could have done much better while being consistent with SQL.
  - _Codemonkeyism 9y ago
    Why can't pg same the same syntax? I'm sure it could detect if it's a table column or a document field - or am I missing something? Why not handle documents and columns the same, with documents a a kind of hierarchical columns.

zitterbewegung 9y ago

The new features related to clustering looks exciting. Also SCRAM support and Full text search for JSON and JSONB . Postgres is cementing itself as not only the safe choice for Databases but also the Swiss Army knife .

alfalfasprout 9y ago

The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases. Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...

jdubs 9y ago
Every time I see a job post mentioning mysql I realize they just haven't discovered postgres, or they have some really gross problem. :/
- ubertaco 9y ago
  Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'
  - synotna 9y ago
    WHERE email ILIKE 'foo@example.com' https://www.postgresql.org/docs/9.6/static/functions-matching.html https://www.postgresql.org/docs/9.6/static/functions-matchin...
  - fphilipe 9y ago
    Does MySQL do that on varchar by default? Can't you just do this in PostgreSQL? SELECT id FROM users WHERE email = lower('Foo@example.com')
  - sametmax 9y ago
    Just lowercase everything. Not that hard.
    - irrational 9y ago

cygned 9y ago

I wished they would implement more from SQL:2011. I have a lot of applications that would benefit from system versioned tables. This is a good sum up of useful modern SQL features: https://www.slideshare.net/MarkusWinand/modern-sql https://www.slideshare.net/MarkusWinand/modern-sql

Tostino 9y ago
Get involved in the mailing list and start discussion on these features and how they would help you.
- cygned 9y ago
  I fear I cannot contribute a lot in my extremely limited time. Is there a 'getting started' guide somewhere?
  - Tostino 9y ago
    Honestly you don't even have to submit a single patch to contribute. Here is the developer's FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ https://wiki.postgresql.org/wiki/Developer_FAQ I know the feeling of having extremely limited time, i'm in the same boat. But I use Postgres every day with my startup, and we lean very heavy on the database (not by volume of data, just by complexity of the problems). I've reviewed a few patches that I wanted to see get into Postgres, and contributed to discussions on the mailing list. That's about the extent I am able to do at the moment, but it's better than nothing. More reviewers are never a bad thing.
saurik 9y ago
I was expecting that presentation to be stuff you were missing, but the presentation is saying that essentially every feature they describe is implemented in PostgreSQL. Of the SQL:2011 ones, only one wasn't (temporal tables), and even the SQL:2016 features had partial support (with the summary slide at the end of other features they didn't do in detail having stuff that looks familiar in a PostgreSQL context). Do you have a reference of features not in PostgreSQL you are hoping to use?

qeternity 9y ago

As someone with little to no Postgres experience, it seems like they are heading in the direction of providing the type of massively parallel, scale out features that Citus provides. Would love to hear thoughts from someone with real expertise.

dhd415 9y ago
Citus is building functionality on PostgreSQL to provide sharding for data sets too large for a single machine. There aren't really any PG 10 features that duplicate that. Some of the new PG features such as increased query parallelism allow for better utilization of single-machine resources. Other features such as logical replication may allow for some horizontal scaling by splitting read workloads across replicas, but none of them provide any sharding capabilities.
- bjt 9y ago
  Aggregate push down to foreign servers seems to overlap with Citus. It's not as transparent, but you could have a dataset sharded across several PG instances, mount them all into a master with foreign data wrappers, and UNION them (for some queries) to aggregate across the set.
  - amitlan 9y ago
    BTW, you can also create partitions as foreign tables: https://www.postgresql.org/docs/10.0/static/sql-createforeigntable.html https://www.postgresql.org/docs/10.0/static/sql-createforeig...
- qeternity 9y ago
  Many of them seem like pre-requisites however. I understand what Citus does vs Postgres. But that gap is narrowing.
mslot 9y ago
Biased view of a Citus engineer here :) Any enhancement to PostgreSQL is also an enhancement to Citus, or rather, the PostgreSQL ecosystem as a whole. For example, PostgreSQL 10's declarative partitioning feature will help enable sharding+partitioning in Citus, which is one of the most frequently requested features. PostgreSQL 10 also gives you the possibility of setting up a partitioned table in which the partitions are postgres_fdw tables, which allows a basic form of manual sharding without Citus. However, as we've learned over the years, there's a huge difference between the ability to distribute a table across multiple servers and addressing a use case. A sweet spot for Citus is multi-tenant (SaaS) workloads, in which all queries and transactions are specific to a particular tenant. In that case, you can typically distribute most of your tables by tenant ID, and use (replicated) reference tables for data that is shared across tenants. Citus makes sure that data for the same tenant is automatically co-located and as long as your query filters by a particular tenant and joins by tenant, you get full SQL pushdown (with parallelism in PG10), and ACID transactions. At the same time, you can perform parallel DDL commands across all tenants to enable migrations, bulk load data through COPY, perform parallel rollups or transformations through INSERT..SELECT, and run parallel analytical queries. Overall, the combination of these features and the trade-offs that Citus makes ensure that if you need to scale out a multi-tenant app, sharding through Citus solves it. In many cases, the only changes you need to make in your app are adding a tenant_id column to your tables [1], being explicit about the tenant in your queries or ORM [2], and adding create_distributed_table calls. None of the Citus features that allow you to scale out a multi-tenant app are available if you do sharding through partitioning+postgres_fdw so far. I also wouldn't expect core postgres to make aggressive trade-offs to optimise for specific use cases. The PostgreSQL way is to make everything pluggable and let extensions specialise. [1] https://www.citusdata.com/blog/2016/08/10/sharding-for-a-multi-tenant-app-with-postgres/ https://www.citusdata.com/blog/2016/08/10/sharding-for-a-mul... [2] https://www.citusdata.com/blog/2017/01/05/easily-scale-out-multi-tenant-apps/ https://www.citusdata.com/blog/2017/01/05/easily-scale-out-m...

milquetoastaf 9y ago

Postgres is awesome and thankfully is much easier to get set up with high availability and replication baked-in (remember pgpool anyone?). AWS Cloud Formation Docker Swarm stack + postgres primary/replica compose file + datadog and you're good to go.

pilif 9y ago

While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-statistics-examples.html https://www.postgresql.org/docs/10.0/static/multivariate-sta...) is the one single knob I wanted to be able to turn so many times in the past now (especially the n-distinct counts). Most of the time, the planner does an ok job, but sometimes you have tables in a peculiar shape and if you're unlucky, the planner will opt into horribly bad plans. Whenever this happened for me, it was due to one of the two things CREATE STATISTICS allows me to tune in the future. Thank you, thank you, thank you to whoever gave us this wonderful feature

colanderman 9y ago
Has anything actually changed with the ROW syntax for UPDATE? Maybe I'm missing something but it looks like the functionality has always been there, but now you're allowed to type the word "ROW". E.g. compare [1] and [2]. [1] https://www.postgresql.org/docs/9.6/static/sql-update.html https://www.postgresql.org/docs/9.6/static/sql-update.html [2] https://www.postgresql.org/docs/10.0/static/sql-update.html https://www.postgresql.org/docs/10.0/static/sql-update.html
- deleted 9y ago
  [deleted]
walrus 9y ago
What's the significance of the `update .. set (..) = row (..) ..` syntax? update comment set modified = now(), body = 'edited comment' where id = 123; vs update comment set (modified, body) = row (now(), 'edited comment') where id = 123; It doesn't seem to provide any new functionality, just a minor difference in syntax.
- colanderman 9y ago
  EDIT: Turns out the below is not true, though you can achieve the same effect with the sub-select syntax. The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.

ilanco 9y ago

PostgreSQL is an amazing project. A no-nonsense database that delivers what it promises. I'm amazed at what a talented group of people can accomplish when they are driven and put their mind to it. Thanks for a wonderful product.

_jal 9y ago
Absolutely agreed. A friend of mine was doing an MBA and was planning a paper on open source collaboration models. She asked me for my opinion on well-run projects and Postgres was the first thing that crossed my mind. They have an excellent combination of goals, structure and expertise, and have created a remarkably low-drama, functional community around delivering excellent tools. I really think there's a lot to emulate there.
- wmu 9y ago
  My short story regarding community. A few years ago I wanted to add a feature to PL/pgSQL that was on their TODO list. I asked about the feature on a public list and while I was unknown to the community I immediately got a private email from a developer who contributed to that part of code. We discussed the required changes and I prepared a patch. He reviewed it and later was supporting during formal review.
irrational 9y ago
Plus their documentation is top notch. After using Oracle for 15 years, I nearly cried when I moved to Postgres recently and saw how wonderful their documentation is.
- swasheck 9y ago
  Completely agree! I've often said that I didn't learn English grammar until I studied German, I didn't understand Linux until I read the Arch documentation, and I didn't comprehend relational data until I read the Postgres documentation.

Tostino 9y ago

So glad that GiST indexes now support UUID and ENUM data types. That was a big wart for me due to needing exclusion constraints.

15155 9y ago
I always just cast to text in this scenario.
- Tostino 9y ago
  The issue with that is performance and index size. At first that was my solution, but as more data got into the tables that just wasn't working. For UUID's I had to move to casting to bytea using the uuid_send(uuid) function. For enums, I had to create a function to cast an enum to a unique integer, and used that in my exclusion constraints. It's hacky as hell and not something I like, but it's worked for the past 3 years. I'll be really really happy to get rid of those hacks come PG 10 though.
pjungwir 9y ago
I wrote the initial patch for the UUID support! It's my only Postgres contribution so far (except for docs), and I really only write C a few times a year. I made a few revisions but as I fell behind others picked it up and got it over the finish line. I'm pretty proud to say I participated, and maybe others can be encouraged to get involved too. Most of my day I write Ruby and Javascript. :-)

crgwbr 9y ago

Will having logical replication make doing a DB version upgrade in production easier? We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.

webrobots 9y ago
Also interested in this topic. Upgrade with downtime is kind of crappy option for us.
anarazel 9y ago
> Will having logical replication make doing a DB version upgrade in production easier? Yes, that's one of the major goals. > We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime. Unfortunately on RDS your options are a bit more limited than on plain postgres. Otherwise you could use londiste, pglogical, .. to keep the time to switch over to something very small.
endymi0n 9y ago
I guess I remember 9.3 -> 9.4 on RDS sucked big time for us (no migration option whatsoever except pg_dump/pg_restore), but since 9.4 they had it pretty much nailed. Update worked flawlessly during the small failover maintenance window you have every now and then anyway. (You do run a HA setup, right?) YMMV of course, better have your fingers crossed. If you don't trust it, do it the hard way: Switch to write-only mode (easiest: remove write rights for all DB users), notify your users, make a snapshot with pg_dump, restore to a fresh new DB copy and switch your services over. Worked for us, different story if you can't take any write downtime of course.
deleted 9y ago
[deleted]
cryptonector 9y ago
The most important (to me) thing about logical replication is that with it I can have additional schema elements in replicas. This is important for some use cases where one uses another group's DB and needs to add schema elements that the other DB's admins do not want to host (e.g., because they might think them risky) or where you don't want to have to be constrained by the other DB's maintenance schedules (e.g., for updating your schema elements).

snuxoll 9y ago

I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being 32-bits severely limits the usefulness of the feature without using convoluted workarounds (multiple databases).

anarazel 9y ago
Postgres dev here. > but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. What sizes of files and such are you interested in? What kind of read/write patterns? I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches. It'd be cool if you could comment on the pgsql-hackers list.
- snuxoll 9y ago
  Small files, 25-50K on average (images of patient charts), and they're all they're all write-once read-many.
  - anarazel 9y ago
    Hm, in that case, what stops you from just using a bytea column?
    - snuxoll 9y ago
      My major issue with bytea is two fold, the same oid issue that you have with LOB's (we have 10's of billions of pages) so you're forced to less than optimal solutions like table inheritance for large numbers of records, plus it severely bloats the size of the heap file making a VACCUM FULL take a century if needed.

jakobegger 9y ago

The biggest news for me is ICU support for collations (text sorting). Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt). Now, you can optionally use ICU for collations, which gives you reliable, versioned collations. This is a big step forward! ICU collations are not the default, you need to add them with CREATE COLLATION. You have a lot more collations available to choose from, but I think it's not yet possible to change any of the advanced settings that ICU provides. (Also, when I tried it, it seems that the ICU collations are case insensitive -- but I think case insensitive collations aren't fully supported yet.)

petereisentraut 9y ago
ICU collations are prepopulated; see <https://www.postgresql.org/docs/devel/static/collation.html#collation-managing> https://www.postgresql.org/docs/devel/static/collation.html#.... Also, ICU collations are case sensitive, just like libc locales.
- anarazel 9y ago
  s/insensitive/sensitive/?
  - petereisentraut 9y ago
    fixed, thanks
- jakobegger 9y ago
  Hm, I didn't get it to work with the prepopulated collations - I'll have to try again tomorrow when I'm back at my development machine. Anyway, thanks a lot for this patch! It looks like it was a lot of work, and I'm very happy this made it into PostgreSQL 10.
wmu 9y ago
There's a wiki page: https://wiki.postgresql.org/wiki/Todo:ICU https://wiki.postgresql.org/wiki/Todo:ICU

jakobegger 9y ago

If you're looking for a quick way to try PostgreSQL 10 on a Macbook, we made a special build of Postgres.app that includes PostgreSQL 10beta1. You can download it from Github: https://github.com/PostgresApp/PostgresApp/releases https://github.com/PostgresApp/PostgresApp/releases

petereisentraut 9y ago
Or if you use Homebrew you might like: brew install --devel petere/postgresql/postgresql@10

gigatexal 9y ago

unrelated to this particular release, but since we're talking about postgres... how do people find the ability to write stored procedures and functions in languages other than just SQL? I'm coming from a MSSQL shop and curious how writing a query in python for example has benefited anyone if at all -- did you / can you use modules like numpy or pandas in a postgres python procedure?

zie 9y ago
Python under PG can do anything python can do.. but that said, just because you CAN import numpy/pandas and go to town, doesn't mean you SHOULD. In fact you probably really shouldn't :) That said, we use python inside of PG, and it's awesome. It's not really any more powerful than PG/SQL(their built-in language) i.e. in terms of what you can do in the DB itself. Our main app is also python, so we have a continuity of language from front-end to back-end. We do have some DB functions that take advantage of python's flexibilities, but like I said, we try to keep external modules and craziness out of the PG backend if possible. Sometimes there is no help for it, and you do what you must, and it's very handy that you can just go do it.
- gigatexal 9y ago
  Sometimes I'd rather do python magic than self join correlated sub-queries or write window functions and have it all become a convoluted mess. But I see your point. edit: i'm still trying to train myself to think in a relational way.
  - zie 9y ago
    relations are awesome, but it def. takes training! I'd suggest avoiding window functions for pagination.. see http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset as one method.

combatentropy 9y ago

So it looks like PostgreSQL will finally be web scale ;) (ducks and runs)

deadliftpro 9y ago
That, i suppose, was meant to be a joke. see https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs and just replace mysql with pg.
- combatentropy 9y ago
  Yes, it was a reference to that. My comment is currently sitting at -2. I'm still getting the hang of Hacker News' sense of humor. On Slashdot, I would be at +5. Ho hum. Anyway, just to make my intentions clear, I love Postgres, have used it for over a decade, and have yet to use MongoDB or any other NoSQL database.
  - grzm 9y ago
    In general, strive for substantive and constructive comments on HN. If you've got that, a little humor added in can be appreciated. Comments that are submitted only for humor value (which you knew yours was, given your parenthetical addendum) are likely to be less appreciated on HN than on other sites. Here's a recent thread where this has been discussed: https://news.ycombinator.com/item?id=13760333 https://news.ycombinator.com/item?id=13760333 There are likely others that describe it better, but I don't have them at hand. Edit to add: Of course there are. Here's some comments by 'dang on the topic: https://hn.algolia.com/?query=author:dang%20humor&sort=byPopularity&prefix&page=0&dateRange=all&type=comment https://hn.algolia.com/?query=author:dang%20humor&sort=byPop...
    - combatentropy 9y ago
      So what I want to know is how the members of Hacker News act as one. Presumably these are the same people who appreciate humor on other sites, upvote it, and participate in it. But on Hacker News they somehow all know to downvote it. It's eerie.

alixaxel 9y ago

Wasn't primary-primary replication from 2ndQuadrant BDR tool supposed to make GA with PostgreSQL 10?

petereisentraut 9y ago
The new logical replication feature is based on the experiences from BDR and pglogical, but it isn't multimaster yet.

noobsquared 9y ago

What is a good resource for learning databases in general (besides just queries) and then focus on PostgreSql?

pgaddict 9y ago
So you want something that covers all types of database systems? I don't think any such book exists, but maybe look at the database-oriented courses at CMU, Berkeley, Stanford, MIT ... Those are typically comprehensive and include links to source books/papers. In particular, look at Joseph M. Hellerstein at CMU: db.cs.berkeley.edu/jmh/ (doesn't load for me at the moment, not sure what's wrong). Also, the Redbook (prepared by Hellerstein, Bailis and Stonebraker) is great: http://www.redbook.io/ http://www.redbook.io/ but it's more an update on various topics than introduction.