Re: [GENERAL] Benchmarking partitioning triggers and rules

ting the data directly into the right partition (without any triggers). -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] Benchmarking partitioning triggers and rules

CUTE '...') is simply more expensive, as it needs to do more stuff (on every execution). There are reasons for that, but you may think of it as regular queries vs. prepared statements. Prepared statements are parsed and planned once, regular query needs to be parsed and planned over and ov

Re: [GENERAL] Slow query with join

1 FROM f_intervenant_ref WHERE (actor_id = ir_actor_id) AND (ir_dos_id = '5226')); or even an explicit join SELECT v_actor.* FROM v_actor JOIN f_intervenant_ref ON (actor_id = ir_actor_id) WHERE ir_dos_id = '5226')

Re: [GENERAL] DB on mSATA SSD

rather terrible idea. In case of crash you won't be able to even start the database, because it will try to recover the tablespace. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgs

Re: [GENERAL] DB on mSATA SSD

power-loss protection (which you need for a database, although a UPS may help a bit here). But maybe that changed recently. If you're on Linux use smartctl. Also, as others have pointed out 8.4 is out of support, so consider upgrading. +1 to this -- Tomas Vondra http:

Re: [GENERAL] DB on mSATA SSD

equent and tuning the kernel/mount options may help a lot. Putting the DB into RAM and backing up periodically to disk is a validsolutions? Well, that depends on your requirements. You may lose the changes since the last backup. Or, is storing indexes on a ram drive possible? No, not really.

Re: R: [GENERAL] DB on mSATA SSD

database to work after a crash or a reboot. You might keep a snapshot of the database (e.g. using pg_basebackup), and use it to 'seed' the database after a server restart. But don't expect the database to start without a tablespace that got lost because of being placed in

Re: [GENERAL] BDR Across Distributed Nodes

to 10.0.0.1. So something like this: # from 10.0.0.1 psql -h 10.0.0.2 -p 5432 demo # from 10.0.0.2 psql -h 10.0.0.1 -p 5432 demo If that does not work, you probably need to investigate - firewall settings, pg_hba.conf and postgresql.conf (maybe it's not listening on this address)? regards

Re: [GENERAL] Block Corruption issue..

rror, please immediately notify the sender at Cyient and delete the original message. Sure ... -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] Planner cost adjustments

tting random_page_cost = 2 (instead of default 4). Sadly, the original post provides very little information about the context, e.g. database size, storage system, explain plans etc. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remot

Re: [GENERAL] Planner cost adjustments

leases. work_mem = 16MB maintenance_work_mem = 256MB checkpoint_segments = 64 checkpoint_completion_target = 0.8 effective_cache_size = 10GB logging_collector = on track_counts = on autovacuum = on Otherwise, I don't see anything terribly misconfigured. regards -- Tomas Vondra

Re: [GENERAL] Help me recovery databases.

ses without /base? I have archive_status folder. -- С Уважением,Генералов Юрий -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] advocating LTS release and feature-train release cycles

with this topic. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_start_backup does not actually allow for consistent, file-level backup

pg_start_backup takes care of that. If you don't like that, you have multiple options - stop the database while performing the backup, perform file system level backup (e.g. lvm snapshot) or use tools like pg_basebackup. regards -- Tomas Vondra http://www.2ndQuadran

Re: [GENERAL] Turn off streaming replication - leaving Master running

data directory, remove the recovery conf and start it again as a standalone database? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.o

Re: [GENERAL] Forced external sort?

entations of the data, and the on-disk is much more compact. It's not uncommon to see 1:3 ratio, i.e. when external (on-disk) sort needs 100MB, the in-memory sort would need 300MB. So when the on-disk sort needs 1253824kB, you'll probably need ~4GB work_mem to actually do that in mem

Re: [GENERAL] Postgresql 9.4 and ZFS?

not as tightly integrated with the kernel, but it should release the memory when needed and such. Perhaps not letting it to use all the RAM is a good idea, but 1/3 seems a bit too aggressive? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, R

Re: [GENERAL] Postgresql 9.4 and ZFS?

ecently done on SSD, EXT4 / XFS are ~2x faster than ZFS. But of course, if the ZFS features are interesting for you, maybe it's a reasonable price. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Servi

Re: [GENERAL] Postgresql 9.4 and ZFS?

On 09/30/2015 07:33 PM, Benjamin Smith wrote: On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: I think this really depends on the workload - if you have a lot of random writes, CoW filesystems will perform significantly worse than e.g. EXT4 or XFS, even on SSD. I'

Re: [GENERAL] Postgresql 9.4 and ZFS?

On 09/30/2015 03:45 PM, Patric Bechtel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Tomas, Tomas Vondra schrieb am 30.09.2015 um 14:01: Hi, ... I've also done a few runs with compression, but that reduces the performance a bit (understandably). I'm somewhat su

Re: [GENERAL] RAID and SSD configuration question

#x27;Always Write Back' allows the controller to buffer writes beyond what the devices do. AFAIK there's no difference. It's an option that disables write cache in case the battery on BBU dies for some reason (so the write cache would become volatile). With capacitors this is n

Re: [GENERAL] RAID and SSD configuration question

vulnerable to "write hole" problem. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] Avoid sorting when doing an array_agg

for free, especially with arrays of ids. regards -- Tomas Vondra  http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Strange activity of prepared queries

of the time the planning is fast enough not to be logged, so it's invisible for pgbadger. regards --  Tomas Vondra  http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Tomas Vondra  http://www.2ndQuadra

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

; Have you looked at the Postgres log entries that cover these > episodes? > > Is there anything of interest there? > In particular look at checkpoints. In the config file you've changed checkpoint_timeout, but you haven't changed max_wal_size, so my guess is the checkpoints ha

Re: [GENERAL] Special index for "like"-based query

s also possible to use simple btree index with varchar_pattern_ops. https://www.postgresql.org/docs/9.6/static/indexes-opclass.html regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent

Re: [GENERAL] Special index for "like"-based query

On 12/30/2016 12:46 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra mailto:tomas.von...@2ndquadrant.com>>wrote: On 12/30/2016 12:33 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:21 PM, Job mailto:j...@colliniconsulting.it>

Re: [GENERAL] Index impact on update?

ng the long update in this case - there may be other bits slowing it down - e.g. foreign keys checks, triggers. CREATE TABLE AS SELECT would not pay any of those costs, of course. Also, if you're running with wal_level=minimal, it would not have to write the changes into WAL, while the regu

Re: [GENERAL] Performance degradation when using auto_explain

is whether you actually need the timing - the total duration + row counts are far more important in my experience, so by setting auto_explain.log_analyze = on auto_explain.log_timing = off you may significantly reduce the impact on query performance, while retaining the most valuable

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/07/2017 04:43 AM, Venkata B Nagothi wrote: On Sat, Jan 7, 2017 at 2:56 AM, Job > wrote: __ Hi guys, really much appreciated your replies. >> You might want to include the query plans for each server W e use a function, the explain

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

it does not call gettimeofday() and so the clock source overhead is pretty much irrelevant. Moreover it's marked as 'stable' which makes repeated calls unnecessary. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remo

Re: [GENERAL] Slow index scan - Pgsql 9.2

but you'll have to include all columns into the index, even those in the index predicate: CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode) WHERE NOT is_demo AND NOT deleted; I'd bet all of those will outperform the current plan. regards -- Tomas Vondra

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

ext, and we reset it on RESET ALL. > In short, I agree with Guyren, there are features needed here that we don't have and it would be a great deal better if we did. Yeah. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DB

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

ecution time. Both planning time limits (reject based on estimates) and runtime (abort query if limit is reached). That might be an interesting feature, but it's also significantly more complex than the topic of implementing a safe context for secrets, making RLS less problematic with connection p

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

send the results to the mailing list: select name, setting from pg_settings where source != 'default'; That'll tell us how was the configuration modified. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

_id = a.master_user_id) while the 9.6 plan does not include anything like that, i.e. there's only a single aggregate at the top level, without any group keys. Also, the SQL query you've provided does not include any GROUP BY clause, so I claim that those plans are from two different qu

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

. Of course, there are other things we might need to know. For example if there are foreign keys between the tables, 9.6 is using that for the estimates (unlike 9.5). And so on. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote D

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

#x27;ll only consider group aggregate, because that can work with very little memory. At execution time we'll only use as much memory as actually needed. The trouble is that if we under-estimated the amount of memory, there's no way back. regards -- Tomas Vondra

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

-- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] using hstore to store documents

at you're looking for though, you'll probably need to explain what you're trying to achieve to get better answers. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-gen

Re: [GENERAL] full_page_writes on SSD?

one containing a lot of synchronous, single threaded, reads. Even with SSDs that can very quickly lead to not being able to keep up with replay anymore. I don't immediately see why that would happen? Can you elaborate? regards -- Tomas Vondra http://www.2ndQuadrant.com Po

Re: [GENERAL] full_page_writes on SSD?

s being handed over to the SSD. Of course, there might be other benefits of further lowering page sizes at the OS/database level (and AFAIK there are SSD drives that use pages smaller than 4kB). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Supp

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

2007/10/22, Martin Marques <[EMAIL PROTECTED]>: Pavel Stehule wrote: Hello I am unsure, did you check config values? Don't know which ones you are talking about, but all enable_* are set to on. Anything else? shared_buffers work_mem effective_cache_size Pavel Well, the cost_* values m

Re: [GENERAL] any way for ORDER BY x to imply NULLS FIRST in 8.3?

Reece Hart <[EMAIL PROTECTED]> writes: However, it's not clear that you've considered a clause like 'ORDER BY (foo IS NULL), foo', which I believe is not implementation dependent. Yeah, that should work reasonably portably ... where "portable" means "equally lousy performance in every implement

Re: [GENERAL] INSERT performance deteriorates quickly during a large import

Try to one of these: a) don't use INSERT statements, use a COPY instead b) from time to time run ANALYZE on the "public" table (say 1000 inserts, then one analyze) c) create the table without constraints (primary / foreign keys in this case), import all the data, and then create the const

[GENERAL] strange row count estimates with conditions on multiple column

Hi everyone, I've just noticed a strange behaviour when estimating row counts (I'm running 9.0.1). A small demonstration - let's create table with two columns, and fill it with data so that the columns are not independent: = --

Re: [GENERAL] strange row count estimates with conditions on multiple column

OK, thanks for the explanation. Cases (A), (B) and (D) are clear now. But I'm not sure about (C) ... Dne 17.11.2010 04:03, Tom Lane napsal(a): > Tomas Vondra writes: >> I'm not quite sure why (C) has an estimate of 1. > > It's smart enough to see that each of the

Re: [GENERAL] strange row count estimates with conditions on multiple column

Dne 17.11.2010 05:22, Tom Lane napsal(a): > Tomas Vondra writes: >> Estimate for condition >>... WHERE (col_a BETWEEN 33 AND 33) >> is about 10k rows, which is quite precise. On the other side estimate >> for condition >>... WHERE (col_b BETWEEN 33 A

Re: [GENERAL] strange row count estimates with conditions on multiple column

> BTW I think the default estimate used to be 1000, so it was changed in > one of the 8.x releases? Can you point me to the docs? I've even tried > to find that in the sources, but unsuccessfully. OK, I've found it right after submitting the e-mail. It's defined in selfuncs.h as DEFAULT_RANGE_INE

Re: [GENERAL] where is pg_stat_activity (and others) in the documentation?

Dne 17.11.2010 16:22, Tom Lane napsal(a): > t...@fuzzy.cz writes: >>> I do think that we need per-column documentation of the Standard >>> Statistics Views. > >> What's wrong with these docs? >> http://www.postgresql.org/docs/9/static/catalogs.html > > Or, more to the point, these docs: > http://

[GENERAL] How to identify whether the stats were reset?

Hello everyone, we're collecting snapshots of the statistics (pg_stat, pg_statio etc.) regularly so that we can analyze the performance, detect trends etc. We need to identify whether the stats were reset between the snapshots, because then the collected data are useless (and the scripts that do

Re: [GENERAL] How to identify whether the stats were reset?

Dne 19.11.2010 05:56, Tom Lane napsal(a): > Tomas Vondra writes: >> We need to identify whether the stats were reset between the snapshots, >> because then the collected data are useless (and the scripts that do the >> analysis are quite confused). > > The stats ar

Re: [GENERAL] strange row count estimates with conditions on multiple column

Dne 17.11.2010 06:58, Tom Lane napsal(a): >> BTW I think the default estimate used to be 1000, so it was changed in >> one of the 8.x releases? Can you point me to the docs? I've even tried >> to find that in the sources, but unsuccessfully. > > It's DEFAULT_RANGE_INEQ_SEL, and AFAIR it hasn't cha

Re: [GENERAL] Can the query planner create indexes?

Dne 21.12.2010 16:34, Jeremy Harris napsal(a): >> There really is no automatic way to solve this puzzle using a single >> query. Indexing strategy is a very tough design discipline, and it >> requires a complex knowledge of the workload. One slow query does not >> mean >> the index should be create

Re: [GENERAL] Can the query planner create indexes?

Dne 21.12.2010 20:03, Jeremy Harris napsal(a): > On 2010-12-21 18:50, Tomas Vondra wrote: >>> Then the index you just built gets automatically dropped, as I said >>> above. >> >> I'm a bit confused. Should the indexes be dropped automatically (as you >>

Re: [GENERAL] database is growing... 1GB per day basis

Dne 11.1.2011 19:11, Karayappalli, Raghunath (Raghu) napsal(a): > I am missing some of your questions: what are the tuning parameters are > you referring here that would prevent running auto vacuum? There is a bunch of parameters influencing autovacuum - how often it runs, how much work it does, e

Re: [GENERAL] How to generate unique invoice numbers foreach day

If the gaps (user gets a number from a sequence and then rollbacks the transaction) are not a problem, then the sequences (reset every day) are probably the best solution. If the gaps are a problem (which is usually the case with invoicing systems), then you need to manage that on your own, e.g. u

Re: [GENERAL] How to generate unique invoice numbers foreach day

In that case you have to manage the IDs on your own, the sequences won't help you in this (unless you really create one sequence for each day, which does not seem like a good solution to me). A really simple solution might be to do a BEFORE INSERT trigger that checks the last ID inserted for the d

Re: [GENERAL] Need help writing exclusion constraint

Dne 15.1.2011 21:07, Daniel Popowich napsal(a): > CREATE OR REPLACE FUNCTION overlap_at_dest(dest integer, > s timestamp, > e timestamp) > returns boolean as $_$

Re: [GENERAL] How to generate unique invoice numbers for each day

Dne 16.1.2011 03:29, Jasen Betts napsal(a): > On 2011-01-15, Andrus Moor wrote: >> Invoice numbers have format yymmddn >> >> where n is sequence number in day staring at 1 for every day. >> >> command >> >> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), >> '^[0-9]*'),'')::int),

Re: [GENERAL] How to generate unique invoice numbers for each day

Dne 16.1.2011 22:44, Andrus Moor napsal(a): > Thank you. > >> 2. In point 2. add FOR UPDATE >> 3. Use READ COMMITED TRANSACTION ISOLATION LEVEL >> >> Don't lock tables, You wrote you can generate invoices for few days >> backward, >> so you don't need locking whole table. >> >> Don't use seqences,

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 20:32, Merlin Moncure napsal(a): > On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie wrote: >> Hello Merlin, >> Thank you very much for your reply. >> I don't see any setting for lc_collate. I assume it would be in >> postgresql.conf file if it were there? These are the only lc_..

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 21:38, Davenport, Julie napsal(a): > OK, I did the explain analyze on both sides (using a file for output instead) > and used the tool you suggested. > > 8.0 - http://explain.depesz.com/s/Wam > 8.4 - http://explain.depesz.com/s/asJ Great, that's exactly what I asked for. I'll rep

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 22:31, Pavel Stehule napsal(a): > 2011/3/16 Tomas Vondra : >> Dne 16.3.2011 21:38, Davenport, Julie napsal(a): >>> OK, I did the explain analyze on both sides (using a file for output >>> instead) and used the tool you suggested. >>> >>>

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

4 > (1 row) > > srn_mst=# show seq_page_cost; > seq_page_cost > --- > 1 > (1 row) > > srn_mst=# show work_mem; > work_mem > -- > 1MB > (1 row) > > Thanks, > Julie > > > > -Original Message- > From: Tomas Vondra [mailto:t.

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 17.3.2011 19:29, Davenport, Julie napsal(a): > I have not yet had time to try Tomas' suggestion of bumping up the work_mem > first (trying to figure out how to do that from within a coldfusion script). > Many thanks for all your help guys! Well, just execute this 'SQL query' just like the o

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 18.3.2011 16:42, Davenport, Julie napsal(a): > This helped, is now down from 14.9 min to 10.9 min to run the entire script. > Thanks. Still, it's way slower than the 8.0 :-( regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 18.3.2011 19:18, Merlin Moncure napsal(a): > On Fri, Mar 18, 2011 at 10:42 AM, Davenport, Julie > wrote: >> This helped, is now down from 14.9 min to 10.9 min to run the entire script. >> Thanks. > > can you try disabling nestloop and see what happens? In the session, > before running th

Re: [GENERAL] a question about row estimation in postgres

Dne 20.3.2011 08:20, Reynold Xin napsal(a): > Following the formula outlined > in http://www.postgresql.org/docs/8.3/static/row-estimation-examples.html > > Both gradrate 10 and gradrate 11 would fall in the first bucket. > > Shouldn't the row estimation be: > > (11 - 8) / (33 - 8) / 10 * 1302 =

Re: [GENERAL] postgres conferences missing videos?

Dne 21.3.2011 20:23, Vick Khera napsal(a): > On Mon, Mar 21, 2011 at 1:45 PM, Aljoša Mohorović > wrote: >> postgres is losing a lot of promotional and educational materials, why >> is this happening? >> > > Someone has to do lots of work to tape the talks, get proper > permissions from the presen

Re: [GENERAL] postgres conferences missing videos?

Dne 21.3.2011 21:45, David Fetter napsal(a): > Who proposes, volunteers! How are you going to help? Well, if this is the only problem then I happily volunteer to take care of this for pgday 2011. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] postgres conferences missing videos?

Dne 22.3.2011 00:20, David Fetter napsal(a): > On Mon, Mar 21, 2011 at 11:55:20PM +0100, Aljoša Mohorović wrote: >> On Mon, Mar 21, 2011 at 9:45 PM, David Fetter wrote: >>> Who proposes, volunteers! How are you going to help? >> >> being on a different continent and unable to attend doesn't actua

Re: [GENERAL] postgres conferences missing videos?

Dne 22.3.2011 20:29, Susan Cassidy napsal(a): > Personally, I vastly prefer written information to video. I can read far > faster than someone can read/explain something to me. Plus, it is easier to > refer back to. > > Susan > 100% true. But it's also true that slides (and that's all we ha

[GENERAL] wal_level=minimal produces more data than archive level

Hi everyone, I've been playing with various wal_level settings, mainly to find out what is the overhead of hot standby, and I've noticed a strange thing. In some cases the wal_level=minimal produces signigicantly more xlog data than wal_level=archive (and hot_standby). ===

Re: [GENERAL] wal_level=minimal produces more data than archive level

Dne 3.4.2011 02:45, Tom Lane napsal(a): > Tomas Vondra writes: >> I've been playing with various wal_level settings, mainly to find out >> what is the overhead of hot standby, and I've noticed a strange thing. >> In some cases the wal_level=minimal produces signi

Re: [GENERAL] Replication: slave is in permanent startup 'recovery'

Dne 13.4.2011 20:42, Henry C. napsal(a): > > Forgot to mention recovery.conf on slave: > > standby_mode = 'on' > primary_conninfo = 'host..." > restore_command = 'cp /home/psql-wal-archive/%f "%p"' > archive_cleanup_command = 'pg_archivecleanup /home/psql-wal-archive %r' > > > The wiki states "

Re: [GENERAL] question about memory usage

On 23 Červenec 2014, 15:56, klo uo wrote: > Bill, thanks for your reply. > > "shared_buffers" is set to "128MB". > > Now that you mention config file, the only thing I did change there, and > was suggested to me while I made some on my databases was > "max_locks_per_transaction = 5" (which has

Re: [GENERAL] Performance problem on 2 PG versions on same query

Hi, Dne 5 Listopad 2014, 17:31, Rémy-Christophe Schermesser napsal(a): > Hi, > > We have 2 instances of PG, one in 9.1.1 and 9.1.14. They have the same > data, schema, PG configuration, and are almost identical machines, same > number of cores and memory, but different cloud provider. The data was

Re: [GENERAL] Performance problem on 2 PG versions on same query

Dne 5 Listopad 2014, 18:10, Tom Lane napsal(a): > "Tomas Vondra" writes: >> Dne 5 Listopad 2014, 17:31, R??my-Christophe Schermesser napsal(a): >>> We have 2 instances of PG, one in 9.1.1 and 9.1.14. They have the same >>> data, schema, PG configuration, an

Re: [GENERAL] invalid memory alloc request size

On 10.12.2014 17:07, Gabriel Sánchez Martínez wrote: > Hi all, > > I am running PostgreSQL 9.3.5 on Ubuntu Server 14.04 64 bit with 64 GB > of RAM. When running pg_dump on a specific table, I get the following > error: > > pg_dump: Dumping the contents of table "x_2013" failed: > PQgetResult

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

basic description first. Is it querying the database? Is it inserting or updating large amounts of data? Or is it something more complex? Aside from that, some basic system stats would be really helpful, so that we can identify the bottleneck (is that a CPU, I/O, locking, ...). A few lines from thes

Re: [GENERAL] Fwd: Data corruption after restarting replica

Filesystem is ext4 mounted with nobarrier > option. Good ;-) > Database is fairly large ~120GB with several 50mil+ tables, lots of > indexes and FK constraints. It is mostly queried, > updates/inserts/deletes are only several rows/s. So it was queried for a few hours? Any idea if

Re: [GENERAL] newbie how to access the information scheme

nformation_schema' > AND n.nspname !~ '^pg_toast' > AND pg_catalog.pg_table_is_visible(c.oid) > ORDER BY 1,2; > > I found the above by using the command: psql -E Or just use the information_schema like this: select table_schema, table_name from informatio

Re: [GENERAL] : :Full text search query ::

WHERE part_number_a = '104' Another option is to use an index with a 'varchar_pattern_ops' opclass, which allows you to do prefix LIKE queries [1] CREATE INDEX custom_partnum_idx ON partdetails (part_number varchar_pattern_ops); SELECT ... FROM partdetails

Re: [GENERAL] Hosting PG on AWS in 2013

Hi David, On 7.4.2013 03:51, David Boreham wrote: > > First I need to say that I'm asking this question on behalf of "a > friend", who asked me what I thought on the subject -- I host all the > databases important to me and my livelihood, on physical machines I own > outright. That said, I'm curi

Re: [GENERAL] Hosting PG on AWS in 2013

On 7.4.2013 19:43, David Boreham wrote: > > I thanks very much for your detailed response. A few answers below inline: > > On 4/7/2013 9:38 AM, Tomas Vondra wrote: >> As for the performance, AFAIK the EBS volumes always had, and probably >> will have, a 32 MB/s limit.

Re: [GENERAL] Memory usage after upgrade to 9.2.4

Hi, we got a report of (probably) the same issue on a local mailing list. Maybe it'll help in finding the root cause, so I'm resending the info here too. On 21.4.2013 01:19, Adrian Klaver wrote: > On 04/20/2013 04:08 PM, Daniel Cristian Cruz wrote: >> I think I didn't make it clear: the session m

Re: [GENERAL] Memory usage after upgrade to 9.2.4

On 21.4.2013 15:14, Tom Lane wrote: > Tomas Vondra writes: >> I do have a log with the memory context info printed after the OOM >> killed the session - see it attached. > > The only thing that seems rather bloated is the CacheMemoryContext, > which seems to be becaus

Re: [GENERAL] inserting huge file into bytea cause out of memory

Hi, On 6 Srpen 2013, 9:12, liuyuanyuan wrote: > Error detail: > org.postgresql.util.PSQLException: Error: out of memory > Details:Failed on request of size 268443660. Seems like an issue with the OS, not with PostgreSQL, to me. What OS and HW are you using? How much memory you have and do you

Re: [GENERAL] Unexpected WAL-archive restore behaviour

ww.postgresql.org/docs/9.6/static/continuous-archiving.html [2] https://wiki.postgresql.org/wiki/Warm_Standby regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

you'll have such a busy system, you probably do some research and testing first, before choosing the database. If we don't perform well enough, you pick something else. Which removes the data point. Obviously, there are systems that start small and get busier and busier over time. And

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

it (power-loss protected), can do ~1GB/s of sustained O_DIRECT sequential writes. But when running pgbench, I can't push more than ~300MB/s of WAL to it, no matter what I do because of WALWriteLock. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 04/28/2017 01:34 AM, Andres Freund wrote: On 2017-04-28 01:29:14 +0200, Tomas Vondra wrote: I can confirm this observation. I bought the Intel 750 NVMe SSD last year, the device has 1GB DDR3 cache on it (power-loss protected), can do ~1GB/s of sustained O_DIRECT sequential writes. But when

Re: [GENERAL] looking for a globally unique row ID

s of partitions), and thus overhead. It's a bit foolish to say you would live with the performance hit when you don't know how serious would it be. It may be fine but it may also be much worse than you expected. I ran into a number of people who used global indexes on other DBs, and

Re: [GENERAL] random row from a subset

r by c_d_id) as rn , count(*) over() max_rn from customer where c_d_id=5 ) t where rn = (select floor(r*(max_rn))+1 from rand); or define an immutable wrapper for random(): CREATE FUNCTION random_stable() RETURNS DOUBLE PRECISION AS 'SELECT random()' LANG

Re: [GENERAL] random row from a subset

On 09/20/2017 01:28 PM, bluefrog wrote: > > thanks, interestingly your method works in both Oracle and PostgreSQL, > albeit with a different random function call. > It does not work in SQL Anywhere though. > You will have to ask SQL Anywhere people, I guess. cheers -

Re: [GENERAL] shared_buffers smaller than max_wal_size

the database will receive writes, etc. So there's nothing inherently wrong with (shared_buffers > max_wal_size) or (shared_buffers > max_wal_size), it depends on your workload. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support

Re: [GENERAL] shared_buffers smaller than max_wal_size

disk so WAL can be deleted? > The last step in a checkpoin is fsync() on the files. Without that, the checkpoint is considered incomplete and the database won't rely on it. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support,

Re: [GENERAL] shared_buffers smaller than max_wal_size

f expiration period, so a single page may end up being written to disk repeatedly. With sufficiently large shared buffers the page would be repeatedly modified in shared buffers, and written our just once by the checkpoint process. regards -- Tomas Vondra http://www.2ndQuadran

Re: [GENERAL] Speed of conversion from int to bigint

d RENAME TO table_id; COMMIT; You may need to tweak this to handle the inheritance tree. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

  1   2   3   4   >