Subscribe: A Hacker's Diary
http://pgsnaga.blogspot.com/atom.xml
Preview: A Hacker's Diary

A Hacker's Diary



Some thoughts around PostgreSQL and OpenSource.



Updated: 2018-03-23T21:21:48.061+09:00

 



pgDay Asia 2016: Call for Presentation has started

2015-12-03T16:24:48.094+09:00

As I sent the announce to pgsql-announce a few days ago, we are pleased to announce the first pan-Asian PostgreSQL conference in March 2016 in Singapore. pgDay Asia 2016 http://2016.pgday.asia/ pgDay Asia is the PostgreSQL conference series for all PostgreSQL enthusiasts in the Asia Pacific region. pgDay Asia 2016 is a 2-day event linking to FOSSASIA 2016, one of the largest FLOSS



sql_firewall: a SQL Firewall Extension for PostgreSQL

2015-08-29T17:00:52.307+09:00

A few days ago, I had released a brand-new PostgreSQL extension, called "sql_firewall". https://news.ycombinator.com/item?id=10109566 https://github.com/uptimejp/sql_firewall sql_firewall is intended to protect PostgreSQL database from SQL injection attacks by limiting SQL queries to be executed on the database. In this entry, I would like to introduce how it works and how to use it. How



[Postgres Toolkit] pt-table-usage/pt-index-usage commands

2015-05-21T18:23:39.952+09:00

In this entry, I would like to explain how to use pt-table-usage and pt-index-usage commands in the Postgres Toolkit which I introduced in the previous entry. If you have never heard about Postgres Toolkit, please visit the previous entry. A Hacker's Diary: Postgres Toolkit 0.2 released pt-table-usage and pt-index-usage are the commands which can be used to obtain usages of the tables/indexes



Postgres Toolkit 0.2 released

2015-05-12T16:22:10.763+09:00

Today, we pleased to announce the release of Postgres Toolkit 0.2. http://www.github.com/uptimejp/postgres-toolkit/ https://postgres-toolkit.readthedocs.org/ ■What is "Postgres Toolkit"? Postgres Toolkit is a collection of scripts and utilities which is intended to help PostgreSQL DBA to improve quality and productivity of their daily jobs and operations. With having Postgres Toolkit, DBA



Deploying Postgres-XL in 2-minutes with Chef/serverspec

2014-06-14T18:48:14.748+09:00

As you may know, Postgres-XL, a MPP implementation of PostgreSQL, was released  last month. Postgres-XL | Open Source Scalable SQL Database Cluster http://www.postgres-xl.org/ Most of recent topics in the PostgreSQL development are related to implementing data warehouse. And Postgres-XL is getting attention. However, such enhancement which consists of several PostgreSQL servers is a bit



monetdb_fdw: PostgreSQL meets column store. Finally.

2013-07-19T17:31:04.095+09:00

As you may know, FDW is one of the advantages of PostgreSQL. There are variety of FDWs to federate different data sources (including PostgreSQL itself) with PostgreSQL. And also, you may know everyone in the industry is talking about BigData and analytics everyday. We know that most of them must be a buzz though. :p Yesterday, I released a brand-new FDW, monetdb_fdw, which allows you to



PostgreSQL Unconference in Tokyo

2013-02-19T13:20:08.760+09:00

Last Saturday, we, Japan PostgreSQL Users Group, had the first PostgreSQL "Unconference" in Tokyo. The event had about 40 participants and 17 talks in 4 hours, and it was successfully run by the participants. As you may know, "conference" requires its host to arrange the program *much before* the event starts. As opposed to that, "unconference" doesn't. "Unconference" is filled with many



Announcing pg_part extension, a table partitioning utility for PostgreSQL

2013-01-01T17:12:15.606+09:00

I'm pleased to announce my first PostgreSQL extension, pg_part extension, to take advantage of table (range) partitioning on PostgreSQL. uptimejp/pg_part https://github.com/uptimejp/pg_part In the PostgreSQL world, the table (range) partitioning (a.k.a. Constraint Exclusion) is one of the advantages of PostgreSQL for analytical workloads, but using lots of DDL commands for this purpose still



pgstatview: Visualize your PostgreSQL in a minute

2012-06-01T14:41:42.432+09:00

As you all may know, understanding activities inside database management system is one of the important, but difficult and painful tasks for DBAs, not only for newbies, but also experienced DBAs. I saw many DBAs struggling with PostgreSQL because of such difficulties. Today, I'd like to introduce a brand-new tool, pgstatview. pgstatview (pre-alpha) http://www2.uptimeforce.com/pgstatview/



Japan PostgreSQL Users Group received Achievement Award 2011 from DBSJ

2012-03-06T13:23:04.183+09:00

Today, Japan PostgreSQL Users Group (JPUG) has been awarded Achievement Award 2011 from the Database Society of Japan (DBSJ), the largest (umbrella) database research community in Japan, because of its activities on database research, educating engineers and promoting database technology in Japan. This is actually impressive thing in the JPUG history, and in my mind. I really appreciate many



PostgreSQL Conference 2012 on February 24 in Japan

2012-02-03T14:51:02.323+09:00

Japan PostgreSQL Users Group will be having an annual 1day technical conference, PostgreSQL Conference 2012, on February 24 in Tokyo. There will be two keynote sessions and 13 sessions on PostgreSQL. (The photo was taken by @koyhoge at PostgreSQL Conference 2011) PostgreSQL Conference 2012 - NPO法人 日本PostgreSQLユーザ http://www.postgresql.jp/events/pgcon2012/ (Google translated) The opening keynote



xlogdump 0.5.1 released

2011-11-15T22:52:35.699+09:00

The latest xlogdump has been released. This version allows users to lookup object names from object ids for built-in database objects by reading the lookup table file instead of the system catalog. ---------------------------------------------------------------------- 2011-11-15 Satoshi Nagayasu * Version 0.5.1 * Allows to lookup the object names for



Performance impact of the pg_stat_statements module (Updated: Nov-04-2011)

2011-11-04T13:49:32.507+09:00

When I tweeted that I thought pg_stat_statements should be integrated to the core a few weeks ago, Josh responded me that it would be up to submitting a patch and figuring out the performace overhead. So, before starting a patch work, I've decided to figure out the performance impact of the pg_stat_statements module. The right pic is the results from pgbench, averages from 5 times runs each,



Loading data into UNLOGGED tables, and considering ALTER TABLE

2011-10-22T23:28:22.662+09:00

UNLOGGED table has been introduced in 9.1, and it would improve INSERT/UPDATE/DELETE performance in several situations. At this time, I'm curious how it would work well on data loading. To load large amount of rows into PostgreSQL, WAL logging would be a performance penalty, especially under the situation that have several tables on different spindles, because WAL logging forces data loading



Index-only scans and heap block reads

2011-10-21T02:56:37.580+09:00

As you may know, the Index-only scans feature has been committed to the PostgreSQL repository and it's available to all the developers. I'm very curious about such performance feature, and always want to understand what would change the things better and how it works. So, I just tried the index-only scans to determine what would be changed. The right figure shows PostgreSQL index scans and



pgbench on UNLOGGED table(s), Round 2

2011-10-07T22:04:12.813+09:00

As one pointed out in a comment in the last post, I have tried additional pgbench runs for "synchronous_commit = off" and "fsync = off". The results are shown in the right pic. It shows that "sync_commit = off" and "fsync = off" are almost the same, and these are faster as much as "UNLOGGED", except a tiny behind. I think the tiny behind means the difference between "sync_commit = off (or



pgbench on UNLOGGED table(s)

2011-10-05T00:30:23.996+09:00

To satisfy my curiosity, I just tried a quick run for pgbench with UNLOGGED tables. Under the pgbench transaction model, a kind of tpc-b, changing the history table attribute to "UNLOGGED" did not contribute to the performance. However, changing all tables to "UNLOGGED" improved the performance *substantially*. :) The results shown in the right image are averages of running pgbench 10 times



xlogdump 0.5.0 released

2011-09-26T23:36:01.908+09:00

Hi, pg folks, xlogdump is a tool for extracting data from WAL segment files. I'm pleased to announce the latest version of xlogdump, 0.5.0, after a few weeks from the previous release 0.4.0. I think xlogdump would be helpful to understand PostgreSQL behaviors, and you can enjoy brand-new features in this version, so please try it if you're interested in it. In this version 0.5.0, xlogdump is



xlogdump 0.4.0

2011-09-21T13:38:04.067+09:00

I'm pleased to announce the latest release of xlogdump. xlogdump is a tool for extracting data from WAL segment files. Here is xlogdump README: https://github.com/snaga/xlogdump/blob/master/README.xlogdump xlogdump was originally developed by Tom Lane and Diogo Biazus around five years ago, but not be maintained these few years. So, I picked it up and have fixed some problems and enhanced. It



PostgreSQL Query Cache - "pqc"

2011-03-01T12:02:47.784+09:00

I would like to introduce a new open source software, PostgreSQL Query Cache, which enables to improve query performance extremely (10x~100x) by caching query results in front of backends. PostgreSQL Query Cache: waits connections on the different port from the clients. delegates queries in front of the backends, like a proxy. intercepts and caches SELECT query results. also manages lifecycle of



5 steps to implement a PostgreSQL replication system

2010-05-17T02:30:54.923+09:00

Yesterday, I tried to build a PostgreSQL master-slave replication system with new feature "Streaming Replication (SR)", which is coming in the next PostgreSQL major release 9.0 as a built-in replication solution, and it worked well on my servers. I feel it is very easy to configure, so I have decided to make some memos here for building a PostgreSQL master-slave replication system. Here are 5



PostgreSQL 9 TestFest Japan on Apr.4

2010-04-01T21:52:48.264+09:00

Some Japanese PostgreSQL developers are inspired by the SFPUG’s TestFest announce, and we are now planning to have our TestFest at 3am-10am Apr.4 in Tokyo/Japan, which is equivalent to 11am-6pm Apr.3 PST in SanFrancisco. PostgreSQL 9 is going to be a very exciting release, and we hope to try many new things around PostgreSQL 9. So we have decided to have our TestFest as an all-night event,



PostgreSQL Cluster: “Too Many Projects” problem

2009-12-28T23:48:15.112+09:00

“Too Many Projects” problem The “too many projects” problem of PostgreSQL Cluster was raised and discussed in the PostgreSQL Clustering Developer Meeting at the JPUG Conference. Nowadays, the PostgreSQL developer community has many clustering/replication projects, but many of them are not production ready. Though, new project is starting, and old project is still unmature. I think this is one



Back to the OpenSource world

2009-12-05T04:53:34.500+09:00

Finally, I’m back to the OpenSource world. In 2004, I started working as a PostgreSQL developer and database engineer. While I was working as a PostgreSQL engineer, I was joining several PostgreSQL cluster projects, including Slony-II. Unfortunately, some were not happen, but it gave me great experiences. At the same time, I was working as a director of Japan PostgreSQL Users Group (JPUG), and



New Explain Format

2006-04-10T10:52:52.580+09:00

Bruce has just added a new todo item. - Allow EXPLAIN output to be more easily processed by scriptsI think the well-formed xml is a better format to express the tree structure of the explain output. I don't know xml can be processed easily by *scripts*, but it is easy for the utility programs using xml processing libraries. And xml elements can have many attributes which can be used to hold rows,