151–160 of 172 posts
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…
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...
I work with MySQL in my current job. After years of Postgres it feels like dealing with some parody of database.
Will having logical replication make doing a DB version upgrade in production easier? 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.
Earlier quoted context omitted.
I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?
I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much…
"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."
As someone with little to no Postgres experience, it seems like they are heading in the direction of providing the type of massively parallel, scale out features that Citus provides. 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...
Earlier quoted context omitted.
Here's an example of doing that in PostgreSQL: 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');
actually it is easier 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.
Will having logical replication make doing a DB version upgrade in production easier? 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.
> 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?
Will having logical replication make doing a DB version upgrade in production easier? 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.
> E.1.2. Migration to Version 10 > 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?
I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. 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…