Re: [GENERAL] Uber migrated from Postgres to MySQL

On 26-07-2016 21:04, Dorian Hoxha wrote: Many comments: https://news.ycombinator.com/item?id=12166585 https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/ On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe wrote: Honestly, I've

Re: [GENERAL] Uber migrated from Postgres to MySQL

Just a few points on reading this. First, the timeline bugs regarding replication (particularly iirc in the 9.1 days). I remember accidentally corrupting a (fortunately only demonstration!) database cluster in the process of demonstrating promotion at least once. Iirc last time I tried to

[GENERAL] low perfomances migrating from 9.3 to 9.5

hello all, We switched from PostgreSQL server 9.3 to 9.5. From the very beginning there was a noticeable drop in performances (for example : when injecting our SQL dumps into 9.5, COPY and CREATE INDEX were very slow). Our configuration file was the same for 9.3 and 9.5, except for the

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 27/07/2016 10:15, Condor wrote: On 26-07-2016 21:04, Dorian Hoxha wrote: Many comments: https://news.ycombinator.com/item?id=12166585 https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/ On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe

[GENERAL] GIN Indexes: Extensibility

Dear All, Here is what I have: user=# create table FOO (key jsonb); CREATE TABLE user=# insert into FOO(key) values ('[2014]'), ('[2015]'), ('[2016]'), ('[2014, 2]'), ('[2014, 2, 3]'), ('[2014, 3]'), ('[2014,2,4]'), ('[2014, 2,4]'), ('[2014,3,13]'), ('[2014, 2, 15]'); INSERT 0 10 user=# SELECT

Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3

On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost wrote: > That'd be great. It's definitely on my list of things to look into, but > I'm extremely busy this week. I hope to look into it on Friday, would > be great to see what you find. Sequences that are directly defined in

Re: [GENERAL] Uber migrated from Postgres to MySQL

> On Jul 27, 2016, at 00:15, Condor wrote: > > On 26-07-2016 21:04, Dorian Hoxha wrote: >> Many comments: https://news.ycombinator.com/item?id=12166585 >> https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/ >> On Tue, Jul 26,

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

On Wed, Jul 27, 2016 at 5:54 PM, thomas veymont wrote: > We switched from PostgreSQL server 9.3 to 9.5. From the very beginning there > was a noticeable drop in performances (for example : when injecting our SQL > dumps into 9.5, COPY and CREATE INDEX were very slow). >

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

2016-07-27 11:06 GMT+02:00 Michael Paquier : > On Wed, Jul 27, 2016 at 5:54 PM, thomas veymont > wrote: > > We switched from PostgreSQL server 9.3 to 9.5. From the very beginning > there > > was a noticeable drop in performances (for example :

Re: [GENERAL] question on parsing postgres sql queries

> What problem are you trying to solve here?​ to whit not everything that can be parsed is documented - usually intentionally. I am tyring to see whether we could use the documentation as a kind of formal specification of the language but I understand that the devil is in the details and that

Re: [GENERAL] GIN Indexes: Extensibility

On 07/27/2016 07:44 AM, Vick Khera wrote: On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich wrote: In my situation this order is invalid. Obviously, year 2016 should go after 2014, like that: I think you expect JSONB to sort differently than it does. I cannot imagine

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 27/07/2016 10:15, Condor wrote: > >> On 26-07-2016 21:04, Dorian Hoxha wrote: >> >>> Many comments: https://news.ycombinator.com/item?id=12166585 >>> >>>

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 10:22:27AM -0400, Scott Mead wrote: > That being said, it doesn't really provide a back-out plan.  The beauty of > replication is that you can halt the upgrade at any point if need be and cut > your (hopefully small) losses. If you use -k, you are all in.  Sure, you could >

Re: [GENERAL] GIN Indexes: Extensibility

On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich wrote: > In my situation this order is invalid. Obviously, year 2016 should go after > 2014, like that: I think you expect JSONB to sort differently than it does. I cannot imagine what a "natural" ordering of arbitrary JSON

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

2016-07-27 14:11 GMT+02:00 Michael Paquier : > > > And do you see changes if you increase min_wal_size? This will > increase the number of WAL segments recycled instead of removed at > each checkpoint. > -- > Michael > I have seen no improvment with the following

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

On Wed, Jul 27, 2016 at 11:01 AM, Adrian Klaver wrote: > On 07/27/2016 07:52 AM, thomas veymont wrote: > >> >> 2016-07-27 14:11 GMT+02:00 Michael Paquier > >: >> >> >> >> >> And do you see changes if you

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote: > technical reasons. Most developers will harp on at their boss about how > terrible their current database is and how performs > much better. Eventually one of two things happens: either a) those > developers end up in a position

Re: [GENERAL] Uber migrated from Postgres to MySQL

With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions. https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS Would reintroducing such a feature address some

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote: > On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian wrote: > > > > > I agree, but I am not sure how to improve it. The big complaint I have > > >

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 27/07/16 18:54, Chris Travers wrote: > Another one I think they obliquely referred to (in the subtle problems > section) was the fact that if you have longer-running queries on the > replica with a lot of updates, you can get funny auto-vacuum-induced > errors (writes from autovacuum on the

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier wrote: > Stupid question here, but do we provide any less then what MySQL does? I’m > reading: mysql provides same functionality for rollback like oracle/db2 provides. That is, rollback on a minor version upgrade

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 10:54:25AM -0700, Marc Fournier wrote: > http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-paths > > == > >Unless otherwise documented, the following downgrade paths are > supported: > > • Downgrading from a release series version to an older

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote: > > I am surprised PG does not even allow minor version rollback. It almost never happens that a minor version (N.M.x, x is minor) requires an upgrade at all. Change your binaries and you're done. Catalogue incompatibility

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian wrote: > I agree, but I am not sure how to improve it. The big complaint I have > heard is that once you upgrade and open up writes on the upgraded > server, you can't re-apply those writes to the old server if you need to > fall

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 27 July 2016 at 17:11, Andrew Sullivan wrote: > Given > the discussion in the post in question, the decision to use MySQL > appears to have been well-justified: > ​Well yes, but that's pretty-much the point of back-justification, isn't it? ​[snip a whole bunch of good

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 12:51:40PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian wrote: > > > Yes. I was saying I don't know how to improve pg_upgrade to address it. > > This problem is there even in oracle/db2/sqlserver. None of them allow >

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 4:22 PM, Scott Mead wrote: > On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > >> On 27/07/2016 10:15, Condor wrote: >> >>> On 26-07-2016 21:04, Dorian Hoxha wrote: >>> Many comments:

Re: [GENERAL] Uber migrated from Postgres to MySQL

> On Jul 27, 2016, at 09:59, Bruce Momjian wrote: > > I think long-term we are looking at pg_logical for zero-downtime > upgrades and _downgrades_, and pg_upgrade for less overhead (I don't > want to make a second copy of my data) upgrades (but not downgrades). > > I think

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian wrote: > Yes. I was saying I don't know how to improve pg_upgrade to address it. This problem is there even in oracle/db2/sqlserver. None of them allow rollback to the lower version unless it is a minor version upgrade. Major

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 09:17:58AM +0200, Chris Travers wrote: > The replication section made me wonder though if they were using the right > replication solution for the job.  If you don't want an on-disk copy, don't > use > physical replication.  This being said there is one serious issue here

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian wrote: > > > I agree, but I am not sure how to improve it. The big complaint I have > > heard is that once you upgrade and open up writes on the upgraded > > server,

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier > wrote: > > > Stupid question here, but do we provide any less then what MySQL does? I’m > > reading: > > mysql provides same functionality for

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 12:59:59PM -0400, Bruce Momjian wrote: > On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote: > > On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > > > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian wrote: > > > > > > > I agree,

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 2:07 PM, Andrew Sullivan wrote: > It almost never happens that a minor version (N.M.x, x is minor) > requires an upgrade at all. Change your binaries and you're done. > Catalogue incompatibility historically was the basis for something > becoming a

[GENERAL] How to give complete ownership of a new DB to a new user (non-SU)?

Hi, *Problem Summary:* I'm facing changing ownership issue for a DB and all its DB objects (DB, schema, tables, sequences etc ownership). *Experience So Far:* As per my knowledge and experience every DB being created use a DB template at the time of creation (default to "template1"). If "WITH

[GENERAL] question on parsing postgres sql queries

Hello, I am doing some research on postgres sql query parsing. I have found the https://github.com/lfittl/libpg_query project which manages to re-use the native postgres server parser. For using this, you need to accept an external dependency on a lib compiled out of the postgres source. I was

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 9:22 AM, Scott Mead wrote: > On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios > wrote: >> Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like >> seconds. (with the -k option) >> However, be warned

Re: [GENERAL] Server side backend permanent session memory usage ?

Hi, I've been knocking heads with this issue for a while without updating this thread. I have implemented a refresh connection behavior from our pooler/client side which seems to be a successful work-around for memory loss indications on the backend side. With that "solution" in my pocket

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 2:15 AM, Condor wrote: > They are right for upgrades. > It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to > finish upgrade and meanwhile database is offline. What? I had a cluster over 3TB and it was offline for only 10

Re: [GENERAL] How to give complete ownership of a new DB to a new user (non-SU)?

Billal Mahmood writes: > *Problem Summary:* > I'm facing changing ownership issue for a DB and all its DB objects (DB, > schema, tables, sequences etc ownership). While there's no magic single statement for this, I think you'll find that REASSIGN OWNED takes most of

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 02:33:54PM -0500, Kevin Grittner wrote: > Until you get to the end of the upgrade and *start the cluster > under the new version* you can fall back to the old version. Yeah, but to be fair a lot of well-funded businesses (note what started this discussion) are

Re: [GENERAL] Uber migrated from Postgres to MySQL

We can't use the pg_upgrade in our 3TB database just does not work.. that's the main reason we're still using 9.2.

Re: [GENERAL] Server side backend permanent session memory usage ?

"Day, David" writes: > We have a server side function written in plpgsql called by the client side > application. That fx does work that includes > Invoking a server side plpython2u function. > If I repeat calling this severer side logic/function outside our app from a > a

pg_upgrade and not working (was Re: [GENERAL] Uber migrated from Postgres to MySQL)

I bet there are some people around here who could help you troubleshoot. Just sayin' (I'm not among them.) A On Thu, Jul 28, 2016 at 09:34:05AM +1200, Patrick B wrote: > We can't use the pg_upgrade in our 3TB database just does not work.. > that's the main reason we're still using 9.2. --

Re: [GENERAL] question on parsing postgres sql queries

Kevin Grittner wrote: > On the other hand, try connecting to a database with > psql and typing: > > \h create index > > ... (or any other command name). The help you get there is fished > out of the docs. BTW I noticed a few days ago that we don't have a "where BLAH can be one of" section for

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 4:34 PM, Patrick B wrote: > We can't use the pg_upgrade in our 3TB database just does not work.. > that's the main reason we're still using 9.2. I think it's safe to say that that has absolutely nothing to do with the size being 3TB. They

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

On 07/27/2016 07:52 AM, thomas veymont wrote: 2016-07-27 14:11 GMT+02:00 Michael Paquier >: And do you see changes if you increase min_wal_size? This will increase the number of WAL segments recycled instead of removed at

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 27 July 2016 at 15:22, Scott Mead wrote: > "The bug we ran into only affected certain releases of Postgres 9.2 and > has been fixed for a long time now. However, we still find it worrisome > that this class of bug can happen at all. A new version of Postgres could > be

Re: [GENERAL] question on parsing postgres sql queries

On Tue, Jul 26, 2016 at 4:20 AM, Jerome Wagner wrote: > I am doing some research on postgres sql query parsing. > I was wondering what people think of the conformance with regards to the > real parser of the documentations on > -

Re: [GENERAL] Uber migrated from Postgres to MySQL

Patrick B wrote: > > > > I think it's safe to say that that has absolutely nothing to do > > with the size being 3TB. They symptoms you report are a little > > thin to diagnose the actual cause. > > might be... we're using SATA disks... and that's a big problem. But still.. > the size of the DB

Re: [GENERAL] Locking issue

On 07/26/2016 01:16 AM, David Harrison wrote: > Hi Josh, > > Attached is the function and below the query that calls it, below that the > result of SELECT version(); > > SELECT tl_guest_list('13313880', '174880', null, '151094636600', null, null); > > > > > > "PostgreSQL 8.4.9 on

Re: [GENERAL] Locking issue

On Thu, Jul 28, 2016 at 10:18 AM, Josh Berkus wrote: > On 07/26/2016 01:16 AM, David Harrison wrote: > where sps.sessionid = ses and > sps.rankid = rank and > ... > pg_try_advisory_lock(seatid) > order by s.row_number, s.seat_number_in_row > limit 1 > for

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

On Wed, Jul 27, 2016 at 6:22 PM, thomas veymont wrote: > as far as I remember these settings were for faster bulk loading. > > in 9.5 we set: > max_wal_size = 6GB # > https://www.postgresql.org/docs/9.5/static/release-9-5.html, max_wal_size = > (3 *

Re: [GENERAL] Multiple clusters with same tablespace location

Tom, thanks for the advice. I brought up a new instance yesterday, with the intent of trying it, and discovered that Wal-e with the "blind-restore" option would put everything in the pg_tblspc directory, instead of symlinking it. For this use case, that worked great. Nate On Wed, Jul 20, 2016 at

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 02:23:18AM -, Greg Sabino Mullane wrote: > > Marc wrote: > > I donât have a 1TB database to try it on, mind you, so your > > âwait couple of daysâ might be *with* the âlink option? > > I think you mean *without*, but yeah, there is no way the --link > option is

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 07:02:52PM -0400, Alvaro Herrera wrote: > Patrick B wrote: > > > > > > I think it's safe to say that that has absolutely nothing to do > > > with the size being 3TB. They symptoms you report are a little > > > thin to diagnose the actual cause. > > > > might be... we're

Re: [GENERAL] Uber migrated from Postgres to MySQL

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Marc wrote: > I donât have a 1TB database to try it on, mind you, so your > âwait couple of daysâ might be *with* the âlink option? I think you mean *without*, but yeah, there is no way the --link option is going to take that long. Hard

Re: [GENERAL] FTS with more than one language in body and with unknown query language?

On 15.07.2016 21:34, Stefan Keller wrote: I actually expect that stemming takes place for english and german. And we will in fact have queries in english and in german as well. So I think we still have some issues to resolve...? I performed the following things: - patch for PostgreSQL:

Re: [GENERAL] Uber migrated from Postgres to MySQL

> > I think it's safe to say that that has absolutely nothing to do > with the size being 3TB. They symptoms you report are a little > thin to diagnose the actual cause. might be... we're using SATA disks... and that's a big problem. But still.. the size of the DB is indeed a problem.

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 7:23 PM, Greg Sabino Mullane wrote: > Marc wrote: >> I donât have a 1TB database to try it on, mind you, so your >> âwait couple of daysâ might be *with* the âlink option? > > I think you mean *without*, but yeah, there is no way the --link > option is

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 7/27/2016 9:39 PM, Jeff Janes wrote: That depends on how how many objects there are consuming that 1 TB. With millions of small objects, you will have problems. Not as many in 9.5 as there were in 9.1, but still it does not scale linearly in the number of objects. If you only have thousands

Re: [GENERAL] Uber migrated from Postgres to MySQL

So, millions is a lot, but it's not difficult to get to a place where you have thousands or tables. Image a case in which census data and the associated geometries. https://github.com/censusreporter/census-postgres has 22 surveys, each with 230+ tables. That's 5000+ tables right there. Now, the