21 ms·

New in PostgreSQL 10

962 points by okket 9y ago

TCM 9y ago
Full text search in JSON & JSONB looks exciting. https://wiki.postgresql.org/wiki/New_in_postgres_10#Full_Text_Search_support_for_JSON_and_JSONB https://wiki.postgresql.org/wiki/New_in_postgres_10#Full_Tex...
- godzillabrennus 9y ago
  As someone working with json output from the gmail api I’m curious to see how people smarter than me take advantage of this new functionality so I can adopt it as well.
qume 9y ago
Floating point timestamps - the only item which affects my work is also one of the few in this wiki page with no description. These were removed with no breaking compatibility. For anyone else using them, I just did a bit of reading [1] and it looks like they are inferior to default 8 byte timestamps. FP timestamps have microsecond precision around the year 2000, but it drops off as it moves away from there. I was using them to be the same as some GPS timing code, but it seems this isn't needed or desirable. [1] https://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-DATETIME-TABLE https://www.postgresql.org/docs/9.0/static/datatype-datetime...
- chowells 9y ago
  Yeah.. You should never use floating point when your use case wants uniform precision across the full range. That's the opposite of what floating point gives you.
davedx 9y ago
How does PostgreSQL 10 compare with Cassandra for BigData requirements?
- SEJeff 9y ago
  Define "big data"? You can buy normal tier 1 server vendor hardware with 6T of memory. If it fits into 1 machine or a few, it isn't "big data".
  - deleted 9y ago
    [deleted]
  - willtim 9y ago
    And the postgres maximum table size is 32T.
    - Tostino 9y ago
      You can partition your table if it got anywhere near that large (with the shiny native partitioning introduced in PostgreSQL 10).
    - qaq 9y ago
      You really don't want to have the whole DB be much above 30TB. You'd either want to shard or use some alternative solution.
  - pulam 9y ago
    I've heard this 6TB number thrown around a bit recently, but no one can actually provide me with links to hardware I can buy - has anyone successfully setup a machine with this much ram who can post links to what they used?
    - qaq 9y ago
      http://www3.lenovo.com/us/en/data-center/servers/mission-critical/System-x3950-X6/p/WMD00000002 http://www3.lenovo.com/us/en/data-center/servers/mission-cri...
    - okaram 9y ago
      Don't know about hardware you can buy, but AWS will rent you a machine with 3TB Ram by the hour :) https://aws.amazon.com/ec2/instance-types/ https://aws.amazon.com/ec2/instance-types/
TCM 9y ago
SCRAM authentication looks like a nice security improvement also. No description on the site but http://paquier.xyz/postgresql-2/postgres-10-scram-authentication/ http://paquier.xyz/postgresql-2/postgres-10-scram-authentica... gives a good overview.
- xyzzy_plugh 9y ago
  It's an improvement for sure, but I am curious -- does anyone situate a Postgres instance where it is publicly accessible? Who was asking for this feature?
  - dalyons 9y ago
    Lots of cloud managed dbs are/can be publicly accessible. Eg all heroku hosted Postgres instances
    - merb 9y ago
      are they? even without ssl? by default?
      - pdimitar 9y ago
        Not sure about SSL, but in the past customers of mine have copy-pasted full Heroku PG URLs to me and I was able to get in via `psql` immediately. So yes they're public but their addresses are basically impossible to guess.
        cbowal 9y ago
        Known as "Security through Obscurity" [0] [0] https://en.wikipedia.org/wiki/Security_through_obscurity https://en.wikipedia.org/wiki/Security_through_obscurity
        wongarsu 9y ago
        Debatable. If the address is really unguessable, the address acts like a regular key. It's still not good practice, since most systems treat addresses with far less care than passwords and often save and/or transmit them unencrypted.
gshulegaard 9y ago
Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consider "Big Data". > This means that users no longer need to create triggers for routing data; it's all handled by the system. Trigger routing has always been a performance foot gun...to the point that it's sometimes better to handle table routing in application logic. I wonder what the performance is comparatively between this new "native" partitioning and existing methods (triggers and rules) and whether or not this makes application routing always inferior.
- koolba 9y ago
  Parallel query was already there in 9.6 though this release improves on it further.
  - Tostino 9y ago
    It makes parallel query much more useful. There were very few places that parallel query helped with my workload in 9.6, that is not true for 10.
    - snuxoll 9y ago
      Parallel index scan is going to be huge for me, I’ve got a table with 80 million rows I have to constantly dig through, indexes help a lot but when they get so stinking big being limited to one thread really hampers performance.
      - deleted 9y ago
        [deleted]
- adrianratnapala 9y ago
  I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.
alceta 9y ago
If anyone even remotely involved with the maintenance and development of pg reads this thread - Thank you! - for all your efforts in building and improving a first class product that keeps me amazed at the strides it takes with each major. release.
- kentt 9y ago
  Seconded. Postgres is such a high quality project in so many ways. I'm rarely left wanting when using it, and very often pleasantly surprised.
- jorgeleo 9y ago
  Third!
- jackmott 9y ago
  agreed. after years of having used Oracle and SQL Server at work, to find I could have a working database in about 10 seconds with sudo apt-get install postgresql was amazing to me!
  - manigandham 9y ago
    SQL Server on Linux in a container is just as easy now: docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password' -p 1433:1433 -d microsoft/mssql-server-linux EDIT: Or with apt-get too: sudo apt-get install -y mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#platforms https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...
    - grzm 9y ago
      Let's not let this devolve into a db war. This submission is about PostgreSQL 10. Your parent expresses how easy it is for them to get PostgreSQL up and running in their environment—no comparison saying that others are bad (edit: though they did have some difficulty in the past). It's great that you've got a good solution that works for you! And I'm sure there are other ways of doing this as well for other dbs. If you have experience with PostgreSQL and SQL Server features that you'd like to compare that are relevant to these PostgreSQL release notes, please do share them constructively. I'm sure there are people that would be interested in hearing about them.
maxpert 9y ago
Postgresql keeps getting better and better everytime! It's amazing the type of features you can pull off once you have a solid foundation :) (P.S. Have been using MySQL in past and MySQL now feels like stoneage database)
no1youknowz 9y ago
Does anyone know whatever happened to the columnar storage work that 2nd quadrant posted on their blog: https://blog.2ndquadrant.com/postgresql-10-roadmap/ https://blog.2ndquadrant.com/postgresql-10-roadmap/ Is this essentially a pipe dream?
- petereisentraut 9y ago
  Not a priority for 2Q at the moment. Still general interest in the community, but hard to predict right now.
- heliosAtwork 9y ago
  There seems to be an in progress patch for pluggable storage: https://www.postgresql.org/message-id/CAJrrPGdMu8TzxpRZQKK5EL4795by4WAOka1VbxhCtVx7yJoTsQ%40mail.gmail.com https://www.postgresql.org/message-id/CAJrrPGdMu8TzxpRZQKK5E... The PostgreSQL 11 roadmap also mentions "multi-model" database https://wiki.postgresql.org/wiki/Fujitsu_roadmap#Multi-model_database https://wiki.postgresql.org/wiki/Fujitsu_roadmap#Multi-model...
- kbaker 9y ago
  See this discussion also: https://news.ycombinator.com/item?id=15258166 https://news.ycombinator.com/item?id=15258166
jhoechtl 9y ago
Very exciting. Still badly miss (bi-)temporal tables and native query support [1] without using extensions [2]. [1]https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables https://docs.microsoft.com/en-us/sql/relational-databases/ta... [2]https://pgxn.org/dist/temporal_tables/ https://pgxn.org/dist/temporal_tables/
IMTDb 9y ago
PgSQL more and more looks like the "redis of databases". Whatever the problem, you can almost always find a good reason to use it :) You get great performance for the simple "dumb" use case (relational data in DB term, key/value store in redis case), and lots of awesome additional feature on top of it for more complex situations. I love it.
- skrebbel 9y ago
  > the "redis of databases" That's like calling Swift "the Ruby of programming languages".
  - nat 9y ago
    and it says more about redis and ruby than postgres and swift :p
  - wiz21c 9y ago
    or the vi of emacs ? Or was it the other way around ? :-)
- takeda 9y ago
  > "redis of databases". That's like saying <good truck> is a "Prius of 8 wheelers". That's really weird comparison. Especially that PostgreSQL is more of a database than redis is.
- mmgutz 9y ago
  Maybe you meant the swiss knife of databases.
dream42 9y ago
Nice !
pbnjay 9y ago
> Cross-column Statistics Holy crap I didn't even know this was feasible much less in development! Can't wait to test this out, as significant amount of my data sets have these kinds of relationships. Parallel query will also be great for certain queries I do regularly. Been looking forward to this! Thank you Thank you to all the devs that had a part of this!
- bonesmoses 9y ago
  Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages. https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-column-crusade/ https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-col...
  - ComodoHacker 9y ago
    To be honest, skewed data problem can be addressed with partitioning in many cases. Cross-column statistics is a great feature nevertheless.
    - pgaddict 9y ago
      Yes, but partitioning also has a number of limitations that may be quite undesirable. Like inability to create UNIQUE constraints or primary keys, for example.
      - anarazel 9y ago
        More importantly it doesn't help at all with cross-column correlations. It can help with skew in between various rows though.
- pgaddict 9y ago
  Thanks, I'm happy it got into PG10 too. Just to make it very clear - the current implementation is fairly simple, and may not be smart enough to deal with more complicated dependencies between columns. Hopefully that will be improved in PG11.
swsieber 9y ago
Completely off topic... but did anybody notice how they used partitioning example column names were in Spanish?
gfodor 9y ago
I noticed hash indexes are now crash proof and replicated -- this seems to make them actually usable in production. In other words, this release effectively "adds" a new index type. That seems like a much bigger deal than is being talked about, is there any reason to believe that new databases shouldn't be using hash indexes for columns that won't be supporting range queries? (In other words, pretty much all keys.) I've seen mixed reports about hash indexes being beneficial.
- anarazel 9y ago
  I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support. There's also still some performance kinks to work out with the current hash index performance - large when growing the index quickly or under very high concurrency.
  - Tostino 9y ago
    Do you know how that'd work out for those of us with UUID primary keys rather than integer primary keys?
    - anarazel 9y ago
      Not quite sure I understand exactly what you're getting at? Are you wondering if those constraints are less important because you less frequently order by UUIDs? If so, I don't think it makes a huge difference - merge joins are still relevant, and you'll often ORDER BY anyway just to get stable resultsets. If the question is if there's bigger benefits to hash joins for largely unordered data? Then yes, that's true.
  - brlewis 9y ago
    Stop it! You're giving away all possible answers to one of my favorite interview questions. Edit: not seriously asking you to stop, but the interview question part is true
rb808 9y ago
Shouldn't that be PostgreSQL X? Seriously good work. I'm not sure why my firm still buys Oracle licenses.
- paulryanrogers 9y ago
  Certifications or compliance?
- nine_k 9y ago
  Well, this Postgres release introduces automatic partitioning; in Oracle, it has been available for like 20 years. Also, some shops are heavily invested in PL/SQL code. The price of migration may far outweigh the license savings.
  - wiz21c 9y ago
    >> The price of migration may far outweigh the license savings. definitely. The cost of work is big compared to licensing. Now, PG has a "cool factor" so I bet some people would migrate to it off hours just for fun :-)
  - takeda 9y ago
    My previous company migrated to Postgres by using EnterpriseDB[1]. This allowed them to run existing Oracle data with PL/SQL as they were working on migrating it to the raw PostgreSQL. So where there is a will there's a way. [1] https://www.enterprisedb.com https://www.enterprisedb.com
JunkDNA 9y ago
Would love to know about FDW push down. That could be really good for specialized applications where you want to use a relational db and wrap a specialized data store outside postgres with a FDW.
dantiberian 9y ago
What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.
- aidos 9y ago
  This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs). http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write... Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.
- martin_marques 9y ago
  There is no native solution as there is no unique scenario regarding failover. Some people prefer to do fencing using network tools like firewall rules or managed switches, other just reconfigure their applications connection string to remove the failed node, or even reconfigure connections with a middle-ware like pgbouncer. There surely are other fencing solutions that don't come to my mind now, and others I don't even know of. For failover you can use tools like repmgr which provides automatic failover, or set up your own monitoring which can either trigger an alert to your pager and you run the failover. Or you have it trigger some configuration manager to reset the nodes (I strongly suggest using Ansible ;) ) This is what makes postgres flexible, as it's not tied to one solution.
- ww520 9y ago
  For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast. Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby. Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds. Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover. The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.
wooptoo 9y ago
For anyone using postgres on a daily basis I would highly recommend pgcli. It has a very good auto-complete, and can even suggest JOIN statements (parts of it actually).
- hobolord 9y ago
  do you find that it's significantly slower than just using psql?
  - Manozco 9y ago
    Yes, in many cases (until I stopped using it), it messed up my benchmarks. I was testing some queries, and often it was: - pgserver fast, returns 10000 rows, pgcli takes time to parse -> result: total time 10s - pgserver slow, returns 1 row, pgcli instant -> result: total time 10 sec. As we are working in efficient C/C++, our internal time to parse the query set is closer to psql than pgcli, so we tend to prefer the pgserver fast, but the benchmark using pgcli did not clearly show the best time. Also, when you are testing a query on one machine, you have to carefully monitor the CPU usage to differentiate the CPU pegging by postgres from the one by pgcli. All those issues were gone when I got back to standard psql.
    - KayEss 9y ago
      For bench marking and optimising queries you almost certainly want `EXPLAIN ANALYZE`. That will give you what you want about what Postgres thinks and the time actually taken for the query.
      - Manozco 9y ago
        Yes, sure, but executing the query a couple of times on the command line is also something I do
      - KingMob 9y ago
        `EXPLAIN ANALYZE` - I read that in a Dalek's voice.
dzonga 9y ago
To those working on and contributing to the goat DB PostreSQL thanks a lot
mrfusion 9y ago
What does native partitioning do?
- mihn 9y ago
  "This means that users no longer need to create triggers for routing data; it's all handled by the system. "
- jeltz 9y ago
  It basically just adds a convenient syntax for creating partitions plus adds automatic tuple routing (routing inserts on the partitioned table to the right partition). This is for most users not a huge improvement over using PostgreSQL's table inheritance to implement partitioning, but it is a very important stepping stone for implementing competitive partitioning in PostgreQL. There are lots of promising patches in the PostgreSQL 11 release cycle which improve on the native partitioning, making it useful for more users.
  - mrfusion 9y ago
    Thanks. I guess I meant to ask what a partition is used for? When is it useful to use one and why?
    - jeltz 9y ago
      Oh, you meant partitioning in general. It is useful when you have huge tables with many rows, and works by splitting a table into multiple smaller tables which acts like it was one big table to the outside. Some advantages: - You can partition by for example month and cheaply delete or archive old data by instead of deleting all rows just dropping the entire partition which is much cheaper. - You can have different indexes on different partitions. This is commonly used when some indexes are only used by queries which operate on recent data. - You can put different partitions and their indexes in different table spaces. E.g. you can put your older and less frequently accessed data on cheaper and slower disks. - Random inserts into B-tree indexes are expensive and when you for example have time series data on the format (device_id, timestamp, v1, v2, ...) it can be useful to partition on the device id so all rows are inserted in strictly increasing timestamp order in each partition. This way you can get more write performance out of your hardware. - Most databases have a maximum table size (PostgreSQL's maximum is 32 TB) and you can get around that by splitting your huge table into multiple smaller partitions which all fit in the maximum table size. - The partitioning condition can be used as a very coarse index to entirely skip looking at some of the partitions (even at plan time for some queries). This does not always give you much over if you just had had one big table with one index over all of it, but in some cases this can be a big win. - For PostgreSQL specifically it looks like you can have some of your partitions be foreign tables. Meaning some of your data can be on an entirely different server, not necessarily PostgreSQL or even a relational database system at all. I am not sure how useful this will be in practice. Partitioning has a maintenance overhead and adds a bit of extra work to query execution and planning so it is not always worth it.
mrfusion 9y ago
Can the xml table accept any xml document? What if it's a bunch of unrelated elements? Can you get back to xml from the table?
- petereisentraut 9y ago
  Any XML document that you can address via XPath in a sensible way. If your XML document is a mess, you'll only be able to get out a mess. :) To get XML from a table, functions already existed in previous releases (XMLELEMENT etc.).
gigatexal 9y ago
SQLSERVER admin and dev here: these features look really compelling. Though the best part is a team of devs could run these in production and do some self support and basically pay nothing. Not so in SQLSERVER land.
bratao 9y ago
What I miss the most from Postgree is the compression feature in the same level as RocksDB or Tokudb. The actual compression is very bad compared to them. Do anyone know if they have something planned in this department ?
- pritambaral 9y ago
  Advanced compression of data is really something that should be looked at separately from database design. Look into using ZFS beneath Postgres for some pretty good compression.
developer2 9y ago
> ICU Collation Support This tiny little mention is a massive win! We now have access to the "und-x-icu" collation. As the postgres docs[1] put it: "ICU root collation. Use this to get a reasonable language-agnostic sort order." I've been waiting on this forever. Columns containing strings from any/all languages can now be sorted well enough to use postgres in truly multi-language applications. If your application has users writing in English, German, Russian, Japanese, et al, using the "und-x-icu" collation should make your application much more user-friendly (as opposed to using the en_US or C locale). The ICU root collation is far from a perfect system, but it's the best option available today. I've been tied to MySQL purely for its similarly-purposed utf8mb4_unicode_ci collation. Now I can give postgres another very serious look. [1] https://www.postgresql.org/docs/10/static/collation.html https://www.postgresql.org/docs/10/static/collation.html
hardwaresofton 9y ago
Does anyone have any use cases where PostgreSQL falls down/loses to other DB systems? I know sharding/replication has long been a sticking point, but what else is there? Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?
- dk8996 9y ago
  Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.
  - deepsun 9y ago
    Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.
    - dk8996 9y ago
      UPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.
      - mst 9y ago
        MVCC is almost always a feature for my workloads but it's well remembering it's a trade-off. Thanks for reminding HN of this point.
      - eloff 9y ago
        MyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.
xfalcox 9y ago
What happens if I try to insert into a master partition table, and there are no child tables that can store my data? For example, data is partitioned by month, and I have no table for the month in the data I'm trying to insert.
- jeltz 9y ago
  It will throw an error. Partitioning support in PostgreSQL 10 only solves a few simple use cases well, but the next version should get much more generally useful partitioning. This is how PostgreSQL usually releases major features.
GoodbyeEarl 9y ago
Hey, HN! Would you recommend me a practical introduction to data modelling with Postres?
- mst 9y ago
  Learn normalisation from wikipedia, then read "The Art of SQL" to get a feeling for the shape of the weirdnesses, then read the postgresql documentation from end to end. (disclaimer: worked for me, may not for anybody else)
  - 3131s 9y ago
    I intend to read the PostgreSQL docs end to end at some point, but last time I checked the full PDF comes out to something like 3600 pages! I am astounded by the quality of everything about PostgreSQL, the community included.
mcdoh 9y ago
Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.
- phaedryx 9y ago
  I'd start with the docs: https://www.postgresql.org/docs/ https://www.postgresql.org/docs/ They are pretty good.
- grzm 9y ago
  You'll likely find the PostgreSQL docs[0] very useful. They're organized differently than the MySQL reference making it easier (in my experience) for reading stand-alone.† They're quite well-written and up-to-date. [0]: https://www.postgresql.org/docs/ https://www.postgresql.org/docs/ † The MySQL reference is very useful particularly when you're looking to compare feature differences between versions.
- kawera 9y ago
  You may like PostgreSQL Exercises: https://www.pgexercises.com/ https://www.pgexercises.com/
ianamartin 9y ago
Postgres isn't just my favorite database. Postgres is an example for all people about how a project should be run. There are lots of stakeholders who want different things. The dev team looks at what's being asked for and what is reasonable within a certain timeline. And then the team delivers features and supports them. And then on top of that there is best-in-class documentation. Thank you to all of the team members who put your time and effort into this project. It's not only a wonderful tool that I use every day, it's also a model of how to manage a project.
- rc_bhg 9y ago
  "best-in-class documentation" ?? what am I missing. I dread having to lookup how to do things in postgres because their documentation is so horrible to find things in.
  - grzm 9y ago
    Can you provide an example? In my experience I find the index[0] particularly useful, as well as the SQL command reference[1], the datatype reference[2], the function reference[3], and the server configuration section[4]. I know other people have different usage patterns and different amounts of familiarity with the docs, so I'm aware that my experience may not match yours. [0]: https://www.postgresql.org/docs/current/static/bookindex.html https://www.postgresql.org/docs/current/static/bookindex.htm... [1]: https://www.postgresql.org/docs/current/static/sql-commands.html https://www.postgresql.org/docs/current/static/sql-commands.... [2]: https://www.postgresql.org/docs/current/static/datatype.html https://www.postgresql.org/docs/current/static/datatype.html [3]: https://www.postgresql.org/docs/current/static/functions.html https://www.postgresql.org/docs/current/static/functions.htm... [4]: https://www.postgresql.org/docs/current/static/runtime-config.html https://www.postgresql.org/docs/current/static/runtime-confi...
deleted 9y ago
[deleted]
odammit 9y ago
Does anyone know if the partitioning can enforce a uniqueness constraint across child tables? I couldn’t tell from the write up if this is one of the things addressed with Native Partitioning
- tomc1985 9y ago
  Seconded, this is a pretty important feature for some use cases
- Tostino 9y ago
  No it can't currently. There has been some discussion on how to do that for pg 11 though.
- martin_marques 9y ago
  No. You need Global indexes for that.
transposed 9y ago
I recently started using PostgreSQL for a project and have been pleasantly surprised on more than one occasion. I also have been hearing more and more people speak highly of it, especially for its security.
jadbox 9y ago
The only thing I'm upset about is the fact I'll need to wait 3-6 months after it's released for AWS RDS to support it...
nevhan 9y ago
How does the native partitioning compare to something like Timescale's hypertables?
collyw 9y ago
Every time I see stuff about Postgress it sounds so much better than MySQL. Every time I get a new job, the existing system is using MySQL.(Every time it causes problems of some sort of other - it's not a bad database, but has too many quirks like JavaScript that will end catching you out at some point). Last time I got to build something new the time pressure was so great I had to use MySQL, because it was there, set up and working and I knew it. I wanted to use Postgres, but at the end of the day the extra time setting it up wouldn't really have been much benefit as it was a fairly standard Django app, and didn't need any of Postgres's extra features. (I quit that job shortly afterwards).

962 points by okket 9y ago

TCM 9y ago

Full text search in JSON & JSONB looks exciting. https://wiki.postgresql.org/wiki/New_in_postgres_10#Full_Text_Search_support_for_JSON_and_JSONB https://wiki.postgresql.org/wiki/New_in_postgres_10#Full_Tex...

godzillabrennus 9y ago
As someone working with json output from the gmail api I’m curious to see how people smarter than me take advantage of this new functionality so I can adopt it as well.

qume 9y ago

Floating point timestamps - the only item which affects my work is also one of the few in this wiki page with no description. These were removed with no breaking compatibility. For anyone else using them, I just did a bit of reading [1] and it looks like they are inferior to default 8 byte timestamps. FP timestamps have microsecond precision around the year 2000, but it drops off as it moves away from there. I was using them to be the same as some GPS timing code, but it seems this isn't needed or desirable. [1] https://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-DATETIME-TABLE https://www.postgresql.org/docs/9.0/static/datatype-datetime...

chowells 9y ago
Yeah.. You should never use floating point when your use case wants uniform precision across the full range. That's the opposite of what floating point gives you.

davedx 9y ago

How does PostgreSQL 10 compare with Cassandra for BigData requirements?

SEJeff 9y ago
Define "big data"? You can buy normal tier 1 server vendor hardware with 6T of memory. If it fits into 1 machine or a few, it isn't "big data".
- deleted 9y ago
  [deleted]
- willtim 9y ago
  And the postgres maximum table size is 32T.
  - Tostino 9y ago
    You can partition your table if it got anywhere near that large (with the shiny native partitioning introduced in PostgreSQL 10).
  - qaq 9y ago
    You really don't want to have the whole DB be much above 30TB. You'd either want to shard or use some alternative solution.
- pulam 9y ago
  I've heard this 6TB number thrown around a bit recently, but no one can actually provide me with links to hardware I can buy - has anyone successfully setup a machine with this much ram who can post links to what they used?
  - qaq 9y ago
    http://www3.lenovo.com/us/en/data-center/servers/mission-critical/System-x3950-X6/p/WMD00000002 http://www3.lenovo.com/us/en/data-center/servers/mission-cri...
  - okaram 9y ago
    Don't know about hardware you can buy, but AWS will rent you a machine with 3TB Ram by the hour :) https://aws.amazon.com/ec2/instance-types/ https://aws.amazon.com/ec2/instance-types/

TCM 9y ago

SCRAM authentication looks like a nice security improvement also. No description on the site but http://paquier.xyz/postgresql-2/postgres-10-scram-authentication/ http://paquier.xyz/postgresql-2/postgres-10-scram-authentica... gives a good overview.

xyzzy_plugh 9y ago
It's an improvement for sure, but I am curious -- does anyone situate a Postgres instance where it is publicly accessible? Who was asking for this feature?
- dalyons 9y ago
  Lots of cloud managed dbs are/can be publicly accessible. Eg all heroku hosted Postgres instances
  - merb 9y ago
    are they? even without ssl? by default?
    - pdimitar 9y ago
      Not sure about SSL, but in the past customers of mine have copy-pasted full Heroku PG URLs to me and I was able to get in via `psql` immediately. So yes they're public but their addresses are basically impossible to guess.
      - cbowal 9y ago
        Known as "Security through Obscurity" [0] [0] https://en.wikipedia.org/wiki/Security_through_obscurity https://en.wikipedia.org/wiki/Security_through_obscurity
        wongarsu 9y ago
        Debatable. If the address is really unguessable, the address acts like a regular key. It's still not good practice, since most systems treat addresses with far less care than passwords and often save and/or transmit them unencrypted.

gshulegaard 9y ago

Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consider "Big Data". > This means that users no longer need to create triggers for routing data; it's all handled by the system. Trigger routing has always been a performance foot gun...to the point that it's sometimes better to handle table routing in application logic. I wonder what the performance is comparatively between this new "native" partitioning and existing methods (triggers and rules) and whether or not this makes application routing always inferior.

koolba 9y ago
Parallel query was already there in 9.6 though this release improves on it further.
- Tostino 9y ago
  It makes parallel query much more useful. There were very few places that parallel query helped with my workload in 9.6, that is not true for 10.
  - snuxoll 9y ago
    Parallel index scan is going to be huge for me, I’ve got a table with 80 million rows I have to constantly dig through, indexes help a lot but when they get so stinking big being limited to one thread really hampers performance.
    - deleted 9y ago
      [deleted]
adrianratnapala 9y ago
I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.

alceta 9y ago

If anyone even remotely involved with the maintenance and development of pg reads this thread - Thank you! - for all your efforts in building and improving a first class product that keeps me amazed at the strides it takes with each major. release.

kentt 9y ago
Seconded. Postgres is such a high quality project in so many ways. I'm rarely left wanting when using it, and very often pleasantly surprised.
jorgeleo 9y ago
Third!
jackmott 9y ago
agreed. after years of having used Oracle and SQL Server at work, to find I could have a working database in about 10 seconds with sudo apt-get install postgresql was amazing to me!
- manigandham 9y ago
  SQL Server on Linux in a container is just as easy now: docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password' -p 1433:1433 -d microsoft/mssql-server-linux EDIT: Or with apt-get too: sudo apt-get install -y mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#platforms https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...
  - grzm 9y ago
    Let's not let this devolve into a db war. This submission is about PostgreSQL 10. Your parent expresses how easy it is for them to get PostgreSQL up and running in their environment—no comparison saying that others are bad (edit: though they did have some difficulty in the past). It's great that you've got a good solution that works for you! And I'm sure there are other ways of doing this as well for other dbs. If you have experience with PostgreSQL and SQL Server features that you'd like to compare that are relevant to these PostgreSQL release notes, please do share them constructively. I'm sure there are people that would be interested in hearing about them.

maxpert 9y ago

Postgresql keeps getting better and better everytime! It's amazing the type of features you can pull off once you have a solid foundation :) (P.S. Have been using MySQL in past and MySQL now feels like stoneage database)

no1youknowz 9y ago

Does anyone know whatever happened to the columnar storage work that 2nd quadrant posted on their blog: https://blog.2ndquadrant.com/postgresql-10-roadmap/ https://blog.2ndquadrant.com/postgresql-10-roadmap/ Is this essentially a pipe dream?

petereisentraut 9y ago
Not a priority for 2Q at the moment. Still general interest in the community, but hard to predict right now.
heliosAtwork 9y ago
There seems to be an in progress patch for pluggable storage: https://www.postgresql.org/message-id/CAJrrPGdMu8TzxpRZQKK5EL4795by4WAOka1VbxhCtVx7yJoTsQ%40mail.gmail.com https://www.postgresql.org/message-id/CAJrrPGdMu8TzxpRZQKK5E... The PostgreSQL 11 roadmap also mentions "multi-model" database https://wiki.postgresql.org/wiki/Fujitsu_roadmap#Multi-model_database https://wiki.postgresql.org/wiki/Fujitsu_roadmap#Multi-model...
kbaker 9y ago
See this discussion also: https://news.ycombinator.com/item?id=15258166 https://news.ycombinator.com/item?id=15258166

jhoechtl 9y ago

Very exciting. Still badly miss (bi-)temporal tables and native query support [1] without using extensions [2]. [1]https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables https://docs.microsoft.com/en-us/sql/relational-databases/ta... [2]https://pgxn.org/dist/temporal_tables/ https://pgxn.org/dist/temporal_tables/

IMTDb 9y ago

PgSQL more and more looks like the "redis of databases". Whatever the problem, you can almost always find a good reason to use it :) You get great performance for the simple "dumb" use case (relational data in DB term, key/value store in redis case), and lots of awesome additional feature on top of it for more complex situations. I love it.

skrebbel 9y ago
> the "redis of databases" That's like calling Swift "the Ruby of programming languages".
- nat 9y ago
  and it says more about redis and ruby than postgres and swift :p
- wiz21c 9y ago
  or the vi of emacs ? Or was it the other way around ? :-)
takeda 9y ago
> "redis of databases". That's like saying <good truck> is a "Prius of 8 wheelers". That's really weird comparison. Especially that PostgreSQL is more of a database than redis is.
mmgutz 9y ago
Maybe you meant the swiss knife of databases.

dream42 9y ago

Nice !

pbnjay 9y ago

> Cross-column Statistics Holy crap I didn't even know this was feasible much less in development! Can't wait to test this out, as significant amount of my data sets have these kinds of relationships. Parallel query will also be great for certain queries I do regularly. Been looking forward to this! Thank you Thank you to all the devs that had a part of this!

bonesmoses 9y ago
Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages. https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-column-crusade/ https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-col...
- ComodoHacker 9y ago
  To be honest, skewed data problem can be addressed with partitioning in many cases. Cross-column statistics is a great feature nevertheless.
  - pgaddict 9y ago
    Yes, but partitioning also has a number of limitations that may be quite undesirable. Like inability to create UNIQUE constraints or primary keys, for example.
    - anarazel 9y ago
      More importantly it doesn't help at all with cross-column correlations. It can help with skew in between various rows though.
pgaddict 9y ago
Thanks, I'm happy it got into PG10 too. Just to make it very clear - the current implementation is fairly simple, and may not be smart enough to deal with more complicated dependencies between columns. Hopefully that will be improved in PG11.

swsieber 9y ago

Completely off topic... but did anybody notice how they used partitioning example column names were in Spanish?

gfodor 9y ago

I noticed hash indexes are now crash proof and replicated -- this seems to make them actually usable in production. In other words, this release effectively "adds" a new index type. That seems like a much bigger deal than is being talked about, is there any reason to believe that new databases shouldn't be using hash indexes for columns that won't be supporting range queries? (In other words, pretty much all keys.) I've seen mixed reports about hash indexes being beneficial.

anarazel 9y ago
I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support. There's also still some performance kinks to work out with the current hash index performance - large when growing the index quickly or under very high concurrency.
- Tostino 9y ago
  Do you know how that'd work out for those of us with UUID primary keys rather than integer primary keys?
  - anarazel 9y ago
    Not quite sure I understand exactly what you're getting at? Are you wondering if those constraints are less important because you less frequently order by UUIDs? If so, I don't think it makes a huge difference - merge joins are still relevant, and you'll often ORDER BY anyway just to get stable resultsets. If the question is if there's bigger benefits to hash joins for largely unordered data? Then yes, that's true.
- brlewis 9y ago
  Stop it! You're giving away all possible answers to one of my favorite interview questions. Edit: not seriously asking you to stop, but the interview question part is true

rb808 9y ago

Shouldn't that be PostgreSQL X? Seriously good work. I'm not sure why my firm still buys Oracle licenses.

paulryanrogers 9y ago
Certifications or compliance?
nine_k 9y ago
Well, this Postgres release introduces automatic partitioning; in Oracle, it has been available for like 20 years. Also, some shops are heavily invested in PL/SQL code. The price of migration may far outweigh the license savings.
- wiz21c 9y ago
  >> The price of migration may far outweigh the license savings. definitely. The cost of work is big compared to licensing. Now, PG has a "cool factor" so I bet some people would migrate to it off hours just for fun :-)
- takeda 9y ago
  My previous company migrated to Postgres by using EnterpriseDB[1]. This allowed them to run existing Oracle data with PL/SQL as they were working on migrating it to the raw PostgreSQL. So where there is a will there's a way. [1] https://www.enterprisedb.com https://www.enterprisedb.com

JunkDNA 9y ago

Would love to know about FDW push down. That could be really good for specialized applications where you want to use a relational db and wrap a specialized data store outside postgres with a FDW.

dantiberian 9y ago

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.

aidos 9y ago
This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs). http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write... Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.
martin_marques 9y ago
There is no native solution as there is no unique scenario regarding failover. Some people prefer to do fencing using network tools like firewall rules or managed switches, other just reconfigure their applications connection string to remove the failed node, or even reconfigure connections with a middle-ware like pgbouncer. There surely are other fencing solutions that don't come to my mind now, and others I don't even know of. For failover you can use tools like repmgr which provides automatic failover, or set up your own monitoring which can either trigger an alert to your pager and you run the failover. Or you have it trigger some configuration manager to reset the nodes (I strongly suggest using Ansible ;) ) This is what makes postgres flexible, as it's not tied to one solution.
ww520 9y ago
For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast. Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby. Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds. Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover. The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.

wooptoo 9y ago

For anyone using postgres on a daily basis I would highly recommend pgcli. It has a very good auto-complete, and can even suggest JOIN statements (parts of it actually).

hobolord 9y ago
do you find that it's significantly slower than just using psql?
- Manozco 9y ago
  Yes, in many cases (until I stopped using it), it messed up my benchmarks. I was testing some queries, and often it was: - pgserver fast, returns 10000 rows, pgcli takes time to parse -> result: total time 10s - pgserver slow, returns 1 row, pgcli instant -> result: total time 10 sec. As we are working in efficient C/C++, our internal time to parse the query set is closer to psql than pgcli, so we tend to prefer the pgserver fast, but the benchmark using pgcli did not clearly show the best time. Also, when you are testing a query on one machine, you have to carefully monitor the CPU usage to differentiate the CPU pegging by postgres from the one by pgcli. All those issues were gone when I got back to standard psql.
  - KayEss 9y ago
    For bench marking and optimising queries you almost certainly want `EXPLAIN ANALYZE`. That will give you what you want about what Postgres thinks and the time actually taken for the query.
    - Manozco 9y ago
      Yes, sure, but executing the query a couple of times on the command line is also something I do
    - KingMob 9y ago
      `EXPLAIN ANALYZE` - I read that in a Dalek's voice.

dzonga 9y ago

To those working on and contributing to the goat DB PostreSQL thanks a lot

mrfusion 9y ago

What does native partitioning do?

mihn 9y ago
"This means that users no longer need to create triggers for routing data; it's all handled by the system. "
jeltz 9y ago
It basically just adds a convenient syntax for creating partitions plus adds automatic tuple routing (routing inserts on the partitioned table to the right partition). This is for most users not a huge improvement over using PostgreSQL's table inheritance to implement partitioning, but it is a very important stepping stone for implementing competitive partitioning in PostgreQL. There are lots of promising patches in the PostgreSQL 11 release cycle which improve on the native partitioning, making it useful for more users.
- mrfusion 9y ago
  Thanks. I guess I meant to ask what a partition is used for? When is it useful to use one and why?
  - jeltz 9y ago
    Oh, you meant partitioning in general. It is useful when you have huge tables with many rows, and works by splitting a table into multiple smaller tables which acts like it was one big table to the outside. Some advantages: - You can partition by for example month and cheaply delete or archive old data by instead of deleting all rows just dropping the entire partition which is much cheaper. - You can have different indexes on different partitions. This is commonly used when some indexes are only used by queries which operate on recent data. - You can put different partitions and their indexes in different table spaces. E.g. you can put your older and less frequently accessed data on cheaper and slower disks. - Random inserts into B-tree indexes are expensive and when you for example have time series data on the format (device_id, timestamp, v1, v2, ...) it can be useful to partition on the device id so all rows are inserted in strictly increasing timestamp order in each partition. This way you can get more write performance out of your hardware. - Most databases have a maximum table size (PostgreSQL's maximum is 32 TB) and you can get around that by splitting your huge table into multiple smaller partitions which all fit in the maximum table size. - The partitioning condition can be used as a very coarse index to entirely skip looking at some of the partitions (even at plan time for some queries). This does not always give you much over if you just had had one big table with one index over all of it, but in some cases this can be a big win. - For PostgreSQL specifically it looks like you can have some of your partitions be foreign tables. Meaning some of your data can be on an entirely different server, not necessarily PostgreSQL or even a relational database system at all. I am not sure how useful this will be in practice. Partitioning has a maintenance overhead and adds a bit of extra work to query execution and planning so it is not always worth it.

mrfusion 9y ago

Can the xml table accept any xml document? What if it's a bunch of unrelated elements? Can you get back to xml from the table?

petereisentraut 9y ago
Any XML document that you can address via XPath in a sensible way. If your XML document is a mess, you'll only be able to get out a mess. :) To get XML from a table, functions already existed in previous releases (XMLELEMENT etc.).

gigatexal 9y ago

SQLSERVER admin and dev here: these features look really compelling. Though the best part is a team of devs could run these in production and do some self support and basically pay nothing. Not so in SQLSERVER land.

bratao 9y ago

What I miss the most from Postgree is the compression feature in the same level as RocksDB or Tokudb. The actual compression is very bad compared to them. Do anyone know if they have something planned in this department ?

pritambaral 9y ago
Advanced compression of data is really something that should be looked at separately from database design. Look into using ZFS beneath Postgres for some pretty good compression.

developer2 9y ago

> ICU Collation Support This tiny little mention is a massive win! We now have access to the "und-x-icu" collation. As the postgres docs[1] put it: "ICU root collation. Use this to get a reasonable language-agnostic sort order." I've been waiting on this forever. Columns containing strings from any/all languages can now be sorted well enough to use postgres in truly multi-language applications. If your application has users writing in English, German, Russian, Japanese, et al, using the "und-x-icu" collation should make your application much more user-friendly (as opposed to using the en_US or C locale). The ICU root collation is far from a perfect system, but it's the best option available today. I've been tied to MySQL purely for its similarly-purposed utf8mb4_unicode_ci collation. Now I can give postgres another very serious look. [1] https://www.postgresql.org/docs/10/static/collation.html https://www.postgresql.org/docs/10/static/collation.html

hardwaresofton 9y ago

Does anyone have any use cases where PostgreSQL falls down/loses to other DB systems? I know sharding/replication has long been a sticking point, but what else is there? Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?

dk8996 9y ago
Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.
- deepsun 9y ago
  Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.
  - dk8996 9y ago
    UPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.
    - mst 9y ago
      MVCC is almost always a feature for my workloads but it's well remembering it's a trade-off. Thanks for reminding HN of this point.
    - eloff 9y ago
      MyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.

xfalcox 9y ago

What happens if I try to insert into a master partition table, and there are no child tables that can store my data? For example, data is partitioned by month, and I have no table for the month in the data I'm trying to insert.

jeltz 9y ago
It will throw an error. Partitioning support in PostgreSQL 10 only solves a few simple use cases well, but the next version should get much more generally useful partitioning. This is how PostgreSQL usually releases major features.

GoodbyeEarl 9y ago

Hey, HN! Would you recommend me a practical introduction to data modelling with Postres?

mst 9y ago
Learn normalisation from wikipedia, then read "The Art of SQL" to get a feeling for the shape of the weirdnesses, then read the postgresql documentation from end to end. (disclaimer: worked for me, may not for anybody else)
- 3131s 9y ago
  I intend to read the PostgreSQL docs end to end at some point, but last time I checked the full PDF comes out to something like 3600 pages! I am astounded by the quality of everything about PostgreSQL, the community included.

mcdoh 9y ago

Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.

phaedryx 9y ago
I'd start with the docs: https://www.postgresql.org/docs/ https://www.postgresql.org/docs/ They are pretty good.
grzm 9y ago
You'll likely find the PostgreSQL docs[0] very useful. They're organized differently than the MySQL reference making it easier (in my experience) for reading stand-alone.† They're quite well-written and up-to-date. [0]: https://www.postgresql.org/docs/ https://www.postgresql.org/docs/ † The MySQL reference is very useful particularly when you're looking to compare feature differences between versions.
kawera 9y ago
You may like PostgreSQL Exercises: https://www.pgexercises.com/ https://www.pgexercises.com/

ianamartin 9y ago

Postgres isn't just my favorite database. Postgres is an example for all people about how a project should be run. There are lots of stakeholders who want different things. The dev team looks at what's being asked for and what is reasonable within a certain timeline. And then the team delivers features and supports them. And then on top of that there is best-in-class documentation. Thank you to all of the team members who put your time and effort into this project. It's not only a wonderful tool that I use every day, it's also a model of how to manage a project.

rc_bhg 9y ago
"best-in-class documentation" ?? what am I missing. I dread having to lookup how to do things in postgres because their documentation is so horrible to find things in.
- grzm 9y ago
  Can you provide an example? In my experience I find the index[0] particularly useful, as well as the SQL command reference[1], the datatype reference[2], the function reference[3], and the server configuration section[4]. I know other people have different usage patterns and different amounts of familiarity with the docs, so I'm aware that my experience may not match yours. [0]: https://www.postgresql.org/docs/current/static/bookindex.html https://www.postgresql.org/docs/current/static/bookindex.htm... [1]: https://www.postgresql.org/docs/current/static/sql-commands.html https://www.postgresql.org/docs/current/static/sql-commands.... [2]: https://www.postgresql.org/docs/current/static/datatype.html https://www.postgresql.org/docs/current/static/datatype.html [3]: https://www.postgresql.org/docs/current/static/functions.html https://www.postgresql.org/docs/current/static/functions.htm... [4]: https://www.postgresql.org/docs/current/static/runtime-config.html https://www.postgresql.org/docs/current/static/runtime-confi...

deleted 9y ago

[deleted]

odammit 9y ago

Does anyone know if the partitioning can enforce a uniqueness constraint across child tables? I couldn’t tell from the write up if this is one of the things addressed with Native Partitioning

tomc1985 9y ago
Seconded, this is a pretty important feature for some use cases
Tostino 9y ago
No it can't currently. There has been some discussion on how to do that for pg 11 though.
martin_marques 9y ago
No. You need Global indexes for that.

transposed 9y ago

I recently started using PostgreSQL for a project and have been pleasantly surprised on more than one occasion. I also have been hearing more and more people speak highly of it, especially for its security.

jadbox 9y ago

The only thing I'm upset about is the fact I'll need to wait 3-6 months after it's released for AWS RDS to support it...

nevhan 9y ago

How does the native partitioning compare to something like Timescale's hypertables?

collyw 9y ago

Every time I see stuff about Postgress it sounds so much better than MySQL. Every time I get a new job, the existing system is using MySQL.(Every time it causes problems of some sort of other - it's not a bad database, but has too many quirks like JavaScript that will end catching you out at some point). Last time I got to build something new the time pressure was so great I had to use MySQL, because it was there, set up and working and I knew it. I wanted to use Postgres, but at the end of the day the extra time setting it up wouldn't really have been much benefit as it was a fairly standard Django app, and didn't need any of Postgres's extra features. (I quit that job shortly afterwards).