PostgreSQL 10 Released

https://www.postgresql.org/about/news/1786/

by Tostino • 9 years ago

1024 296 9 years ago

Tostino8• 9 years ago

I'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• 9 years ago

The wiki entry linked at the bottom of the page has a ton of good information https://wiki.postgresql.org/wiki/New_in_postgres_10

buro95• 9 years ago

For someone running a bunch of sites on a single Postgres 9.2... what's the best guide for upgrading?

Tostino8• 9 years ago

Can you afford any down time?

buro95• 9 years ago

Oh 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)?

striking• 9 years ago

Give https://blog.2ndquadrant.com/untangling-the-postgresql-upgra... a read perhaps.

buro95• 9 years ago

Thanks, 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.

maxpert• 9 years ago

Really love the builtin sharding and parallelism. Would wait a little before using it in a project.

m12k• 9 years ago

In chess, the pawns go first

irishsultan• 9 years ago

Not really, knights can storm ahead.

Tharkun• 9 years ago

Yeah, but the obstactles they're removing are those very same pawns.

makmanalp• 9 years ago

I 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 :-)

takeda• 9 years ago

In a previous post here or on reddit it was kind of implied that Yandex used MySQL exclusively. Glad to hear that PostgreSQL is also used there.

jimktrains24• 9 years ago

Some of the parallelism was released in 9.6, these are additional updated to what can be parallelized.

jeltz5• 9 years ago

I think the parallelism can be trusted. This is just more work on the infrastructure released in 9.6. Partitioning on the other hand is something which is new in this release.

netcraft• 9 years ago

Congrats to the team! Cant wait to take the performance improvements and hash indexes for a spin.

_m8fo• 9 years ago

As 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?

zitterbewegung2• 9 years ago

The 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#id...

https://www.postgresql.org/docs/

tibbon• 9 years ago

I 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• 9 years ago

Not 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

snuxoll6• 9 years ago

Understanding 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.

mamcx• 9 years ago

This have help me in discover extra powers of sql:

http://modern-sql.com/

And

http://use-the-index-luke.com/

skizm2• 9 years ago

Sort of random/oddly specific usecase, but this article helped me understand a bit more of what happens under the hood with both PostgeSQL and MySQL:

https://eng.uber.com/mysql-migration/

It is a fairly easy read, even for someone (like myself) who has very little knowledge of what's actually going on under the hood of most DBs.

cookiecaper2• 9 years ago

Further reference material:

- PgSQL mailing list thread discussing Uber's post: https://www.postgresql.org/message-id/579795DF.10502%40comma...

- Slides from "A PostgreSQL Response to Uber", a talk that provides the PgSQL-perspective counterarguments to the Uber post: http://thebuild.com/presentations/uber-perconalive-2017.pdf [PDF]

- Previous HN discussion on the above: https://news.ycombinator.com/item?id=14222721

- Slides from "Migrating Uber from MySQL to Postgres" (2013), in which Uber's prior migration _to_ PgSQL is discussed: https://www.yumpu.com/en/document/view/53683323/migrating-ub...

why-el2• 9 years ago

I would suggest the database courses from CMU. Really good stuff and free online.

rbjorklin• 9 years ago

What’s CMU? Do you have a link?

tofflos• 9 years ago

I fully support learning for learning’ sake. But you could also view the new features as solutions to problems. If you don’t feel you’re having problems then you’re probably not missing out. There is no inherent value in using the new functionality.

If you do have a specific problem then you could ask the community about that and they will help you determine whether there is new functionality that addresses it. Then go read about it in the manual, try it out, and see if it works for you. I find learning from solving my own problems easier than reading a manual cover to cover.

pletnes• 9 years ago

I’ve seen devs thinking like that for decades. Since they already know Fortran 77, which is a Turing complete language, they can solve any computing problem.

Still, I’d argue that it’s valuable to explore new things, at least at the level where you get an overview of which tool to use for which job.

qaq6• 9 years ago

How deep do you want to go Bruce Momjian has a ton of talks on internals and other advanced topics so if you want to dig deep would highly recommend them : https://momjian.us/

welder• 9 years ago

Use a cloud database:

https://www.graph.cool/

intsunny• 9 years ago

Congrats 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!

Tostino8• 9 years ago

Unlike previous releases, 10.1..2..3 will be just bug fixes. The next major release will be PostgreSQL 11.

ddorian43• 9 years ago

He's saying new software always (rare in pg) has bugs, so you wait some minor versions before rolling out.

jfbaro• 9 years ago

Congratulations 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• 9 years ago

It usually takes four or five months, although last time it only took a month or so, so hard to say.

oskari2• 9 years ago

PostgreSQL 10 will be available in Aiven (managed Postgres and other databases in multiple clouds including AWS and GCP, see https://aiven.io) within a couple of days after we've completed our validation process with the final version.

esseti2• 9 years ago

well done people!

ciconia• 9 years ago

A 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• 9 years ago

Postgres 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• 9 years ago

Thank you PostgreSQL team you are awesome!!

jcadam2• 9 years ago

Hmm... 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• 9 years ago

The new partitioning requires explicit declaration, so your existing triggers and tables shouldn't be affected.

https://www.keithf4.com/postgresql-10-built-in-partitioning/

jcadam2• 9 years ago

Good, 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.

coldcode• 9 years ago

I 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?

jeffdavis3• 9 years ago

Technology 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.

ngrilly3• 9 years ago

I 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-....

fusiongyro6• 9 years ago

Migrating 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.

ngrilly3• 9 years ago

PostgreSQL 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).

SomeHacker443• 9 years ago

Transactional DDL, by the way, is one of those things that once you use it, you can't imagine how you lived without it.

YorickPeterse• 9 years ago

Not so much a reason to not use it, but something to keep in mind: queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB. This doesn't necessarily mean they're always slower, but it's something you should take into account.

Another thing to take into account is that updating between minor versions (major versions per 10.x) is a bit tricky since IIRC the WAL format can change. This means that upgrading from e.g. 10.x to 11.0 requires you to either take your cluster offline, or use something like pg_logical. This is really my only complaint, but again it's not really a reason to _not_ use PostgreSQL.

ZitchDog• 9 years ago

With logical replication in PG 10 the wal issue shouldn't be a factor going forward :)

ngrilly3• 9 years ago

queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB

As far as I know, that's true when you use the MyISAM storage engine (which is non transactional), but not when you use InnoDB (which has been the default for years now).

wolf550e• 9 years ago

`select count(star)` is fast only on non-transactional MyISAM storage engine which locks the whole table for a single writer and has no ACID support. It's not what people usually mean when they say they want an RDBMS.

When ACID support is required, you use InnoDB, and that has the same `select count(star)` performance as other database engines.

What workload do you have that you need exact count of rows in a big table? Because if the table is not big or if inexact count would suffice, there are solutions (e.g. HyperLogLog).

aaronbwebber• 9 years ago

Since 9.2 Postgres has had index-only scans which can substantially improve the performance of `SELECT COUNT(* )` queries. If you need to get exact counts, and your query plan is not showing an index-only scan, you should try adding an index on the columns you are filtering on to see if you can get the query to run on an index-only scan.*

* <all the usual caveats about attempting to tune query performance>

ComputerGuru• 9 years ago

While the other comments talk about the benefits of switching to pgsql (and I concur whole heartedly) it seems no one has addressed your specific case. Your company isn’t really “switching” to anything, MariaDB was a fork of MySQL when the license kerfuffle was going on and there were issues with the stewardship of the project. It’s more of upgrading to a newer release of MySQL than switching to a different database engine.

i.e. “switching” to MariaDB is probably just a sysadmin upgrading the software and no changes to your code or database queries (unless replication is involved) but pgsql will certainly require more involved changes to the software.

snuxoll6• 9 years ago

There's a drawback with any choice of technology, but PostgreSQL's are pretty well known.

Replication can be a bit of a pain to set up compared to anything in the MySQL family since the tooling to manage it isn't part of the core project (there are tools out there, like repmgr from 2ndQuadrant).

Similar story with backups, bring your own tooling - again, 2ndQuadrant has a great solution with barman, there's also WAL-E if you want to backup to S3 along with many others.

Uber certainly presented a valid pain-point with the way indexes are handled compared to the MySQL family, any updates to an indexed field require an update to all indexes on the table (compared to MySQL which uses clustered indexes, as a result only specific indexes need to be updated). If you have a lot of indexes on your tables and update indexed values frequently you're going to see an increase in disk I/O.

Someone else can probably come up with a more exhaustive list, but the first two are things I've personally been frustrated with - even with the tooling provided by 2ndQuadrant I still have to admit other solutions (namely Microsoft SQL Server) have better stories around replication and backup management, though the edge is in user-friendly tooling and not so much underlying technology.

On the other hand, PostgreSQL has a lot of great quality of life features for database developers. pl/pgsql is really great to work with when you need to do heavy lifting in the database; composite types, arrays and domains are extremely useful for complex models and general manipulation; full-fat JSON support can be extremely useful for a variety of reasons, as can the XML features; PostGIS is king when it comes to spatial data; and a whole hell of a lot more.

PostgreSQL is hands-down my favorite database because it focuses on making my life, when wearing the database developer hat, a lot nicer. With the DBA hat on it complicates things some compared to other products, but the tooling out there is at least decent so it's not a huge deal.

jimktrains24• 9 years ago

Regarding replication, the logical replication in pg10 should make this significantly easier. Between that and "Quorum Commit for Synchronous Replication" I'm really excited to see how these pan out in production. Pg doesn't normally hype or talk about (or let into production) things that don't work.

phil21• 9 years ago

I typically say developers love PostgreSQL and sysadmins utterly despise it. The reverse holds true for MySQL.

Replication can be a bit of a pain to set up compared to anything in the MySQL

This is a very large understatement.

mathnode2• 9 years ago

MariaDB is a great choice over mysql, any day.

MariaDB compared to postgres is a difficult choice, both have a multitude of features. MariaDB 10 was a game changer for the mysql landscape.

To make postgres 10 as easy to scale as mariadb 10, and for query semantics in mariadb 10 to allow for the complexity that postgres 10 allows; both require tradeoffs in management tooling, development practices and understanding.

Neither of them are a silver bullet. They both require effort, like any RDBMS. But both are great choices.

shlomi-noach• 9 years ago

MariaDB 10 was a game changer for the mysql landscape

Can you please elaborate in what respect?

softinio• 9 years ago

Wish they had called it PostgreSQL X :)

jeltz5• 9 years ago

It is confusing enough with Postgres-XC and Postgres-XL to have a PostgreSQL X.

disconnected• 9 years ago

I'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?

manigandham4• 9 years ago

For 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.

zitterbewegung2• 9 years ago

The 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.

manigandham4• 9 years ago

DBeaver is highly recommended: https://dbeaver.jkiss.org

benjaminjackman• 9 years ago

IntelliJ Ultimate has pretty good built in tools as well that handle a lot of different dialects. I've used MSSQL and PG with pretty good results, e.g. code complete that uses the db schemas is really nice to have when exploring databases.

the_rosentotter• 9 years ago

For a web interface, especially for data browsing/editing, TeamPostgreSQL[1] is the best I have used for any database.

[1] http://www.teampostgresql.com/

bdcravens3• 9 years ago

I've been using DataGrip for both SQL Server and Postgresql for over a year now, and am very happy.

wehadfun• 9 years ago

I feel like Microsoft SQL server is the easiest to use and has the best tooling.

I would say that MySQL and Postgres are generally ok to use I slightly perfer MySQL tooling.

Oracle seems to be the most different one but once I got use to it it was ok.

enraged_camel• 9 years ago

>I feel like Microsoft SQL server is the easiest to use and has the best tooling.

Agreed. SQL Server Management Studio is fantastic and is one of the main reasons I enjoy working with MSSQL.

Unfortunately Postgres severely lacks in the tooling department. PgAdmin 3 used to be good, but PgAdmin 4 is simply horrendous. It makes me dread interacting with Postgres.

slysf4• 9 years ago

I'm sorry but I just can't take MSSQL Server seriously when it cannot export valid CSV. It does not escape commas for CSV or tabs for TSV, and has no option to. If they had their own flat file export that could be re-imported I could forgive it because I could write my own library for that format, but there simply is no way to cleanly export data from MSSQL. I had to write a SQL query that was ~2000 lines of 80 columns in order to export a database to CSV and properly escape each field manually, and it took FOREVER.

anonetal3• 9 years ago

Aside from tooling, those systems often perform much better than PostgreSQL for large queries or transactions, as they feature much better optimizations. Even outside of newer optimizations like "columnar" storage, several of those systems do code generation from queries to avoid function calls, branches, etc., which can have huge performance implications. I worked on the internals of PostgreSQL once, and the number of function calls in the innermost loops were very high.

PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines -- I haven't looked into it to see if there has been any fundamental change in the architecture in the last 4-5 years.

Tostino8• 9 years ago

Yes there has been. This release expanded it significantly.

grzm2• 9 years ago

PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines

From the submission:

"Improved Query Parallelism - Quickly conquer your analysis"

Query parallelism was introduced in 9.6 and expanded in 10.

brightball• 9 years ago

The biggest issue with SQL Server is that it is myopic. The tooling and everything around it is geared toward only SQL Server. The database itself is also geared around only SQL Server...making it a huge pain to get your data out to use it with something else like Elastic Search. It's geared towards being comfortable enough to lock you in and hold your data hostage.

irrational2• 9 years ago

We've recently moved from Oracle (after using Oracle for 15 years) and we have found Postgresql's documentation to be superior to Oracle and the SQL dialect to adhere closer to the standard and to make more sense (Oracle has been around for a long time and has a lot of old and non-standard SQL built in). Overall things are simpler yet everything runs as fast or faster than it did on Oracle. Better price, better performance, better documentation, better SQL, better install process... what's not to like?

kuschku10• 9 years ago

yet everything runs as fast or faster than it did on Oracle.

Be careful with that statement, Oracle’s license disallows users of the database from making benchmarks or any kind of performance comparisons (another reason to move to PGSQL)

EDIT: Source that refers to the ToS: https://stackoverflow.com/a/12116865

mohaine3• 9 years ago

The warts from a dev perspective:

1) Oracle is really lacking in modern features/usability. Features where frozen in roughly 1999 and they are pretty still the same (mostly). (You are STILL limited to 30 chars for a table name FFS). They do add new stuff from time to time but anything existing isn't modified. Works but not fun to work with.

2) MSSQL needs NOLOCK everywhere (I've seen codebases with this on EVERY query). The default locking really sucks. I'm sure a DBA can make the locking sane system wide but I've never seen this on any of the DBs I've worked with. Also, SQL Manager is a PITA IMHO. Toad it is not. Almost all DB interactions via a 1G windows only install is a "bad idea"

3) MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

4) Postgres. Lots of nice features and easy to work with but the optimizer will sometimes do something silly. Sometimes you have to cast your bound variable just use an index. (id=? => id=?::NUMBER just because you do a setObject in jdbc)

gnud2• 9 years ago

For the MSSQL locking, you should probably change the transaction isolation level instead of using NOLOCK everywhere.

I think it's a horrible wart that you have to do that for every session, though. The default can't be changed.

adzm2• 9 years ago

Change the mssql translation to run in read committed snapshot to get similar behavior to Oracle.

NOLOCK is usually a bad idea.

skissane• 9 years ago

(You are STILL limited to 30 chars for a table name FFS).

In the latest release, 12.2, the limit has been increased to 128.

https://docs.oracle.com/database/122/NEWFT/new-features.htm#...

reednj• 9 years ago

Theres a server wide option to turn off auto-truncate for MySQL, which I believe is on by default, at least on ubuntu

beefield2• 9 years ago

MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

Looks like I am forced to use MySQL (or some of its variants ) in the near future. This thing about MySQL eating data is a statement I have read about occasionally. Is there any way to identify and beware use cases where this could happen? Would there be any more thorough documentation of this issue anywhere?

wenc4• 9 years ago

I cannot comment on every single aspect of Postgres vs MSSQL, but there are a few things I like in MSSQL that I don't believe exist in Postgres:

1) SQL Server Management Studio (SSMS) - the default GUI is a decent free front-end that integrates well and lets you do advanced stuff like graphically live-profile queries (live execution plans), easily setup security, build indices, setup various things like linked servers, compression, etc. Although I'm a text-editor sort of person, I don't have SQL syntax memorized for infrequent tasks like creating indices so a GUI (or an IDE) can really help productivity in these instances.

Postgres's default GUI, pgAdmin is comparatively weak, and the good ones are third-party payware.

2) Columnar indices - MSSQL has a fairly good implementation called columnstore indices, which creates a derived column-oriented data structure which speeds up analytic queries quite a bit.

3) Speed - SQL Server is very performant and optimized, and doesn't choke on very large datasets. Postgres is decent, but on my datasets it doesn't seem to be very performant.

Also, MSSQL locking is a boon and a bane. It's not the best for environments with high contention, but it is ok for most analytic database use cases. On the other hand, Postgres' MVCC (and oh the vacuuming) can be annoying.

starik36• 9 years ago

What are the good third-party payware GUIs for Postgres? I've looked and never seen any that came anywhere close to SSMS, much less SSMS + SQL Prompt combo.

emptyfile• 9 years ago

Working with pgAdmin on a daily basis is pure torture, and the new v4 JavaScript app is just....shockingly bad.

ianamartin• 9 years ago

I cut my teeth on Oracle when I was first getting started in technology. All I did was write ad hoc queries all day long. My next DB heavy job was using SQL Server, where I built an analytics engine to do bootstrapping for sparse datasets. After that I used it to run the back-end data layer for a credit card processing company. I used MySQL at a different finance company that was doing similar things but at a smaller scale. Ever since then, I’ve been using Postgres.

Based on that experience, I’d rank them in this order:

1. Postgres 2. SQL Server 3. Oracle 99. MySQL

Postgres often lags behind the others in features, but the dev team chooses their battles wisely and ends up with better implementations.

Postgres is a real beacon of light in the open source world. Solid community. Many projects claim the benefits of open source, but they are never fully realized. Also, because Postgres is not operated by a freemium model, you always have access to the latest and greatest features. The extensibility is fantastic and well-leveraged by the community. I’ve never experienced a case where Postgres tried to figure out what I was doing and decided to do the wrong thing. Postgres fails early and loudly when there’s a problem with what I’m asking it to do, which is exactly what I want it to do. I don’t ever want to have to second guess the integrity of my data.

I haven’t run explicit benchmarks between any of these databases. But when I do similar things across two different systems, I feel like they are generally on par. But like I said, I can’t prove that with any numbers. There are probably specific work profiles that people can come up with that would show better performance for one platform over the other. But I don’t think there’s a realistic difference in performance in general. Not one that’s big enough to push your decision.

The real moment of revelation though, is when you find out that you can run your preferred programming language inside of Postgres. When you actually get to the point that transformations are outside of what you want to do in SQL, and you can just write a Python function and have it execute inside your database instead of having to do I/O, process the data, and then push it back . . . it is life-changing.

The only reason SQL Server isn’t tied for first place is because of the lack of extensibility and because it’s expensive to use in production. But it is rock solid, and has some nice things that Postgres doesn’t have, like hinting queries. Again, the Postgres community has discussed this, and it may never actually happen, but there are reasonable points as to why not. But it is really handy in SQL Server to be able to guide the query planner on the fly like that. SQL Server has also had solid pub-sub for a long time, though we’re getting that now with this version of Postgres.

I’m not a huge fan of Microsoft in general, but you absolutely have to give them props for their tooling. There is nothing even close to SSMS for any other database system. It is by far the gold standard for a visual interface to you data.

Obviously, if you’re throwing down money, you’re also getting a certain level of support for the product. I’m not convinced this should be a deciding factor between Postgresand SQL Server because, again, the Postgres community is amazing.

I should also point out that there’s a free version of SQL Server that will suffice for the needs of a great many people. It’s features are limited (no pub/sub, and there’s a size limit on your total dataset), but it’s totally functional for a lot of use cases. Even though I use Postgres for everything in production, I will always keep a PC around to run SQL Server for one off things that are just easier to do there.

Oracle is mostly fine. I was so new to everything when I was using it that I probably can’t speak that well to its strengths and weaknesses. Other people who have used it more recently can probably do it better than me. I just can’t for the life of me understand why anyone would pay their prices when SQL Server and Postgres exist, unless it’s for the support contract. And where I’m kind of meh about Microsoft, I’m actively against Oracle and Sun Microsystems. I’m pretty sure that Larry Ellison’s personal model is, “Just go ahead and be evil.” But that’s kind of a tangent and not really all that relevant.

MySQL is a different animal. It has a different design philosophy than the others. It’s more like MongoDB in principle than the others are. It’s main goal is to be friendly to the developer. And to entice you into upgrading to the paid tier.

Which is all fine. But one consequence of that is that it tries really hard to succeed under any circumstance, even if “success” means corrupting or losing your data. So it fails rarely, late, and quietly under certain conditions.

For that reason, I don’t think of it as even being in the same category as the other three. As in, it would never be an option for me, similar to MongoDB. I want my dev tools and programming languages to be focused on the developer. And I want my data store to be focused on my data. I think that this is a fundamental and deadly flaw with MySQL.

Different use cases have different requirement though, so your mileage will vary. I’m an incredible pedant about data integrity because the work I do requires it. There are legitimate cases where it just doesn’t matter all that much.

But in terms of feature parity and performance, they are all pretty close in general terms. Each will have specific cases that they really excel at and have been optimized for.

int_19h• 9 years ago

The real moment of revelation though, is when you find out that you can run your preferred programming language inside of Postgres. When you actually get to the point that transformations are outside of what you want to do in SQL, and you can just write a Python function and have it execute inside your database instead of having to do I/O, process the data, and then push it back

https://blogs.technet.microsoft.com/dataplatforminsider/2017...

arrty88• 9 years ago

what is the best way to shard on postgres?

zie2• 9 years ago

There are tons of ways to do that, the lazy way would probably be pay someone like Citus (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.

simonw2• 9 years ago

I 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• 9 years ago

maybe 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

simonw2• 9 years ago

Thanks, that's a really detailed article.

tomp3• 9 years ago

Hm... 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• 9 years ago

Why not use a stored procedure?

fusiongyro6• 9 years ago

I'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;

kuschku10• 9 years ago

You 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)

grzm2• 9 years ago

If I understand what you're looking for, you can likely accomplish this using a WITH expression, something along the lines of (untested):

    WITH
      input_rows (name) AS
        (VALUES ('a'), ('b'), ('c')),

      insert_rows AS
        (INSERT INTO entities (name)
           SELECT ir.name
             FROM input_rows AS ir
           ON CONFLICT DO NOTHING
           RETURNING id, name),

      -- returned when insert was successful
      SELECT 'new' AS src, i.id, i.name
        FROM insert_rows AS i
      UNION ALL
      -- return when there's a conflict
      SELECT 'existing' AS src, e.id, e.name
        FROM entities AS e
        JOIN input_rows USING (name);

As for the sequence incrementing, that's the nature of sequences: it's a trade-off between getting fast, unique values versus the overhead required tracking whether the id is used.

jimktrains24• 9 years ago

I'm having trouble justifying semantics that return rows which havn't been inserted or updated.

Actually, I find SQL pretty nice. It's strongly typed in non-MySQL engines. It's pretty expressive, and can be highly optimized without having to update queries. Analytical features found in non-MySQL databases such as windows are also invaluable for many types of analysis.

The biggest issue I've ever had is that nesting queries when doing some analytical work can be a pain. WITH does handle this to some extent, but it doesn't seem quite as nice as something like PIG where results an "flow" through the query.

The other issue is transformations like pivot tables where you don't know the column names ahead of time. pg has extensions for that, but they aren't quite as nice as a native SQL solution would be.

trinitry3• 9 years ago

There was a patch for fixing #1 with new syntax for the next version on the mailing list, so perhaps in a year this'll get fixed?

swasheck3• 9 years ago

Excited for the release. Disappointed that EnterpriseDB is now making me sign up for an account to download (for Windows).

latch• 9 years ago

I 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.

swasheck3• 9 years ago

Ah. I generally get the zipped binaries and that's where I'm bumping into the account creation requirement.

swasheck3• 9 years ago

I'm not sure why I was downvoted. Any insight would be helpful (so that I can be a better community member).

bobjordan• 9 years ago

I 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.

thom11• 9 years ago

It'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.

jeffdavis3• 9 years ago

There'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.

jaltekruse2• 9 years ago

If 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• 9 years ago

I 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.

fatbird2• 9 years ago

This is interesting. When you see these 'obvious to the eye' opportunities, do you stop and treat them as an exercise in generalizing the optimization for a query planner?

I'm curious how much query planners have to make tradeoffs between effective optimizations and not overloading the analysis phase.

anarazel12• 9 years ago

I'm curious how much query planners have to make tradeoffs between effective optimizations and not overloading the analysis phase.

In case of PostgreSQL: constantly. Pretty much every planner improvement has to argue that it's not likely to unduly increase plan time.

thom11• 9 years ago

I care about the total amount of time until I get to do real work with the data returned. Subsecond query plans for ad-hoc workloads are pointless for me if I then wait 10 hours for results.

Here's a concrete example: a view with a GROUP BY. Give it a concrete ID to filter by, and it will push it down to the underlying table and return quick results. I can then script a loop to do this to get the full dataset for a list of IDs. However, if I supply a `WHERE id IN (...)` or a JOIN, the query plan will be totally different and will take forever. This is dumb, and this is with up-to-date statistics etc. I'm happy to accept I'm probably not the target market, but just having the option to leave the query planner running for minutes instead of milliseconds would be great (if it indeed has work to _do_ in those minutes).

alexnewman• 9 years ago

Agreed. You following the cmu research on ml planned dB?

jalayir• 9 years ago

Have your run ANALYZE? ;)

lobster_johnson• 9 years ago

A lower-hanging fruit may be a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning, and query planning based on the actual database activity.

This could even be something that operates as a layer on top of Postgres, although it would probably need more precise data than what is currently available through stats tables, and since Postgres doesn't have query hints there's no way to modify query plans. It would also need the look at logs to see what the most frequent queries are, and possibly require a dedicated slave to "test run" queries on to gauge whether it has found an optimal solution (or maybe do it on the actual database during quiet hours).

AI would definitely be interesting, though.

derefr2• 9 years ago

I've always wondered why there doesn't exist something like this, but architected like Apache's mod_security: something you run for a while in "training" mode to let it learn your usage patterns, which then spits out a hints file (in mod_security's case, a behaviour whitelist; in this case, a migration to add a set of indexes + views).

As a bonus, it could (like mod-security) also have a "production mode" where the DB consumes that hints file and applies the instructions in it as an overlay, rather than as a permanent patch to your schema; that way, you'd be able to re-train after changes and then swap out the hints in production by just swapping out the file and HUPping the DB server (which would hopefully do the least-time migration to turn the old indexes into the new indexes), rather than needing to scrub out all the old indexes before building new ones.

dantiberian• 9 years ago

a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning, and query planning based on the actual database activity.

This is the core idea behind Peloton, a research database from CMU: http://pelotondb.io. Andy Pavlo has a really interesting, and entertaining talk about it https://www.youtube.com/watch?v=mzMnyYdO8jk.

anonetal3• 9 years ago

There is lot of somewhat-researchy work on this topic, the primary one being the effort at Microsoft Research, called AutoAdmin, that started about 20 years ago. They looked at automatically creating indexes, views, histograms, and stuff like that. Peloton@CMU is a more recent incarnation of the same idea with newer tradeoffs.

Although it might sound easy, this turns out to be a very challenging problem for many technical reasons.

A primary reason for it not getting traction in practice is also that, database administrators don't like an automated tool messing with their setup and potentially nullifying all the tricks they might have played to improve the performance. This was especially true in big DB2/Oracle deployments, but is increasingly less true, which has opened it up for innovations in the last few years.

mwpmaybe• 9 years ago

It only handles indexes, so it's just a start, but have you seen HypoPG[0] and Dexter[1]?

0. https://github.com/dalibo/hypopg

1. https://github.com/ankane/dexter

thom11• 9 years ago

Yes. I have views in my database, and CPU cycles (and disk) to spare. Please, please use them.

anarazel12• 9 years ago

I'd like to see your proposal get a bit more concrete. Your request seems a bit more for "magic" than "AI".

You're not likely to succeed with "here's the query and schema" and have AI somehow figure out what the best plan there is. There's a lot of rules to observe for correctness, and you can't just try to execute arbitrary valid plans, because the initial query plans are going to be really bad. So you again need a model of what valid query plans are, and a cost model to evaluate how efficient a plan is, without having executed it.

I think there's plenty opportunities for ML type improvements, but they're in individual parts, less as a wholesale query planner replacement. I think the most likely beneficiaries are around statics collection and evaluation, incremental improvements to query plans, and some execution time -> plan time feedback loops.

haberman2• 9 years ago

So you again need a model of what valid query plans are, and a cost model to evaluate how efficient a plan is, without having executed it.

Surely query optimizers do this already. What about that sounds like "magic"?

Computers can beat Lee Sedol at Go, it hardly seems like "magic" for a computer to come up with a query plan that is obvious to a trained human.

For example, OP mentioned the case of creating a temporary table with an index instead of emitting a lot of temporary files executing a subquery. Surely you could statically verify that such a transformation was legal, and it seems likely that a cost model could accurately predict that such a transformation would be cheaper. So why doesn't the query optimizer perform this transformation? Presumably because it doesn't yet explore this part of the search space of possible transformations.

EDIT: apparently this post is making some people mad. I'm not saying it's easy or obvious, but with ML being applied to so many problems very successfully, it seems very strange to say it's expecting "magic" to suggest that ML could improve on obviously sub-optimal query plans.

We have a cost function and we have a search space of valid transformations. Sounds like a lot of room to experiment with ML-based approaches to me.

thom11• 9 years ago

What I'm saying is that I, as a human, can spot simple situations under which semantically-correct optimizations are available. I can also create queries where literally cutting and pasting IDs over and over again into a WHERE clause is quicker than joining or using an IN clause. These things are dumb.

I personally think there are massive opportunities for data description languages and query languages that make expressing a single set of semantics simpler. SQL is supposedly a declarative language, but almost always requires you to understand _all_ the underlying implementation details of a database to get good performance.

Beyond that, I'd be more than happy with ML that enhanced, heuristically, some parts of the query optimiser. Hell, I'd be happy with a query optimiser that just went away and optimised, 24 hours a day, or at least didn't just spend 19ms planning a query that is then going to run for 8 hours.

rpedela• 9 years ago

I think named entity recognition may help if detailed labels were used. The parser can already label the where clause, select clause, etc. However a component of a where clause in a given query may be better executed as a join clause. Given enough training data, NER should be able to recognize the join clause within the where clause in that specific context and the resulting labels would give the optimizer a hint on how to rewrite that specific query.

Now I have no idea how much training data you would need or if a single, trained model would work for most use cases. But I think it could work in theory.

qaq6• 9 years ago

It's not a big problem to make query planner that generates close to perfect plans. The problem is the amount of time it would take for that query planner to generate a plan.

thom11• 9 years ago

Let me decide the amount of time, then. If I can cut and paste a bunch of queries together to make them faster than the current planner, I'd be happy to let the query planner run for tens of minutes.

silvestrov• 9 years ago

I would prefer they worked on something much simpler for now: the ability to mark a table as "log all full table scans on this table unless the SQL query contains the string xxxxx".

This would make it easy to catch missing indexes without logging all queries and without the nightly batch updates which are expected to do full table scans.

thom11• 9 years ago

I'm sure you're aware of this, but apart from the filtering, you _can_ get a count of sequential scans for a table from pg_stat_all_tables.

macdice• 9 years ago

You might be interested in the AQO project that was presented at PGCon 2017:

https://www.pgcon.org/2017/schedule/events/1086.en.html

It's using machine learning techniques to improve cardinality estimates.

TheCondor• 9 years ago

Are the statistics wrong on your data? Or is it that the subqueries modify the data set such that the statistics are unknown for the outer query?

Arbitrary ad hoc query support is hard, it feels like you want indices on everything but then data insertion is dog slow. Then even with perfect stats I’ve seen vertica and PostgreSQL order aspects of queries wrong, I know the pain you speak of. I’ve not convinced myself that it’s always solvable or obvious until you do it wrong though, I never spent much time explaining queries before executing them or explaining “fast” queries though.

med_abidi• 9 years ago

It's also about time that they upgrade their website. It's so 1999.

Tostino8• 9 years ago

It 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?

majewsky3• 9 years ago

On the other hand, it's always nice when pages finish loading in under 10 seconds.

combatentropy• 9 years ago

Which is why I love it.

lwansbrough2• 9 years ago

Native 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?

qaq6• 9 years ago

What is the largest production instance of CockroachDB ?

lwansbrough2• 9 years ago

No 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.)

MycroftH• 9 years ago

I 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).

awoods187• 9 years ago

Hi all--My name is Andy and I'm a PM here at Cockroach labs. We are currently working through partitioning as a key feature of our 1.2 release scheduled for April 2018. Our solution can be found at this RFC (https://github.com/cockroachdb/cockroach/pull/18683). One thing to note, we don't require you to define your partitions up front. We'd love to get your feedback on this feature--please feel free to comment on the RFC or reach out to me directly at andy@cockroachlabs.com.

doh4• 9 years ago

I think PG is already there and beyond if you consider using Citus.

We've pretty large deployment in our production with 45TB of data, across 1,280 cores 4.8TB of RAM. Most of our tables have hundreds of billions of rows that are under write-heavy pressure. Citus is currently handling around 60k writes per second. Most of our SELECT queries run under 200ms, in some cases those are fairly complex.

The current setup is a result of long tests done across many different database solutions. We never considered CockroachDB seriously for our production, but we did use or test MongoDB, Cassandra (including ScyllaDB), HBase (including Big Table) and Spanner.

We struggled with most, but we used HBase for a year before we moved to PG/Citus. Our expenses dropped by half once we done it as PG is just better suited for our setup. We tested out Spanner fairly heavily as GCP folks tried to convince us to give it a chance. The performance however wasn't even comparable.

Citus has its own quirks and it's not perfect, so definitely do your research before you decide to use it. The best part however that you the best SQL engine underneath with all the powerful features and tools that come with it. We for instance utilize heavily triggers and pub/sub directly from PG. Huge portion of our application logic is done on the DB level through UDF. The part we like about citus the most though is the team behind it. They are incredibly helpful and go beyond the scope of a traditional support even if they don't need to.

barrkel3• 9 years ago

Do you use the column storage stuff?

romanovcode2• 9 years ago

How 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/

anarazel12• 9 years ago

You 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.

romanovcode2• 9 years ago

Oh, OK. Great! Thank you.

seaghost• 9 years ago

Postgres is my go to database for any project size.

LeicaLatte• 9 years ago

I have moved to PostgreSQL in all my production apps and never been happier. Looking forward to migrating to 10 by the year end.

ropeladder2• 9 years ago

Just 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)

majewsky3• 9 years ago

Did 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.)

ropeladder2• 9 years ago

Just started writing it up! Thanks for the encouragement.

esseti2• 9 years ago

Does 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?

PostgreSQL 10 Released

Related Stories