Application Development Example of PostgreSQL and pgpool-II
for DAI-ICHI HOKI co., ltd.
With the introduction of PostgreSQL and pgpool-II, a significant improvement in retrieval performance across large quantities of data such as legal precedents
From left, Publishing editorial office publishing support unit Mr. Yasuhiro Tanabe, Publishing support unit Mr. Shinya Hagiwara,
Publishing support unit Ms. Anri Naito, Editorial first unit Mr.Daisuke Muraki
DAI-ICHI HOKI co., ltd. is a company that sells law-related books and provides online data.
SRA, Inc. (SRA OSS, Inc. Japan group company) developed a system to handle legal precedent data.
They employed PostgreSQL as a database, and pgpool-II for redundancy and to improve performance.
Q: Please give a brief outline of the system you implemented.
One of our company's products is the D1-Law.com database. It is a service for customers to search laws, legal precedents, and law literature. The system that SRA constructed for us this time is the "D1-Law.com precedent system". This is a system that systematically organizes precedents and allows searches through a database of them.
"D1-Law.com precedent system" configuration diagram of DAI-ICHI HOKI co., ltd.
The points of the new system were "improved frequency of updates and improved search performance"
Q: What were the main points of this system renewal?
New precedent information is added daily. Previously, we provided customers with the added information on paper with a looseleaf publication. When we made this available online 8 years ago, there were many issues. Among them, the issues of the frequency of precedent updates and the search retrieval performance were particularly challenging.
In the past, updates used to be done with a frequency of once or twice a month, but we changed this to every day. Through this, we made the previous day's precedents searchable today. For search performance, we increased the speed to eliminate stress for customers even with large quantities of data.
Significant search performance improvement through PostgreSQL and pgpool-II
Q: What were your means of improving the search performance?
There were two main methods in the construction of the database, which were proposed by SRA.
We employed the open-source PostgreSQL in the database, and used pgpool-II middleware, developed by SRA OSS, to create a structure of separate databases for updates and for referencing. This enabled us to reduce the load on the database and improve search functionality. Even if the number of requests increases in the future, it is possible to increase functionality by adding a reference database.
Splitting write-intensive database and read-intensive database using pgpool-II
The other method was the use of pgpool-II's memory cache. In pgpool-II, there is a system by which database searches are cached on shared memory, so if another user sends the same request, the answer is supplied more quickly from the cache. This time, we took advantage of this to improve search functionality.
pgpool-II's in memory query cache
"No concern" about employing PostgreSQL for the core database
Q: Your company was renewing its core database. Were you concerned about using open-source software?
I had used PostgreSQL in the past. It is just my personal impression, but PostgreSQL feels like a very easy database to handle. It is difficult to break, and it is continuously being upgraded. The speed with which bugs are repaired is quite fast. I think that among RDBMS, it is the best choice.
I was not concerned about it being open-source. No one else around me voiced such concerns either. Precedents have a lot of ancillary information, and they have complex relationships. Furthermore, we plan to add more various information in the future. In such a data structure, I felt that open-source was rather maneuverable and easy to use.
"Peace of mind" for system operations personnel
Q: Is everything running stably since the release?
Yes. The system has been running for about a year now, and compared to the past, it is stabler. There are no performance-related problems either.
Actually, there was one instance of a system failure. But the cause was not PostgreSQL, but something else entirely. With SRA's proposal, we have redundancy in the PostgreSQL database using pgpool-II. We lost the ability to access one of the PostgreSQL servers, but because of the redundancy system in pgpool-II, this was automatically compensated for, and we were able to provide our service to our customers without interruption.
I have heard of many situations where redundancy systems do not work well, leading to failures, but, as I just said, thanks to the redundancy system of PostgreSQL and pgpool-II constructed by SRA, our operations are stable, which has been a great help to us.
From the standpoint of system operations, it truly provides peace of mind that when there is some kind of failure, the system automatically compensates and we can continue operations. In addition, I am grateful that even if the amount of data increases, the search performance does not deteriorate.
System convenience, spread by "word-of-mouth"
Q: What do your users think of the system?
The reputation is extremely good. Since the update frequency of our precedent information improved, you can now search for the previous day's precedents today, where the information used to be from one or two months before. Furthermore, from our company's perspective, in the past if there was some kind of typographical error, it took a month to correct it, but now we can fix it immediately. The search performance is also viewed quite highly.
It is thanks to this that our number of users is increasing as well. We are happy to hear feedback from our customers including public prosecutors and lawyers that they tried the new system and found it very good and easy to use.
Q: Please tell us about your company's future prospects.
Our company sells a lot of books, but there is still quite a bit that is not online. In the future, since smart devices are proliferating, I think we will be focusing more and more on methods of providing information through web services using the Internet. I would like to provide our customers with more and more information in a database format.
DAI-ICHI HOKI co., ltd.
Headquarters: 2-11-17,Minami-Aoyama, Minato-ku, Tokyo, 107-8560, Japan