PostgreSQL (14) 썸네일형 리스트형 count_distinct improvements count_distinct improvementsAlmost a year ago, I wrote a custom experimental aggregate replacing COUNT(DISTINCT). The problem with the native COUNT(DISTINCT) is that it forces a sort on the input relation, and when the amount of data is significant (say, tens of millions rows), that may be a significant performance drag. And sometimes we really need to do COUNT(DISTINCT).Extensibility is one of t.. postgresql upgrade postgresql upgrade 하는 방법1. dump 백업으로 복구하는 방법. 이 방식은 데이터 싸이즈가 큰 경우 downtime이 많이 발생 하므로 사용하기 힘든점이 있다. 2. pg_upgrade 사용하는 방법. 이 방식 또한 downtime이 발생한다. 내부적으로 dump로 restore하는 방식이다.* http://www.postgresql.org/docs/9.3/static/upgrading.html 3. Slony-I(Community), xDB (EDB) Replication 이 두 가지 방식은 Replication을 이용해서 upgrade를 한다. 두가지 모두 DB 버전이 다른 경우에도 사용이 가능하며, downtime을 최소화 할 수 있는 방법이다. Slony-I(Community) .. trigger-overhead 출처 : http://www.openscg.com/2014/05/trigger-overhead-part-2/I found a bit more time dig into timing of triggers and their overhead so I wanted to see how much overhead the choice of procedural language affected performance. I followed the same testing methodology from my original trigger test. For this test I created an empty trigger in the following languages:PL/pgSQLCREATE FUNCTION empty_trigg.. 9.4에 추가 되는 Statistics Collector VIEW pg_stat_archiver 9.4에 추가 되는 Statistics Collector VIEW Table 27-3. pg_stat_archiver ViewColumnTypeDescriptionarchived_countbigintNumber of WAL files that have been successfully archivedlast_archived_waltextName of the last WAL file successfully archivedlast_archived_timetimestamp with time zoneTime of the last successful archive operationfailed_countbigintNumber of failed attempts for archiving WAL fileslast_fail.. Making sorts happen in memory instead of on disk Making sorts happen in memory instead of on diskIf you have disk-based sorts occurring frequently, you can find the size of the largest of the frequent queries and then set this as the standard threshold for sorts. The parameter for this in/var/lib/pgsql/data/postgresql.conf is 'work_mem'. For example, if you had frequent disk-spaced sorts using up to 64MB, you would set the following in /var/li.. PostgreSQL Concurrency With MVCC How MVCC works in PostgreSQLLet's look deeper into how MVCC works in PostgreSQL to allow "no-locking." Each row in PostgreSQL has two transaction IDs: a creation transaction ID for the transaction that created the row, and an expiration transaction ID for the transaction that expired the row. When an UPDATE is performed, PostgreSQL creates a new row and expires the old row. It's the same row -- .. How To Setup Ruby on Rails with Postgres IntroductionPostgres (or PostgreSQL) is an open source database. Ruby on Rails is an open source web framework written in Ruby. Rails is database agnostic, meaning it can be used with a variety of different databases. By default it assumes that MySQL is being used, but it's quite easy to use with Postgres instead. This guide will step you through creating a Rails application that uses a Postgres.. postgresql & ruby & gem pg install ==========================================================================================================ruby & gem pg install yum install rubygemsyum install ruby-develgem install pg==========================================================================================================[root@ppas ~]# gem install pgBuilding native extensions. This could take a while...Successfully installed pg.. 이전 1 2 다음