PostgreSQL 10 Beta 1 Released
postgresql.org
postgresql.org
Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta...) is the one single knob I wanted to be able to turn so many times in the past now (especially the n-distinct counts).
Most of the time, the planner does an ok job, but sometimes you have tables in a peculiar shape and if you're unlucky, the planner will opt into horribly bad plans. Whenever this happened for me, it was due to one of the two things CREATE STATISTICS allows me to tune in the future.
Thank you, thank you, thank you to whoever gave us this wonderful feature
[1] https://www.postgresql.org/docs/9.6/static/sql-update.html [2] https://www.postgresql.org/docs/10.0/static/sql-update.html
update comment set modified = now(), body = 'edited comment' where id = 123;
update comment set (modified, body) = row (now(), 'edited comment') where id = 123;
The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.
update comment set (modified, body) = (select now(), 'edited comment') where id = 123;
UPDATE books
SET (title, isbn) = (
SELECT title, isbn FROM other_books
WHERE other_books.foo = books.bar);
UPDATE books
SET title = other_books.title, isbn=other_books.isbn
FROM other_books
WHERE other_books.foo = books.bar; SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT ) }
I don't quite understand the commit message, but I think the new syntax resolves some ambiguity in the grammar in an edge case. From the diff of src/test/regress/sql/update.sql:
-- *-expansion should work in this context:
UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j)
WHERE update_test.a = v.i;
-- you might expect this to work, but syntactically it's not a RowExpr:
UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j)
WHERE update_test.a = v.i;Just speculating off hand, but this sounds like a schema-defined column dependency which is doubly-troublesome since, like I said, this is within our primary key index. I am super excited to see just how much a difference CREATE STATISTICS will improve overall performance.
Maybe the changes in 9.6 that allow using foreign key constraints during estimation would help, though?
If that's the case (which I suspect it is, having used prior PSQL versions), then most people will never need to optimize it, but those who have an extreme use case that can benefit from such tweaks will have such options available.
The ultimate goal is to allow detecting this automatically, and either create the statistics right away or at least providing some suggestions to the users. That's not in Pg10 though.
My favorite improvements are full text search of JSON & JSONB; this makes pg a full replacement for Mongo for my use cases.
I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
Is this the correct talk: https://youtu.be/dwMQXLOXUco?t=5380 ?
What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.
Postgres storing JSON types != All mongo functionality
I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
(1) It's a proof of concept, (2) it hasn't been updated in 3 years and (3) it still isn't the same syntax as MongoDB.
The point still remains that PostgreSQL isn't just a 1-1 replacement for MongoDB which is pretty common sense to me anyway.
1) it's a proof of concept that others have improved upon to show that you can indeed replace the functionality of mongo that most developers tend to rely on.
2) neither has mongodb at that level
3) that's correct, you need to actually use the term SELECT when you use the functions
and you are correct, pg is missing the random data loss that comes with mongo. it will never be 1-1 in regards to that.
EDITING TO ADD:
the important part is the note (the bulk of the comment) that I'm updating partial JSONB data on a very regular basis, and do it using PLV8, getting rid of the need for an unreliable database and instead using exactly what this news story is about.
"ToroDB Server
It is a MongoDB-compatible server that supports speaks the MongoDB Wire Protocol (and therefore can be used with the same drivers used to connect to any standard MongoDB server) but stores your data into a reliable and trusted ACID database."
https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there.
You can also roll your own (if you really want)...and it is relatively approachable to do so using built-in features like partitioning.
And, of course, with the 10 Beta it would seem that logical replication is being brought into core which sets the foundation for future replication features such as BDR to also be brought into core.
I would also point out that since Mongo's BI connector fiasco, it would seem more and more Mongo users are finding more reasons to just use Postgres (where relational interfaces are desirable for BI): https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...
That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products.
The point still remains that after all these year PostgreSQL's scalability story is still a mess.
https://www.postgresql.org/community/contributors/
Of that group, both 2nd Quadrant and CitusDB are represented...so in a way you could say their support is "by the vendor". Not to mention EnterpriseDB which also has support options.
> we can't use CitusDB when it is not supported by other vendors for use with their products.
CitusDB is no longer a fork, it's an extension...this means so long as your other vendors products support Postgres, they support CitusDB. More over, CitusDB itself will sell you an Enterprise package.
> The point still remains that after all these year PostgreSQL's scalability story is still a mess.
If by mess you mean specifically there is no knob and dial arrangement in the core of Postgres I would agree.
But within the core of PostgreSQL there are primitives which make scaling approachable (I myself am working on a data ingestion process that utilizes table partitioning and hand rolled sharding for PostgreSQL).
And there are now a plethora of extensions and tools provided by core contributors such as 2nd Quadrant and CitusDB to offer somewhat out of the box solutions and they even come with support.
PostgreSQL is not the right tool for every job and the replication/clustering area has been a a sore spot for Postgres in the past. But it certainly isn't bereft of options now...and the inclusion of logical replication in this beta is only the first step in bringing these options closer/into the core.
This is absolutely true. I don't get the irrational downvotes when it comes to postgres.
It's a great database but it is sorely behind with scalability features and is just now finally getting single-node parallelism and logical replication. It's still hampered by the requirement on 3rd party tools to get connection scaling, decent backups, HA and distributed clustering. The future looks interesting but the other databases aren't sitting around idly either.
Two gripes:
1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields)
2. Why is the Postgres JSON syntax so different? Why not just support SELECT document.field.field instead of (inconsistent) document->'field'. Imho pg JSON syntax is hard to read and new to learn.
Native Table Partitioning - https://www.keithf4.com/postgresql-10-built-in-partitioning/ Logical Replication - https://blog.2ndquadrant.com/logical-replication-postgresql-...
or what is new in PG 10 https://wiki.postgresql.org/wiki/New_in_postgres_10
Postgresql really awesome!
A friend of mine was doing an MBA and was planning a paper on open source collaboration models. She asked me for my opinion on well-run projects and Postgres was the first thing that crossed my mind.
They have an excellent combination of goals, structure and expertise, and have created a remarkably low-drama, functional community around delivering excellent tools. I really think there's a lot to emulate there.
I've often said that I didn't learn English grammar until I studied German, I didn't understand Linux until I read the Arch documentation, and I didn't comprehend relational data until I read the Postgres documentation.
Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt).
Now, you can optionally use ICU for collations, which gives you reliable, versioned collations. This is a big step forward!
ICU collations are not the default, you need to add them with CREATE COLLATION. You have a lot more collations available to choose from, but I think it's not yet possible to change any of the advanced settings that ICU provides.
(Also, when I tried it, it seems that the ICU collations are case insensitive -- but I think case insensitive collations aren't fully supported yet.)
Also, ICU collations are case sensitive, just like libc locales.
Anyway, thanks a lot for this patch! It looks like it was a lot of work, and I'm very happy this made it into PostgreSQL 10.
You can download it from Github: https://github.com/PostgresApp/PostgresApp/releases
Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...
As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that
SELECT id FROM users WHERE email='foo@example.com'
SELECT id FROM users WHERE email='Foo@example.com'https://www.postgresql.org/docs/9.6/static/functions-matchin...
Can't you just do this in PostgreSQL?
SELECT id FROM users WHERE email = lower('Foo@example.com') create table user_email (
email text not null
);
-- create a index on the lowercase form
-- of the email
create unique index user_email_case_idx
on user_email (lower(email));
-- select using the index, with the lowercase form.
select 1
from user_email
where lower(email)=lower('Foo@foo.com');SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';
WHERE lower(email) = lower('foo@example.com')
I'm not sure ILIKE can hit an index in your example.
solution immediately came up at SO:
SELECT id FROM users WHERE LOWER(email)=LOWER('Foo@example.com')Everyone's answer is "just lowercase everything".
I'll respond just a bit:
1. You don't always have control over all the queries that have been written against your database.
2. You would probably lose the ability to use ORMs without a moderate amount of customization.
3. If you're migrating from a different database, you may have checksums on your data that would all need to be recalculated if you change case on everything stored.
4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it?
citext on postgresql seems a decent option - the citext docs even mention drawbacks of some of the other recommended options.
And doing the runtime lower() on everything will generally not be slower than citext. If you look at the source for the citext comparison (https://github.com/postgres/postgres/blob/aa9eac45ea868e6dda...) you'll see it is internally converting the values to lowercase and comparing them. All it saves you is the overhead of a sql function invocation, and you'd have to do a lot of comparisons to make that difference measurable. But if you're doing a lot of those comparisons, unless you're just running the calculation on the same couple values over and over, the memory and disk latency will dominate performance, not the minimal overhead of the sql function invocation.
I agree you should probably use citext if you need case-insensitive unique or primary key values, but be aware of the drawbacks. https://www.postgresql.org/docs/current/static/citext.html
Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?
1. first install the following (be sure to replace [your schema]:
CREATE EXTENSION pg_trgm with schema extension;
CREATE EXTENSION unaccent with schema extension;
CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE;
2. then in your query you can use:
where insensitive_query(my_table.name) LIKE insensitive_query('Bob')
It's discouraged by RFC5321 whilst also being defined by it.
Logical replication is frequently a requirement which reduces your options to not Postgres until now.
There's a whole lot of documentation and an ecosystem of operations tooling (think Percona) and MySQL experts are much more numerous.
MariaDB's Galera cluster is really solid and had years of production use now.
Postgres is catching up, but for now, MySQL/MariaDB win in that regard.
I tend to think the HN groupthink is strong on this subject, and 90%+ wouldn't ever see an effect beyond placebo from switching. MySQL (or MariaDB, which I actually use these days) has also changed drastically since those 200x-years where most of today's folk wisdom originates.
I don't touch MySQL much, but as a generalisation it's a good idea to flush outstanding writes + freeze the filesystem(s) for pretty much any database before snapshotting.
From previous experience with Oracle (years ago), that specifically would explode dramatically if things weren't flushed first.
This is a good sum up of useful modern SQL features: https://www.slideshare.net/MarkusWinand/modern-sql
Here is the developer's FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ
I know the feeling of having extremely limited time, i'm in the same boat. But I use Postgres every day with my startup, and we lean very heavy on the database (not by volume of data, just by complexity of the problems). I've reviewed a few patches that I wanted to see get into Postgres, and contributed to discussions on the mailing list. That's about the extent I am able to do at the moment, but it's better than nothing. More reviewers are never a bad thing.
I can of course do that already manually, but it is tedious and I hope it will be faster if implemented directly.
This lets me materialize a view and then inspect deltas between refreshes, with history, and even update the materialized view from triggers and have those changes recorded in a history table automatically.
Here's the recording for that very presentation, by the way. https://www.youtube.com/watch?v=8wMybGTlf8I
Yes, that's one of the major goals.
> We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.
Unfortunately on RDS your options are a bit more limited than on plain postgres. Otherwise you could use londiste, pglogical, .. to keep the time to switch over to something very small.
YMMV of course, better have your fingers crossed. If you don't trust it, do it the hard way: Switch to write-only mode (easiest: remove write rights for all DB users), notify your users, make a snapshot with pg_dump, restore to a fresh new DB copy and switch your services over. Worked for us, different story if you can't take any write downtime of course.
> A dump/restore using pg_dumpall, or use of pg_upgrade, is. > required for those wishing to migrate data from any previous > release. (https://www.postgresql.org/docs/devel/static/release-10.html) This means no, I guess. This is what I don't get. How can anyone with a sizeable db in production do that?
Would love to hear thoughts from someone with real expertise.
Any enhancement to PostgreSQL is also an enhancement to Citus, or rather, the PostgreSQL ecosystem as a whole. For example, PostgreSQL 10's declarative partitioning feature will help enable sharding+partitioning in Citus, which is one of the most frequently requested features.
PostgreSQL 10 also gives you the possibility of setting up a partitioned table in which the partitions are postgres_fdw tables, which allows a basic form of manual sharding without Citus. However, as we've learned over the years, there's a huge difference between the ability to distribute a table across multiple servers and addressing a use case.
A sweet spot for Citus is multi-tenant (SaaS) workloads, in which all queries and transactions are specific to a particular tenant. In that case, you can typically distribute most of your tables by tenant ID, and use (replicated) reference tables for data that is shared across tenants. Citus makes sure that data for the same tenant is automatically co-located and as long as your query filters by a particular tenant and joins by tenant, you get full SQL pushdown (with parallelism in PG10), and ACID transactions. At the same time, you can perform parallel DDL commands across all tenants to enable migrations, bulk load data through COPY, perform parallel rollups or transformations through INSERT..SELECT, and run parallel analytical queries. Overall, the combination of these features and the trade-offs that Citus makes ensure that if you need to scale out a multi-tenant app, sharding through Citus solves it. In many cases, the only changes you need to make in your app are adding a tenant_id column to your tables [1], being explicit about the tenant in your queries or ORM [2], and adding create_distributed_table calls.
None of the Citus features that allow you to scale out a multi-tenant app are available if you do sharding through partitioning+postgres_fdw so far. I also wouldn't expect core postgres to make aggressive trade-offs to optimise for specific use cases. The PostgreSQL way is to make everything pluggable and let extensions specialise.
[1] https://www.citusdata.com/blog/2016/08/10/sharding-for-a-mul... [2] https://www.citusdata.com/blog/2017/01/05/easily-scale-out-m...
For enums, I had to create a function to cast an enum to a unique integer, and used that in my exclusion constraints. It's hacky as hell and not something I like, but it's worked for the past 3 years.
I'll be really really happy to get rid of those hacks come PG 10 though.
I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being 32-bits severely limits the usefulness of the feature without using convoluted workarounds (multiple databases).
> but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM.
What sizes of files and such are you interested in? What kind of read/write patterns?
I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches.
It'd be cool if you could comment on the pgsql-hackers list.
Would the "native table partitioning" feature introduced in this release be a solution (or, at least, more optimal) for this?
Alternately, if you want to go off-heap, what about using Foreign Data Wrappers (https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_...)? These two both sound like they might solve your problem:
fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be stored in the database heap (you could create some native functions similar to the existing LOB functions, but then you run into atomicity issues again).
Honestly, this would be easy to write if the extension API supported off-heap storage of custom types - though I understand there's a ton of technical complexity in implementing such a feature.
As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management.
Personally, I think the pragmatic solution would be to replicate the files themselves outside of Postgres, and have Postgres just hold opaque references to their paths, which you would join through an FDW to fill a column with their contents.
As you say, the files are Write-Once-Read-Many—so you (presumably) aren't worried about how Isolated or Consistent writes to the file data would be without Postgres. Plain filesystems provide Atomicity and Durability guarantees all by themselves. It's less convenient to do things this way—you have to manage e.g. "WAL-E + a separate rsync" rather than just WAL-E—but it's not particularly bad in terms of the engineering trade-offs.
Consistency is actually a huge issue for DR purposes, right now with the DB+filesystem approach it's nearly impossible to get a clean restore from backups if needed. We could alleviate this a bit by using something like GlusterFS' geo-replication support and replicas offsite, then verifying the tail of the data on-disk, but it would be nice to have a solution that could just work correctly with PostgreSQL's built-in replication (just like FILESTREAM does with MSSQL).
Edit: I should add, PostgreSQL's existing LOB support already works great with binary replication. The biggest issue with them is simply the 4B record limit, due to the use of OID's. It'd be nice if it was hidden away automatically with a special BYTEA-variant like FILESTREAM does with MSSQL, but it's "good enough" without that limitation.
> As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management.
There's really not too much of a technical problem here. What the sub-op is complaining about mainly just some work that needs to be put into parts of postgres that have been designed long ago. Not entirely trivial due to compatibility concerns, but also not super hard. The first thing would be to have a separate type of toast table with 64bit ids (can't drop support for 32 bit ids without making in-place upgrades impossible), and to have per table toast id. Then use same infrastructure for the LOB piece.
Btw, postgres doesn't mmap() data. On some systems it uses mmap(MAP_ANONYMOUS) to allocate its buffer cache, but that's just a configurable size.
Let me put it this way: how much overhead do you think there would be if you split your Postgres data across "ten billion" tablespaces? Because that's essentially what's being talked about here—little tablespaces containing one {oid, LOB} table, with one row, with a "storage engine" that represents that tablespace as a file.
...of course, if you do just (fix and then) use LOBs rather than insist on externally-visible files, none of that matters. :)
That said, we use python inside of PG, and it's awesome. It's not really any more powerful than PG/SQL(their built-in language) i.e. in terms of what you can do in the DB itself.
Our main app is also python, so we have a continuity of language from front-end to back-end. We do have some DB functions that take advantage of python's flexibilities, but like I said, we try to keep external modules and craziness out of the PG backend if possible. Sometimes there is no help for it, and you do what you must, and it's very handy that you can just go do it.
edit: i'm still trying to train myself to think in a relational way.
(ducks and runs)
Anyway, just to make my intentions clear, I love Postgres, have used it for over a decade, and have yet to use MongoDB or any other NoSQL database.
Here's a recent thread where this has been discussed:
https://news.ycombinator.com/item?id=13760333
There are likely others that describe it better, but I don't have them at hand.
Edit to add: Of course there are. Here's some comments by 'dang on the topic:
https://hn.algolia.com/?query=author:dang%20humor&sort=byPop...
One example (mentioned in the HN guidelines) is Reddit. There are people who frequent both HN and Reddit, yet it's clear that members on HN—even those who use Reddit as well—don't want HN and Reddit to be the same. As such, they behave differently on each site. That's not to say one is better than any other: they're just different.
And HN members don't act as one—just as they don't act as one on any other site. If they did, your comment would have been downvoted to the point that it was flagged dead (given it's likely been seen by hundreds, if not thousands) or not flagged at all. Given it's current shade of gray, I suspect you've received only a few downvotes. (Edit to add: I see you did say it is at -2.)
Does that make sense? Or am I misreading you?
Anyway, this is quite off-topic now. I posted these in the hopes of providing a bit more insight into the HN community. I hope they've been more helpful than frustrating.
I don't frequent Reddit. It sounds like it's so jocular that people come here to get away from it all.
In particular, look at Joseph M. Hellerstein at CMU: db.cs.berkeley.edu/jmh/ (doesn't load for me at the moment, not sure what's wrong).
Also, the Redbook (prepared by Hellerstein, Bailis and Stonebraker) is great: http://www.redbook.io/ but it's more an update on various topics than introduction.