PostgreSQL CTO
5 2011 5 2011 9 2012 5 2013 10 2013 11 1 5000 JOIN
4
1. 2. 5
6
http://www.slideshare.net/mistakah/gpsgnss
Location Base ( )
PostgreSQL
x PostgreSQL 2011/8 MySQL MongoDB PostgreSQL GIS 2011/9 Launch PostgreSQL 9.0/PostGIS1.5 2012/1 9.1 upgrade Hstore 2013/9 9.2/PostGIS 2.0 upgrade JSON 9.3
PostGIS GPS - (ST_Distance) - (ST_Within) - JOIN Filter
PostGIS Postgis GEO 2009 PGCons http://www.postgresql.jp/events/pgcon09j/doc/b1-2b.pdf/view
PostGIS GoogleMap PostGIS
DB GIS http://www.slideshare.net/henrikingo/spatial-functions-in-mysql-56-mariadb-55-postgis-20-and-others
Hstore URL URL / ALTER Hstore Key/Value
Hstore Extension 9.1 Hstore - RDB KVS - INDEX(gist) hstore - Hstore Hstore - TEXT - 9.2 JSON (PostgreSQL 9.3
MessageQueue select * from mq where case when (schedule is null or schedule <= current_timestamp)) then pg_try_advisory_lock(tableoid::int, id) else false end order by id limit 1; SELECT dequeue) App INSERT(enqueue) PGSQL 1 Proc PostgreSQL Queue - ( PostgreSQL - - Scheduled Queue Python https://github.com/smihica/py-q4pg http://d.hatena.ne.jp/n_shuyo/20090415/mq
3. Listen Event Maneger 4. Fork Fire Event Event Runner API 1. Enqueue 2. Notify DB Execute event Event Runner 5. Dequeue Event Runner Listen/Notify JobQueue
Streaming Replication PostgreSQL hot standby SlaveDB WAL-e Heroku AWS postgresql Python OpenSouce https://github.com/wal-e/wal-e Wal-e S3 WAL AWS S3 Glacier Glacier 0.01 /GB S3 Glacier
WAL S3 Backup Amazon S3 Glacier Glacir Datacenter WAL WAL Streaming replication Primary DB Secondary DB Analytics DB StreamingReplication Wal-e S3 Analytics DB WAL Glacier
Glacier WAL S3 Backup Amazon S3 Datacenter WAL WAL Recovery Streaming replication Primary DB Secondary DB Analytics DB StreamingReplication Primary DB WAL
Ansible Puppet Chef Python Playbook Playbook VM Jenkins CI Alembic migration Alembic
Python Alembic DB DB Alembic 1. 2. DB 3. DB 2.
$ alembic revision -m "Add user table" $ ls alembic/versions/ 1b4b2e259907_add_user_table.py git DSL SQL DB def upgrade(): op.create_table( table_name, Column('id', Integer, primary_key = True), Column('name', String), ) def downgrade(): op.drop_table(table_name) DB $ alembic upgrade head App Deploy DB
$ alembic $ ls alembic/versions/ INFO [alembic.migration] Context impl PostgresqlImpl. INFO [alembic.migration] Will assume transactional DDL. Current revision for postgres://test@localhost/test: 4feb86442f82 -> 37a38bba9dfd (head), drop obsolete table and columns $ alembic history 4feb86442f82 -> 37a38bba9dfd (head), add user table DB 4feb86442f82 -> 57afagasduw4 (head), add shop table 39624ab9474b -> 4feb86442f82(branchpoint), change aaaaaa None -> 39624ab9474b, initial create tables
NewRelic - - - (PHP,Perl,Python etc ) - DB EnterpriseDB NewRelicPlugin ( Slow
- SQL 9/9 9:00-21:00 NTV NewRelic Index - DB Buffer - DB Warming -Master : m3.2xlarge standard 1000 IOPS -Slave : m1.large - APP - ELB PreWarming
- 3,000req/sec (API - 3,000 TPS - 16,000query/sec SELECT INSERT UPDATE AppServer DB PostgreSQL
Amazon RDS 2013/11 RDS PostgreSQL http://aws.typepad.com/aws_japan/2013/11/amazon-rds-for-postgresql-now-available.html - PostgreSQL 9.3 - PostGIS - Hstore/JSON - - IO ( 30,000 IOPS) - AWS RDS
7 PostgreSQL PostGIS Hstore, JSON AWS (RDS)
db tech showcase http://www.slideshare.net/yoshiyukiasaba/dbtechshowcase2013-smapo