Subscribe: A Hacker's Diary
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


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 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


A few days ago, I had released a brand-new PostgreSQL extension, called "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


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


Today, we pleased to announce the release of Postgres Toolkit 0.2. ■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


As you may know, Postgres-XL, a MPP implementation of PostgreSQL, was released  last month. Postgres-XL | Open Source Scalable SQL Database Cluster 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.


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


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


I'm pleased to announce my first PostgreSQL extension, pg_part extension, to take advantage of table (range) partitioning on PostgreSQL. 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


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)

Japan PostgreSQL Users Group received Achievement Award 2011 from DBSJ


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


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ユーザ (Google translated) The opening keynote

xlogdump 0.5.1 released


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)


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


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


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


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)


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


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


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: 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"


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


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


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


“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


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


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,