A Tale of Two Oracle Migrations

Video thumbnail (Frame 0) Video thumbnail (Frame 1377) Video thumbnail (Frame 2297) Video thumbnail (Frame 4090) Video thumbnail (Frame 4902) Video thumbnail (Frame 8522) Video thumbnail (Frame 9577) Video thumbnail (Frame 11095) Video thumbnail (Frame 12815) Video thumbnail (Frame 14320) Video thumbnail (Frame 16022) Video thumbnail (Frame 18665) Video thumbnail (Frame 20042) Video thumbnail (Frame 22825) Video thumbnail (Frame 24302) Video thumbnail (Frame 25082) Video thumbnail (Frame 26795) Video thumbnail (Frame 28020) Video thumbnail (Frame 29370) Video thumbnail (Frame 33610) Video thumbnail (Frame 38737) Video thumbnail (Frame 39905) Video thumbnail (Frame 41122) Video thumbnail (Frame 44137) Video thumbnail (Frame 46220) Video thumbnail (Frame 48725) Video thumbnail (Frame 50512)
Video in TIB AV-Portal: A Tale of Two Oracle Migrations

Formal Metadata

A Tale of Two Oracle Migrations
Deploying PostgreSQL at Japan's second-largest stock exchange
Alternative Title
"A Tale of Two Oracle Migrations"
Title of Series
Number of Parts
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
In this presentation I'll talk about two migrations from Oracle to PostgreSQL - the first back in 2001, which is how I got into PostgreSQL in the first place, and the second in 2013 involving the second largest stock exchange in Japan. One of the hotter PostgreSQL topics recently is the increasing trend towards enterprise-scale migrations from Oracle and other commercial databases to PostgreSQL - not only for cost savings but also for its rich and expanding feature set combined with a solid reputation for reliability. But it's hardly a new trend - that's how I first came across PostgreSQL back in 2001. I'll give a brief and hopefully humourous description of what I did back then (with some pictures of cute animals) and how it led to the Japanese stock market. Which is where I ended up in charge of the database underpinning the back office of Japan's second largest stock exchange, SBI Japannext. Since late last summer this has been powered by PostgreSQL and the changeover from Oracle has gone very smoothly. The main part of this presentation will be about how this migration was planned and implemented, and the foundations it has created for further adoption of PostgreSQL.
Process (computing) Slide rule Insertion loss Oracle Form (programming)
Multiplication sign Boom (sailing) Database Insertion loss Coma Berenices Mereology Product (business) Programmer (hardware) Mathematics Network topology Internetworking Hypermedia Implementation Oracle Task (computing) Boss Corporation Focus (optics) Slide rule Internet service provider Bit Database Complete metric space Connected space Human migration Internet service provider Boom (sailing) Oracle
Human migration Medical imaging Slide rule Open source Key (cryptography) Multiplication sign Computer-generated imagery Database Database transaction Procedural programming Oracle
Slide rule Vacuum Open source Confidence interval Multiplication sign Source code Student's t-test Replication (computing) Coprocessor Goodness of fit Sign (mathematics) Computer configuration Data structure Physical system God Email Graph (mathematics) Slide rule Stress (mechanics) Database Human migration Personal digital assistant Website Table (information) Oracle
Frequency Process (computing) Exterior algebra Slide rule Software Personal digital assistant Software testing Physical system Oracle Physical system
Area Group action Graph (mathematics) Slide rule Execution unit Shared memory Volume (thermodynamics) Bit Mereology Mereology Flow separation Rule of inference Product (business) Internet service provider Speech synthesis Right angle Figurate number Volume Oracle Physical system
Dataflow Service (economics) Slide rule Twin prime Computer-generated imagery Data center Volume Oracle Connected space
Dataflow Server (computing) Service (economics) Link (knot theory) State of matter Direction (geometry) Connectivity (graph theory) Multiplication sign Database Real-time operating system Client (computing) Mathematics Telecommunication Internetworking Directed set Integrated development environment Software testing Office suite Traffic reporting Oracle Physical system Social class Slide rule Dataflow Client (computing) Database Integrated development environment Data warehouse System programming Video game Software testing Faktorenanalyse Physical system Resultant Oracle Row (database) Traffic reporting
Dataflow State observer Building Divisor Open source Workstation <Musikinstrument> Latent heat Internet forum Computer configuration Integer Physical system Source code Workstation <Musikinstrument> Slide rule Server (computing) Graph (mathematics) Open source Shared memory Bit Control flow SI-Einheiten Tangent Computer configuration Integrated development environment Personal digital assistant System programming Freezing Window Oracle
Scheduling (computing) NP-hard Slide rule Broadcast programming Planning Bit Database Cartesian coordinate system Mereology Planning Human migration Process (computing) Term (mathematics) System programming Whiteboard Renewal theory Oracle Physical system Oracle
Backup Scripting language Slide rule Workstation <Musikinstrument> Computer program Parallel port Insertion loss Dynamic random-access memory Power (physics) Hand fan Impulse response Personal digital assistant Military operation Revision control Software testing Software testing Office suite Resultant Oracle Physical system Oracle Exception handling
Classical physics Boolean algebra Multiplication sign Patch (Unix) Numbering scheme Floating point Data storage device Medical imaging Mathematics Type theory String (computer science) Damping Configuration space Data conversion Right angle Condition number Oracle Scripting language Slide rule Timestamp Human migration Uniform resource locator Personal digital assistant Website output Data conversion Whiteboard Table (information) Oracle
Scripting language INTEGRAL Direction (geometry) Multiplication sign Decision theory Database Insertion loss Shape (magazine) Order of magnitude Type theory Military operation Office suite Data conversion Extension (kinesiology) Error message God Physical system NP-hard Stress (mechanics) Internet service provider Bit Mereology Variable (mathematics) Flow separation Data warehouse Order (biology) System programming Software testing Data conversion Summierbarkeit Procedural programming Quicksort Volume Physical system Service (economics) Gene cluster Data storage device Planning Network topology Energy level Directed set Integrated development environment Configuration space Implementation Default (computer science) Standard deviation Slide rule Dataflow Computer program Mathematical analysis Line (geometry) Cartesian coordinate system Query language Personal digital assistant Boom (sailing) Revision control Statement (computer science) Faktorenanalyse Table (information) Window Oracle
Boolean algebra Slide rule Floating point Database Database Data warehouse Statistics Flow separation Product (business) Wave packet Human migration Data warehouse File archiver Right angle Right angle Oracle Electric current Physical system Row (database)
Point (geometry) Backup Game controller Code Source code Numbering scheme Control flow Temporal logic Frequency Customer relationship management Term (mathematics) Query language Mathematical optimization Partition (number theory) Oracle Mobile Web Slide rule View (database) Data storage device Shared memory Database transaction Database Data warehouse Human migration Process (computing) Software Query language Data warehouse Revision control Speech synthesis Quicksort Procedural programming Mathematical optimization Oracle Row (database)
Point (geometry) Service (economics) Slide rule View (database) Multiplication sign Software maintenance Human migration Customer relationship management Operator (mathematics) System programming Revision control Data conversion Procedural programming Window Oracle
Slide rule Mereology Human migration Process (computing) Human migration Term (mathematics) System programming Data conversion Whiteboard Endliche Modelltheorie Office suite Oracle Physical system
Functional (mathematics) Multiplication sign Numbering scheme Cartesian coordinate system Neuroinformatik Impulse response Velocity Atomic number Partition (number theory) Thumbnail Oracle Boss Corporation Slide rule Relational database Planning Bit Data warehouse Price index Sequence Word Query language Normal (geometry) Table (information) Oracle Spacetime
OK and the minority there and right so ostensibly
with glamor unending barley can be using on British I lived in Germany for 15 years and this loss if you have been living in Japan happy using PostgreSQL professionally continuously since 2001 you may have seen name made this noun announcement however this is a tell us he acting never ever met anyone from any form of phosphorus community and systematic critical never and now all since April of the work I have the pleasure of working for 2nd quadrant and in this talk I'll be talking about my previous job which was looking to on for a finance company and human migration on the stock exchange and that's
agenda and when those things put together I thought it might be a little bit unwarranted just about the oracle and my thinking about thinking that when I start the reason why I found phosphorus in the 1st place was because when I started off by 2001 my 1st task was to migrate our collective assault going up little bit and this to show the PostgreSQL and even back then it was pretty awesome and then I'll talk about the year and focus change migration and nothing
about 2001 and in the year of the dot com boom and all kinds of countries creating all kinds and then the nebulous products and can't make money off that get like that with them with Internet connections and by the time I was working in germany frequently called other media which was then given this online and even a learning provider and now this is kind of like they also includes the time to complete movements venture-capital grants and financing in the early days of the Internet no and you will be doing as far as the went so they're just from boatloads of money at some programmers support stuck suddenly 2001 carrying the whereabouts need to make a profit on economic loss and they started cutting back so that I found myself as part of a very useful in my teaching and the bosses looking around with although Oracle database and let's get rid of that and you can use them to migrate up my SQL city I thought I was quite a and B and what they do it's the the yeah sure no problem let's do it and so I started looking at my school l and at the
time I slowly was the open source database cruel and I loved it down I could have this image of it to be like this I looked into its this is the 1st time
that I based I started from I saw the migration Oracle Fusion for things like foreign keys procedures transactions and get my so I thought no this is not going work and so 1 by the
graph options at the time there wasn't really much in the front of the source databases went unnoticed and muscular which is pretty much only way out there was Interbase Firebird which which has been open source haven't released by uh woman you know which sure what it was and I started to write this and database with phenomenon called posc rescue Ellison think people were saying you know some is good but it's come slowly as if factions thing which way too difficult to work with by checked out anyway and I it seemed to have all the prerequisites which we needed at times um except the year and it was actually the people bomb slow slide back but I can't think of a problem because this migration and the year system was supposed to be reorganized so the database will become the website and obviously you you don't want would like to be and slow down by the database of the fact the time so I you know 2001 I send a e-mail to you the post stress mainly students questions about that I got some varies replies certain things tumbling health complaints and which they have the confidence to to matching affordable with migration and to the good of what I was able to do the out here the whole world vacuum she was I wrote a very simple and trigger-based replication system replicated those tables which necessary for the website to where the people could a register sign up make payments directly get updated to mice to OWL which was an island called quantity because it's a new cables and so are and Indiana map processor actually that was not able to do that his time intestinal very much on them yes it's a not being used in PostgreSQL for too much for everything now and
fast forward 12 years of + stresses come a long way uh at the start of 2013 I was ability and for about 5 years um the blue cruiser competently asked me if I'd be interested in helping them what they called upon sitting on the stock exchange migrate assistant for more of phosphorus what's the structure was like 0 my god this and and don't my finances like all wet weather minor-league violence following told them to find out what they need it and by the end of them being hired for you to assist without migration so this is
helpless with tools stock exchange is that it was written iStockphoto to this there and so I mentioned test
before and it funds for proprietary trading system uh um these alternatives to establish stock exchanges which originated in the 19 nineties in the US mainly yeah together with the right electronic trading and the increase improvements network capitalism well if there often associated with high frequency trading and that's not everything um that of course available to institutional traders only so he's the need to go through brokered straight on um show interested in more detail on industry visible called dark pools by glycols competition whoever it is a very good job of describing I case so
the company I was looking for called SPI to come next um based upon 2nd largest trading then you off the top of the exchange and it's 1 of produces and you of cortex at don't go into it was established in 2006 quite a while ago growth speaking uh is part of the sky group which is a largest banking groups in Japan which was written only part of the political Soft user Edicom provider males at which it took over and sprint in the US but it's been spent then and is now not separate from soft it gives Japan and they do mainly yeah and we shall 1 thinks they run Japan's largest online the stock trading system which is not part of our next that's a separate business units and and currently they account for around 5 % of the district exist trading volume in Japan the idea of the
business rules and starts revivalists to this is from 2010 to 12 in the 2013 so 4 3 3 4 years ago there were very small growth can reach again until and 2013 um figure hard to read on the rights in the market share helps steadily growing be leading bottle market volume a portion of the graph using was in um 100 million is about 1 1 million US 1 billion and opinion is about 1 billion US dollars so uh in the last you know it it would doing between 1 and 10 billion US dollars with the production today which may name may not be alignment demanding perspectives and since then growth is no bit but it's still and in that general area so I will explain a bit
about how trading actually happens and it's not like fish and then there's no trade and flow the iraq shouting waiting this paper then exists but sorry
and guess it's bunch of service
and these are in a very large data centers which built on reclaimed land interlinking of a which also happens to be where subject has its service and we're pretty much anyone the anyone in finance and he keeps the services the the latency of course is very Strange Places in light of all all about certain now depending where only a the rooms located therein if you'll connection go that's the wrong way you all you would have the disadvantage that your competitors because your data really able to Malta to reach the and extensive and so um
trading flow which would like this we have the political front office with actual trading takes place that's a writing system and bought in from Nasdaq electrical strokes are about horrible green it that thinks there are direct links from co client system when intimate with pockets Internet tools most many classes in Russia co located within the same server room again for a and B vacancy issues and um was called matching engine um somebody's the disappointed that actually not database based um but it does generate a large amount of data and but the record of all of all trades think about 40 Miracle Day with more recently uh and that's exporters of CSP data to the back and which would those western play because it needs to be yeah stored as reliably as quick as possible he didn't have to be quite real time and there is a delay of firms the 2nd so minutes depending on circumstances but it does need to be told reliably and from that database um the flight reporting intellectual reporting also seen chicken provided trading all that kind of stuff is that is done that so on previously had basically oracle and now prosperous and now
why would a company like that with migrant post press um technically the system there's no component to need to migrate moral looking for time um however the system the service that were running on was really long were region the end of life to replacing um Lucky bowling was picking up which results in a reason to to a great systems and and also general requirement to reduce costs and not that making a loss but in unified will the finance obviously they ever sensitive about money and then the problem was they need is to maintain not only production environment but also testing of our customers which was not really a production environment but still involve the license fees and states incentive to make changes in uh
y plus was particularly well costs and freezing there isn't 1 factor they also looks into options think check was evaluated mice growl with think thank you will that what would it be and this what there quite a few people he's known within and they had to that shares into it and because of the book and the main reason why rejected because they thought they would have to rewrite all their skulkers which would mean too much with indicators and that they would have had other problems and they were already using imports house for internal systems like he represses trucker so that wasn't familiar familiarity with it and the note that no 1 actually with it directly and this is not the fact of very important is you can sit there you can look at look at specifications and say OK this should work this crude prices are data but some are we sure all and where things like case that is a very important and like it's a bit freaky because when I was contacted by the recruiter I don't know that is completely so I asked among people I know integer if they knew about it and turned out I news so I need to buy a forum adding new nodes may no idea what he did he said all I need is complete long out of the air flow into another building it turned out that he would untidy he previously deployed prosperous he this company and and he had up to recommend the CIA to the various bodies prophesy for yes it does work it's for a good and that was once why they decided to phosphorus and like tangent
and a wide open source in a way I mean you might imagine funds completed topical is amazing throat why isn't since them and in this case it was a bit of a and again as money is a slope was in that they want to save money books and a very important reason is they do not want to be vendor-dependent they can avoid it because it is a very high performance environment you want to know your systems and you want to have people in has on graphs when something goes down you're not paying on phone to support and so pretty much where possible in everything is an open-source also that we that your Windows 1 and look workstations the winner in Oaks um pretty much all the internal observations LeanUX and yeah so the
migration planning started around of 2012 before I came on board that they said a hard deadline of 2013 food sounds quite short term the reason was I think grand auto 2013 year various licenses renewal and they also move forward to the opportunity to restructure the existing systems which has grown rather constantly over the last few years um
so migration schedule look like this is kind of a bit hard to read um special about divided into 3 parts they set crescent of humor and then move on to committing applications the applications which took the term for most on our luckily for fortunately or the activation access the database were enhance custom applications the didn't they were not bound to external vendors which made the process a lot easier than my the voice of being also probably in somewhat disappointed with the way that we use oracle was actually fairly straight forward so there were known to be truthful this OK this oracle does this because of can't conduit it um so these changeover
starters um rounding 2039 imported data from Oracle released Oracle Data wants the year top stations have been ported they started running them parallel office promise to oracle so for about a month so so from the 2 systems were running up to parallel to sort out any as final tests and unable to testing and results Testing the
switch over went like this the power Powell come running was in August 2013 1st Monday in September 2013 and impulse was switched to primary oracle with cleft running as a backup in case you have something in the fan and it would be be possible to switch that any data loss and with the exception of some few small issues yet to run very smoothly so by October of Oracle was switched off and so it's like it's going to and and since then um it's being run entirely on PostScript's Um
k few issues encountered during migration and the role of classical or issues still some of of the of the with location of a big issue and the so many scripts and knowing that the whole image in the Perl would be Python PHP in the world so many places where the assumption was OK column names they will be coming up next that's CSI insurmountable he just takes time to go through everything and not handled by the change at all to ensure that the partnership and know the classical problem is an overseas and string on Oracle has a slight some issues with Knowles and which was a problem because the year when importing get data from Oracle um in quite a few places they forgot to convert and functional which meant that isn't the weekend in the summer will to go in and do the thing from scratch again that didn't striving actually to send in patch to improve the public from which would be very useful time and other big problem is 1 this is an issue which probably have um occurred because no 1 when the and conversion began no 1 had any pickup experience of before I came on board and they basically the very simplistic condition of the much the oracle schema to Posterous um which is schemes you quite a from produces so many inefficiencies if you have a very large datasets for example in oracle the dates and basic site is actually a timestamp as in many cases they were storing the data is not the time but this is the input of phosphorus say that he committed to a timestamp so you had these very large tables where only they papers being stalled which but it was the diseases for why day column but everything 8 by 10 pounds of gold and which doesn't have a lot of time when you're dealing with them vapor tables on the Commission for they the data 18 days training data table uh meaningful that 40 million rows and make this into a depth and
so how to say to myself
um yeah SQL statement filter queries would need to be converted to them again as an insurmountable it's there was no error um there are also cases where queries with from fine novel window dropped and for stress that that in Oracle delivering in less than a minute for example of a very large extent stress and as he's they were taking several orders magnitude longer which was a problem because the year the at 14 mentioned before but there was some internal resistance to movement for stressed because the deviation only Oracle people and they're using using all my god out crusade taking some so long analysis such as but there's actually nothing which couldn't be resolved by the Russian Aquarius cup and exceed is help lot with that then stored procedure conversion was also a bit tricky sometimes so there is some recommendation prosperous stocks that if you use Oracle's pockets level variables you can seem like an even temper tables so no obvious here at that and it's implemented some of them quite critical procedures which then went on to create loss and some a very expensive temperature time around uh and she had to direction Cheney knowing when I got there they were still using the defaults and integration 128 megabytes and shape of the sum 642 system uh that was sentenced to 6 and 1 problem if you remove the analysis of all the kind advice don't kill dashed line postmaster @ for HA solution they used in house and pairs of clusters running the RGB and H pieces gossip and when he fell over service got if the applications that shut down cleanly within a certain time so this Scott decision about which no I wasn't aware of no 1 else was aware of but it turned out that sort of thing over um office of being kills the puddles of being killed off like that and we're getting this corruption and in that's never which was solvable and then that's the data like in it's
2014 I still thousand from 1 the database you know properly in um what is right is in it and the 1 way of doing it and so you'd be surprised how many different ways are with and uh and see what you now unlike the original founders migration was too tight to consolidate all these systems can of it unfortunately yeah icings I knew it was things go in how I would like to donate it but it's just a you say that the late so
um I can't give too much detail about systems as all the March 2014 the daily production database which contains contains the data last 4 months so is about 2 terabytes below 40 million rows being that each training session has grown by to 20 to quot daily and they also have a requirement to archive all basis for about 10 years and it's going data warehouse which about 8 terabytes set as a separate database um yes so after my what's initial
migration is completed they still have a lot to do you would going and now you look at the code to the queries is still the obvious oracle database him like with with the ampullae and so there would be a lot more which could be done especially the data thought scheme optimization based something nothing can we shouldn't starts but no 1 really rethinking about its and imports speech the we were dealing with 40 million rows day um + Western packed away no problem um he she was however starting to occur so we have a high frequency trading semantic it bursts of transactions which turned out and toward the end of the trading day so and it was starting to like and we ended up going to a great sermon breaks to my solution that's um backups again numerous a slight problem because you have taken based back base buckets of the we each week and I and it was taking in more than a day to do and actually the bucket was what's the point with was to running wild trading with half an inch it's was fine but not send something to do you want of having and and the problem is like that we have these these huge data warehouse uh getting tricky to run some sort some queries on and they were starting to look at the solutions including some putting the data into something like him see store access enough of foreign data off of um yet
common complaints and requests that an awful lot to flat campestris call 1 you will about that's partitioning partition quite a lot and it works but terms they were interested in mobile um controls solutions they also made and a lot of use of quite complicated use and problem is enforced reciprocity you source is not stored so they were actually define the installed procedures and expertise executing from that and upgrades were also something they weren't sure about there were 9 . 2 they would have liked to have it's my 3 at some points puts them here um no 1 will we want show how the best way to go about that and the other is a she's temporal queries you see a lot the decrees and other people the BBB B share the deal would idle except for the warm creates software process and share the
conclusions my secure 1 be yeah I yeah I mean you have to
pull the world was on wasn't well over 2 places with that much the snow no 1 was at that point was sure how the procedure would go what the best way of go about it would be uh and that is for all and my way of being 1 of the 2 of you will know and of all the time Molly um DHA was done was implemented selected with the RGB and its it service got and fortunately this is not the principles of operation only needs to be working during trading are our so there were some windows for the maintenance you so like is it there were quite a few
problems it came up because they started the current migration that much cost with the City Knowledge so if someone is doing that then they do need to get some board about PostScript to make sure you don't end up in into a corner um ideally you should and should not be converting from Oracle put 2 plus crests and 1 1 a model on the other end of the post process was to make the system databases database false possible so if in the future in a that I did it in light of threatening it would be the less strict heating something else and also an opportunity to shut the system and like I said it didn't really have to um yeah and but you should treat migration is an investment which may cost more on short term um parts you could take opportunity should they were at the time and so you um in
Japan officers lecture it's difficult to say year because the 2 are equal to with a much out which is Japanese for thank cannot thank you for not falling asleep and
1 final word from my and my boss he said that cooperative hiring anyone interested than about what is the relation of I will give it here um colleges will indices in the normal course with sequences of movements of in their union is the origin here this evening and trigger yeah using both with the fall of the world was lost yet it is when do with Dick they ended up doing the in know the sequence the D full-body soft yes to set it yet I did atoms here and what is it there was 1 thing is want proceed it without actually issues and I believe that she initially converted it using temporal tables uh actually looked at that and what they should have done is that national to do that without it possible to rewrite function in another way it was a mistake in space conflation some data from and when the table which we could have done by computers on that table and he and makeup table globally that'll in certain I don't think so like it or anything a slight born to that the oracle scheme was fairly simplistic and so forth and around to many as important to report all interesting solutions that you were at the top here that was the 1st of all I was tending to the plus 9 2 of with year 1 year we got here for what 1 would hear the word I I want my um because they don't only and of course it would unary queries overrun partition tables unlabeled producing as is they were producing very and inefficient plants an impulse crests and with what we have here velocity joins the queries involved them I don't I don't have any and all kinds of all year that was a plane and you change the the amount of time it it was pretty much the same like sitting I was a bit surprised it was a question solicitation spatially a data warehouse with a bunch of tail of the plane thumb data and but when I arrived in hot warm formed in the universe and hand-marked The