19 ms·
PostgreSQL 10 Released
- Tostino 9y agoI'm super excited for the better parallelism, and all the little quality of life improvements (like enums / uuids being able to participate in exclusion constraints). There was an amazing amount of work that has gone into this release. Amazing job everyone who has worked on it.
- brianskarda 9y agoThe wiki entry linked at the bottom of the page has a ton of good information https://wiki.postgresql.org/wiki/New_in_postgres_10 https://wiki.postgresql.org/wiki/New_in_postgres_10
- buro9 9y agoFor someone running a bunch of sites on a single Postgres 9.2... what's the best guide for upgrading?
- Tostino 9y agoCan you afford any down time?
- buro9 9y agoOh yeah, absolutely. I have 100GB of data in Postgres 9.2 on a single machine. I have it backed up to Tarsnap, and a tonne of spare disk space locally, and can make a snapshot of the machine at the beginning. I really need to know: What is the general flow for upgrading... can I go from 9.2 to 10? Or do I need to install and migrate to every intermediate version? If there are multiple approaches to this, which is considered the safest (even if the downtime is a little longer)?
- orf 9y agoI'd go with a pg_dump to a plaintext SQL file, then run `psql < backup.sql` and see what happens. I'd run pg10 concurrently in a docker container or something to try before trying it live. If 10 fails, try 9.6 (docker run postgres:9.6) and do the same, to see if it's an issue exclusively with 10.
- slobotron 9y agoI think pg_restore supports pg_dump custom format (-f) from all the way down to 8.4, that way you can do faster parallel import (pg_restore -j).
- Tostino 9y agoSo you have two easy options. You can do an in-place upgrade with pg_upgrade: https://www.postgresql.org/docs/10/static/pgupgrade.html https://www.postgresql.org/docs/10/static/pgupgrade.html I've personally never used this tool. I normally schedule a maintenance period, and use pg_dump / pg_dumpall: https://www.postgresql.org/docs/10/static/upgrading.html https://www.postgresql.org/docs/10/static/upgrading.html Then I create a new server and install the new version of Postgres, and apply all custom configurations, turn off the database to external access, use pg_dump / pg_dumpall to create logical backups, restore them to the new server, configure a new replica, test my application internally, then when i'm happy i'll turn everything back on for external access.
- buro9 9y agoV. helpful, thank you. Probably going to go the pg_dump route.
- koolba 9y ago> I have 100GB of data in Postgres 9.2 on a single machine. I have it backed up to Tarsnap, and a tonne of spare disk space locally, and can make a snapshot of the machine at the beginning. Are you backing up the database data directory via tarsnap while the database is running? If so then it's possible your backup wouldn't be a consistent snapshot. Snapshotting the data directory is an option, but you'd can't blindly do it without stopping the cluster first. If you haven't already, I'd suggest setting up automated jobs to backup the database via pg_dump for a logical backup. That'd be a fallback in case you run into any issues upgrading the database directly.
- mnw21cam 9y agoNot necessarily true. Postgres is designed to always have a consistent (or rebuildable) on-disc format. That is, at an instant the entire fileset is consistent with itself. The main problem when backing up the entire database directory is that a backup program will read the files over an extended period of time, and you can't guarantee that Postgres won't have changed stuff in-between. The main problem is with the WAL. Everything[1] gets written to the WAL before going to the actual database tables. Stuff that has definitely made it to the actual database tables then drops off the end of the WAL, because Postgres knows that it is safe. However, you can tell Postgres that a backup is in progress[2], and it will stop stuff dropping off the WAL. Then, no matter how many database writes happen, nothing disappears from the database directory[3]. You can take a database backup by just copying the files. Just make sure you tell Postgres when you have finished taking your backup[4], and it will then drop the old WAL entries. Otherwise, the WAL will grow until you run out of disc space. When you restore your backup, Postgres will rerun all the actions recorded in the WAL in the state it was in when the backup program read it. [1] Well, not necessarily everything, but close enough. [2] Using SELECT pg_start_backup('label'); See https://www.postgresql.org/docs/9.1/static/continuous-archiving.html https://www.postgresql.org/docs/9.1/static/continuous-archiv... [3] Well, yes, stuff will change in the database directory, but the WAL says that it is going to change, and that record is still there, so it's all good. [4] Using SELECT pg_stop_backup();
- zie 9y agoAwesomely said. also, tools like pgbarman(from 2ndQuadrant) and other PG backup tools, do all of these steps for you.
- striking 9y agoGive https://blog.2ndquadrant.com/untangling-the-postgresql-upgrade/ https://blog.2ndquadrant.com/untangling-the-postgresql-upgra... a read perhaps.
- buro9 9y agoThanks, that's helpful. For the database size it seems the recommended approach is the pg_dump > pg_restore... with a maintenance window and a new VM holding Postgres 10. This looks pretty safe and I'd be happy following that process, and it would let me practise it a few times first which I like.
- snuxoll 9y agoYou can practice with pg_upgrade as well, backup your PG_DATADIR, restore it on your new machine and run the upgrade. I typically don't bother doing it on a separate VM myself, I just run the upgrade without the --link flag so it copies all the data files and log segments, test and then open it up.
- buro9 9y agoAlso helpful :) Thank you.
- maxpert 9y agoReally love the builtin sharding and parallelism. Would wait a little before using it in a project.
- m12k 9y agoIn chess, the pawns go first
- irishsultan 9y agoNot really, knights can storm ahead.
- Tharkun 9y agoYeah, but the obstactles they're removing are those very same pawns.
- makmanalp 9y agoI think this is a fair assessment, though it's not like the postgres team is known for "move fast and break things" - they do slow and incremental. Plus, in the press release a DBA from Yandex says they're excited about it so we should know soon :-)
- jimktrains2 9y agoSome of the parallelism was released in 9.6, these are additional updated to what can be parallelized.
- netcraft 9y agoCongrats to the team! Cant wait to take the performance improvements and hash indexes for a spin.
- tabeth 9y agoAs someone who's familiar and uses postgres, but not familiar with the more detailed things databases/postgres related where would be a good place to start? I don't know what I don't know and am not familiar with situations in which the new functionality introduced here should be used. TLDR: am database/postgres noob. Help?
- zitterbewegung 9y agoThe manuals are really well written and easy to access. I would start at the release notes from the manual and then look at the corresponding manual pages. https://www.postgresql.org/docs/10/static/release-10.html#idm46046834158304 https://www.postgresql.org/docs/10/static/release-10.html#id... https://www.postgresql.org/docs/ https://www.postgresql.org/docs/
- tibbon 9y agoI find they are well written for some things, but not others. They are good manuals for documentation, but not guides. I was trying to read about certain lock events recently, and understand their impacts, and aside from a single reference in the manually there was absolutely nothing. They tell the what, but rarely the why.
- tarsinge 9y agoNot specific to Postgres but I found that knowing how to write complex and efficient SQL queries can be overlooked but is a very useful skill, and then understanding indexes and query plans allows to drastically improve performances and thus reduce scaling needs Edit: wording
- snuxoll 9y agoUnderstanding your database configuration is important as well. I spent way longer than I'd like to admit trying to improve performance of complex query that would not execute the way I wanted it to no matter what, PostgreSQL absolutely refused to use a calculated index on one table and then do a HASH JOIN to a second, instead devolving into some really hairy execution plan I can't even remember at this point. Turns out, I had done everything right in my index design and query - but due to the sheer volume of data the query planner didn't use the "correct" plan because work_mem was set too low. Always be mindful of how your database is tuned, you could be doing everything right but the query planner will seem to act insane regardless.
- intsunny 9y agoCongrats on releasing logical replication! No more WAL horror stories, or whole "host to host" replication. I suspect many larger shops will wait till PGSQL 10.1 before going whole-hog on this feature, but exciting stuff nonetheless!
- jfbaro 9y agoCongratulations to the PG team! Another great release. Does anyone know when will PostgreSQL 10 be available on public cloud (AWS in particular) as a managed service (RDS)?
- Alex3917 9y agoIt usually takes four or five months, although last time it only took a month or so, so hard to say.
- oskari 9y agoPostgreSQL 10 will be available in Aiven (managed Postgres and other databases in multiple clouds including AWS and GCP, see https://aiven.io https://aiven.io) within a couple of days after we've completed our validation process with the final version.
- ciconia 9y agoA heartfelt thank you to all PostgreSQL contributors for such a wonderful solid, well documented and versatile tool. One of my absolute favorite pieces of software.
- jarym 9y agoPostgres continues to astound me and the dedication of the people working on it is wonderful. Thank you to everyone who ever contributed a line of code or to testing or to documentation. Everything is top-notch!
- thebiglebrewski 9y agoThank you PostgreSQL team you are awesome!!
- jcadam 9y agoHmm... I have some tables partitioned using the popular pg_partman extension. I wonder how that will interact with pg 10's new partitioning functionality. I half expect everything to be borked if I try to upgrade to 10 :/
- scott_karana 9y agoThe new partitioning requires explicit declaration, so your existing triggers and tables shouldn't be affected. https://www.keithf4.com/postgresql-10-built-in-partitioning/ https://www.keithf4.com/postgresql-10-built-in-partitioning/
- jcadam 9y agoGood, that makes it less 'urgent' at least. Though, I really hate the clunky way partitioning works in Postgres<10... I might have to take the time to rework my partitioning at some point.
- deleted 9y ago[deleted]
- coldcode 9y agoI haven't worked with databases in a while, at my employer we are moving to MariaDB (from MySQL) - is there some reason why we wouldn't be considering PostgreSQL? Is there some drawback to P?
- jeffdavis 9y agoTechnology choices are complex, so there are always some plausible reasons. But at this point, I think PostgreSQL should be the default choice for most people for new systems, and then move away if you have some real problem with it. The chance of regret is a lot lower with postgres.
- ngrilly 9y agoI concur. I was hit (again) today by a choice of MySQL made a few years ago: https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-full-text-search-61f644a54dfa https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-....
- deleted 9y ago[deleted]
- fusiongyro 9y agoMigrating to Postgres will certainly be significantly more work than going to MariaDB for you. But I would still recommend you consider it, because Postgres is safer, more powerful and has fewer edge-case behaviors. If your systems are highly-wedded to MySQL's idiosyncrasies, the difference in cost may be too high for you to do it right now, but I would seriously consider it.
- ngrilly 9y agoPostgreSQL is really better at executing arbitrarily complex queries, and the documentation is so much better. PostgreSQL also provides additional features like LISTEN/NOTIFY, Row Level Security, transactional DDL, table functions (like generate_series), numbered query parameters ($1 instead of ?), and many others. In my opinion, the only reasons for choosing MySQL/MariaDB are if you absolutely need clustered indexes (also known as index organized tables — PostgreSQL uses heap organized tables) or if you architecture relies on specific MySQL replication features (for example using Vitess to shard your database).
- softinio 9y agoWish they had called it PostgreSQL X :)
- jeltz 9y agoIt is confusing enough with Postgres-XC and Postgres-XL to have a PostgreSQL X.
- disconnected 9y agoI've used both mysql and postgresql and they worked just fine for my needs. But I have always been curious: how does postgresql (or even mysql) stack up vs proprietary databases like Oracle and Microsoft sql server?
- manigandham 9y agoFor 99% of standard SQL, they all work the same today. The commercial databases are still faster since they have more advanced algorithms and optimizations, as well as better scale out options and tooling - but Postgres is quickly catching up and will be fine for the majority of scenarios. Postgres also has better general usability with robust JSON support, CSV handling, foreign data(base) access, lots of extensions and other features that help make it a powerful data platform. Today the real difference will be for companies that have some combination of existing Oracle/Microsoft tools and services, advanced clustering needs, complex security requirements, or a dependency on the more advanced features like MSSQL's in-memory OLTP.
- zitterbewegung 9y agoThe tools to access Microsoft SQL server are really good since they have Visual Studio based tool. MySQL workbench is almost at that quality but I'm not sure what a Postgres alternative would be.
- manigandham 9y agoDBeaver is highly recommended: https://dbeaver.jkiss.org https://dbeaver.jkiss.org
- LeonM 9y agoBetter link to the official website: https://dbeaver.com/ https://dbeaver.com/
- manigandham 9y agoThat's the enterprise/commercial version which recently has a cost involved. Worth it for the nosql access.
- arrty88 9y agowhat is the best way to shard on postgres?
- zie 9y agoThere are tons of ways to do that, the lazy way would probably be pay someone like Citus (https://www.citusdata.com/product https://www.citusdata.com/product). Alternatively, use a 3rd party tool, do manual sharding, or the PG FDW, or ... the list is pretty endless. Depends a lot on your needs, requirements, how much sharding you have to do, etc. It's not a simple topic. First I'd make sure your DB sizes really NEED that before going down that road, it's a lot of hassle, and I'd personally push for just sizing your DB box up and up and up before going down that road if at all possible. Simple solutions(read: not sharding) usually win, especially when production falls over and you have to prop it back up.
- simonw 9y agoI feel like they’re burying the lede a bit in this one: “PostgreSQL 10 provides better support for parallelized queries by allowing more parts of the query execution process to be parallelized. Improvements include additional types of data scans that are parallelized as well as optimizations when the data is recombined, such as pre-sorting. These enhancements allow results to be returned more quickly.” This sounds HUGE. I want to see detailed benchmarks and examples of the kind of workloads that are faster now.
- riffraff 9y agomaybe the benchmarks are not so impressive and the workloads not super common, so they didn't want to mislead you? :) Anyway, some more info http://rhaas.blogspot.hu/2017/03/parallel-query-v2.html http://rhaas.blogspot.hu/2017/03/parallel-query-v2.html
- simonw 9y agoThanks, that's a really detailed article.
- tomp 9y agoHm... looks like it's mainly focused on distributed stuff. Instead, I would hope they focus on the bare essentials as well. I've recently started working with PostgreSQL and stumbled upon a problem with their UPSERT implementation. Basically, I want to store an id -> name mapping in a table, with the usual characteristics. CREATE TABLE entities (id SERIAL PRIMARY KEY, name VARCHAR(10) UNIQUE NOT NULL); Then, I want to issue a query (or queries) with some entity names that would return their indices, inserting them if necessary. It appears that's impossible to do with PostgreSQL. The closest I can get is this: INSERT INTO entities (name) VALUES ('a'), ('b'), ('c') ON CONFLICT (name) DO NOTHING RETURNING id, name; However, this (1) only returns the newly inserted rows, and (2) it increments the `id` counter even in case of conflicts (i.e. if the name already exists). While (2) is merely annoying, (1) means I have to issue at least one more SELECT query. We like to mock Java, C++ and Go for being stuck in the past, but that's nothing compared to the state of SQL. Sure, there are custom extensions that each database provider implements, but they're often lacking in obvious ways. I really wish there was some significant progress, e.g. TypeSQL or CoffeeSQL or something.
- zimpenfish 9y agoWhy not use a stored procedure?
- fusiongyro 9y agoI'm having trouble imagining why you need this. Often when people come up with "interesting" scenarios involving UPSERT, they are trying to use the database as persistent memory for some algorithm, rather than as a relational database. What are you actually trying to do? Why would it be insufficient to do something like: BEGIN; INSERT INTO entities (name) (VALUES ('a'), ('b'), ('c') EXCEPT SELECT name FROM entities); SELECT id, name FROM entities WHERE name IN ('a', 'b', 'c'); COMMIT;
- kuschku 9y agoYou can even go further. Use WITH select id, name from entities where name in :data AS existing Then return a UNION of existing and (INSERT into entities (name) :data except existing returning id, name)
- swasheck 9y agoExcited for the release. Disappointed that EnterpriseDB is now making me sign up for an account to download (for Windows).
- latch 9y agoI just tried and wasn't forced to sign up. I'd link straight to the exe which doesn't require a token / session as proof, but rather they have the analytics data.
- swasheck 9y agoAh. I generally get the zipped binaries and that's where I'm bumping into the account creation requirement.
- swasheck 9y agoI'm not sure why I was downvoted. Any insight would be helpful (so that I can be a better community member).
- bobjordan 9y agoI will say that after going through their download form and download button, I learned a bit about how not to design a download form and download button.
- thom 9y agoIt's 2017, and despite all this parallel stuff, query planners are still dumb as bricks. Every day I get annoyed at various obvious-to-the-eye WHERE clause pushdown opportunities, or situations where I can manually copy a view to a table, add indexes, and build a query on top of that quicker than letting the database fill up the entire disk with temporary files trying to do it as a subquery. It's utterly maddening if you spend your life on ad-hoc analytics/reporting workloads. I strongly believe there are huge opportunities for AI to come along and, given a logical description of the data and query, do a better job of physically organising and serving the data. I realise some database management systems do this to an extent, but it's pretty weak sauce in my experience.
- jeffdavis 9y agoThere's a fair amount of research in that area and at PGCon there is usually at least one talk about how to make it work in postgres. But it's a ways off and can take many different forms.
- jaltekruse 9y agoIf you want to take part in a community effort to fix this problem, consider contributing the to Apache Calcite project. It has relational algebra that spans across many different databases and dialects, including NoSQL databases like MongoDB and Elasticsearch. I haven't been very involved with the project directly, but I've worked on several projects that heavily depended on it, and have written extensions for those projects.
- xyzzy_plugh 9y agoI took a peek into calcite some time ago and was disappointed. The project seems to be overly bureaucratic, and the code quality quite low. (Side note, I've noticed a steady decline in quality of Apache projects for at least the past decade or so.) I wanted to perform some introspection on queries in-flight, and potentially re-write them, but this seemed like an awful chore with calcite. One, calcite didn't implement my dialect -- it looked non-trivial to add new dialects. Two, going from query to parse tree (with metadata) back to query doesn't seem to be something that's intended to be supported. Calcite is really a replacement frontend with its own SQL dialect that you can tweak the compiled output (SQL, CQL, etc.). It has its own worts and is not generally applicable to other projects. It works well within the Apache ecosystem but I don't envision much adoption outside of it (especially non-Java, like Postgres). If you were writing a Tableau replacement, you might consider using Calcite to generate your queries. I don't see many other use cases.
- med_abidi 9y agoIt's also about time that they upgrade their website. It's so 1999.
- Tostino 9y agoIt works fine for me. If you think it's holding things back, why not collaborate with the community and work on something you feel is better?
- majewsky 9y agoOn the other hand, it's always nice when pages finish loading in under 10 seconds.
- combatentropy 9y agoWhich is why I love it.
- lwansbrough 9y agoNative partitioning, as well as the advancements on the replication and scaling side of things look like good first steps for a distributed SQL system. Can anyone speak to how much closer this brings Postgres to being able to work like Spanner/CockroachDB? Partitioning is great but having to define my own partitions ahead of time isn’t a great solution for us folks who want to be able to spin up new databases on the fly (without the overhead of assigning new ranges for the tables on these new instances.) Obviously CockroachDB has a long way to go before it works as well as a single instance Postgres DB. But how far does Postgres have before it works as well as a multi-instance Cockroach DB?
- qaq 9y agoWhat is the largest production instance of CockroachDB ?
- lwansbrough 9y agoNo idea! It’s still in its infancy as a DB. Functional, but probably not worth the switch, unless you really really don’t want to deal with scaling a DB manually (which is where I’m at.)
- qaq 9y agoRight but to have a meaningful answer to your question one would need to know specifics right? As example a spanner setup that will perform on par with the largest reasonable PG setup you could have would be in 100K-150K/month range
- MycroftH 9y agoI can't talk about the largest production database, but our largest internal cluster we've tested on so far is 128 machines and hit each one with our own continuous load generators and a chaos monkey (who turns on and off machines at random).
- qaq 9y ago
- romanovcode 9y agoHow to properly install it on Xenial? When I go to how-to link[0] it still shows instructions for 9.6. [0] https://www.postgresql.org/download/linux/ubuntu/ https://www.postgresql.org/download/linux/ubuntu/
- anarazel 9y agoYou should be able to install by simply installing 10 instead of 9.6. I just checked, and it's in the repository. I.e. apt-get install postgresql-10 all supported versions can be installed that way.
- romanovcode 9y agoOh, OK. Great! Thank you.
- seaghost 9y agoPostgres is my go to database for any project size.
- LeicaLatte 9y agoI have moved to PostgreSQL in all my production apps and never been happier. Looking forward to migrating to 10 by the year end.
- ropeladder 9y agoJust upgraded and it's been rocky so far. (I'll spare you the upgrade details, just make sure you don't try to upgrade using apt if you initially installed the enterprisedb.com bundle) But now that I've got v10 running I'm unable to do a fairly simple CREATE TABLE command that worked fine in 9.6: it maxes out my RAM and my swap until the db crashes. (which never happened once in 9.6)
- majewsky 9y agoDid you file a bugreport? (This is not directed at you personally, but a lot of people just complain about bugs on public forums like this one without filing a bugreport, thus not giving the developers a chance to address the problem. I'm guilty of this myself way too often.)
- ropeladder 9y agoJust started writing it up! Thanks for the encouragement.
- esseti 9y agoDoes anyone have a tutotrial on how to upgrade the version in Debian? If I install it from the apt then the service remained connected to the 9.6 and not the 10. Plus, I've to migrate all the configs, is there anaything that helps in doing this?