INSERT ... ON CONFLICT ... is not currently implemented, even DO NOTHING if you have a primary key defined, e.g.:

postgres=# create table testy (id int PRIMARY KEY, v1 text, v2 text) using columnar;
CREATE TABLE
postgres=# insert into testy values (1, 'a', 'b') ON CONFLICT DO NOTHING;
ERROR:  columnar_tuple_insert_speculative not implemented

Presumably DO NOTHING would be easier to implement first.

A bit about insert_speculative from https://github.com/postgres/postgres/blob/master/src/include/access/heapam.h :

/*
 * Perform a "speculative insertion". These can be backed out afterwards
 * without aborting the whole transaction.  Other sessions can wait for the
 * speculative insertion to be confirmed, turning it into a regular tuple, or
 * aborted, as if it never existed.  Speculatively inserted tuples behave as
 * "value locks" of short duration, used to implement INSERT .. ON CONFLICT.
 *
 * A transaction having performed a speculative insertion has to either abort,
 * or finish the speculative insertion with
 * table_tuple_complete_speculative(succeeded = ...).
 */
static inline void
table_tuple_insert_speculative(Relation rel, TupleTableSlot *slot,
							   CommandId cid, int options,
							   struct BulkInsertStateData *bistate,
							   uint32 specToken)
{
	rel->rd_tableam->tuple_insert_speculative(rel, slot, cid, options, bistate, specToken);
}

How this is done in heap tables is available at https://github.com/postgres/postgres/blob/master/src/backend/access/heap/heapam_handler.c#L259-L280 though it largely relies on setting a flag then calling heap_insert ( https://github.com/postgres/postgres/blob/master/src/backend/access/heap/heapam.c#L2007-L2205 ).

blocking #53

What's wrong?

pg16 is not supported :)

What's wrong?

I used pg_ivm to synchronize a table to columnar. When testing, I found that the cloumnar table count is very slow, and even panic can be seen in the log (maybe triggered when Ctrl +C)

mainnet=# \d+ reader.staking_actions;
                                                Table "reader.staking_actions"
    Column     |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
---------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id            | bigint                |           |          |         | plain    |             |              | 
 block_height  | bigint                |           |          |         | plain    |             |              | 
 bucket_id     | bigint                |           |          |         | plain    |             |              | 
 owner_address | character varying(42) |           |          |         | extended |             |              | 
 candidate     | character varying(42) |           |          |         | extended |             |              | 
 amount        | numeric(42,0)         |           |          |         | main     |             |              | 
 act_type      | character varying(42) |           |          |         | extended |             |              | 
 sender        | character varying(42) |           |          |         | extended |             |              | 
 act_hash      | text                  |           |          |         | extended |             |              | 
 auto_stake    | boolean               |           |          |         | plain    |             |              | 
 duration      | bigint                |           |          |         | plain    |             |              | 
Indexes:
    "reader_idx_staking_actions_block_height" btree (block_height)
    "reader_idx_staking_actions_bucket_id" btree (bucket_id)
    "staking_actions_index" UNIQUE, btree (id)
Access method: columnar

mainnet=# \timing
Timing is on.
mainnet=# select count(*) from reader.staking_actions;
  count   
----------
 15461576
(1 row)

Time: 114561.246 ms (01:54.561)

log:

2023-10-07 01:33:44.962 UTC [2301598] postgres@mainnet ERROR:  canceling statement due to user request
2023-10-07 01:33:44.962 UTC [2301598] postgres@mainnet STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:25.549 UTC [2302149] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:25.549 UTC [2302149] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:35.463 UTC [2302150] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:35.463 UTC [2302150] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:41.751 UTC [2302147] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:41.751 UTC [2302147] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:51.202 UTC [2302146] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:51.202 UTC [2302146] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:57.400 UTC [2302148] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:57.400 UTC [2302148] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:35:58.147 UTC [2302152] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:35:58.147 UTC [2302152] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:36:04.139 UTC [2302151] PANIC:  stuck spinlock detected at AdvanceStripeRead, columnar_reader.c:810
2023-10-07 01:36:04.139 UTC [2302151] STATEMENT:  select count(*) from reader.staking_actions;
2023-10-07 01:36:05.553 UTC [1277] LOG:  background worker "parallel worker" (PID 2302150) was terminated by signal 6: Aborted
2023-10-07 01:36:05.553 UTC [1277] DETAIL:  Failed process was running: select count(*) from reader.staking_actions;
2023-10-07 01:36:05.553 UTC [1277] LOG:  terminating any other active server processes
2023-10-07 01:36:10.618 UTC [1277] LOG:  issuing SIGKILL to recalcitrant children
2023-10-07 01:36:10.672 UTC [1277] LOG:  all server processes terminated; reinitializing
2023-10-07 01:36:10.907 UTC [2302684] LOG:  database system was interrupted; last known up at 2023-10-07 01:23:46 UTC
2023-10-07 01:36:11.048 UTC [2302684] LOG:  database system was not properly shut down; automatic recovery in progress
2023-10-07 01:36:11.487 UTC [2302684] LOG:  invalid record length at 8E/1BB28F40: wanted 24, got 0
2023-10-07 01:36:11.487 UTC [2302684] LOG:  redo is not required

Update: I deleted the table and use pg_ivm rebuild, the bug is gone

What's wrong?

(not actually a bug but the repository does not have the right issue type. Feel free to change the issue type)

Hi!

Recently I tested a lot of software with PGO and measured the performance improvements from PGO - the results are here. Since my results show interesting improvements on a lot of databases (including PostgreSQL). I think it would be a good idea to measure PGO effects on Hydra PostgreSQL extension as well - e.g. reduce CPU usage in some cases. If the results will show an improvement - would be great to see a note in the documentation about PGO benefits for Hydra.

When an index is used, chunks are loaded for each lookup by index, then thrown away. The loaded data should instead be cached in memory until the query is completed.

likely by supporting pg_ivm (ensuring it works), and then adding it to the image

Would it ever be possible to design/develop Hydra using TLE?

This would open up the RDS use case.

I know this is probably the opposite of what, Hydra, as a company wants.

But figured I'd try to start this discussion, just in case it would be possible.

Also, not sure of the technicals behind all of this, I am not an extension expert. Maybe it would require a complete rewrite, or the TLE framework is too limiting and would make it entirely impossible for implement a columnar store.

Docs: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html

no matter how I give the required param after docker run 26293962061a
-e POSTGRES_PASSWORD=password
or
-e "POSTGRES_PASSWORD=password"
or
"-e POSTGRES_PASSWORD=password"

This message is prompted and docker is exited
Database is uninitialized and superuser password is not specified. You must specify POSTGRES_PASSWORD to a non-empty value for the superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run". You may also use "POSTGRES_HOST_AUTH_METHOD=trust" to allow all connections without a password. This is *not* recommended. See PostgreSQL documentation about "trust": https://www.postgresql.org/docs/current/auth-trust.html

Create repeatable benchmarks. Have "quick" benchmarks that run on a PR, and more substantial benchmarks when merged to main. Publicly display results of the benchmarks.

Currently, we are using GreenPlum as our Data Warehouse, but the performance is a little poor, so we are looking for some PostgreSQL's alternatives, we are looking at Hydra and TimescaleDB. Seems TimescaleDB will support
retention policy(move old data into s3 to save disk space) https://www.timescale.com/blog/save-money-and-improve-performance-by-carefully-managing-your-time-series-data/ .

So does Hydra has some similar plans to support it?

currently we use https://hub.docker.com/_/postgres/ which means we are supporting a Debian base image for OSS and a Ubuntu base image for our hosted service. Switching to https://hub.docker.com/r/ubuntu/postgres would give us a more consistent base image between the two images.

I don't know how different the images are to operate and whether this would cause pain for users of the current image. We could continue to build the Debian-based image for some time (e.g. on bulleye, but not upgrade to bookworm) until we reach a deprecation date.

What's wrong?

FIY: Discord channel is not working and you allow now only bug reports but this is are questions/feature request

If I have data arriving later (data for 2 days ago) and I run a ETL every 15 min towards Hydra:
Should I at least insert the older data separate than data from the last 15 min or should I do something else ?
Will it find the existing chunk for that day and append there ?
Should I worry about data timespan fragmentation/overlapping between different stripe/chunks ?

Hi,

I am using hydra docker PG 14 and I inserted 146 million gps & speed position for ~10K cars(targets) for a period of 30 days.
I am now trying to query speed values or average for a specific car in a given interval.
No matter if the interval is 1 hour or up to 7 days it takes 1 minute for the query to complete.

I have added a btree index on datetime and targetId both separately and together with speed column included or without, but there was no improvement in the duration.

This is the EXPLAIN ANALYZE for a where datetime between '2023-02-22' and '2023-02-23' and targetid = 3511 :

"  Workers Planned: 7"
"  Workers Launched: 1"
"  ->  Parallel Custom Scan (ColumnarScan) on trackpoint  (cost=0.00..784.03 rows=20925447 width=3) (actual time=4.571..57802.876 rows=148 loops=2)"
"        Filter: ((datetime >= '2023-02-22 00:00:00'::timestamp without time zone) AND (datetime <= '2023-02-23 00:00:00'::timestamp without time zone))"
"        Columnar Projected Columns: datetime, targetid, speed"
"        Columnar Chunk Group Filters: ((datetime >= '2023-02-22 00:00:00'::timestamp without time zone) AND (datetime <= '2023-02-23 00:00:00'::timestamp without time zone))"
"        Columnar Chunk Groups Removed by Filter: 13073"
"        Columnar Vectorized Filter: (targetid = 3511)"
"Planning Time: 168.464 ms"
"Execution Time: 58013.067 ms"

Am I doing something wrong ? Is there anything else I should be doing ?

• Update columnar code to support pg15
• Everything else has to work with pg15 too

What's wrong?

The previous Vacuum issue seems fixed, but now a repetitive single issue still happens (I tested 4 times):
After inserting ~11milion rows (11.576.120) in batches and columnar table reaches 5663MB, always at this same exact point, the ETL migration freezes due to Vacuum command: "VACUUM public.trackpoint" being unable to finish (CPU: 30% of 1 Core but IOPS going to 100%).
If I kill that one 2 new commands arise: "autovacuum: VACUUM ANALYZE public.trackpoint" with same behaviour and after I kill that one a new one "autovacuum: VACUUM columnar.row_mask" with same behavior.
I have stopped the docker and than started again and tried to run manually again only "VACUUM verbose public.trackpoint" on existing data and same behavior, it never finishes or at least not for 3 hours I waited (instead of avg 50ms until this point).
Details after stopping after 3 hours:
INFO: statistics for "trackpoint":
storage id: 10000000016
total file size: 9603817472, total data size: 382361669
compression rate: 2.83x
total row count: 11576120, stripe count: 84, average rows per stripe: 137810
chunk count: 14376, containing data for dropped columns: 0, zstd compressed: 14376
Now it gets stranger: if I insert one more batch and than run "VACUUM public.trackpoint" it finishes in 6 seconds. And than again a new batch insert the vacuum command resumes the 50ms speed.
The only thing that is more peculiar about that moment in migration is that the latest batches size before vacuum freezes are very very small: only 500-700rows for last 40 batches instead of 8-15K rows, but the same goes for 1 day of data before and it doesn't freeze there.

What's wrong?

I was testing Updating a row and than running the columnar.vacuum_full().
Because it was taking to long I stopped that one and run a more precise columnar.vacuum for the specific partitioned table.
Something happened and I got the error:
ERROR: attempt to read columnar data of length 8168 from offset 24 of block 3927 of relation 29101 CONTEXT: SQL statement "SELECT columnar.vacuum(tablename, stripe_count)" PL/pgSQL function columnar.vacuum_full(name,real,integer) line 25 at SQL statement

If I now run a select on that table I get this error:
ERROR: Unknown frame descriptorzstd decompression failed ERROR: zstd decompression failed SQL state: XX000 Detail: Unknown frame descriptor

This is new in 1.0.0-rc only after running ALTER EXTENSION columnar UPDATE.

create table t4 (i1 integer);
insert into t4 select generate_series(1, 1000000);
select sum(length(i1::text)) from t4;

This causes segfault of the connection.

Backtrace indicates:

#0  0x00007fe743d938fa in __libc_pread64 (fd=32, buf=0x7fe539794b80,
    count=8192, offset=40960) at ../sysdeps/unix/sysv/linux/pread64.c:25
#1  0x00005570ae12b606 in FileRead ()
#2  0x00005570ae15ad54 in mdread ()
#3  0x00005570ae12686a in ?? ()
#4  0x00005570ae1274e8 in ReadBufferExtended ()
#5  0x00007fe7410b1f7a in ReadFromBlock (rel=0x7fe534243288, blockno=5,
    offset=24, buf=0x5570b07c09e2 "", len=8168, force=<optimized out>)
    at ./hydra/columnar/src/backend/columnar/columnar_storage.c:691
#6  0x00007fe7410b20a4 in ColumnarStorageRead (amount=23109,
    data=0x5570b07bf368 "...[truncated]..."...,
    logicalOffset=35086, rel=0x7fe534243288)
    at ./hydra/columnar/src/backend/columnar/columnar_storage.c:496
#7  ColumnarStorageRead (rel=0x7fe534243288, logicalOffset=35086,
    data=0x5570b07bf368 "...[truncated]..."...,
    amount=23109)
    at ./hydra/columnar/src/backend/columnar/columnar_storage.c:473
#8  0x00007fe7410b3b3a in LoadColumnBuffers (
    attributeForm=<error reading variable: Cannot access memory at address 0x7fff40d20338>,
    stripeOffset=<error reading variable: Cannot access memory at address 0x7fff40d20340>, chunkCount=<optimized out>, chunkSkipNodeArray=<optimized out>,
    relation=<error reading variable: Cannot access memory at address 0x7fff40d20330>)
    at ./hydra/columnar/src/backend/columnar/columnar_reader.c:1322
#9  LoadFilteredStripeBuffers (snapshot=0x5570b06fc908,
    chunkGroupsFiltered=<error reading variable: Cannot access memory at address 0x7fff40d20308>, whereClauseVars=<optimized out>,
    whereClauseList=<optimized out>, projectedColumnList=<optimized out>,
    tupleDescriptor=<optimized out>,
    stripeMetadata=<error reading variable: Cannot access memory at address 0x7fff40d20320>, relation=<optimized out>)
    at ./hydra/columnar/src/backend/columnar/columnar_reader.c:1249
#10 BeginStripeRead (
    stripeMetadata=<error reading variable: Cannot access memory at address 0x7fff40d20320>, rel=<optimized out>, tupleDesc=<optimized out>,
    projectedColumnList=<optimized out>, whereClauseList=<optimized out>,
    whereClauseVars=<optimized out>, stripeReadContext=0x5570b07a91d0,
    snapshot=0x5570b06fc908)
    at ./hydra/columnar/src/backend/columnar/columnar_reader.c:757
#11 0x00007fe7410b4c97 in ColumnarReadNextVector (
    readState=<error reading variable: Cannot access memory at address 0x7fff40d20358>, columnValues=0x5570b0774ca0,
    columnNulls=<error reading variable: Cannot access memory at address 0x7fff40d203c0>,
    rowNumber=<error reading variable: Cannot access memory at address 0x7fff40d203e8>,
    newVectorSize=<error reading variable: Cannot access memory at address 0x7fff40d20370>)
    at ./hydra/columnar/src/backend/columnar/columnar_reader.c:1963

What's wrong?

2023-09-14 01:35:41.500 UTC [98] LOG:  redo starts at 26/12A2A0C0
2023-09-14 01:35:42.245 UTC [98] LOG:  redo done at 26/1AC4BFB0 system usage: CPU: user: 0.50 s, system: 0.14 s, elapsed: 0.74 s
2023-09-14 01:35:42.480 UTC [1] LOG:  database system is ready to accept connections
2023-09-14 01:37:14.598 UTC [1] LOG:  server process (PID 105) was terminated by signal 9: Killed
2023-09-14 01:37:14.598 UTC [1] DETAIL:  Failed process was running: COPY  block_action FROM STDIN WITH(FORMAT CSV,HEADER, DELIMITER ';');
2023-09-14 01:37:14.598 UTC [1] LOG:  terminating any other active server processes
2023-09-14 01:37:14.602 UTC [1] LOG:  all server processes terminated; reinitializing
2023-09-14 01:37:14.630 UTC [107] LOG:  database system was interrupted; last known up at 2023-09-14 01:35:42 UTC
2023-09-14 01:37:14.702 UTC [107] LOG:  database system was not properly shut down; automatic recovery in progress
2023-09-14 01:37:14.705 UTC [107] LOG:  redo starts at 26/1AC4C0C0
2023-09-14 01:37:15.327 UTC [107] LOG:  redo done at 26/219C9FB8 system usage: CPU: user: 0.44 s, system: 0.10 s, elapsed: 0.62 s
2023-09-14 01:37:15.525 UTC [1] LOG:  database system is ready to accept connections
2023-09-14 01:39:40.010 UTC [1] LOG:  server process (PID 114) was terminated by signal 9: Killed
2023-09-14 01:39:40.010 UTC [1] DETAIL:  Failed process was running: COPY  block_action FROM STDIN WITH(FORMAT CSV,HEADER, DELIMITER ';');
2023-09-14 01:39:40.010 UTC [1] LOG:  terminating any other active server processes
2023-09-14 01:39:40.013 UTC [1] LOG:  all server processes terminated; reinitializing
2023-09-14 01:39:40.040 UTC [116] LOG:  database system was interrupted; last known up at 2023-09-14 01:37:15 UTC
2023-09-14 01:39:40.106 UTC [116] LOG:  database system was not properly shut down; automatic recovery in progress
2023-09-14 01:39:40.109 UTC [116] LOG:  redo starts at 26/219CA0C0
2023-09-14 01:39:40.655 UTC [116] LOG:  redo done at 26/27207F90 system usage: CPU: user: 0.40 s, system: 0.07 s, elapsed: 0.54 s
2023-09-14 01:39:40.807 UTC [1] LOG:  database system is ready to accept connections

What's wrong?

I used two tables public.receipts and reader.receipts in the test,public.receipts is a heap table, and reader.receipts is generated as columnar using pg_ivm. They have the same hash indexes on action_hash.

testnet=# \d+ public.block_receipts;
                                                       Unlogged table "public.block_receipts"
        Column        |          Type          | Collation | Nullable |        Default        | Storage  | Compression | Stats target | Description 
----------------------+------------------------+-----------+----------+-----------------------+----------+-------------+--------------+-------------
 id                   | bigint                 |           | not null |                       | plain    |             |              | 
 block_height         | bigint                 |           |          |                       | plain    |             |              | 
 action_hash          | character varying(64)  |           | not null |                       | extended |             |              | 
 gas_consumed         | integer                |           | not null | 0                     | plain    |             |              | 
 contract_address     | character varying(42)  |           | not null | ''::character varying | extended |             |              | 
 status               | smallint               |           | not null | 0                     | plain    |             |              | 
 execution_revert_msg | character varying(255) |           | not null | ''::character varying | extended |             |              | 
Indexes:
    "block_receipts_pkey" PRIMARY KEY, btree (id)
    "idx_block_receipts_action_hash" hash (action_hash)
Access method: heap
testnet=# \d+ reader.block_receipts;
                                                    Table "reader.block_receipts"
        Column        |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                   | bigint                 |           |          |         | plain    |             |              | 
 block_height         | bigint                 |           |          |         | plain    |             |              | 
 action_hash          | character varying(64)  |           |          |         | extended |             |              | 
 gas_consumed         | integer                |           |          |         | plain    |             |              | 
 contract_address     | character varying(42)  |           |          |         | extended |             |              | 
 status               | smallint               |           |          |         | plain    |             |              | 
 execution_revert_msg | character varying(255) |           |          |         | extended |             |              | 
Indexes:
    "block_receipts_index" UNIQUE, btree (id)
    "idx_block_receipts_action_hash" hash (action_hash)
Access method: columnar

Then use the following query

select id,block_height,action_hash,gas_consumed,contract_address,status,execution_revert_msg 
from reader.block_receipts 
where action_hash='3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94';

I have noticed that queries on columnar databases are relatively slow, and the query performance is comparable to when using no indexes.

explain results

#On Heap
 Index Scan using idx_block_receipts_action_hash on block_receipts  (cost=0.00..113907.92 rows=125081 width=786)
   Index Cond: ((action_hash)::text = '3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94'::text)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(5 rows)
#On Columnar
 Gather  (cost=1000.00..30599.12 rows=125081 width=786)
   Workers Planned: 7
   ->  Parallel Custom Scan (ColumnarScan) on block_receipts  (cost=0.00..17091.02 rows=3573749 width=786)
         Filter: ((action_hash)::text = '3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94'::text)
         Columnar Projected Columns: id, block_height, action_hash, gas_consumed, contract_address, status, execution_revert_msg
(5 rows)

Time: 0.941 ms

A simpler docker image not based on Spilo, likely built on top of the postgres image, that uses UID/GID 70.

(Feedback from #21)

What's wrong?

Right now if you query for avg(something) on 1 Month of data the CPU spikes to 8 CORE 100% for the few seconds the query runs.
Please add a limiter option on the query so multiple customers/request can be run in parallel.

What's wrong?

FIY: Discord channel is not working and you allow now only bug reports but this is are questions/feature request.

Previous Discord advice was to try to make the ETL batch as closer to 150K.
As I am unable to run with more than 15min delay I am around 30k batch insert.
But because I choose to partition my tables by tenants for query performance the actual 30k is probably split by 10 tenants internally and reaches 10 different strip/chunks with an average of 3K/batch. Thus being again very far from the 150K or at least the 10k/chunk. I now run simple Vacuum after each insert as advised on Discord.

Is there any mechanism or would you consider adding such feature to merge manually or automatically several stripe/chunks beside/togheter with vacuum or other command ?
Does the columnar.vacuum make and actual stripe merge/reduction of number of stripes/chunks ?
Documentation only states: "stripes to consolidate" but being in the Space Reclamation area I am not sure it actually merges them or only defragments the space between them ?

What's wrong?

Documentation https://docs.hydra.so/organize/data-modeling/row-vs-column-tables states:
each stripe as a row of metadata that also holds up to 150,000 rows of data. Within each stripe, data is divided into chunks of 1000 rows but I believe it should be 10.000 rows.
As also described on the blog: https://www.hydra.so/blog-posts/2023-03-02-columnar-updates-and-deletes
Each stripe contains up to 15 chunks (with each chunk contains up to 10,000 rows)

• combine stripes that have at least 20% of deleted rows
  • possibly stripes that are not full are also candidates (so maybe any stripe with less than 120,000 non-deleted rows?)
• track "spaces" in the file that are open to be overwritten (aka deleted stripes)

Example:

CREATE TABLE sampletable (x numeric);
INSERT INTO sampletable SELECT i FROM generate_series(1,1000000) i;

-- since there are some parallel workers reading from heap, and CommandCounterIncrement
-- is not expected to be called during parallel operations
CREATE TABLE sampletable_columnar USING columnar AS SELECT * FROM sampletable ORDER BY 1 ASC;
ERROR: cannot start commands during a parallel operation

In this case the workaround is to temporarily disable parallelism, e.g. set max_parallel_workers = 1.

What's wrong?

=> set columnar.enable_column_cache = 't';
SET
=> explain select count(1) from t1;
SSL SYSCALL error: EOF detected

Workaround is to disable the cache:

=> set columnar.enable_column_cache = 'f';
SET
=> explain select count(1) from t1;
[ normal output ]

With some queries, using EXPLAIN ANALYZE will avoid this issue.

What's wrong?

pg13 is 3 years old, I think we can at least announce we are deprecating support, and plan to remove support soon thereafter.

We do need to migrate a few cloud users over to pg14+ first.

What's wrong?

When I am trying set the access method to columnar I am getting following error.

SQL Error [22P02]: ERROR: invalid input syntax for type boolean: "2"
  Where: PL/pgSQL function columnar.alter_table_set_access_method(text,text) line 57 at IF

Table Definition

CREATE TABLE public.master_country (
	id uuid NOT NULL,
	country_name varchar(100) NOT NULL,
	country_code varchar(5) NOT NULL,
	numeric_code int4 NOT NULL,
	status int4 NOT NULL,
	created_at timestamptz NOT NULL,
	modified_at timestamptz NOT NULL,
	created_by_id uuid NULL,
	modified_by_id uuid NULL,
	latitude varchar(35) NULL,
	longitude varchar(35) NULL,
	CONSTRAINT master_country_country_code_key UNIQUE (country_code),
	CONSTRAINT master_country_country_name_key UNIQUE (country_name),
	CONSTRAINT master_country_pkey PRIMARY KEY (id),
	CONSTRAINT master_country_created_by_id_61f003bf_fk_account_user_id FOREIGN KEY (created_by_id) REFERENCES public.account_user(id) DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT master_country_modified_by_id_23d8e1a8_fk_account_user_id FOREIGN KEY (modified_by_id) REFERENCES public.account_user(id) DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX master_country_country_code_60ea7291_like ON public.master_country USING btree (country_code varchar_pattern_ops);
CREATE INDEX master_country_country_name_f78e10e8_like ON public.master_country USING btree (country_name varchar_pattern_ops);
CREATE INDEX master_country_created_by_id_61f003bf ON public.master_country USING btree (created_by_id);
CREATE INDEX master_country_modified_by_id_23d8e1a8 ON public.master_country USING btree (modified_by_id);

Query I am using to change access method

SELECT columnar.alter_table_set_access_method('master_country', 'columnar');

What's wrong?

FIY: Discord channel is not working and you allow now only bug reports but this is are questions/feature request.

Is the Postgres Autovacuum enabled or off ? Does it have any effect compared to columnar.vacuum ?

Hydra looks super interesting! Could you compare and contrast with ClickHouse? Couldn't find anything on the website or docs about it.

Worth adding to https://github.com/ClickHouse/ClickBench/

If I try to use where on text/datetime or uuid columns it works, but if I do it on INTEGER column I have this error:
ERROR: unsupported byval length: 16 SQL state: XX000

I am able to make it work with a cast to text or varchar though.

I am running latest docker for PG 14.
I would like to create a new hydra db inside the docker beside the default postgres db
to be able to test different schemas and as best practices states to have another db beside postgres.

I can enable columnar on the new db by
CREATE EXTENSION IF NOT EXISTS columnar;
Is this enough for all hydra capability/features ?

Is either template0 or template1 better/recommended ?

We should really be adding ourselves to PGXN. we can create a META.json file following the spec, and add a release.

we need to discuss which aspects to add, and what would be most effective.

What's wrong?

I have 2 query. If I run each one independently it takes ~ 8sec and they finish while using 8 core 100%.
If I start one and than a second one immediately, the first one finishes after 8 seconds but the second one doesn't finish after 16 seconds but after 60 seconds.
In between second 8 (after the first query finished) to second 60 the cpu usage is 1 core 100%.

First query Explain Analyze:
"[ { ""Plan"": { ""Node Type"": ""Aggregate"", ""Strategy"": ""Hashed"", ""Partial Mode"": ""Simple"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 5, ""Actual Loops"": 1, ""Group Key"": [""targetid""], ""HashAgg Batches"": 1, ""Peak Memory Usage"": 409, ""Disk Usage"": 0, ""Plans"": [ { ""Node Type"": ""Gather"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 38088, ""Actual Loops"": 1, ""Workers Planned"": 7, ""Workers Launched"": 7, ""Single Copy"": false, ""Plans"": [ { ""Node Type"": ""Result"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 4761, ""Actual Loops"": 8, ""Workers"": [ ], ""Plans"": [ { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Outer"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": true, ""Async Capable"": false, ""Relation Name"": ""trackpoint"", ""Alias"": ""trackpoint"", ""Actual Rows"": 4761, ""Actual Loops"": 8, ""Filter"": ""((speed > '0'::numeric) AND (datetime <= now()) AND (targetid = ANY ('{68162,68163,68164,68165,68166,68167,68168,68169,68170,68171,68172,68173,68174,68175,68176,68177}'::integer[])) AND (datetime >= (now() - '1 mon'::interval)))"", ""Rows Removed by Filter"": 48110141, ""Columnar Projected Columns"": ""datetime, targetid, speed"", ""Columnar Chunk Group Filters"": ""((datetime >= (now() - '1 mon'::interval)) AND (datetime <= now()))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Workers"": [ ] } ] } ] } ] }, ""Triggers"": [ ] } ]"

Second query:
"QUERY PLAN" "[ { ""Plan"": { ""Node Type"": ""Aggregate"", ""Strategy"": ""Hashed"", ""Partial Mode"": ""Simple"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 3, ""Actual Loops"": 1, ""Group Key"": [""targetid""], ""HashAgg Batches"": 1, ""Peak Memory Usage"": 409, ""Disk Usage"": 0, ""Plans"": [ { ""Node Type"": ""Gather"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 45531, ""Actual Loops"": 1, ""Workers Planned"": 7, ""Workers Launched"": 0, ""Single Copy"": false, ""Plans"": [ { ""Node Type"": ""Result"", ""Parent Relationship"": ""Outer"", ""Parallel Aware"": false, ""Async Capable"": false, ""Actual Rows"": 45531, ""Actual Loops"": 1, ""Workers"": [ ], ""Plans"": [ { ""Node Type"": ""Custom Scan"", ""Parent Relationship"": ""Outer"", ""Custom Plan Provider"": ""ColumnarScan"", ""Parallel Aware"": true, ""Async Capable"": false, ""Relation Name"": ""trackpoint"", ""Alias"": ""trackpoint"", ""Actual Rows"": 45531, ""Actual Loops"": 1, ""Filter"": ""((mileage > '0'::double precision) AND (datetime <= now()) AND (targetid = ANY ('{72171,72172,72173,72174,72175,72176,72177,72178,72179,72180}'::integer[])) AND (datetime >= (now() - '1 mon'::interval)))"", ""Rows Removed by Filter"": 384873682, ""Columnar Projected Columns"": ""datetime, targetid, mileage"", ""Columnar Chunk Group Filters"": ""((datetime >= (now() - '1 mon'::interval)) AND (datetime <= now()))"", ""Columnar Chunk Groups Removed by Filter"": 0, ""Workers"": [ ] } ] } ] } ] }, ""Triggers"": [ ] } ]"

What's wrong?

and maybe deprecate v13

Postgis extension is one of the most popular extension/motive to use postgres.
and "Postgis is already available in Hydra's cloud product." according to @wuputah

What's wrong?

Running parallel sequential scan can lead to OOM problem.

I have run a migration of 143mil rows into 1 table and 50mil rows into a second one.
After advice given on Discord about running Vacuum after each 5min batch insert for read query to work after,
I run the migration like first time with 2 parallel inserts on the 2 tables fallowed by vacuum after each insert for their respective table.
I now encounter random errors from time to time which I did not on the first migration (I am using same docker and same tool to migrate):
Exception data: Severity: ERROR SqlState: XX000 MessageText: insufficient data for reading boolean array File: columnar_reader.c Line: 1728 Routine: DeserializeBoolArray
or
Exception data: Severity: ERROR SqlState: XX000 MessageText: attempted to modify an unexpected stripe, columnar storage with id=10000000021 does not have stripe with id=1117 File: columnar_metadata.c Line: 1692 Routine: UpdateStripeMetadataRow

Hydra docker is now only using 230MB RAM (it has 60GB available).

What's wrong?

Docker compose file copying the local postgres configuration file to

./files/postgres/postgresql.conf:/var/lib/postgresql/data/postgresql.conf

The PG instance is using different config file

postgres=# show config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

postgres=# 

Either symlink the /var/lib/postgresql/data/postgresql.conf to /etc/postgresql/postgresql.conf or fix path in PG build.

What's wrong?

latest is tracking PG14:
https://github.com/hydradatabase/hydra/pkgs/container/hydra

This might break things for people pulling down latest, they will need to run pg_upgrade on their data, so we should look into that and advise on how to address that.

What's wrong?

Hey, looks like a great project! I just wanted to give you an FYI that I used to be part of a project called Hydra and we were forced to change our name because there is a software company in Germany with a trademark on the name Hydra in the software sphere and we were facing a legal challenge. Our project changed its name to Samvera. Just wanted to let you know so you have lots of warning in case you're ever hoping to trademark the name.

Best wishes!

More info: https://insights.uksg.org/articles/10.1629/uksg.383

What's wrong?

Awesome product you are building!

I apologize for asking this question here but I'm having trouble getting access to discord (long story).

I noticed in one of your blog posts vectorized query development started with where clauses and would eventually be implemented for other areas/functions. Has this happened yet?

If it has been implemented, your product might be an easy drop in replacement for our current analytics platform currently based on postgres and situated in the fintech industry.

Thanks for any insight!

What's wrong?

It seems that Hydra's columnar is using the same name as citus columnar

This may introduce a problem: you can not install hydra and citus packages simultaneously.

It's okay to choose one on the database CREATE EXTENSION level, but conflict during installation may be unnecessary.

  file /usr/pgsql-15/share/extension/columnar--10.0-3--10.1-1.sql conflicts between attempted installs of hydra_15-1.0.0-1.el8.x86_64 and citus_15-12.0.0-1PGDG.rhel8.x86_64
  file /usr/pgsql-15/share/extension/columnar--10.1-1--10.2-1.sql conflicts between attempted installs of hydra_15-1.0.0-1.el8.x86_64 and citus_15-12.0.0-1PGDG.rhel8.x86_64
  file /usr/pgsql-15/share/extension/columnar--10.2-2--10.2-3.sql conflicts between attempted installs of hydra_15-1.0.0-1.el8.x86_64 and citus_15-12.0.0-1PGDG.rhel8.x86_64
  file /usr/pgsql-15/share/extension/columnar--10.2-3--10.2-4.sql conflicts between attempted installs of hydra_15-1.0.0-1.el8.x86_64 and citus_15-12.0.0-1PGDG.rhel8.x86_64
  file /usr/pgsql-15/share/extension/columnar--9.5-1--10.0-1.sql conflicts between attempted installs of hydra_15-1.0.0-1.el8.x86_64 and citus_15-12.0.0-1PGDG.rhel8.x86_64
  file /usr/pgsql-15/include/server/citus_version.h conflicts between attempted installs of citus_15-devel-12.0.0-1PGDG.rhel8.x86_64 and hydra_15-1.0.0-1.el8.x86_64

Hi,

Timescaledb has the built-in auto partitioning on 1 or 2 dimension (time and tenant/deviceid/etc).

Your documentation states Partitioning to be used for time partitioning, but still it can be done only manually.
It would be great if you implement something for auto-creating the partitions based on configuration of intervals and/or secondary criteria. This can help a lot on the usecase of GDPR cleaning where dropping older data would be a lot faster/easier.