Skip to main content

New answers tagged

1 vote
Accepted

What impact has uniqueness constraint jsonb on postgres db performance

I'll ignore the "why" question. Look at the pgsql-hackers archives; it is all open source. You need a B-tree index for a unique constraint. Putting the index directly on the JSON column is ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

Postgres view role permissions

This is a minor variation of a query that I've been using for listing database object grants. WITH relkinds AS ( SELECT * FROM ( VALUES ( 'c', 'type' ), ...
gsiems's user avatar
  • 3,323
0 votes

Restore issue with Pgbackrest

Wild guess here, check the filesystem type. We had trouble restoring the Postgres cluster on Kubernetes platform using the pgbackrest tool, if the persistent volumes (disks) for Postgres and ...
Martin's user avatar
  • 1
0 votes
Accepted

PostgreSQL logical replication startup on large tables has bursts of activity and long waits

After looking into the source and destination database waits, this turned out the be the result of a proxy device used to expose the source database. The proxy VM was a t3.nano burstable instance. By ...
Jarrett Prosser's user avatar
0 votes

Is it possible to write a function in postgresql which directly writes its parameters to a text file?

The above examples start a new unix process every time you want to write to the file. Suppose you plan to write to the file a bunch of times during a single session, for example with a persistent ...
Mark Hedges's user avatar
3 votes
Accepted

How to run a query against AWS RDS "locally"?

One way I have done these "long-running connection required" tasks on AWS is to start an EC2 instance, install the proper tools, run tmux and let it go. Detach, turn my computer off. The ec2 ...
Craig's user avatar
  • 156
-2 votes

Search on concatenate columns

SELECT * FROM users WHERE (first_name || ' ' || last_name) ILIKE '%lvi%';
QuanNH's user avatar
  • 97
1 vote

How a B-Tree index on a columnar table would look like?

This answer is for PostgreSQL specifically. A B-tree index on a table that uses columnar storage will look just like any other B-tree index. The leaf nodes reference a table row. In PostgreSQL, the ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

How to convert bit(n) to bytea when n >= 256?

create table bittest (val bit(512)); insert into bittest (val) values (x'...
Quadratus Lumborum's user avatar
0 votes
Accepted

How do model different logical views on data

You need to make set have a FK on machine, and this should be a secondary composite unique key along with its main primary key. create table "set" ( id serial not null primary key, ...
Charlieface's user avatar
  • 13.6k
0 votes

Can you have a minimal transactions data warehouse?

What you describe is basic data warehouse architecture, where you have both normalized transaction data, and from that load tables that are modeled for easy consumption and fast query performance. In ...
David Browne - Microsoft's user avatar
-2 votes

Using internal or external ids when storing external data

Having written many applications with such databases, I would always use your internal id within the database. There are several reasons (not all will apply to you) The internal id is smaller and ...
Rohit Gupta's user avatar
  • 1,682
-2 votes

Can you have a minimal transactions data warehouse?

Your question is a classic example of the generic question: ‘A’ doesn’t work. Should I replace it with ‘B’? Who knows? It depends entirely on why ‘A’ does not work how you expect. If ‘A’ doesn’t ...
OldFart's user avatar
1 vote

PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT

Fastest solution for skewed data would be partial index. In your case an index like below will be fast as well super compact. You can tweak and test the index by changing index column or filtering ...
mediocre's user avatar
  • 168
2 votes
Accepted

PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT

PostgreSQL has two choices to process the query: it can use an index for the WHERE clause, then sort and return the first few results (that's your fast plan) it can use an index for the ORDER BY ...
Laurenz Albe's user avatar
  • 53.8k
1 vote

Alter timestamptz column to timestamp, without re-writing the table

You already saw my related answer addressing the reverse conversion timestamp → timestamptz without table rewrite: ALTER timestamp column to timestamptz, without "converting" data? Well, ...
Erwin Brandstetter's user avatar
0 votes

My postgresql indexes are read slowly from disk

For each entry found in the normal index scan there is a lookup in the heap which is a random read measured in IOPS. Sequential I/O takes roughly only 25% of random I/O timing. In your case since you ...
mediocre's user avatar
  • 168
0 votes
Accepted

Does LZ4 compression have an effect on JSONB columns?

Does LZ4 compression have an effect on JSONB columns? Basically, the data type jsonb can be compressed: SELECT typstorage FROM pg_type WHERE typname = 'jsonb'; -- 'x' See: Why *not* ERROR: index ...
Erwin Brandstetter's user avatar
0 votes

AWS DMS Fails with "Command failed to load data with exit error code 1"

I encountered the "Command failed to load data with exit error code 1" error while using AWS Database Migration Service (DMS). Upon enabling CloudWatch Logs for my PostgreSQL Aurora RDS ...
Salman Habibkhan's user avatar
0 votes

Does LZ4 compression have an effect on JSONB columns?

I assume that you are talking about ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4 That will certainly have an effect, but only if the table row you insert is big enough that PostgreSQL starts ...
Laurenz Albe's user avatar
  • 53.8k
-1 votes

ALTER TABLE … ADD COLUMN on a small table takes forever, pg_stat_activity doesn't show any queries on this table

Just like what @Laurenz has mentioned, locking is happened. Hence no transaction can be processed during the lock period. If you must release the lock period, here something you can do. Find out the ...
Tommy Leong's user avatar
0 votes

PostgreSQL: Long running auto vacuum and high wal rate

You could throttle auto vac to run slower by decreasing autovacuum_cost_limit or increasing autovacuum_cost_delay on the master. If the WAL is generated slower, it should also be replayed slower, ...
jjanes's user avatar
  • 40.3k
0 votes

Trouble with only read user in Postgresql 14

I can identify three problems in what you write: ALTER DEFAULT PRIVILEGES doesn't work the way you want. That's a frequently asked question. That statement only affects objects created by the user in ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

Finding id of last child in table structure

I am not sure if its efficient but this is how I solve such "tree/hierarchy" problem. WITH RECURSIVE message_tree(message_id, message_ids, hierarchy_level) AS ( -- create a tree by using ...
Sahap Asci's user avatar
  • 2,999
0 votes
Accepted

PostgreSQL16 concurrency upsert rows with subquery in trugger funtion got maybe bad read

The problem is the subquery you are using in the UPDATE branch. The problem will go away if you write ... ON CONFLICT (name) DO UPDATE SET score = name_score.score + 1 as long as you are using the ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

PostgreSQL: Long running auto vacuum and high wal rate

-- Adjust autovacuum thresholds for the large table ALTER TABLE your_large_table SET ( autovacuum_vacuum_threshold = 5000, autovacuum_vacuum_scale_factor = 0.01, ...
warashi nguyen's user avatar
1 vote

How to extract and transform filename from org-mode link with PostgreSQL regex?

CREATE TABLE org_links ( id serial PRIMARY KEY, link text ); INSERT INTO org_links (link) VALUES ('[[/path/to/file][text description]]'), ('[[/another/path/to/file][another text ...
warashi nguyen's user avatar
0 votes

Creating indexes before bulk-loading data is weirdly much faster than vice versa

-- Configuration (optimized for bulk loading) ALTER SYSTEM SET archive_mode = off; ALTER SYSTEM SET max_wal_senders = 0; ALTER SYSTEM SET wal_level = minimal; ALTER SYSTEM SET fsync = off; ALTER ...
warashi nguyen's user avatar
0 votes

How to enable streaming replication if we clone database disks to new virtual machine?

Instead of running pg_basebackup, you can use the low-level backup API to perform a physical backup that can be used to initialize a standby server. See step 3 of the instructions: Perform the backup,...
Laurenz Albe's user avatar
  • 53.8k
1 vote

Create table that has a sequence column counting from 1 for every different foreign key

A serial (or IDENTITY) column is not the right tool for your purpose. Those are meant to handle concurrent write access gracefully, and expressly not to produce a gapless sequence of row numbers, much ...
Erwin Brandstetter's user avatar
0 votes

Postgres - Improving multi-column GIN text search performance

Your index doesn't look very sensible to me. A multicolumn GIN index is pretty much identical to multiple single-column GIN indexes. It isn't like a Btree index, where selectivity on multiple ...
jjanes's user avatar
  • 40.3k
1 vote
Accepted

How can I allow a user to alter tables they do not own in Postgres?

No, there is no way around that: only a member of the owning role or a superuser can ALTER or DROP an object. Perhaps you can make the user that is to drop the objects a member of all other users?
Laurenz Albe's user avatar
  • 53.8k
5 votes
Accepted

Locking for update inside CTEs

The foundation of my answer is provided in the manual: If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are ...
Erwin Brandstetter's user avatar
0 votes

Performance of single vs multiple column unique constraint

According to the postgresql documentation here, single column indexes are preferred for performance reasons: Multicolumn indexes should be used sparingly. In most situations, an index on a single ...
Parker H Gordon's user avatar
1 vote
Accepted

Views in Postgresql replica

If you are using streaming replication, no, because the replica is read-only. If you are using logical replication, yes.
Laurenz Albe's user avatar
  • 53.8k
3 votes
Accepted

Scope of column aliasing in Postgres?

The immediate cause for the error message is that you cannot reference output column names of nested subqueries in the outer SELECT list. Could be fixed like: SELECT "chat_channel"."...
Erwin Brandstetter's user avatar
0 votes

SQL query to get the names of the partitions which are detached from the parent table in postgres?

Those tables are no longer attached to the partition parent, you need another way to identify them. Your comment indicates the naming pattern 'user_session_data_%'. This returns all tables matching ...
Erwin Brandstetter's user avatar
0 votes

How to speed up autovacuum

The best things to speed up autovacuum are Upgrade. PostgreSQL 9.6 has introduced substantial performance improvements for VACUUM, and later releases have improved that even more. Increase ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

is it possible to run postgres with no WAL files being produced?

For anyone wondering with regards to GCP Cloud SQL, WAL is produced when enabling Logical Replication and Decoding with this flag cloudsql.logical_decoding set to "on" It can be disabled ...
dimisjim's user avatar
  • 101
2 votes
Accepted

PostgreSQL error code not covered in documentation

The error is documented in your operating system's documentation. It is not documented in PostgreSQL because it is not a PostgreSQL error message. The meaning of the error is that the remote host ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

id increases even if the line has not been created

Autoincrement generator generates any definite value only once. If some value was generated already then it will be generated again never (until the generator reset, of course). When you try to insert ...
Akina's user avatar
  • 20.1k
0 votes

Why does postgresql have so many replication slots open?

import psycopg2 # Database connection parameters db_params = { 'dbname': 'your_database', 'user': 'your_username', 'password': 'your_password', 'host': 'your_host', 'port': '...
warashi nguyen's user avatar
0 votes

Importing the content of a text file in a text field in Postgres

import os import psycopg2 # Database connection parameters db_params = { 'dbname': 'your_database', 'user': 'your_username', 'password': 'your_password', 'host': 'your_host', '...
warashi nguyen's user avatar
2 votes

id increases even if the line has not been created

It's not a bug; it's a feature. This is a known behavior of sequence types across multiple platforms. Don't try to solve for it. You're not going to run out IDs. By the time you do encounter any of ...
Peter Vandivier's user avatar
1 vote

Postgres planning time is very high

1.6M dead tuples in pg_statistic is way beyond normal. If you are at liberty to do so: VACUUM FULL pg_catalog.pg_statistic; Else, at least plain VACUUM. I wonder how you got there. Looks like very ...
Erwin Brandstetter's user avatar
0 votes

Alter table generated text column to normal text column

alter table product_history alter column name DROP EXPRESSION; This form of alter table turns a stored generated column into a normal base column. Was added in postgresql 13.
Melkij's user avatar
  • 3,249
0 votes

Using ENUM as partition key when RANGE Partitioning PostgreSQL

Enum is a list of values with an order you can fill a enum with dummy values and have so your ranges as needed. Of course you don't have integers any more, instead you need to use the status texts ...
nbk's user avatar
  • 8,466
4 votes
Accepted

How to optimize query with multiple many-to-many relationships

It would seem you can radically simplify to: SELECT e.* FROM events e WHERE EXISTS ( SELECT FROM events_assets ea JOIN groups_assets ga USING (asset_id) WHERE ea.event_id = e.id ...
Erwin Brandstetter's user avatar
0 votes

List PostgreSQL extensions per database

I'm not sure if this can be automated directly in psql, as AFAIK you can't do cross-database queries. But you can easily automate this with some Bash. Here is quick script, which will list all ...
Kepi's user avatar
  • 101
0 votes
Accepted

After upgrade Postgres python is the same version

plpython3u extension should be compiled against the correct python header files. Considering the fact In Red Hat Enterprise Linux 8, Python 3.6 is the default. It is expected that the plpython3 ...
Melkij's user avatar
  • 3,249

Top 50 recent answers are included