How to specify fillfactor for a table in sqlalchemy?

Question

Let's say I have a simple table. In raw SQL it looks like this:

CREATE TABLE events (id INT PRIMARY KEY) WITH (fillfactor=60);

My question is how to specify fillfactor for a table using sqlalchemy declarative base?
Of course I can achieve that by using raw SQL in sqlalchemy like execute("ALTER TABLE events SET (fillfactor=60)"), but I'm interested whether there is a way to do that using native sqlalchemy tools.

I've tried the following approach, but that didnt't work:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SimpleExampleTable(Base):
    __tablename__ = 'events'
    __table_args__ = {'comment': 'events table', "fillfactor": 60}

    id = Column(Integer, primary_key=True)

TypeError: Additional arguments should be named <dialectname>_<argument>, got 'fillfactor'

Looking through documentation I've managed to find information only about fillfactor usage in indexes.

My environment:

python 3.9
sqlalchemy 1.3.22
PostgreSQL 11.6

have you tired `postgresql_fillfactor` – rho Oct 18 '21 at 03:41 — rho, Oct 18 '21 at 03:41

score 1 · Answer 1 · answered Aug 03 '22 at 15:23

Tbh, have the same question, and the only thing I found which is related to the fillfactor in the SQLAlchemy docs is the one with index (link to docs):

PostgreSQL allows storage parameters to be set on indexes. The storage parameters available depend on the index method used by the index. Storage parameters can be specified on Index using the postgresql_with keyword argument:

Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50})

But it seems, there is no setting option where you can set the fillfactor directly for the table.

But there is still an option to run the raw SQL query (as the alembic migration, let's say):

ALTER TABLE mytable SET (fillfactor = 70);

Note that setting fillfactor on an existing table will not rearrange the data, it will only apply to future inserts. But you can use VACUUM to rewrite the table, which will respect the new fillfactor setting.

The previous quote is taken from here

score 1 · Answer 2 · answered Aug 24 '22 at 08:38

Extending the answer from Max Kapustin, you can use an event listener to automatically execute the ALTER TABLE statement when the table is created.

import sqlalchemy as sa

engine = sa.create_engine('postgresql:///test', echo=True, future=True)

tablename = 't65741211'
tbl = sa.Table(
        tablename,
        sa.MetaData(),
        sa.Column('id', sa.Integer, primary_key=True),
        listeners=[
            (
                'after_create',
                sa.schema.DDL(
                    f"""ALTER TABLE "{tablename}" SET (fillfactor = 70)"""
                ),
            )
        ],
)

tbl.drop(engine, checkfirst=True)
tbl.create(engine)

How to specify fillfactor for a table in sqlalchemy?

2 Answers2