Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 11.3 and 10.8

postgresql.org

Post reply on HN
Print view View original

121–130 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#121

Post by astine »

ape4 wrote: post #40
dspillett wrote: post #17

Earlier quoted context omitted.

Out of interest (SQL Server guy mainly, so only partly keep up with what other engines are doing), what changes significantly affect performance (without making changes to your own code/configuration to make use of new features) in 10.x & 11.x?

It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server". I mean, there are other sql servers.

I find that the bigger problem for me is the number of RDBMs that use 'SQL' in the name somewhere means that non-technical people get confused between 'knowing SQL', as in knowing how to develop SQL code, and 'knowing SQL' as in knowing how to manage a particular database product (usually SQL Server). That's how I got turned into a DBA without realizing it.

Re: PostgreSQL 11.3 and 10.8

#122

Post by Alex3917 »

hans_castorp wrote: post #102
Alex3917 wrote: post #5

Earlier quoted context omitted.

Postgres users actually generally upgrade faster than those using other databases because there are a lot of new features each year. But once your database gets huge then upgrading still becomes a pain, so that's why they keep providing security support and bug fixes for older versions as well.

pg_upgrade with the --link option is extremely fast and doesn't really depend on the size of the database.

Interesting. I just use RDS and it always seems to take 15+ min even though our database is tiny.

Re: PostgreSQL 11.3 and 10.8

#123

Post by taffer »

atombender wrote: post #81
eberkund wrote: post #44

I maintain a couple of MySQL based applications. I don't really use any features outside of "standard SQL" is there a reason to switch over to Pg? I haven't used Pg before and usually default to MySQL.

One big argument: Transactional DDL. For example: begin; alter table foos add answer int not null default 42; alter table foos drop column plumbus; update foos set name = upper(name); create table bars (t serial); drop table dingbats; rollback; // Or, of course, commit What's the benefit? Atomic migrations. You can create, alter, drop tables, update data, etc. in a single transaction, and it will either commit comple…

If Oracle DDL is not transactional, what's the point of its Edition-Based Redefinition feature?

Re: PostgreSQL 11.3 and 10.8

#124

Post by atombender »

taffer wrote: post #123
atombender wrote: post #81

Earlier quoted context omitted.

One big argument: Transactional DDL. For example: begin; alter table foos add answer int not null default 42; alter table foos drop column plumbus; update foos set name = upper(name); create table bars (t serial); drop table dingbats; rollback; // Or, of course, commit What's the benefit? Atomic migrations. You can create, alter, drop tables, update data, etc. in a single transaction, and it will either commit comple…

If Oracle DDL is not transactional, what's the point of its Edition-Based Redefinition feature?

Oracle's "editions" are more like versioning, I think. Last I checked, only a very limited set of schema objects were editionable (views, triggers, procedures, etc.), not including tables or indexes.

Re: PostgreSQL 11.3 and 10.8

#125

Post by taffer »

Macha wrote: post #77
buster wrote: post #59

Earlier quoted context omitted.

https://en.wikipedia.org/wiki/SQL_Server Same as "FTP Server" doesn't mean proftpd, "HTTP Server" doesn't mean apache and "C compiler" doesn't mean gcc. SQL is a language. Server is a generic term. Another example: I may ask my colleague the following: "Which SQL server should we use in our new project?". Does this mean, i would like to know the edition and version of the MS SQL Server or maybe (and from my point of…

But httpd does mean Apache, and sh means Bourne shell. It also helps that nobody says "The SQL server is down" when their mysql instance is down. Even when using a generic term it's "the database is down"

> It also helps that nobody says "The SQL server is down" when their mysql instance is down.

I've heard such sentences many times from many people.

Re: PostgreSQL 11.3 and 10.8

#126

Post by yjftsjthsd-h »

Macha wrote: post #77
buster wrote: post #59

Earlier quoted context omitted.

https://en.wikipedia.org/wiki/SQL_Server Same as "FTP Server" doesn't mean proftpd, "HTTP Server" doesn't mean apache and "C compiler" doesn't mean gcc. SQL is a language. Server is a generic term. Another example: I may ask my colleague the following: "Which SQL server should we use in our new project?". Does this mean, i would like to know the edition and version of the MS SQL Server or maybe (and from my point of…

But httpd does mean Apache, and sh means Bourne shell. It also helps that nobody says "The SQL server is down" when their mysql instance is down. Even when using a generic term it's "the database is down"

Eh... sh only refers to the generic specification of a bourne-family shell, usually bash or dash; almost nobody is actually using the original Bourne shell.

Re: PostgreSQL 11.3 and 10.8

#127

Post by devereaux »

Symbiote wrote: post #28
profmonocle wrote: post #10

Earlier quoted context omitted.

Moving between major versions of Postgres requires downtime proportional to the size of the database. Supporting older versions allows users to go many years without having to do this.

I upgraded from 9.3 → 11.2 a few months ago using pg_upgrade[1], on a master+slave database with 150GB of data. I did a fair amount of testing, but the final procedure was very fast and smooth. 1. Test the upgrade: set up an additional secondary (9.3), break the replication link (promote it to a master). Test the upgrade on that. It was really fast, under 30 seconds to shut down the old DB, run the in-place upgrade,…

This is a nice way to do that, but you have a low volume of data, and you think 30 seconds is fast and 1 minute of downtime is acceptable. I question these assumptions.

Consider the situation when you're adding thousands of new records per seconds, and the database is being used every second (quite literally: to compute per seconds statistics).

A better solution is to have triggers on the old master, to do the same inserts on the new master (after copying the data/promoting a replica/whatever), and have similar triggers on the new master when the IP is not the old master (to be able to backout to the old server)

Then both the new and the old master run "in parallel", with the same data, and you can have the apps use the new server (on a new domain name, new ip, new port, whatever) when you want - on a app by app basis if you want. You can keep both until you decide to decommission the old master.

Re: PostgreSQL 11.3 and 10.8

#128

Post by mixmastamyk »

evanelias wrote: post #111
mixmastamyk wrote: post #96

Earlier quoted context omitted.

Ok, looks like that version is in LTS dists now. Perhaps to poster's legacy apps are taking advantage of them. There were other deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now.

Even if OP is using 5.6 or previous, strict mode has been available as an option for over 15 years, and can be enabled dynamically (no restart required). I simply don't see any valid argument for avoiding MySQL due to "data reliability" concerns in 2019. > There were other minor deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now. Well, I can't really respond to no…

It takes years to build up trust, seconds to lose it. Obsolete documentation to disappear. It's an uphill battle. In the meantime there's postgres, and it's free.

Re: PostgreSQL 11.3 and 10.8

#129

Post by hans_castorp »

evanelias wrote: post #108
hans_castorp wrote: post #101

Earlier quoted context omitted.

Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time. Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table. But MySQL doesn't support indexing the complete JSON value for arbitrary queri…

> If you want to support several different expressions you need to create a new column each time Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression. > Additionally, an ALTER TABLE blocks access to the table. It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table…

> What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

You create a single index, e.g:

create index on the_table using gin(jsonb_column);

And that will support many different types of conditions,

e.g.: check if a specific key/value combination is contained in the JSON:

where jsonb_column @> '{"key": "value"}'

this also works with nested values:

where jsonb_column @> '{"key1" : {"key2": {"key3": 42}}}'

Or you can check if an array below a key contains one or multiple values:

where jsonb_column @> '{"tags": ["one"]}' or where jsonb_column @> '{"tags": ["one", "two"]}'

Or you can check if all keys from a list of keys are present:

where jsonb_column ?& array['key1', 'key2']

All those conditions are covered by just one index.

Re: PostgreSQL 11.3 and 10.8

#130

Post by jtdev »

eropple wrote: post #33
Scarbutt wrote: post #29

Earlier quoted context omitted.

Out of interest ;) SQL Server is such an expensive beast, ~$15K per core, what are your reasons for prefering it over PG?

I quite like Postgres and use it preferentially, but your numbers are off. SQL Server Enterprise costs more like $7500/core; that $15K pack, as far as I am aware, comes with two core licenses. SQL Server Standard 2016 costs $931 for a license if you use CALs ($209 a pop), or $3700/core. Also bear in mind that almost nobody pays list price for any of this.

Having to spend time dealing with the MS licensing mess, rather than building software, seems wasteful and in my experience can be likened to sitting on a cactus for an extended period of time.
Post reply on HN