71–80 of 172 posts
Earlier quoted context omitted.
Honestly, MySQL/MariaDB use has more do with features PostgreSQL didn't have until now. (i.e. Logical replication)
Postgres also has many features that MySQL doesn't.
Logical replication is frequently a requirement which reduces your options to not Postgres until now.
The biggest news for me is ICU support for collations (text sorting). Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt). Now, you can optionally use ICU for collations, which gives you r…
Also, ICU collations are case sensitive, just like libc locales.
Earlier quoted context omitted.
Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'
not sure why you got downvoted so much. 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 b…
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
The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases. Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...
Does PostgreSQL offer something comparable to MySQL multi-source replication in combination with auto_increment_offset?
Congratulations to the team. The replication/partition improvements are significant and much appreciated. My favorite improvements are full text search of JSON & JSONB; this makes pg a full replacement for Mongo for my use cases.
Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?
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...
PostgreSQL is an amazing project. A no-nonsense database that delivers what it promises. I'm amazed at what a talented group of people can accomplish when they are driven and put their mind to it. Thanks for a wonderful product.
Plus their documentation is top notch. After using Oracle for 15 years, I nearly cried when I moved to Postgres recently and saw how wonderful their documentation is.
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.
Earlier quoted context omitted.
Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'
If you want to do case-insensitive for all languages you can do this: 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…
While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta... ) is the one…
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.
The biggest news for me is ICU support for collations (text sorting). Previous versions of PostgreSQL relied only on strcoll, which is horribly broken on BSD and macOS. On platforms where it wasn't completely broken, it had the potential for subtle data corruption bugs (eg. an update to glibc might change sort order, causing indexes to become corrupt). Now, you can optionally use ICU for collations, which gives you r…
ICU collations are prepopulated; see " rel="nofollow">https://www.postgresql.org/docs/devel/static/collation.html#... . Also, ICU collations are case sensitive, just like libc locales.